Cascading Combo Mystery! ! !

[Will_J]

Hi All,

Thank you very much for all help and input in advanced, it's hugely appreciated!

I've been puzzling over this for FAR too long now, probably in the region of 6 hours; I'm a novice ... can you tell! : P Either way we all have to learn someway so here is my puzzle.

I am trying to write some code that gives another combo a record source based on the data selected in the first, there is some other criteria that has to be meet as well.

The code I have written is as follows;

Private Sub cmbTestRegion_AfterUpdate()
Dim strSource As String

strSource = "SELECT tblLocation.LocationName" & _
            "FROM tblLocation " & _
            "WHERE tblLocation.RegionID = '" & cmb.TestRegion & " '" & _
            "AND tblLocation.InventoryAtLocation = 1" & _
            "ORDER BY tblLocation.LocationName;"

Me.cmbTestLocation.RowSource = strSource
Me.cmbTestLocation.RowSource = vbNullString

End Sub

The English explanation is as follows;

The user will select a region from cmbTestRegion. When I select this region I want cmb.TestLocation to only show the regions based on the selection made in cmb.TestRegion and also and equal to 1 from the InventoryAtLocation Field in the Location Table (1 = Yes(I have a Yes / No combo in that field)).

Please help me! I'm going nuts and I know it's starring me in the face, in-fact it's jumping up and down going 'Your are stupid arn't you!!!' Haha.

Look forward to hearing from you all.

Best,
William

[tfurnivall]

Hi William,

You've built the Query string for the RowSource of your combobox - have you also set the RowSourceType to "Table/Query"?

Tony

[mrojas]

I would create a query, in design mode, that has as its criteria the cmbTestRegion bound column. This query would have the TestRegion and Location tables.
The syntax,for the criteria under the bound column field, would be something like this:
=Forms!YourFormName.RegionID
In the form where your cmbTestLocation control is, under its RowSource property, enter the name of the query you've just created.
If this is as clear as mud, shoot me a copy of your database and I'll see if I can make it work.

[cupidsrose]

Hi, everybody, I am a new friend.