3146 ODBC - call failed......an urgent task for my boss ARGH!

[Doolittle]

Hi,
I'm a relatively new VBA programmer but have been given the task of
repairing a database that currently appears with "3146 ODBC--call
failed." This happens when various criteria are input into a form
(called InvCredit), and then cmdProcess is clicked. Then we are asked
if we can "Confirm you want to process?" and when Yes is clicked the
error appears. Following is the code. I have found that the break
appears at the .Update point close to the bottom.

Thanks,
J.Meager

Private Sub cmdProcess_Clic k()
On Error GoTo handler
If MsgBox("Confirm you want to process", 292) = vbNo Then Exit Sub
Dim db As DAO.Database
Dim rsReq As DAO.Recordset
Dim strSQL As String
Dim dteLoop As Date
Dim lngInvNo As Long
dteLoop = Format(DateFrom , "long date")
Set db = CurrentDb
strSQL = "SELECT DateRequired,Sc hoolCodeID,Requ ired,InvoiceNum ber,InvoiceDate ,ProductID,InvC red,Requirement sID
FROM tblRequirements ;"

Set rsReq = db.OpenRecordse t(strSQL, 2, dbSeeChanges, 2)

lngInvNo = DLookup("[Next Invoice Number]", "Control")

strSQL = "UPDATE Control SET [Next Invoice Number]=[Next Invoice
Number]+1;"
db.Execute strSQL

With rsReq

Do
.AddNew
!DateRequired = dteLoop
!SchoolCodeID = SchoolName
'!Required = AdjustmentQuan
!Required = Nz(Me(Format(dt eLoop, "dddd")), 0)
!InvoiceNumber = lngInvNo
!InvoiceDate = Date
!ProductID = Products.Column (3)
Debug.Print Format(dteLoop, "dddd")
!InvCred = IIf(Nz(Me(Forma t(dteLoop, "dddd")), 0) < 0, 1, 0)
.Update
dteLoop = dteLoop + 1
Loop Until dteLoop > Format(DateTo, "long date")
End With

DoCmd.OpenRepor t "InvoiceInvCred ", acViewPreview, , "InvoiceNumber= " &
lngInvNo
Exit Sub
handler:
Select Case Err.Number
Case 3022
Resume Next
Case Else
MsgBox Err.Number & " " & Err.Description
End Select
End Sub

[Salad]

Doolittle wrote:

Hi,
I'm a relatively new VBA programmer but have been given the task of
repairing a database that currently appears with "3146 ODBC--call
failed." This happens when various criteria are input into a form
(called InvCredit), and then cmdProcess is clicked. Then we are asked
if we can "Confirm you want to process?" and when Yes is clicked the
error appears. Following is the code. I have found that the break
appears at the .Update point close to the bottom.

Thanks,
J.Meager

Private Sub cmdProcess_Clic k()
On Error GoTo handler
If MsgBox("Confirm you want to process", 292) = vbNo Then Exit Sub
Dim db As DAO.Database
Dim rsReq As DAO.Recordset
Dim strSQL As String
Dim dteLoop As Date
Dim lngInvNo As Long
dteLoop = Format(DateFrom , "long date")
Set db = CurrentDb
strSQL = "SELECT DateRequired,Sc hoolCodeID,Requ ired,InvoiceNum ber,InvoiceDate ,ProductID,InvC red,Requirement sID
FROM tblRequirements ;"

Set rsReq = db.OpenRecordse t(strSQL, 2, dbSeeChanges, 2)

lngInvNo = DLookup("[Next Invoice Number]", "Control")

strSQL = "UPDATE Control SET [Next Invoice Number]=[Next Invoice
Number]+1;"
db.Execute strSQL

With rsReq

Do
.AddNew
!DateRequired = dteLoop
!SchoolCodeID = SchoolName
'!Required = AdjustmentQuan
!Required = Nz(Me(Format(dt eLoop, "dddd")), 0)
!InvoiceNumber = lngInvNo
!InvoiceDate = Date
!ProductID = Products.Column (3)
Debug.Print Format(dteLoop, "dddd")
!InvCred = IIf(Nz(Me(Forma t(dteLoop, "dddd")), 0) < 0, 1, 0)
.Update
dteLoop = dteLoop + 1
Loop Until dteLoop > Format(DateTo, "long date")
End With

DoCmd.OpenRepor t "InvoiceInvCred ", acViewPreview, , "InvoiceNumber= " &
lngInvNo
Exit Sub
handler:
Select Case Err.Number
Case 3022
Resume Next
Case Else
MsgBox Err.Number & " " & Err.Description
End Select
End Sub

One suggestion. You use MsgBox("Confirm you want to process", 292) and
Set rsReq = db.OpenRecordse t(strSQL, 2, dbSeeChanges, 2). Unless you
have memorized all the possible constants and don't mind adding them up,
why not spell your stuff out? Why not use VbYesNo + vbQuestion + ...?
It's much easier for those reading your code to make a comment.

I would next put a comment (') in front of your OnError Goto line.

You say it breaks on the .Update line. I would then change your line
Set rsReq = db.OpenRecordse t(strSQL, 2, dbSeeChanges, 2)
to
Set rsReq = db.OpenRecordse t(strSQL, dbOpenDynaset)
Maybe play around with various settings. In fact, I'd make a copy of
your table, call it Test, and attempt to add a record to Test.

Also, do you have any validation rules or fields that are required but
you don't update or fill with data? Overall, your code looks fine, but
somethings not quite right if it errors on you.

Also, does it add any records in your loop? Or does it bomb on the
first pass? I would step thru your code by clicking on the vertical bar
in the code window next to the With rsReq. When you run the code you
can follow the steps line by line and see the values you are storing.

Hmmmm...actuall y I think the problem is with the line
!InvCred = IIf(Nz(Me(Forma t(dteLoop, "dddd")), 0) < 0, 1, 0)

What is Me(Format...?

"dddd" returns a day, as in Wednesday. How would that be less than
zero? How could Wednesday ever be less than zero?

Why not state !InvCred = IIF(IsNull(dteL oop), 1, 0)

That line is really goofy.