Create a lookup statement based on selected criteria

[Mikelinva]

I am creating a database that creates travel requests. In my main table, I have two tables
[TAR] that contains
[Going to] (looks up using [Per Diem Rates])
[Lodging] Yes/No
[Lodging Rate]
[Per Diem Rates] contains
[State]
[Destination]
[Lodging Rate]

When a person selects yes on the [Lodging] button and selects a a destination by using [Destination] on the [Per Diem Rates] table, I want the Lodging rate to populate with the rate that is listed with the selected destination.

Any help would be appreciated

[twinnyfo]

You would have two combo boxes on your form, one for the state, one for the destination (which begins disabled). When you select a state, requery the row source of the destination combo box based on the state results (include destination and per diem rate in the row source) and enable it. When the user selects a destination, update the TAR.[Per Diem Rate] with the value of the destination combo box.

[zmbd]

Please take a look at our insights article covering cascading comboboxes. While it doesn't directly apply to your question, it will provide some more depth to TwinnyFo's answer:
[*Main list*]Microsoft Access / VBA Insights Sitemap

[*The article*]Cascaded Form Filtering
As mentioned, it's how to setup the comboboxes that applies directly to your quesition....

Don't worry if you don't follow all of the code... we'll help with the rough spots. (^_^)

My other concern here is the normalization of your database... >> Database Normalization and Table Structures.