Dlookup

[mercury]

Hi everyone

I am using dlookup to get the price from a table called Table1 so that it would appear on a form called Prescription.
What i was trying to allow a user to make a selection from three combo boxes. These three selections would then be combined to make 1 selection. This 1 selection would then be used to search for the price,(just to let you know that each of the three selections when combined is eqivqlent to a price).

What i did was to place a text box on the form called prescription and in the control source i placed the dlookup which goes something like this:

=DLookUp("[Price]","Table1","[Combinations]="_&Forms![Prescription]!Combination)

but with this code i keep getting an error message stating that i omited an operand or character or forgot a quote. Also when i changed the Dlookup function i didnt get an error message but everytime i changed the combination from the three combobox i still got the price from the old selection, it just isnt changing. Also i cannot add a record to to the form anymore. I figured that this has something to do with my table design,im not certain.

If anyone could help i'd be eternally greatful.
Im posting the database so that you could get a look at what im sayiing.
There are only TWO combinations that could be used at the moment .

Combination 1
Material -----GL7
Style---------PR
Color------------ PBX
This combination should give the price of $ 12.OO

AND
Combination 2

Material -----GL7
Style---------PR
Color------------ CLR
This combination should give the price of $ 120.OO

This price should appear in the cost text box, when the prescription button is clicked. On the Prescription form.

mercury

 

[pbaldy]

This works:

=DLookUp("[Price]","Table1","Combinations='" & [txtcombinations] & "'")

But Table1 contains the same combination (GL7PRCLR) for both records, so that has to be fixed. Also the form's source query only brings up one record, so I had to change txtCombinations control source to this:

=cboMaterial.column(1) & cboStyle.column(1) & cboColor.column(1)

since the contents of that control didn't change when different selections were made in the combos.

I would avoid using spaces in your object names. It's more trouble than it's worth in the long run. Use either underscores (Material_Item_no) or capitalization (MaterialItemNo).

 

[mercury]

hello,
Thanks alot for your help.