I currently have a subform that displays a select query. I want to update all the records of Table B that are showing in the subform with information from the form. The subform is not necessary. I was just using it to make sure my select query was displaying correctly.
Table A has 3 columns (OID, Project_Number, Landowner)
Table B has 4 columns (OID, PhoneNum, Address, Year)
These tables have a one to many relationship. One OID in Table A relates to many in Table B
Table A
1 A10 Bill
2 B10 Sally
3 A10 Bill
Table B
1 555 123 blah st 2012
1 2013
2 111 456 aaa st 2012
3 2012
The form allows the user to enter information that populates Table B. The subform displays a list of records where Project_Number, Landowner, and Year are equal to the record showing on the form
For example. If the form is showing
1 A10 Bill
the subform is showing
1 A10 Bill 2012
3 A10 Bill 2012
When I click a save command button I would like it to run the update query but I'm having issues with the SQL command.
My Select query is as follows:
SELECT B.Project_Number, A.LANDOWNER, B.Year
FROM A INNER JOIN B ON A.OBJECTID = A.OBJECTID;
The subform is setup
Link Child Fields: Project_Number; Year; Landowner
Link Master Fields: B.Project_Number; Year; A.Landowner
I would like:
UPDATE B.PhoneNum, B.Address, B.Year
WHERE items found in my subform
WITH information from my form
Is it easier to forget the subform and do it all through a single update query?
UPDATE B SET B.phonenum = [New_Info]![PhoneNumCtrl], B.Address = [New_Info]![AddressCtrl]
WHERE [A]![Landowner] = The same landowner as the OID selected, [A]![Project_Number] = The same project number as the OID selected, [New_Info]![Year] = [B]![Year]
Thanks in advance for any help!
Everything is working now. I wanted to add to djphatic's answer.
When doing this make sure to add [Forms]![formname]![controlname]