Update Query in VBA

[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.

 

[John Spencer]

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

 

[Beetle]

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).

 

[Jim Franklin]

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

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

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.