Question about dependent comboboxes

[ga11ardo]

I have two question about dependent combo boxes.

First is it possible to make a combobox that functions as follows: Two comboboxes (CompanyName and CustomerID) on load both comboboxes come up with all possible options. If you start Typing "Amer" in the CompanyName combobox, it will limit the CompanyName combobox to all the Companies starting with the letters "Amer" Also, it will limit the CustomerID comobox to all the Customer ID's that match any Company name that starts with "Amer" Then If you delete "Amer" in the CompanyName combobox it will once again give you all the company name and customer Id options. I also want it to work the same way with the CustomerID combobox so that if you type the beginning of a customer ID it will limit the Company names to onces that match what you have typed so far.

My second question is: Is there anyway to reset the default value in a combobox to the first option after the combobox has been requeried. I know how to set the default value to the first option, but when the combobox is requeried the default value doesn't change to the new first option.

Thanks for your help, If any of these are possible please explain in detail how it is done, or uploading an example would be a huge help.

 

[ajetrumpet]

First is it possible to make a combobox that functions as follows: If you start Typing "Amer" in the CompanyName combobox, it will limit the CompanyName combobox to all the Companies starting with the letters "Amer"

I don't know about yours, but my combo boxes do that automatically, without instruction from me.

Also, it will limit the CustomerID comobox to all the Customer ID's that match any Company name that starts with "Amer"

Sure, you could have it do this. Actually, you can update the 2nd combo box for ever single letter that you type in the 1st one. But...WHY?? Faster automation?? I don't know....seems like a lot of effort to gain little ground.

Then If you delete "Amer" in the CompanyName combobox it will once again give you all the company name and customer Id options.

Doing this is the same thing as typing in a value, keystroke by keystroke in the 1st combo.

My second question is: Is there anyway to reset the default value in a combobox to the first option after the combobox has been requeried. I know how to set the default value to the first option, but when the combobox is requeried the default value doesn't change to the new first option.

Then loop the processes that you already know of back around, and do it again after the REQUERY.

I seriously wonder what this would do the program, especially if I was typing in the combo box (I type fairly quick)....

Private Sub Combo1_KeyPress

  Me.Combo2.Rowsource = "SELECT table.ComboField2 FROM table WHERE " & _ 
"table.Combo1Field LIKE '"*" & [Combo1] & "*"'"

I'm almost certain I would crash the program....

 

[ga11ardo]

I have taken your advice; however, I am still unable to get the comboboxes to limit off each other. I put the following code in the keypress event of the first combobox:

Me.cboCustomerID.RowSource = "SELECT Customers.CustomerID FROM Customers WHERE " & _
"Customers.CompanyName LIKE ' " * " [cboCompanyName] & " * " '"

but i get a type mismatch error

I have attached the database and if you could find the time to look at it and see if you can figure out how to get it to work I would really appreciate it. The two comboboxes that i want to function like this are cboCompanyName and cboCustomerID in the Orders Form. (Also, in the continuous subform, when i update the productID, it sets the Unit measure on the second record to the first value of on the first record. I want it to set the first records default value to its first option, but I can't figure out how to fix this)

 

[ajetrumpet]

The "KeyPress" option was not to be taken seriously. I doubt something this could ever work, but theoretically it makes sense...

I will have a look either later on tonight or tomorrow and let you know what I think.

 

[ajetrumpet]

Me.cboCustomerID.RowSource = "SELECT Customers.CustomerID FROM Customers WHERE " & _
   "Customers.CompanyName LIKE ' " * "  [cboCompanyName] &  " * " '"

but i get a type mismatch error

Did you know you had the RowSource Type of the 2nd Combo box set to "Value List"???

That's a pretty big conflict when you try to mix it with the RowSource SQL you are requesting in the module. As far as things doing what you want, I think it pretty much does now. Also, you were originally pulling records from a query for your 1st combo's Rowsource. That query was an exact replica of two fields from a table. That is not necessary, but it'll get you confused real quick!! Just use the table for the RowSource (I changed this). The requery action of the second combo still uses the the KeyPress Event, although I still don't know why you are interested in this.

As far as the values popping up in the combo box as you type, I have no idea what you want from this question. By default, Access uses a sort of "intellisense" function just like Excel when typing a value into a control that has a dropdown menu. Thus, as soon as you start typing characters, it automatically starts populating and updating the value in the box to reflect the closest match to your fully typed string, so that's really not even relevant here (unless I'm missing something).

Also, careful with the SPACES in the SQL RowSource Statement. I used the LIKE operator with the single quote for the criteria...

Combo2 = "SELECT field FROM table WHERE field LIKE [Combo1] & '*'"

The multiple character wildcard (*) is only on the right side because, as you type each character, it will give you an updated list of Combo2's values that are in the same record as Combo1's values that = "Combo1String*".

Hope this makes sense to you, because it sure doesn't to me!! It certainly works though...

 

[unclejoe]

Hi,

I am not sure that this will work for you. I suggest you use the on change event of the combo box like………..

Private Sub Combo1_Change()
‘Requery the form
Me.Requery

‘change the rowsource to filter current combo box
Me.Combo1.RowSource = "select * from tblErmax where mark like '" & Me.Combo1.Value & "*'"

‘drop down the list after filtering
Me.combo1.Dropdown
End Sub

To reset the combo box, just delete the value.

PS. Code base on unbound control.

I have two question about dependent combo boxes.
First is it possible to make a combobox that functions as follows: Two comboboxes (CompanyName and CustomerID) on load both comboboxes come up with all possible options. If you start Typing "Amer" in the CompanyName combobox, it will limit the CompanyName combobox to all the Companies starting with the letters "Amer" Also, it will limit the CustomerID comobox to all the Customer ID's that match any Company name that starts with "Amer" Then If you delete "Amer" in the CompanyName combobox it will once again give you all the company name and customer Id options. I also want it to work the same way with the CustomerID combobox so that if you type the beginning of a customer ID it will limit the Company names to onces that match what you have typed so far.
My second question is: Is there anyway to reset the default value in a combobox to the first option after the combobox has been requeried. I know how to set the default value to the first option, but when the combobox is requeried the default value doesn't change to the new first option.
Thanks for your help, If any of these are possible please explain in detail how it is done, or uploading an example would be a huge help.

 

[ga11ardo]

Thank you for all your help guys. I have got the updating comboboxes working as I wanted them to. I am still having two minor problems with my form that I was hoping you guys could help me fix so I could get everything working. Sorry for the inconvenience and I thank you for your patients with me.
1) For some reason in the Orders Form I am unable to get the billing address to auto-fill off the customerID combobox (cboCustomerID) I had it working before, but when I changed the company name and customer ID comboboxes it stopped auto-filling.
2) in the OrderDetails subform in Orders, when I enter a productID, it put the first value of the first unitmeasure combobox into the unitmeasure combobox on the second row. I want it to defaultly fill out the unit measure when a productID is selected. If this is unclear just try entering a couple productID's and I'm sure you understand what I am saying

I have uploaded an updated version of my database. Thank again.

 

[unclejoe]

What a mess! These aren’t minor problems.

I have modified the table and created new forms. Take a look at the attached.

You have used the lookup table “customers” to join the “Orders” table, that’s incorrect method. If you want to do that, create a new table “OrdersCustomer” with the “OrderID” matching the “Orders” table.

I have also recreated a new query “qryOrders”, your “Orders Qry” which is causing problem on the main form.

In the mean time, I have added new columns into the “Orders” table so that you auto fill the textboxes (Bill To). Take a look at the combo’s event.

In the new forms, I have also modified the “Dlookup”. However, you still have a problem if a customer has more the one “Ship To” addresses. I will leave that to you to decide on what to do.

I have disabled unesscessary events and some codes as they causing problems.

Thank you for all your help guys. I have got the updating comboboxes working as I wanted them to. I am still having two minor problems with my form that I was hoping you guys could help me fix so I could get everything working. Sorry for the inconvenience and I thank you for your patients with me.
1) For some reason in the Orders Form I am unable to get the billing address to auto-fill off the customerID combobox (cboCustomerID) I had it working before, but when I changed the company name and customer ID comboboxes it stopped auto-filling.
2) in the OrderDetails subform in Orders, when I enter a productID, it put the first value of the first unitmeasure combobox into the unitmeasure combobox on the second row. I want it to defaultly fill out the unit measure when a productID is selected. If this is unclear just try entering a couple productID's and I'm sure you understand what I am saying

I have uploaded an updated version of my database. Thank again.