db.Execute Error

[Guest]

After recently learning that, unlike the OpenQuery or RunSQL methods,
db.Execute enabled one to determine the result of the query, I changed a
previous RunSQL which was working to the following code. It generates an
error on the db.Execute statement "Too few parameters. Expected 2."

MsgBox statements reveal that strSQL is assigned correctly and that
dbFailOnError is equal to 128. Does anyone know what's going on?

Thank you.
Sprinks

Dim db As DAO.Database
Dim strSQL As String

Set db = DBEngine(0)(0)

strSQL = "INSERT INTO TSHistory (StaffID, PeriodEnding) " & _
"SELECT Timesheet.StaffID, [PeriodStartDate]+13 AS
PeriodEndDate " & _
"FROM Timesheet " & _
"WHERE
(((Timesheet.StaffID)=[Forms]![Timesheet]![txtStaffID]) " & _
"AND
((Timesheet.PeriodStartDate)=[Forms]![Timesheet]![PeriodStartDate]));"

db.Execute strSQL, dbFailOnError
Set db = Nothing

 

[chris.nebinger]

Using the RunSQL method utilizes the Access engine, where
CurrentDB.Execute bypasses Access and uses JET only. JET does not know
the Access Forms collection, so can not decypher Forms!Timesheet to be
an open form. You can get around this by:


strSQL = "INSERT INTO TSHistory (StaffID, PeriodEnding) "
strSQL = strSQL & " SELECT StaffID, [PeriodStartDate]+13 AS
PeriodEndDate "
strSQL = strSQL & " FROM Timesheet WHERE "
strSQL = strSQL & " StaffID=" &
Forms("Timesheet").Controls("txtStaffID")
strSQL = strSQL & " AND PeriodStartDate=#" &
Forms("Timesheet").controls("PeriodStartDate") & "#"



Chris Nebinger

 

[Guest]

Testing the code below, I get a different error message--"Too FEW (emphasis
mine) parameters. Expected 1." on the db.Execute line:

stResponse = MsgBox("Bill Project " & Me![ProjectNumber] & "?", _
vbOKCancel + vbDefaultButton2, "Confirm Billing")
If stResponse <> vbCancel Then

Set db = DBEngine(0)(0)
stDocName = "SetBillStatus"
db.Execute stDocName, dbFailOnError
Me.Requery
Set db = Nothing

 

[Guest]

Chris,

Thank you very much. I had some trouble on the following call also.

db.Execute "MyQuery", dbFailOnError

The stored query had Criteria referencing a form control. I tried changing
the criteria using the Jet-style syntax, but Access wouldn't accept it. I
was however able to cut and paste the query's SQL directly into the module
and use it as you suggested.

Thanks again.
Sprinks

Using the RunSQL method utilizes the Access engine, where
CurrentDB.Execute bypasses Access and uses JET only. JET does not know
the Access Forms collection, so can not decypher Forms!Timesheet to be
an open form. You can get around this by:


strSQL = "INSERT INTO TSHistory (StaffID, PeriodEnding) "
strSQL = strSQL & " SELECT StaffID, [PeriodStartDate]+13 AS
PeriodEndDate "
strSQL = strSQL & " FROM Timesheet WHERE "
strSQL = strSQL & " StaffID=" &
Forms("Timesheet").Controls("txtStaffID")
strSQL = strSQL & " AND PeriodStartDate=#" &
Forms("Timesheet").controls("PeriodStartDate") & "#"



Chris Nebinger

After recently learning that, unlike the OpenQuery or RunSQL methods,
db.Execute enabled one to determine the result of the query, I changed a
previous RunSQL which was working to the following code. It generates an
error on the db.Execute statement "Too few parameters. Expected 2."

MsgBox statements reveal that strSQL is assigned correctly and that
dbFailOnError is equal to 128. Does anyone know what's going on?

Thank you.
Sprinks

Dim db As DAO.Database
Dim strSQL As String

Set db = DBEngine(0)(0)

strSQL = "INSERT INTO TSHistory (StaffID, PeriodEnding) " & _
"SELECT Timesheet.StaffID, [PeriodStartDate]+13 AS
PeriodEndDate " & _
"FROM Timesheet " & _
"WHERE
(((Timesheet.StaffID)=[Forms]![Timesheet]![txtStaffID]) " & _
"AND
((Timesheet.PeriodStartDate)=[Forms]![Timesheet]![PeriodStartDate]));"

db.Execute strSQL, dbFailOnError
Set db = Nothing

 

[Dirk Goldgar]

Chris,

Thank you very much. I had some trouble on the following call also.

db.Execute "MyQuery", dbFailOnError

The stored query had Criteria referencing a form control. I tried
changing the criteria using the Jet-style syntax, but Access wouldn't
accept it.

You can use the QueryDef object for that:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs("MyQuery")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

qdf.Execute dbFailOnError

 

[Guest]

Thanks, Dirk. Another arrow in the quiver.

Sprinks

 

[chris.nebinger]

What happens when you execute the query SetBillStatus? Does it ask for
a parameter? If so, use Dirk's code to have it evaluate all the
parameters first.



Chris Nebinger