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:
Code:
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.
Code:
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:
Code:
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.