• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 824
  • Last Modified:

Passing date parameter to access query from excel userfom.

Hi...I thought I had this working, but I am stuck at passing the date parameter to the access query.  I have several queries in an Access dba. 2 require no dates, but the others need a date from the user. On an excel userform, I ask for start and end dates. In each query that requires a date I have Between [txtStartDate] And [txtEndDate] in the criteria of the query. Not sure if that helps. When in access I have a form that the user enters the start and end dates which works great.  

I need the same results when running the queries from Excel but I continue to get "Run-time error '3061' Too few parmeters. Expected 2. and debugs at
Set MyRecordset = MyQueryDef.OpenRecordset

Open in new window

Can anyone see where I am going wrong?
Private Sub cmdLAR_Click()
'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer
Dim qryArr(), j As Integer
 
'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
("R:\Tracking.accdb")

qryArr = Array("qryHoeqDotInProcess", "qryMtgInProcess", "qryHoeqDotApproved", "qryHoeqDotReceived", _
                "qryHoeqDotDenied", "qryMtgApproved", "qryMtgReceived", "qryMtgDenied", _
                "qryMpcReceived", "qryAoClosedLoans")

For j = 0 To UBound(qryArr)
Set MyQueryDef = MyDatabase.QueryDefs(qryArr(j))
Set MyRecordset = MyQueryDef.OpenRecordset

'Step 3: Define the Parameters

If qryArr(j) = "qryHoeqDotApproved" And "qryHoeqDotReceived" Then
    With MyQueryDef
    .Parameters("[[Forms]![frmLar]![txtStartDate]]") = txtStartDate
    .Parameters("[[Forms]![frmLar]![txtEndDate]]") = txtEndDate
    End With
 End If
 
'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
 
'Step 5: Clear previous contents
'Step 6: Copy the recordset to Excel
        Select Case j
            Case 0
                Sheets("HOEQ DOT IN PROCESS").Select
'                                ActiveSheet.Range("A10:E17").ClearContents
                ActiveSheet.Range("A4").CopyFromRecordset MyRecordset

            Case 1
                Sheets("MTG IN PROCESS").Select
'                                ActiveSheet.Range("A10:E17").ClearContents
                ActiveSheet.Range("A4").CopyFromRecordset MyRecordset

            Case 2
                Sheets("HOEQ DOT APPROVED").Select
'                                ActiveSheet.Range("A4:E399").ClearContents
                ActiveSheet.Range("A4").CopyFromRecordset MyRecordset
               
           Case 3
                Sheets("HOEQ DOT RECEIVED").Select
'                                ActiveSheet.Range("A4:E399").ClearContents
                ActiveSheet.Range("A4").CopyFromRecordset MyRecordset

        End Select

Next j
MsgBox "Your Query has been Run"
End Sub
0
KLM56
Asked:
KLM56
  • 5
  • 4
3 Solutions
 
IrogSintaCommented:
Try this:
If qryArr(j) = "qryHoeqDotApproved" Or qryArr(j) =  "qryHoeqDotReceived" Then
0
 
KLM56Author Commented:
Thanks for you help. I still get the same error with your changes.
0
 
Arthur_WoodCommented:
are you geting the error on thes line:

For j = 0 To UBound(qryArr)
   Set MyQueryDef = MyDatabase.QueryDefs(qryArr(j))
   Set MyRecordset = MyQueryDef.OpenRecordset

if so, the problem is that the parameters have not been defined yet, they are being set a few lines later in the code.  That call is not necessary and can be deleted, since you open the record set after the parameters are defined.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
KLM56Author Commented:
After deleting that line, I get error 3265 Item not found in this collection.  @ the parameters section. I will try to figure that part out.

Thanks for your help.
0
 
IrogSintaCommented:
Definitely delete that line.  When you get the error what is your variable j set to?
0
 
KLM56Author Commented:
J = 2

Thanks
0
 
IrogSintaCommented:
And what line exactly are you getting the error at now?  If it's in one of the parameters, make sure the parameter exists in the query.
0
 
KLM56Author Commented:
I am getting the error at ".Parameters("[[Forms]![frmLar]![txtStartDate]]") = txtStartDate"

 Between [txtStartDate] And [txtEndDate] is the parameter in each of the queries that require dates.
0
 
IrogSintaCommented:
Then why do you have [Forms]![frmLar]! In your parameter name  instead of just [txtStartDate]?
0
 
KLM56Author Commented:
I guess I wasn't thinking. In the Access I was using [Forms]![frmLar]!.......... and assumed I had to use the same information to call it from Excel. But I am greatful for all the help. You guys are the best.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now