I’m using VBA code in a Module to pass SQL code as a Pass-Through query so that I can utilize Parameters to dynamically update the Month and Year within my SQL code. The updated values for Month and Year are extracted from a Text Box within a Form called Dates.
When I run the SQL code as a stand-alone SQL query within Access, I get a list of Loan Numbers with a heading of LOANNUMBER in a Datasheet View table as expected. When I run the VBA code (below), it takes the same amount of time to run as the stand-alone SQL query and I don’t get any errors; however, once the code has completed I’m not seeing the Datasheet View table with results either.
Is there some additional VBA code that I need to add to my code below in order to get the Datasheet View table with results to appear?
Sub DATE_Discreet_Loan_Numbers()
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim p1 As String, p2 As String
Dim SQL As String
p1 = Format([Forms]![Dates]![txtCurrentMonthDate], "MM")
p2 = Format([Forms]![Dates]![txtCurrentMonthDate], "YYYY")
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = "ODBC;DSN=MyDSN;UID=MyUID;DATABASE=MyDatabase;Trus ted_Connection=Yes"
qdf.SQL = "Select Distinct LOANNUMBER " _
& "From dbo.MyTable " _
& "Where left(EFFDATE,2) = '" & p1 & "' " _
& "and right(effdate,4) = '" & p2 & "' " _
& "and left(INV,1) in ('a','b','c','4','7','8')"
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset
rst.Close
Set rst = Nothing
Set qdf = Nothing
End Sub
Thank you for any and all help you may provide.