multiple combo-box based lookup field

[pmangan]

Hi all,
I'm looking for some help with a project in my healthcare insurance database. On a form the user selects data from three different combo boxes: State, Insurance Company, and Insurance Policy. Each specific combination of those three fields has a specific amount associated with it, for example: Illinois, Aetna, HMO would need to return a value of $1,000. As of now, the user just has an excel spreadsheet with the amounts in it and they enter it in manually onto the form. Each combination of state, company, and policy has its own value associated with it.


Is there a way to build a calculated field in the form that will say something to the effect of "if the user selects this state, this company, and this policy in each of the respective combo boxes, it will return this value"?
I'm still an access novice but in thinking about this issue I'm imagining a table with 4 fields: State, Company, Policy, and Amount and a calculated field on the form which will look up and return the amount associated with each selected combination of state/company/policy. The trouble I'm having is figuring a way to relate the combo-boxes to the table and looking up the amount based on the combo-box criteria.
Can anyone point me in the right direction? Thanks in advance!

[June7]

Perhaps cascading (dependent) comboboxes will be useful. Select state in combo1 then combo2 is limited to only companies in that state, select company in combo2 and combo3 is limited to only policies available with that company. Combo3 also has the amount as a column of combo3. A textbox can reference the amount column: =Combo3.Column(1)

Cascading comboboxes will require code. This is a very common topic.

[pmangan]

If I set up cascading combo boxes will the user be able to add records at each level on the form like they can in the normal combo box that's there now?
Will I be able to update the table to move the existing values into the new fields?

[ranman256]

the table would have all fields: State, Company, Policy,Amt
cboState would update the Company combo

sub cboState_Afterupdate()
  cboCo.requery
end sub

the company combo box sql looks at the State cbo
select distinct [company] from tTable where [state]=forms!myForm!cboState

then do it again for Company:

sub cboCo_Afterupdate()
  cboAgency.requery
end sub

[June7]

What do you mean by 'add records at each level'? This table is a 'lookup' table to facilitate data entry into another table such as CustomerPolicy. Only the ID of Policies table would be saved into CustomerPolicy. Saving state, company, policy, amount into CustomerPolicy is duplication of data and not in accordance with relational database principles. However, since amount can change over time, it is justified to save the amount but that will require a little bit of code.