asked on

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

SOLUTION

IrogSinta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KLM56

ASKER

Thanks for you help. I still get the same error with your changes.

SOLUTION

Arthur_Wood

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KLM56

ASKER

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.

IrogSinta

Definitely delete that line. When you get the error what is your variable j set to?

KLM56

ASKER

J = 2

Thanks

IrogSinta

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.

KLM56

ASKER

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.

ASKER CERTIFIED SOLUTION

IrogSinta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KLM56

ASKER

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.