Search Form - Cascading Combo Boxes

[WeeTerrier]

Hi,

I am new to Access, VBA and SQL so I'm afraid so the answer to my question may be very obvious...

I'm in the process of creating a search form based on a single table. My search form has 2 combo boxes. The first contains the field names from the table (cboSearchField). I was hoping that depending on which field is selected in the first combo box the second box (cboSearchText) would update itself so that users can start typing the text they want to search for (e.g. a product name) but then select it from a list (to minimise spelling errors etc). However I'm struggling with getting the second box to update.

I have tried the following query in the row source for the second combo box:

SELECT tblProducts.ProductID, tblProducts.ProductName
FROM tblProducts
WHERE (((tblProducts.ProductID)=[Forms]![SearchBox2]![cboSearchField]));



I have also added the following code into the "After Update" section on the Event tab for the first combo box:

Private Sub cboSearchField_AfterUpdate()
Me.cboSearchText.Requery
End Sub


But the second combo box remains blank. Can anyone help me? Am I on the right track?

Any advice appreciated.

[Toyman]

How many columns do you have in cboSearchField? If it has more than one columns, make sure the "bound column" setting for the combo box is set correctly pointing to the correct column that contains the value for the query

[WeeTerrier]

Both the "Column Count" and "Bound Column" are set to 1.

[Toyman]

Both the "Column Count" and "Bound Column" are set to 1.

Try this in the after update event of the first combo box to see if it works:

Dim sString as String
Dim sSql as String

sString = Me.cboSearchField
sSql = "SELECT tblProducts.ProductID, tblProducts.ProductName "
sSql = sSql & "FROM tblProducts "
sSql = sSql & "WHERE tblProducts.ProductID = " & sString

Me.cboSearchText.RowSource = sSql
Me.cboSearchText.Requery

[WeeTerrier]

I tried the text you suggested but couldn't seem to get it to work so I have been playing around with it to see what happens. I currently have the following:

Private Sub cboSearchField_AfterUpdate()
Dim sString As String
Dim sSql As String

sString = Me.cboSearchField
sSql = "SELECT tblProducts.ProductID, tblProducts.ProductName "
sSql = sSql & "FROM tblProducts "
sSql = sSql & "WHERE tblProducts.ProductID OR ProductName = " & sString

Me.cboSearchText.RowSource = sSql
Me.cboSearchText.Requery

End Sub


When the user chooses a field in combo box one (these are field names from the table) I want combo box two to update and provide them with the data which comes under that field name in the table. But at the moment the above code provides me with the product IDs in the second combo box, but this happens for almost every drop down option selected in the first combo box. Do you have any ideas where I'm going wrong?

Thanks again.

[Toyman]

I tried the text you suggested but couldn't seem to get it to work so I have been playing around with it to see what happens. I currently have the following:

Private Sub cboSearchField_AfterUpdate()
Dim sString As String
Dim sSql As String

sString = Me.cboSearchField
sSql = "SELECT tblProducts.ProductID, tblProducts.ProductName "
sSql = sSql & "FROM tblProducts "
sSql = sSql & "WHERE tblProducts.ProductID OR ProductName = " & sString

Me.cboSearchText.RowSource = sSql
Me.cboSearchText.Requery

End Sub


When the user chooses a field in combo box one (these are field names from the table) I want combo box two to update and provide them with the data which comes under that field name in the table. But at the moment the above code provides me with the product IDs in the second combo box, but this happens for almost every drop down option selected in the first combo box. Do you have any ideas where I'm going wrong?

Thanks again.

First, what value does the first combo box return; the ProductID or the ProductName? Once you determin that, your sql statement should only reflect either the productID or the ProductName and not both. If ProductID and it is a number field, then your "Where Statement" for the Sql will look like this:
WHERE tblProducts.ProductID= " & sString
If ProductID is a text field, then the where statement will look like this:
WHERE tblProducts.ProductID= '" & sString & "'"

If the first combo box returns the ProductName and it is a text field, then your where statement for the sql will look like this:
WHERE tblProducts.ProductName = '" & sString & "'"
If for some reason, the ProjectName is a number field, then it will look like this:
WHERE tblProducts.ProductName = " & sString

Set the column count to 2. Set the column width to 0", 1". What was happening was mostly likely you have your column count set to one. In this case, it would display the first column of the sql statement; the productID field. Yon need to set you bound column to the column in which you want to bound your data

[WeeTerrier]

From what you're asking I'm not sure if my form can work the way I was looking for...

The first combo box is a drop down list of the fields in the table, so for example the user could potentially choose Product ID or Product Name. So the sql statement would only consider one and not the other. Would the other fields require separate statements?

Apologies if I'm being completely stupid, I'm afraid I'm struggling with this VBA stuff!

I've come up with another potential layout for a search form if this one doesn't work. I'm still having issues with the query for that one, but hey you don't learn if you don't try...

[Toyman]

From what you're asking I'm not sure if my form can work the way I was looking for...

The first combo box is a drop down list of the fields in the table, so for example the user could potentially choose Product ID or Product Name. So the sql statement would only consider one and not the other. Would the other fields require separate statements?

Apologies if I'm being completely stupid, I'm afraid I'm struggling with this VBA stuff!

I've come up with another potential layout for a search form if this one doesn't work. I'm still having issues with the query for that one, but hey you don't learn if you don't try...

The combo box can only be bound to one field eventhough it may display mulitiple. Look at the property setting for the combo box. See "Bound Column". This is the column in the list in which the combo box is bounded to. What ever is chosen in the combo box, the value from this column is the selected value.