URGENT! Data Lookup

[StacyStacy]

HELP!
How do I create a combo box that looks up a location based on another combo box in the same form that shows the Provider name?

Example:

The first combo box allows you to select the Provider ID. In this instance, the Provider ID = 506. The Provider ID, "506" represents Hinds Community College.

Now, Hinds Community College has 6 locations (1 in a different city). the city of Raymond = 0. The city of Jackson = 1. The city of Utica = 3, etc.

In the second combo box, I want to select the location of the Provider ID --> 506. (I want to see a list showing the Location ID associated with the Provider Id 506, which is: "0", "1", "2" and "3" etc.

How do I do this? Please help. I need to deliver this application 1st thing in the morning.

 

[norman]

If you use search for "Cascading Combo Boxes" there are a number of replies which have attached samples on this.

HTH

Norman

 

[StacyStacy]

I still need some assistance. Would someone be willing to give me a call or if it is long distance, I am willing to call you. Just let me know and send a private message .
Thanks

 

[jaydwest]

There are several ways to do this depending on your skills and preferences.

1) Query By Form. Create a Query in which the provider is based on the value of the first combo box using the Control on the form. Using the Query Design Window, enter the following for the criteria.

= Forms![Combo1]

Make this Query by Form the RowSource for Combo2

2) Parameter Query. If you are familiar with parameter queries, you can create a parameter query and update the parameter on the AfterUpdate event of the first Combo1. Make the parameter query the Row Source for Combo2

Combo1_AfterUpdate

Dim dbs as DAO.Database
Dim qry as DAO.Querydef

set qry = dbs.Querydefs("Name of Parameter Query")
qry.Parameters(ProviderID) = Me![Combo1]
Me![Combo2].Requery

3) Code SQL on Combo1_AfterUpdate. You can also write the SQL directly for the Row Source in the Combo1_AfterUpdate event.

Dim mySQL as string

mySQL = "SELECT ... WHERE ??? = " & Me![Combo1] & "); "
Me![Combo2].RowSource = mySQL
Me![Combo2].Requery

Judging from your problem, I suggest trying Option1.

Good Luck. If you have any questions, email me.

 

[StacyStacy]

Thanks. I sent you an email from an "aol" email address.

I did create a query and in the combo box preferences, indicated something similar to what you said. But, it's not working.

In the samples db, that is exactly what I want. But I need a little more assistance ..