Selecting price from table using data in another table

[PmF]

I have a table that stores booking info for a hotel,
dates etc.
It also lets you specify the season (winter, summer, christmas, new year) the number of nights (1 to 7) the room type (deluxe, single, luxury) and service (B&B or Dinner, B&B)
I then have a table tbl_Prices which stores a price for all the different possibilites of nights, room type, season and service.
How do I get a text box on a form to display the correct price selected from the Price field of the table tbl_Prices using the criteria specified on the bookings form frm_Bookigns which stores its data on a table tbl_Bookings?

Please help, urgent

 

[JoeMiller]

Dlookup would work for you:

DLookup("[MyPriceField]","MyTable","[Criteria1]='Criteria1' AND [Criteria2]='Criteria2' AND [Criteria3]='Criteria3'&quot

You'll need to use an event to assign the value to the text box, most likely the afterupdate event of each of the criteria fields.

HTH Joe Miller
joe.miller@flotech.net

 

[PmF]

I can get the field to work for one specified criteria but not anymore. Below is what I have for the control BPrice as the value

=DLookUp("[Price]","tbl_Prices","[Season]=Forms![frm_Booking]![BSeason]" And "[Service]=Forms![frm_Booking]![BService]&quot

on the same form is the control BSeason, BNights, BService and BRoom which is the criteria that matches up with the fields Season, Nights, Service and Room on the Prices Table

When I specify more than one criteria it just returns the value of the first record in the price field on the price table.

Any ideas?

ps. thanks for the tip

 

[PmF]

Sorted it, the formula works fine but when I enter it Access says it can't find the field name Forms???
as soon as I go back to the form and test it, it works fine, thanks again

 

[JoeMiller]

=DLookUp("[Price]","tbl_Prices","[Season]=[Forms]![frm_Booking]![BSeason]" And "[Service]=[Forms]![frm_Booking]![BService]&quot Joe Miller
joe.miller@flotech.net

 

[PmF]

I have messed up somewhere. I renamed the boxes on the form taking out the B because I realised I can call them the same name as fields on a table. Now I have #Error in the calculated control and when I select anything from the list boxes I get the message:

the expression After Update you entered as the event property setting produced the following error: A problem occurred while MSAccess was communicating with the OLE server or Active X control. etc...

Can't see why it wont work, I even tried it with just one selection criteria:

=DLookUp("[Price]","tbl_Prices","[Season]=[Forms]![frm_Booking]![Season]&quot

just can't get it to work again.

Help

 

[JoeMiller]

You can't name the control the same as the field and have the calculated equation work. When you do this, access doesn't know which thing to reference when looking for the criteria value so it returns #error.

Joe Miller
joe.miller@flotech.net