Thanks for the help, new error :(

  • Thread starter Thread starter rich2
  • Start date Start date
R

rich2

Ok, I took into consideration the suggestions from my
previous post. Let me show you what I have now, with a
little more detail.

Dim strSQLRec As String
Dim lngOrderNo As Long

lngOrderNo = rstBillQry("Signing ID")

If IsNull(rstBillQry("Intercomp_Billing_Recon")) Then

strSQLRec = "UPDATE [S-Signings] SET [S-
Signings].Intercomp_Billing_Recon = 'R' WHERE (([S-
Signings].Signing_ID) = lngOrderNo);"

dbs.Execute zap
End If

Important things to know
[Signing ID] is a autonum Key Field on the S-Signings table

What I am trying to accomplish.
Putting "R" in a field that is null where the querydef
Signing ID value is = to the table Signing ID.

I am getting a Type Mismatch error in the criteria when I
execute. HOWEVER. When I enter a number in place of
lngOrderNo in the SQL, it works.

i.e. strSQLRec = "UPDATE [S-Signings] SET [S-
Signings].Intercomp_Billing_Recon = 'R' WHERE (([S-
Signings].Signing_ID) = 37270);"

That works like a charm. I am very confused, please
help. Thank you.
 
strSQLRec = "UPDATE [S-Signings] SET [S-
Signings].Intercomp_Billing_Recon = 'R' WHERE (([S-
Signings].Signing_ID) = lngOrderNo);"
Click to expand...

Point One: SQL is human readable, but only if you help.

Point Two: you might now that lngOrderNo is a vba variable, but poor old
Jet has no knowledge of these things at all. You need to provide the value
itself.

strSQLRec = _
"UPDATE S-Signings " & vbNewLine & _
"SET Intercomp_Billing_Recon = ""R"" " & vbNewLine & _
"WHERE Signing_ID = " & lngOrderNo & ";"

Point Three: be kind to yourself and put in some debugging code until you
have things fixed.

MsgBox strSQLRec, dbInformation, "Debugging code"

Point Four: the reference to zap was presumably a typo, as you do need to
execute the string you just created.

Point Five: do use dbFailOnError because otherwise you will be back here
asking why no records got updated.

db.Execute strSQLRec, dbFailOnError

As for this stuff about querydefs I don't really understand, but I have a
feeling that this could be done all in one query...

B Wishes


Tim F
 
Back
Top