Query not getting criteria from form

[RangerJeff]

I am creating a reservation system. I have a table called schedule which list all the available dates and how many products are available for each date, products are passengers, boats, canoes, kayaks, bikes.



I have second table ResTrip that contains the actually reservations for each date and which products. I create the reservation using a form (subform actually). When I add it to the table using a macro which run an UPDATE query that is suppose to subtract 1 from the number of spaces available in the Schedule table.

For some reason the query is not getting the two piece of information from the form, (salidate and product) to use as criteria for the query and ends up updating a bunch of unrelated records. If i run the query standalone and manually enter the parameters it works fine.

I am not sure if I can do this from a macro or from VBA, or am I not setting my criteria correctly? I am still new

Here are the different items

form


Actual field names


ResTrip table

Schedule table



Update Query

UPDATE schedule SET schedule.AvaiIable = [schedule]![AvaiIable]-1
WHERE (((schedule.Sailing_Date)=[Forms]![ResTrip_subform]![SailDate]) AND ((schedule.product)=[Forms]![ResTrip_subform]![Product]));

ADD Marco

[pbaldy]

The syntax changes when the form is a subform:

http://www.theaccessweb.com/forms/frm0031.htm

[RangerJeff]

Thanks Paul

Does this mean I have to do this in a VBA module instead of a Marco. Still new to ACCESS and still learning VBA. I see another question on here and looks like the same issue and they used VBA. For such a simple task I though I could do it in Marcos. If I run it standalone with enter the values as parameters it works find, so it is just a matter of getting the criteria to the query from the form without have to repeat asking for the information already on the form.

Lots of quirks in ACCESS i am still learning.

[pbaldy]

It doesn't matter where the query is run from, macro or VBA. The point is that instead of

[Forms]![ResTrip_subform]![SailDate]

it would be

Forms!MainformName!ResTrip_subform.Form!SailDate

putting the name of your main form in place of the bit in red.