Pass Date Parameters to Query using .OpenRecordset

[P5C768]

Hi all, I have a parameter query that uses a start date and end date and I want to run some code that will open the query, allow the user to enter parameters and then send an email to anyone found in the query.



I've got the code working well with a table, but switching to a query gives me the dreaded ""too few parameters, expected 2" error. How can I allow the user to enter the start and end date?

Will I need to create a form and to populate the dates for the query from user input? Any help is appreciated.

[June7]

Why are you getting the error? Aren't the two dates being input?

I never use query input parameter prompts, can't validate user input. Yes, a form for user input is the best approach. The query parameters would refer to controls on form for inputs.

[P5C768]

It never gives the opportunity to enter the prompt. But that's a great point about the data verification. Hand't thought of that.

How do you do that for query forms? I use a date picker for a query based report in the onopn event, but a select query has no on open. I'm guessing you put it in the form and have the query reference the form fields.

[June7]

Not sure I understand your question. A query can be the RecordSource for a form or report. If the parameters are in the query, then the form or report will open with those criteria applied.

[P5C768]

But what if I want the user to just click a button, enter the date range and then generate an email? Do I have to put the result of the query in a form first?

I tried changing the onclick event of my button to open a form that allows the user to enter the date range and then when another button is clicked, it generates the email. However, I am still getting the "too few aprameters" error.

[June7]

Going in circles here. Post your code or the project for analysis.

[P5C768]

I've managed to get around the problem by making the query a maketable query, but that is obviously not ideal. I would rather just have the code run when the prompt is entered and be based on a select query.

So I have a button on the main menu that opens a date range form:

 
Private Sub Command3_Click()
DoCmd.OpenForm "DateRange_frm", , , , , acDialog, "Email Vendors by Registration Expiration Date"
End Sub

Once the user enters the date range, it check the dates and runs the email code. I use the text boxes on the date range form as the criteria in the query.

 
Private Sub Preview_Click()
    If IsNull([Beginning Order Date]) Or IsNull([Ending Order Date]) Then
        MsgBox "You must enter both beginning and ending dates."
        DoCmd.GoToControl "Beginning Order Date"
    Else
        If [Beginning Order Date] > [Ending Order Date] Then
            MsgBox "Ending date must be greater than Beginning date."
            DoCmd.GoToControl "Beginning Order Date"
        Else
            Me.Visible = False
        End If
    End If
 
    DoCmd.OpenQuery "By_Exp_Date_qry"
 
    Call Send_Invoice_Email
 
End Sub

There is a lot of code (and as I mentioned before I know that it works with tables) but this is the part that cause the error:

 
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Set db = CurrentDb()
Set MailList = db.OpenRecordset("By_Exp_Date_qry")

It appears that the parameters entered on the date range form are not being passed to the code when it runs.

[June7]

You don't need to 'open' the query. Eliminate the DoCmd.OpenQuery.

Does the query have reference to the form's date input textboxes as input parameters? Something like:
BETWEEN Forms!DateRange.[Beginning Order Date] AND Forms!DateRange.[Ending Order Date]

Alternative is not using a saved Access query but to build the SQL in the VBA code:

db.OpenRecordset("SELECT * FROM tablename WHERE DateRange BETWEEN #" & Me.[Beginning Order Date] & "# AND #" & Me.[Ending Order Date] & "#")

[P5C768]

Got it. It was the openquery. Now that you say that it makes perfect sense, I was trying to access the query twice. SQL solution worked like a charm too. Thanks so much.