0

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]

mkmitchell
  • 681
  • 3
  • 10
  • 23

1 Answers1

1

Depending on where the controls are on your form you may need to change the control reference.

Use the query builder GUI to create a select query which has the columns you wish to update and filter the records using the controls on your form. Once you have this you can change the query to an update query and set the values the controls on your form.

UPDATE  B
SET B.phonenum = [formname]![controlname], ...
FROM B JOIN A ON B.OID = A.OID
WHERE A.PROJECTID = [formname]![controlname]
AND B.YEAR = [formname]![controlname]
mheptinstall
  • 2,109
  • 3
  • 24
  • 44
  • That's a great start. I'm not at all sure how to type out my WHERE conditions. – mkmitchell Nov 16 '12 at 20:49
  • Are all child records being updated or just certain ones? How would you determine which one would be updated? You would use this basis in your WHERE clause. – mheptinstall Nov 16 '12 at 20:52
  • In the example I'm trying to update only phone number and address. In my actual database I have about 20 columns that need to be updated, and 2 that need to remain the same. Is there anyway to reference the subform and make the update query easier or should I remove the subform and do the entire selection and updating through one single command? – mkmitchell Nov 16 '12 at 21:05
  • Here is what I have and what I'm lacking: 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, [B]![Year] = [New_Info]![Year] – mkmitchell Nov 16 '12 at 21:11
  • Does this make sense: UPDATE B INNER JOIN B ON A.OID = B.OID SET B.phonenum = [New_Info]![PhoneNumCtrl], B.Address = [New_Info]![AddressCtrl] WHERE [A]![Landowner] = [New_Info]![OID], [A]![Project_Number] = [New_Info]![OID], [B]![Year] = [New_Info]![Year] – mkmitchell Nov 16 '12 at 21:28