Data Lookup via Drop Down Fields

[Tomfernandez1]

Hello,

I have a Table that has the following fields:

RTM
4day
5day
7day



RTM = round trip miles: 20;25;30;35.....
4day shows rates based on the RTM for a 4day work week
5day shows rates based on the RTM for a 5day work week
and so on...

I would like to create two drop down fields and a text box on a Form. The first drop down shows RTM (20-200 in five-mile increments). The second drop down shows work week options. Based on the options selected in the two drop down fields, I would like to text box to show the resulting fare, say for example the options chosen were 20 RTM and 5day.

How can I do this? I am somewhat familiar with VBA.

Thanks in advance for your assistance!

Tommy

[NTC]

right - so you need to make these 2 tables to be look up tables for your dropdowns (combo boxes).... they would be single column tables - no key - and just enter your data. We programmers have lots of these on file; weekdays, months, States, etc....they are used over and over. I refer to them as "list tables" as their function is just to feed comboboxes.

you trigger your math in the AfterUpdate event of these. put the exact same math in both since you won't know which combobox might be enter or changed first....

Me.TextBoxName = Me.Combo1Name * Me.Combo2Name

obviously use the actual names not my generic ones....

give your comboboxes a default value so it doesn't barf on an empty value when the math first gets triggered.

hope it helps.

[Joe_A]

Sorry

Thank you

[Tomfernandez1]

Thanks for your interest and advice. I see how the math works, but what I was hoping for was sort of a VLOOKUP or DLOOKUP that would produce a result based on data array and criteria. For example, my Table datasheet view is this:

RTM 4day 5day 7day
20 $100 $200 $300
25 $200 $400 $600
30 $300 $600 $900
35 $400 $800 $1200
.... ...... ....... ........

What I want on a Form are two combo box fields. One named "RTM" the other "Workweek". RTM would show RTM increments; Workweek would be field names of the remaining fields in the Table (4day; 5day; 7day). Finally, the Form will also have a text box that when 20 is selected in RTM combo and 5day in Workweek combo, a function (VLOOKUP or DLOOKUP) would find the intersecting value $200 and display it.

I have done this in Excel, but I'm hoping I can also perform this in Access. Can it be done??

Again, thanks for your interest and advice!

Tommy

[NTC]

the way to do it is to make your 2nd combobox's table list look like this

20 $100 4day
20 $200 5day
20 $300 7day
25 $200 4day
25 $400 5day
25 $600 7day
etc

then set up this second comboboxes source to be a query of the table list above, but with a query criteria defined by the first combobox's value.....so if the user has selected 20 then the combobox choice is:

$100 4day
$200 5day
$300 7day

where the money value is the bound value of combobox 2 - - then from there you can apply your AfterUpdate math....

Hope it helps.

[Tomfernandez1]

NTC - would you mind providing me with a database that performs this? I will be able to understand it better with an actual Form, which I can then modify to suit my needs.

Thanks for your help!

Tommy