Linking drop down boxes

[SeanPT]

I have a simple data entry form that has a drop down box that queries the
store numbers from a table. However, no one ever remembers the store numbers,
just the store names (east, west, etc.)

I'm trying to figure out how to do a text box that auto populates based on
what is in the drop down box. For example, if they choose "West" in the drop
down box, it auto populates the store # 1.

(The store information table has the Store #s as the PK along with the store
names so I have a table I can query)

This seems like something that should be easy but I'm banging my head on the
wall here. Thank you!

 

[SeanPT]

So aparently this is known as cascading lists and is apparently a very
popular question.

My table is dbo_ViewStoreInfo
My Store Number combo box is StoreNumber
My Store Name combo box is StoreName

So using the code examples I could find I came up with this, but it isn't
working...

Private Sub StoreName_AfterUpdate()
On Error Resume Next
cboStoreNumber.RowSource = "SELECT dbo_ViewStoreInfo.StoreNum " & _
"FROM dbo_ViewStoreInfo " & _
"WHERE dbo_ViewStoreInfo = '" & cboStoreName.Value & "' " & _
"ORDER BY dbo_ViewStoreInfo.StoreNum;"
End Sub

But that doesn't populate anything in the cboStoreNumber

 

[SeanPT]

The example code didn't clarify that the "cbo" part isn't some specific call
to a combo box. Removing the cbo took care of it!

 

[Jeff Boyce]

Sean

"cbo" is a prefix commonly-used to indicate that the object is a combobox.
Using it and prefixes for other objects is part of a consistent naming
convention ... and having a consistent naming convention means that the next
person who has to understand/maintain your application (or you, if you walk
away for 6 months!) will have a better idea of what the pieces are and how
they work.

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.