Further to responses above, 3061 is often a misspelled variable name ---check that out.
You should ALWAYS HAVE Option Explicit as the second line of every module.
VBA requires you to explicitly dim your variable types.
As micron advised
"Dim mLabattInv, mLabattInvDate, LabattInvoiceNum, mDescription, mBItemNum, mVINum As Long
only mVINum is a Long, the others in that line are variants."
Same goes for tb1, tb2 As Recordset. I would suggest tb1 as DAO.Recordset, tb2 As DAO.Recordset.
I have copied and indented your code for improved readability
Code:
Dim db As Database, tb1, tb2 As Recordset
Dim esvar As Variant
Dim mLabattInv, mLabattInvDate, LabattInvoiceNum, mDescription, mBItemNum, mVINum As Long
Set db = DBEngine.Workspaces(0).Databases(0)
Set tb1 = db.OpenRecordset("LabattOutUSAQuery") 'Query
Set tb2 = db.OpenRecordset("CEInfo") 'Table
Date1 = [Forms]![MainMenu]![Da1]
date2 = [Forms]![MainMenu]![da2]
With tb1
tb1.MoveFirst
Do While Not tb1.EOF
If tb1!InvoiceDate >= Date1 Or tb!InvoiceDate <= date2 Then
If CountryofOrigin <> "USA" Then
mLabattInvDate = InvoiceDate
mDescription = Description
mBItemNum = BItemNum
mVINum = ItemNum
MsgBox (Invoicenum + " " + ItemNum + " " + Description)
With tb2
.Index = "InvoiceNuml"
esvar2 = .Bookmark
.Seek "=", mLabattInv, ItemNum
If .NoMatch Then
'MsgBox (LabattInvoiceNum + " & " + CStr([InvoiceDate]) + " doesn't exist this is add")
.AddNew
!LabattInv = Me.Invoice__
!CEDate = Me.Invoice_Date
!LabattInvDate = Me.Invoice_Date
!BidNum = Me.BItemNum
!WeekNum = Format(Me.Invoice_Date, "WW")
!VINum = Me.Item__
!CEName = "Laredo ISD"
!FoodProduct = Me.Description
!CountryOrigin = Me.CountryofOrigin
!Ruta = "\\WIN-U3C12L3PKMM\Account\USDADoc\BuyAmerican\Labatt Invoice " & Me.Item__ & ".pdf"
!CEID = "01122"
!IndicatesoriginNoUS = True
!Nosourcecanprovide = True
!NoThereisnosubstitutedomesticfood = True
!Nocostdifference = True
!WhatdocumentationhasCE = "Region One Food Items July 1, 2022-June 30,2023 RFP 23-AGENCY-000106"
Else
.Edit
!LabattInv = mLabattInv
!CEDate = mLabattInvDate
!LabattInvDate = mLabattInvDate
!CEName = "Laredo ISD"
!CEID = "01122"
!IndicatesoriginNoUS = True
!Nosourcecanprovide = True
!NoThereisnosubstitutedomesticfood = True
!Nocostdifference = True
!WhatdocumentationhasCE = "Region One Food Items July 1, 2022-June 30,2023 RFP 23-AGENCY-000106"
End If
End With
.Update
Else
End If
Else
End If
tb1.MoveNext
Loop
End With