Cascading combo box - Need VBA help for auto-update of dependent field

[kagoodwin13]

On a form in one of my databases, I have two combo-boxes, and one feeds the other via cascading information. I picked up the tip here: http://fontstuff.com/access/acctut10.htm



The two combo boxes are Supplier Name and Supplier Number.

I need Supplier Name to update Supplier Number automatically. If you choose one Supplier Name, then the Supplier Number cascaded into the combo-box, and then change Supplier Name, Supplier Number does not auto-update, meaning it is now mismatched. This is a potential risk for users entering wrong data into the tables. I am wondering if there is a quick Form Properties or VBA fix for this issue. Any ideas?

[pbaldy]

That sounds more like:

http://www.baldyweb.com/Autofill.htm

[Missinglinq]

I agree with Paul; Cascading Comboboxes are intended to be used when a given selection, in the first Combobox, will generate multiple, possible selections, in the second Combobox.

If the original selection is only going to generate a single, possible item, using a single Combobox, in the manner that his code demonstrates, is the way to go.

FYI, the problem with the code you were given at the above site

Private Sub cboCountry_AfterUpdate()
   On Error Resume Next
   cboCity.RowSource = "Select tblAll.City " & _
            "FROM tblAll " & _
            "WHERE tblAll.Country = '" & cboCountry.Value & "' " & _
            "ORDER BY tblAll.City;"
End Sub

is that it doesn’t reset the second Combobox so that a previous selection, if any, doesn't remain showing. Adding a single line

Private Sub cboCountry_AfterUpdate()
   On Error Resume Next
   cboCity.RowSource = "Select tblAll.City " & _
            "FROM tblAll " & _
            "WHERE tblAll.Country = '" & cboCountry.Value & "' " & _
            "ORDER BY tblAll.City;"
   cboCities = ""
End Sub

would have taken care of that, and is usually standard. BTW, that site usually gives excellent advice/examples, and I'm sure that this was simply an oversight on their part!

Linq ;0)>

[kagoodwin13]

Thanks for both of your replies. I'm still not able to figure out how to make what I need. Attached is my best attempt. Any ideas?

Autofill.mdb

[Missinglinq]

You've got your two Comboboxes mixed up! The one labeled, on your Form, as Supplier Name, is actually the Supplier Number Combobox, and vice versa!

Your actual Supplier Number Combobox has a RowSource of

SELECT tblSUPPLIER.Supplier_Name FROM tblSUPPLIER;

Don't know that it makes any difference, but the line

cmboSupplierNumber = " "

should be

cmboSupplierNumber = ""

setting the cmboSupplierNumber to a Zero-Length String.

All that said, as both Paul and I have indicated, you're going about this wrong! Even if you get this working, you'll have a second Combobox with a single item to be selected, which will have to be selected; it will not automatically show up in the Combobox, which makes no sense!

Follow the link to Paul's site and set up a single Combobox, with both the Supplier Name and Supplier Number in the RowSource, then assign the Supplier Number to a Textbox on your Form, as his demo shows.

Linq ;0)>

[kagoodwin13]

Thanks for your help! I finally got this solved. Attached is the solution for reference.

Autofill.mdb

[Missinglinq]

Glad you got it working, and glad to see that you also replaced you second Combobox with a simple Textbox, which is all that is needed, here!

Good luck with your project!

Linq ;0)>