Last combo box requires to enter Parameter

[Tompham89]

Hi there,


How are you all today ?
I really need help in my project here, I am trying to build a my database structure such as the option from the second combo box will be based on the selection from the first one and so on, the data on the third combo box will be based on the selection of the second one, but unfortunately when I got to the last combo box, it requires me to enter parameter value which I don't know what is going on. I really appreciate all of your help guys. I wish you all have a wonderful day.

Here is the vba coding for the first, second, and the third combo box. I can not upload the file because it is over 500kb

Option Compare Database
Option Explicit




Private Sub cboCustomers_AfterUpdate()


Me.cboLocations.RowSource = "SELECT LocationsID, Locations FROM" & _
" Locations WHERE CustomersID = " & Me.cboCustomers & _
" ORDER BY Locations"

Me.cboLocations = Me.cboLocations.ItemData(0)


End Sub


Private Sub cboLocations_AfterUpdate()
'MsgBox Me.cboLocations


Me.cboProductTypes.RowSource = "SELECT ProductName FROM" & _
" ProductTypes WHERE LocationsID = " & Me.cboLocations & _
" ORDER BY ProductName"

Me.cboProductTypes = Me.cboProductTypes.ItemData(0)


End Sub


Private Sub cboProductTypes_AfterUpdate()
'MsgBox Me.cboProductTypes


Me.cboProducts.RowSource = "SELECT Products FROM" & _
" Products WHERE ProductsID = " & Me.cboProductTypes & _
" ORDER BY Products"

Me.cboProducts = Me.cboProducts.ItemData(0)
End Sub

[ranman256]

Its possible the SQL cant recoginize ME. In a query you must always put the full path to the combo box in the criteria:

forms!frmSearch!cboLocations

[pbaldy]

The parameter prompt is Access telling you it can't find something. Double check the spelling of your table and field names. The parameter prompt should indicate what it can't find. If you compact and then zip your file, you should be able to attach it here.

[pbaldy]

Its possible the SQL cant recoginize ME. In a query you must always put the full path to the combo box in the criteria:

forms!frmSearch!cboLocations

SQL certainly won't recognize Me, but it appears to be used correctly, to concatenate the value into the SQL string. Unless my old eyes are deceiving me.

[ItsMe]

It looks OK to me. I might be missing a small syntax error though.

Did you check the spelling with you fields as they are defined in your SQL string? I believe this is the SQL that is causing the issue??? You have a table and a field named "Products"?
"SELECT Products FROM" & _
" Products WHERE ProductsID = "

Also, what is this supposed to do?
Me.cboProducts = Me.cboProducts.ItemData(0)

Maybe me.cboProducts.requery is what you are looking for...

[pbaldy]

Also, what is this supposed to do?
Me.cboProducts = Me.cboProducts.ItemData(0)

That will (should) select the first item in the rowsource.

[ItsMe]

That will (should) select the first item in the rowsource.

I wonder if a requery is still required, since the RowSource is being changed.

[pbaldy]

I wonder if a requery is still required, since the RowSource is being changed.

Not in my experience. Setting the row source should requery the combo.

[Tompham89]

Yes, I have already checked for typing errors , and make sure everything is correct, but it is still not working. I will post the database here. Anyway, thank you all of you guys so so much for answering and helping me out. I really appreciate all of you.

[Tompham89]

For example, when i click on Customers--> Locations--> ProductType--> Products-- then it asked for parameter value . Quote.zip

[ItsMe]

The properties in the combos were goofed. Also the Customer combo was goofed. For the Customer combo, click the elipses (...) within the property shhet that is next to the Row Source field. Use the Query Builder to bring the ID field and the name field into your combo. If you bring the ID field first, this will be in the first column of your combo and the Bound column property will be "1".

Column Count 2
Column widths 0,3
Bound Column 1

Then, your other combos need their SQL cleared from the property sheet. Adjust the Column Count, widths and bound column accordingly.

Add some code to the page load event to clear your combos.

Me.cboLocations.RowSource = ""
Me.cboProducts.RowSource = ""
Me.cboProductTypes.RowSource = ""
Me.cboLocations.Value = ""
Me.cboProducts.Value = ""
Me.cboProductTypes.Value = ""

The code for your Customer and Locations combos AfterUpdate should look like this.

Private Sub cboCustomers_AfterUpdate()

Me.cboLocations.RowSource = "SELECT LocationsID, Locations FROM" & _
                            " Locations WHERE CustomersID = " & Me.cboCustomers & _
                            " ORDER BY Locations"
                            
Me.cboLocations = Me.cboLocations.ItemData(0)

End Sub



Private Sub cboLocations_AfterUpdate()

MsgBox Me.cboLocations
Me.cboProductTypes.RowSource = "SELECT ProductsID, ProductName FROM" & _
                            " ProductTypes WHERE LocationsID = " & Me.cboLocations & _
                            " ORDER BY ProductName"
                            
Me.cboProductTypes = Me.cboProductTypes.ItemData(0)

End Sub