recordset movenext does not work. (1 Viewer)

[MishaInc]

I have this procedure, but when I ran this, I never skipped the next record. please, can someone tell me where my mistake is?
i am pretended to copy someone's records to another table.

Thank you


Dim db As Database, rsGuruChants, tb, tb2 As Recordset
Set db = DBEngine.Workspaces(0).Databases(0)
Set tb = db.OpenRecordset("Laredoinvoices")
date1 = [Forms]![MainMenu]![Da1] - 1
date2 = [Forms]![MainMenu]![da2] + 1

tb.MoveFirst
Do While Not tb.EOF
If tb.InvoiceDate >= date1 Or tb.InvoiceDate >= date2 Then
MsgBox (InvoiceDate)
MsgBox (LabattInvoiceNum)
MsgBox (Description)
MsgBox ("This is USA")
End If
tb.MoveNext
Loop

 

[cheekybuddha]

This line: If tb.InvoiceDate >= date1 Or tb.InvoiceDate >= date2 Then is wrong

tb is a Recordset, so it should be either:
If tb.Fields("InvoiceDate") >= date1 Or tb.Fields("InvoiceDate") >= date2 Then
Or
If tb!InvoiceDate >= date1 Or tb!InvoiceDate >= date2 Then

I suggest you add Option Explicit to the top of every code module (above or below Option Compare Database).

Then, go to the 'Debug' menu in the VBA editor and click 'Compile'.

Also, do you have any DoCmd.SetWarnings False statements in your code?

 

[ComputerVersteher]

And

Dim db As Database, rsGuruChants, tb, tb2 As Recordset

declares rsGuruChants and tb as Variant, what may prevent the unkown property error message.

@cheekybuddha: I think we shouldn't show the Bang Operator to beginners, as it can cause errors, if one doesn't know what it does.

 

[Gasman]

Learn to walk through your code with F8.
That will then show the path your code takes for certain data.

 

[MishaInc]

This is the code that I have in the program, but when I run it, run-time error 3061 too few parameters. expected 3. tb1 is a query. but when I replace query for table this code never move it the first record.

I have problems with this code.
First : tb1 is a query and when I run it send this error run-time error 3061 too few parameters. expected 3.
Second: When I use Table on Tb1 the program work, but never skip to the next record.

I try to work with query table because I need to relationship two tablet this is focus on this code to try to resolve it.
Thank you so much.

Thank you


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

 

[Gasman]

Please repost using code tags </>. See my signature. .

That way indentation is retained and code easier to read.

 

[MishaInc]

I have problems with this code.
First : tb1 is a query and when I run it send this error run-time error 3061 too few parameters. expected 3.
Second: When I use Table on Tb1 the program work, but never skip to the next record.

I try to work with query table because I need to relationship two tablet this is focus on this code to try to resolve it.
Thank you so much.

DoCmd.SetWarnings False I don't have declared on my program

Thank you

 

[The_Doc_Man]

First, this line:

Dim mLabattInv, mLabattInvDate, LabattInvoiceNum, mDescription, mBItemNum, mVINum As Long

is incorrect, in that EACH VARIABLE requires an AS data-type modifier. All of those unmodified names are effectively declared as variants. The down side of having variants is that if you use one in a way that changes its current datatype, you will never know it because variants can never incur the "Data type mismatch" error - even if they otherwise SHOULD have triggered the error. Your declaration of TB1 suffers from the same design flaw. Since it is TB1 that doesn't behave correctly (regarding the .MoveNext) it might be a good idea to correct this error. Variants CAN become recordset objects - but the problem is that they can silently become something else, too.

The other error is trickier.

First : tb1 is a query and when I run it send this error run-time error 3061 too few parameters. expected 3.
Second: When I use Table on Tb1 the program work, but never skip to the next record.

If you get an error trying to open TB1, it will never skip to the next record because it is not open. Therefore, your "second:" statement should actually be expected. But you should get errors every time you use TB1 to execute other methods ... like .MoveFirst, and you DO get an error (3061) so it seems as though you DO have error checking enabled. This is a logical inconsistency that makes me wonder what you aren't telling us.

Further, I'm not sure what you are actually saying with your "second:" comment because the default for OpenRecordset is dbOpenTable. So maybe my confusion is because your statement is not clear. I can't find your introduction so we don't know much about you. Is English not your first language? It is OK... ABSOLUTELY OK if English is not your native language, but knowing that fact would help us better target our questions. Because right now it seems that we are getting tripped up on descriptions.

 

[Pat Hartman]

Every variable should be properly defined.

Dim db As DAO.Database
Dim tb1 AS DAO.Recordset
Dim tb2 As DAO.Recordset
Dim esvar As Variant
Dim mLabattInv As ????
Dim mLabattInvDate As Date
Dim LabattInvoiceNum As ?????
Dim mDescriptio As String
Dim mBItemNum As String
Dim mVINum As Long

Replace the question marks with the correct data type. DAO and ADO have overlapping object models. DAO is the default object model but it is best to disambiguate the objects anyway so that if you ever need to add ADO code you can add it without a problem.

The error you are getting sounds like your query has parameters and those need to be set in the code if you open the query using DAO.

 

[bastanu]

Cross-posted with solution here:

error run-time error 3061 too few parameters. expected 3. use Query on recordset

I have problems with this code. First : tb1 is a query and when I run it send this error run-time error 3061 too few parameters. expected 3. Second: When I use Table on Tb1 the program work, but never skip to the next record. I try to work with query table because I need to relationship...

www.accessforums.net

Cheers,

 

[MishaInc]

Let me check my variables and I am checking the code
thanks