Update Query in VBA

  • Thread starter Thread starter SAP2
  • Start date Start date
S

SAP2

Hello,
I am using Access 2003. I am trying to run an update query through VBA. My
SQL is this:

UPDATE CostSummarySheet SET CostSummarySheet.Active = Forms!fsubLOA!Active
WHERE (((CostSummarySheet.Proj_Name)=[Forms]![frmCSSheetEdit]![Proj_Name]));

and the code I am attempting to use is this:

Dim db As DAO.Database
Dim strSql As String

strSql = "UPDATE CostSummarySheet SET [Active] =
[Forms]![frmCSSheetEdit]![fsubLOA]![Active] WHERE [Proj_Name]=
[Forms]![frmCSSheetEdit]![Proj_Name];"
Set db = DBEngine(0)(0)
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) were updated."
Set db = Nothing

[Active] is a checkbox on the subform (fsubLOA) and YES/NO format on the
table CostSummarySheet. The code is on the On Click Event for [Active]. When
clicked I get:

"Too few parameters. Expected 2."

I am not a programmer. Any insight?

Thanks.
 
Try the following:
Assumption is that Active and Proj_Name are text fields.

strSql = "UPDATE CostSummarySheet" & _
" SET [Active] = """ & [Forms]![frmCSSheetEdit]![fsubLOA]![Active] & """" & _
" WHERE [Proj_Name]= """ & [Forms]![frmCSSheetEdit]![Proj_Name] & """"

Your problem is that the SQL engine has NO IDEA what those form references
are. When you do this through the query design view, then the expression
service interprets the values of those form references before passing the
query string to the SQL engine.

If Active is not a text field but is boolean (yes/no) or a number field then
remove the extra quotes.
strSql = "UPDATE CostSummarySheet" & _
" SET [Active] = " & [Forms]![frmCSSheetEdit]![fsubLOA]![Active] & _
" WHERE [Proj_Name]= """ & [Forms]![frmCSSheetEdit]![Proj_Name] & """"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
The references to the form controls need to be outside of
the quotes in the SQl string;

strSql = "UPDATE CostSummarySheet SET [Active] = " _
& [Forms]![frmCSSheetEdit]![fsubLOA]![Active] _
& " WHERE [Proj_Name]= " _
& [Forms]![frmCSSheetEdit]![Proj_Name] & ";"

( I also added continuation characters to make it easier to read
in the forum).
 
To add to Sean's answer below, if Proj_Name is a text field then I think you
will also need to wrap your search string (i.e. the form reference) in
single quotes. For example, if [Forms]![frmCSSheetEdit]![Proj_Name] = "abc"
then you want the WHERE part of your strSQL to evaluate to " WHERE
[Proj_Name]= 'abc'" rather than " WHERE [Proj_Name]= abc"

Otherwise you will get the "Too few parameters. Expected 1." error as it
thinks abc is a parameter.

Therefore the code you need to use is:

strSql = "UPDATE CostSummarySheet SET [Active] = " _
& [Forms]![frmCSSheetEdit]![fsubLOA]![Active] _
& " WHERE [Proj_Name]= '" _
& [Forms]![frmCSSheetEdit]![Proj_Name] & "';"

Hope this helps!
Jim Franklin


Beetle said:
The references to the form controls need to be outside of
the quotes in the SQl string;

strSql = "UPDATE CostSummarySheet SET [Active] = " _
& [Forms]![frmCSSheetEdit]![fsubLOA]![Active] _
& " WHERE [Proj_Name]= " _
& [Forms]![frmCSSheetEdit]![Proj_Name] & ";"

( I also added continuation characters to make it easier to read
in the forum).

--
_________

Sean Bailey


SAP2 said:
Hello,
I am using Access 2003. I am trying to run an update query through VBA.
My
SQL is this:

UPDATE CostSummarySheet SET CostSummarySheet.Active =
Forms!fsubLOA!Active
WHERE
(((CostSummarySheet.Proj_Name)=[Forms]![frmCSSheetEdit]![Proj_Name]));

and the code I am attempting to use is this:

Dim db As DAO.Database
Dim strSql As String

strSql = "UPDATE CostSummarySheet SET [Active] =
[Forms]![frmCSSheetEdit]![fsubLOA]![Active] WHERE [Proj_Name]=
[Forms]![frmCSSheetEdit]![Proj_Name];"
Set db = DBEngine(0)(0)
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) were updated."
Set db = Nothing

[Active] is a checkbox on the subform (fsubLOA) and YES/NO format on the
table CostSummarySheet. The code is on the On Click Event for [Active].
When
clicked I get:

"Too few parameters. Expected 2."

I am not a programmer. Any insight?

Thanks.
Click to expand...
Click to expand...
 
Back
Top