Results 1 to 10 of 10
  1. #1
    fjlaraa is offline Novice
    Windows 11 Access 2003
    Join Date
    Feb 2023
    Posts
    1

    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 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

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    1) post code within code tags (use # button on posting toolbar) to maintain indentation and readability
    2) always indicate the line that raises the error you write about; either as a comment in the code or in your post
    3) Either you're not using Option Explicit or variables you use are declared elsewhere and you don't show them (e.g. Date1)
    4) In single line declarations with multiple declarations each variable must be typed individually unless you wish them to default to variants. That would be a bad habit as everyone would be guessing your intent. So with Dim mLabattInv, mLabattInvDate, LabattInvoiceNum, mDescription, mBItemNum, mVINum As Long
    only mVINum is a Long, the others in that line are variants.
    You've done that more than once here and probably all throughout your codes.
    5) Since calling the query is what fails (at least that's how I read your post) then you need to include that in your post(s). Likely it refers to something that is not available (e.g is not open, such as another form) or the reference cannot be resolved.

    Points 1 to 4 are likely not causing your issue - they are suggestions to follow to improve coding and forum posting.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    In addition to Micron's points you need to check if the query has parameters and if yes you need to wrap them in the Eval() function in the query itself (for example to reference Date1: Eval([Forms]![MainMenu]![Da1]) - this goes in the criteria row of the query) or do it in VBA:
    Code:
    Dim db As Database, tb1, tb2 As Recordset
    Dim esvar As Variant
    Dim mLabattInv, mLabattInvDate, LabattInvoiceNum, mDescription, mBItemNum, mVINum As Long
    
    
    Dim qdf as DAO.QueryDef,prm as Parameter
    
    
    Set db = DBEngine.Workspaces(0).Databases(0)
    Set qdf=db.QueryDefs("LabattOutUSAQuery")
    For Each prm in qdf.Parameters
        qdf.Value=Eval(qdf.Name)
    Next prm
    Set tb1 = qdf.OpenRecordset
    'Set tb1 = db.OpenRecordset("LabattOutUSAQuery") 'Query
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Quote Originally Posted by orange View Post
    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
    Most of that also mentioned in the crosspost at https://www.access-programmers.co.uk...t-work.327691/
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Thanks Paul. I wasn't aware of crosspost and OP joined here Feb 23. If he/she is the same person(joined AWF 2009), then should already be aware of Option Expicit, Code tags and informing forum of cross-posts.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by orange View Post
    Thanks Paul. I wasn't aware of crosspost and OP joined here Feb 23. If he/she is the same person(joined AWF 2009), then should already be aware of Option Expicit, Code tags and informing forum of cross-posts.
    I'm beginning to wonder if there is a limit to how many people you can put on your ignore list. Just added fjlaraa so I might hold the record here. No time or patience for such people.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    No time or patience for such people.
    There are two types that have the express route to my ignore list:

    Those who just CANNOT answer questions repeatedly posed to them, and the thread goes on and on for that reason.
    Those that are rude to responders trying to help.

    There are a lot more of the first type.

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Quote Originally Posted by orange View Post
    Thanks Paul. I wasn't aware of crosspost and OP joined here Feb 23. If he/she is the same person(joined AWF 2009), then should already be aware of Option Expicit, Code tags and informing forum of cross-posts.
    I got the heads up from Vlad, who posted the crosspost over there.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    chris-smith is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2023
    Posts
    10
    Hi,

    Googled your query.
    Found these 2 links that may help you find the solution you are looking for.
    https://stackoverflow.com/questions/...-2007-and-2010
    https://www.accessrepairnrecovery.co...-openrecordset

    Best Regards.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Run-time error '3061'. Too few parameters. Expected 1.
    By Homegrownandy in forum Programming
    Replies: 8
    Last Post: 03-11-2020, 07:17 AM
  2. Run-Time Error '3061: Too few parameters. Expected 2.
    By Rickochezz in forum Import/Export Data
    Replies: 1
    Last Post: 11-01-2016, 07:29 AM
  3. Replies: 4
    Last Post: 09-28-2016, 07:13 AM
  4. Replies: 5
    Last Post: 03-27-2015, 03:42 PM
  5. Replies: 1
    Last Post: 05-21-2011, 01:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums