Update Query for master and sub form

[lupis]

I am trying to update one single record each in a main form and subform to online MS SQL Server tables.

I have two update quries that are connected to a form, the main form also includes a subform.

I will explain the structure of my tables.
Local tables:
Contacts (PK=ID)
Actions_local(PK=AID, FK=ID)

Online SQL Server tables:
dbo_Contacts (PK=ID)
dbo_Actions_local(PK=AID, FK=ID)

Masterform (Contact Details)
Subform in Contact Details (frmActions)

I am able to update the main form with a update query to the online SQL Server table, but not the subform.

The sql for the main form is:

UPDATE Contacts INNER JOIN dbo_Contacts ON Contacts.ID = dbo_Contacts.ID SET dbo_Contacts.LastName = [Contacts].[LastName], dbo_Contacts.FirstName = [Contacts].[FirstName]
WHERE (((Contacts.ID)=[Forms]![Contact Details].[ID]));

The sql for the subfrom is:

UPDATE Actions_local INNER JOIN dbo_Actions_local ON Actions_local.AID=dbo_Actions_local.AID SET dbo_Actions_local.ActionDate = [Actions_local]![ActionDate], dbo_Actions_local.Stock = [Actions_local]![Stock], dbo_Actions_local.Amount = [Actions_local]![Amount], dbo_Actions_local.Cost = [Actions_local]![Cost], dbo_Actions_local.ID = [Actions_local]![ID]
WHERE (((Actions_local.AID)=[Forms]![frmActions].[AID]));

I think the syntax I am passing for the criteria for the subform is wrong. I tried to figure out what criteria to pass from the following page: http://www.mvps.org/access/forms/frm0031.htm

Both queries are executed with the same event with each OpenQuery action in a macro for the On Click event of a button.

Thanks
Sohail

 

[JANR]

To get the Form refrence right do this:

1. Make sure the form in question is open.
2. Open your query and place the cursor in the criteria row.
3. Now Click on the Wizzard
4. Select Forms
5. Select Loaded Forms
6. Locate your refrencefield on the subform and select it.

You will then get the correct refrence to the field.

(Probably just missing .Form part)

(((Actions_local.AID)=[Forms]![frmActions[COLOR=black]].[[COLOR=red]Form][/COLOR].[[/COLOR]AID]));

JR

 

[lupis]

Hi JR,

When I have the subform open only, and then to into the update query, and in the criteria row I let the wizzard find reference field which is AID, then it shows the criteria

[Forms]![frmActions]![AID]

I also changed the synthax to the one you suggested, but as before when I update the form I get the "Enter Parameter Value" dialog to supply a value. If a supply nothing only the parent form and parent table gets updated, if I choose cancel I get the following error: 2950 for the macro