Update Table Field Value with SQL Query

Z

Zikar

Good day,

I am seeking help to resolve a query that I want to run from a class Module
in access 2003. I first designed the query by linking the two tables and set
the wanted criteria and then copied the query from SQL View and pasted it in
the Class Model which is as follows:

UPDATE Tbl_Risk_LocalProfiles LEFT JOIN
Tbl_Risk_LocalProfiles_ResponsiblePersons ON Tbl_Risk_LocalProfiles.ProfileID
= Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileID SET
Tbl_Risk_LocalProfiles_ResponsiblePersons.RO_ProfileStatus =
Tbl_Risk_LocalProfiles.Frame_ProfileStatus
WHERE
(((Tbl_Risk_LocalProfiles_ResponsiblePersons.RiskProjectID)=[Forms]![Risk_ProfilesProjectSelection]![Risk_ProfilesProjectSelection_Subform].[Form]![ProjectID])
AND
((Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileID)=[Forms]![Risk_ProfilesProjectSelection]![Risk_ProfilesProjectSelection_Subform].[Form]![txtProfileID])
AND
((Tbl_Risk_LocalProfiles_ResponsiblePersons.RiskPeriodID)=[Forms]![Risk_ProfilesProjectSelection]![Risk_ProfilesProjectSelection_Subform].[Form]![txtPeriod]));


I revised the UPDATE Query to run it as SQL in the Module by concatenating
the criteria to the SQL string. This method did previously work with SQL
Query Code in Form Module to Return Record Count. Steve Sanford who provided
me this help, explained to me that VBA can't evaluate the reference to the
subform and therfore I tried to do the same tequnique for the UPDATE Query in
the BeforeUpdate_Form property as follows:

Dim SQL As String

SQL = "UPDATE Tbl_Risk_LocalProfiles LEFT JOIN "
SQL = SQL & " Tbl_Risk_LocalProfiles_ResponsiblePersons ON"
SQL = SQL & " Tbl_Risk_LocalProfiles.ProfileID =
Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileID"
SQL = SQL & " SET Tbl_Risk_LocalProfiles_ResponsiblePersons.RO_ProfileStatus
= Tbl_Risk_LocalProfiles.Frame_ProfileStatus"
SQL = SQL & " WHERE (RiskProjectID = " & Me!ProjectID And ""
SQL = SQL & " ProfileID = " & Me!txtProfileID And ""
SQL = SQL & " RiskPeriodID= " & Me!txtPeriod & ";"

Debug.Print SQL

I have debugged the Code and did not obtain an error but the query does not
update the table when I run it as a code. However, it does run perfectly well
from the Query design only if I provide the criteria as values and not by
referencing the controls in the Form.

I am sure there is somthing wrong with the code and would greatly apprecite
help with this.

Cheers
 
J

John Spencer

SQL = "UPDATE Tbl_Risk_LocalProfiles LEFT JOIN "
SQL = SQL & " Tbl_Risk_LocalProfiles_ResponsiblePersons ON"
SQL = SQL & " Tbl_Risk_LocalProfiles.ProfileID =
Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileID"
SQL = SQL & " SET Tbl_Risk_LocalProfiles_ResponsiblePersons.RO_ProfileStatus
= [Tbl_Risk_LocalProfiles].[Frame_ProfileStatus]"

===================== Fix the where clause ========================
Extraneous "("
AND is outside quotes instead of inside quotes
===================================================================
SQL = SQL & " WHERE RiskProjectID = " & Me!ProjectID & " And "

SQL = SQL & " ProfileID = " & Me!txtProfileID & " And "

SQL = SQL & " RiskPeriodID= " & Me!txtPeriod & ";"

Also, the assumption is that RiskProjectID, ProfileID, and RiskPeriodID are
all number fields.

You could write the where as
SQL = SQL & " WHERE RiskProjectID = " & Me!ProjectID & _
" And ProfileID = " & Me!txtProfileID & _
" And RiskPeriodID= " & Me!txtPeriod & ";"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Good day,

I am seeking help to resolve a query that I want to run from a class Module
in access 2003. I first designed the query by linking the two tables and set
the wanted criteria and then copied the query from SQL View and pasted it in
the Class Model which is as follows:

UPDATE Tbl_Risk_LocalProfiles LEFT JOIN
Tbl_Risk_LocalProfiles_ResponsiblePersons ON Tbl_Risk_LocalProfiles.ProfileID
= Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileID SET
Tbl_Risk_LocalProfiles_ResponsiblePersons.RO_ProfileStatus =
Tbl_Risk_LocalProfiles.Frame_ProfileStatus
WHERE
(((Tbl_Risk_LocalProfiles_ResponsiblePersons.RiskProjectID)=[Forms]![Risk_ProfilesProjectSelection]![Risk_ProfilesProjectSelection_Subform].[Form]![ProjectID])
AND
((Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileID)=[Forms]![Risk_ProfilesProjectSelection]![Risk_ProfilesProjectSelection_Subform].[Form]![txtProfileID])
AND
((Tbl_Risk_LocalProfiles_ResponsiblePersons.RiskPeriodID)=[Forms]![Risk_ProfilesProjectSelection]![Risk_ProfilesProjectSelection_Subform].[Form]![txtPeriod]));


I revised the UPDATE Query to run it as SQL in the Module by concatenating
the criteria to the SQL string. This method did previously work with SQL
Query Code in Form Module to Return Record Count. Steve Sanford who provided
me this help, explained to me that VBA can't evaluate the reference to the
subform and therfore I tried to do the same tequnique for the UPDATE Query in
the BeforeUpdate_Form property as follows:

Dim SQL As String

SQL = "UPDATE Tbl_Risk_LocalProfiles LEFT JOIN "
SQL = SQL & " Tbl_Risk_LocalProfiles_ResponsiblePersons ON"
SQL = SQL & " Tbl_Risk_LocalProfiles.ProfileID =
Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileID"
SQL = SQL & " SET Tbl_Risk_LocalProfiles_ResponsiblePersons.RO_ProfileStatus
= Tbl_Risk_LocalProfiles.Frame_ProfileStatus"
SQL = SQL & " WHERE (RiskProjectID = " & Me!ProjectID And ""
SQL = SQL & " ProfileID = " & Me!txtProfileID And ""
SQL = SQL & " RiskPeriodID= " & Me!txtPeriod & ";"

Debug.Print SQL

I have debugged the Code and did not obtain an error but the query does not
update the table when I run it as a code. However, it does run perfectly well
from the Query design only if I provide the criteria as values and not by
referencing the controls in the Form.

I am sure there is somthing wrong with the code and would greatly apprecite
help with this.

Cheers
Click to expand...
 
Z

Zikar

John, I thank you very much for the help you provided in solving my problem.
I appreciate your great assistance and would like to say that you are great.
Have a good day.

Cheers

John Spencer said:
SQL = "UPDATE Tbl_Risk_LocalProfiles LEFT JOIN "
SQL = SQL & " Tbl_Risk_LocalProfiles_ResponsiblePersons ON"
SQL = SQL & " Tbl_Risk_LocalProfiles.ProfileID =
Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileID"
SQL = SQL & " SET Tbl_Risk_LocalProfiles_ResponsiblePersons.RO_ProfileStatus
= [Tbl_Risk_LocalProfiles].[Frame_ProfileStatus]"

===================== Fix the where clause ========================
Extraneous "("
AND is outside quotes instead of inside quotes
===================================================================
SQL = SQL & " WHERE RiskProjectID = " & Me!ProjectID & " And "

SQL = SQL & " ProfileID = " & Me!txtProfileID & " And "

SQL = SQL & " RiskPeriodID= " & Me!txtPeriod & ";"

Also, the assumption is that RiskProjectID, ProfileID, and RiskPeriodID are
all number fields.

You could write the where as
SQL = SQL & " WHERE RiskProjectID = " & Me!ProjectID & _
" And ProfileID = " & Me!txtProfileID & _
" And RiskPeriodID= " & Me!txtPeriod & ";"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Good day,

I am seeking help to resolve a query that I want to run from a class Module
in access 2003. I first designed the query by linking the two tables and set
the wanted criteria and then copied the query from SQL View and pasted it in
the Class Model which is as follows:

UPDATE Tbl_Risk_LocalProfiles LEFT JOIN
Tbl_Risk_LocalProfiles_ResponsiblePersons ON Tbl_Risk_LocalProfiles.ProfileID
= Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileID SET
Tbl_Risk_LocalProfiles_ResponsiblePersons.RO_ProfileStatus =
Tbl_Risk_LocalProfiles.Frame_ProfileStatus
WHERE
(((Tbl_Risk_LocalProfiles_ResponsiblePersons.RiskProjectID)=[Forms]![Risk_ProfilesProjectSelection]![Risk_ProfilesProjectSelection_Subform].[Form]![ProjectID])
AND
((Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileID)=[Forms]![Risk_ProfilesProjectSelection]![Risk_ProfilesProjectSelection_Subform].[Form]![txtProfileID])
AND
((Tbl_Risk_LocalProfiles_ResponsiblePersons.RiskPeriodID)=[Forms]![Risk_ProfilesProjectSelection]![Risk_ProfilesProjectSelection_Subform].[Form]![txtPeriod]));


I revised the UPDATE Query to run it as SQL in the Module by concatenating
the criteria to the SQL string. This method did previously work with SQL
Query Code in Form Module to Return Record Count. Steve Sanford who provided
me this help, explained to me that VBA can't evaluate the reference to the
subform and therfore I tried to do the same tequnique for the UPDATE Query in
the BeforeUpdate_Form property as follows:

Dim SQL As String

SQL = "UPDATE Tbl_Risk_LocalProfiles LEFT JOIN "
SQL = SQL & " Tbl_Risk_LocalProfiles_ResponsiblePersons ON"
SQL = SQL & " Tbl_Risk_LocalProfiles.ProfileID =
Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileID"
SQL = SQL & " SET Tbl_Risk_LocalProfiles_ResponsiblePersons.RO_ProfileStatus
= Tbl_Risk_LocalProfiles.Frame_ProfileStatus"
SQL = SQL & " WHERE (RiskProjectID = " & Me!ProjectID And ""
SQL = SQL & " ProfileID = " & Me!txtProfileID And ""
SQL = SQL & " RiskPeriodID= " & Me!txtPeriod & ";"

Debug.Print SQL

I have debugged the Code and did not obtain an error but the query does not
update the table when I run it as a code. However, it does run perfectly well
from the Query design only if I provide the criteria as values and not by
referencing the controls in the Form.

I am sure there is somthing wrong with the code and would greatly apprecite
help with this.

Cheers
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help! Need to update linked SQL table in Acc07 3
update query using like criteria 3
Update table with SQL 5
SQL query help 8
Update table query memo field truncated 2
Selectively Update Field in Table 2
Trying to query off of a table field name 3
Update query - 1 table 9

Top