One Table, One Form, Need help to make it functional....

[Mary P.]

I have a table that contains the following fields:
EffDate, Payor, ProcCode, Mod1, Mod2, ProcDesc,
ProductMnemonic, Fee.
With this table, I need to build a form. For the user,
the form should preform the following: The user will
select a payor(Payor field), select a product
(ProductMnemonic field), enter a date of service(EffDate
field), select a Procedure Code(ProcCode field), enter one
or multiple modifiers(Mod1 and Mod2 fields), and from all
this info either selected or entered, the fee(Fee field)
for this info should populate.
Well, making the form look good, was no problem, now to
make it functional, I haven't a clue. Can anyone help me?
I did start with using combo boxes for the "Select a
Payor" and "Select a Product" portions of the form. I
have also set the "Limit to List" property to "yes". My
problem is that the table for each payor and product there
are at least 7,000 lines of procedure codes, so if the
user tried to select one, now, there are thousands of the
same payor or product to scroll through to make their
selection.
Thank you to the poor soul who tries to help this lost one.

 

[Mary P.]

That worked!!!

Thank you

-----Original Message-----

 

[Mary P]

Ok, now that I have the query working to help the user
only choose 1 payor, 1 product, how do I connect the
queries, so that when a certain payor, and it's product is
chosen, they only have the related effective dates and
procedure codes to choose from?

 

[Mary]

In your query that populates the combo box for the product
control, add the payor field and in the criteria for the
payor field type [forms]![formname]![payor]. That should
limit the product records to only those for the chosen
payer.

In order for the product list to requery as soon as a
payer is chosen create a macro with the action requery and
add it to the On Exit of each field. example: requery
payer and add to On Exit of payer. requery product and
add to On Exit of product.

Hope that helps