CODEWITHSUNDEEP
ms-accesssql-updatems-access-2003
mkmitchell
mkmitchell

Reputation: 701

Access 2003 - Running an update query based on select query results

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]

Upvotes: 0

Views: 2653

Answers (1)

mhep
mhep

Reputation: 2139

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]

Upvotes: 1

Related Questions