error in code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm on the verge of having a solution to my problem because of a posting by
Mark and reply by Allen Browne on 12/7. My posting was also on 12/7 headed
"subform / form. And my original was on 10/7 headed continuous subform /
form...
I revised the code for the checkbox that Allen posted to reflect my
database. Problem is I'm getting a strange error message and can't figure out
how to fix it.
This is the code I came up with...

Private Sub fItem1_AfterUpdate()
Dim db As DAO.DATABASE
Dim strSql As String
If Me.NewRecord Then
Beep
Else
strSql = "UPDATE [tblAsstDiary1] SET [tADItem1] = " & _
Chr(34) & Me.fItem1.Value & Chr(34) & " WHERE tMainID = " & Chr(34)
& Me.fAsstDiaryID & Chr(34) & ";"
Set db = DBEngine(0)(0)
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " related record(s) changed."
Me.[sbfAD1].Form.Requery
End If
End Sub

The error msg is run-time error "3061" Too few parameters, Expected 1
the line db.Execute strSql, dbFailOnError gets highlighted.

I'm assuming the string I'm putting together in strSql is incorrect. I got
rid of other error messages by surrounding the fields in quotation marks for
string fields.
strSql has the value "UPDATE [tblAsstDiary] SET [tITEM1] = "15" WHERE
tMainID = "AD11";
tblAsstDiary is the subform table and tITEM1, tMainID are the fields in the
table
fItem1 & fAsstDiaryID are on the main form. 15 is the new value of fItem1
and AD11 is the value of fAsstDiaryID

Allen, if you happen to spot this maybe you can see right away what I did
wrong. Your code for the checkbox update is pretty much what I've been
looking for.
Thanks very much in advance.
 
Hi,
Not exactly sure where your error is.
Your code does not match the evaluated string you posted.
There are diferences in the field names.

If tITEM1 is numerical, you don't need the quotes.
I assume your getting the evaluated string from the debug window?
As in:

Debug.Print strSql

copy the string from the debug window and paste it into the query designer (in SQL view of course)
and see if it works.
Also, you don't need the [ ] brackets, certainly not around the table name, though I don't think
it would cause problems.
 
Thanks Dan, I had tried to simplify the field names for here and made a typo
but after reading your response and looking through everything I found I had
another typo in my code. Now the code is working.
And thanks Allen for your previous response to the other post.

Dan Artuso said:
Hi,
Not exactly sure where your error is.
Your code does not match the evaluated string you posted.
There are diferences in the field names.

If tITEM1 is numerical, you don't need the quotes.
I assume your getting the evaluated string from the debug window?
As in:

Debug.Print strSql

copy the string from the debug window and paste it into the query designer (in SQL view of course)
and see if it works.
Also, you don't need the [ ] brackets, certainly not around the table name, though I don't think
it would cause problems.

--
HTH
Dan Artuso, Access MVP


Rich J said:
I'm on the verge of having a solution to my problem because of a posting by
Mark and reply by Allen Browne on 12/7. My posting was also on 12/7 headed
"subform / form. And my original was on 10/7 headed continuous subform /
form...
I revised the code for the checkbox that Allen posted to reflect my
database. Problem is I'm getting a strange error message and can't figure out
how to fix it.
This is the code I came up with...

Private Sub fItem1_AfterUpdate()
Dim db As DAO.DATABASE
Dim strSql As String
If Me.NewRecord Then
Beep
Else
strSql = "UPDATE [tblAsstDiary1] SET [tADItem1] = " & _
Chr(34) & Me.fItem1.Value & Chr(34) & " WHERE tMainID = " & Chr(34)
& Me.fAsstDiaryID & Chr(34) & ";"
Set db = DBEngine(0)(0)
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " related record(s) changed."
Me.[sbfAD1].Form.Requery
End If
End Sub

The error msg is run-time error "3061" Too few parameters, Expected 1
the line db.Execute strSql, dbFailOnError gets highlighted.

I'm assuming the string I'm putting together in strSql is incorrect. I got
rid of other error messages by surrounding the fields in quotation marks for
string fields.
strSql has the value "UPDATE [tblAsstDiary] SET [tITEM1] = "15" WHERE
tMainID = "AD11";
tblAsstDiary is the subform table and tITEM1, tMainID are the fields in the
table
fItem1 & fAsstDiaryID are on the main form. 15 is the new value of fItem1
and AD11 is the value of fAsstDiaryID

Allen, if you happen to spot this maybe you can see right away what I did
wrong. Your code for the checkbox update is pretty much what I've been
looking for.
Thanks very much in advance.
Click to expand...
Click to expand...
 
Back
Top