How to do a Query in a Form

[Nick Howes]

Hi,
I've got a table, which include fields Insurer and Code, and I've got a form
to fill them in. I've also got a Fees table which has Insurer,Code,Fee, as
each unique code and insurer have their own fee. I want to have a textbox on
my form, so that when insurer and code are filled in, the fee is looked up
from the Fees table and put into this box. I've tried setting the control
source of the Fees box, using SQL and the like but I can't find anything
that works.

Any ideas?

Thanks
__

Nick H

 

[Jeff Boyce]

Nick

Another approach, rather than using text boxes and forcing the user to
"know" the valid values, would be to use one/more combo boxes and simply
select from a list of valid values. This has the added advantage of being
able to use your Fees table as a list source for the combo box, include the
Fee, and set an unbound textbox on your form to the value in the Fee for the
selected Insurer/Code.

By the way, your description seems to suggest that you have duplicate fields
in the two tables you described. Are those IDs, or actual text duplicated?
(hint: IDs, good! text replication, bad )

 

[Nick Howes]

Nick

Another approach, rather than using text boxes and forcing the user to
"know" the valid values, would be to use one/more combo boxes and simply
select from a list of valid values. This has the added advantage of being
able to use your Fees table as a list source for the combo box, include the
Fee, and set an unbound textbox on your form to the value in the Fee for the
selected Insurer/Code.

I do actually use the multiple columned listbox method for another part of
the
form, and it works pretty well. But I don't think this'd work for what I'm
doing
here, just trying to think how to explain why! Because there's about 30
codes,
and about 10 insurers, and there's a different fee for each combination of
the two, and I want to avoid scrolling a list box of 300 things

I was thinking there might be some VB code that could do a query of the Fees
table, restricting it to where the insurer and code attributes match the
respective form values, and put that in my fees box? Or could an SQL query
reference values in the current form record?

By the way, your description seems to suggest that you have duplicate fields
in the two tables you described. Are those IDs, or actual text duplicated?
(hint: IDs, good! text replication, bad )

The code and insurer are both ID's, there's no duplication - don't worry, I
did a Computer Science module in databases 3 months ago so I know my
fair share of SQL databases

Jeff Boyce
<Access MVP>

Thanks for the quick reply!

Nick Howes

 

[Jeff Boyce]

Nick

Consider another approach ...

One combo box for a list of Insurers. Use the ID and the InsurerName, hide
the first and show the second.

In the AfterUpdate event of that combo box, add code to requery a second
combo box -- looks something like:

Me!cboSecondComboBox.Requery

For the second combo box, build a query that uses what's in the first combo
box as a criterion. Select from your Fee table, those Codes where InsurerID
= what's in cboFirstComboBox, and include the Fee.

In the second combo box's AfterUpdate event, set the value of your unbound
textbox to the column holding the Fee information (in the second combo
box) -- use something like:

Me!txtFee = Me!cboSecondComboBox.Column(n-1), where "n" is the column
number that Fee is in.

By "chaining" together these two combo boxes, you'll only have to list
Insurers in the first box, and only have to list THAT Insurer's Codes in the
second, not all 300 rows!

 

[Nick Howes]

Nick

Consider another approach ...

Thanks Jeff, though I managed to work out a solution on my own before your
reply, which enters the fee after both listboxes are chosen, and as the Fees
table is currently incomplete, I made it so that if the correspondning Fee
wasn't present it would bring up an input box to allow you to enter one into
the database. This seems to be the most efficient approach, as I probably
wasn't able to explain properly but each insurer doesn't have a unique set
of codes, they're all the same, essentially it acts as a lookup matrix
between insurer and code (which is the code for a particular medical
operation, and the fees for each vary depending on who insures the patient).
If anybody on the newsgroup is interested I can post the VB code listing.

cheers

nick h