I have a subform in an Access 2007 db that needs to have its recordsource changed based upon varying search criteria. The accdb is a front end that connects via ODBC to a MS SQL Server 2008 back end.
I have tried multiple different methods and they all provide the same result. The subform unloads the old records but fails to show the results of the new recordsource even though the underlying query does bring back the appropriate records.
The only way I can get the form to show the new records is to close the form and reopen it. For a main form that would not be a problem but not so easy with a subform.
Given that this is supposed to be so simple I am wondering if this is the result of some quirk of ODBC and the requery / refresh method.
I am posting the code for review. It is a function that is called from another routine. The same result occurs whether or not the code is executed in the main form, subform etc.
'Creates a recordsource for frmContactLookupMaster and binds the subform to it.
Dim strWhere As String
If IsDate(Me.txtContactDate) = True Then
'--Add a 60 day timestamp window
strWhere = "AND InquiryTimeStamp >= #" & DateAdd("d", -30, txtContactDate) & "# AND InquiryTimeStamp < #" & DateAdd("d", 30, txtContactDate) & "# "
'--Build the filtered SQL statement and bind the recordsource
Me.frmContactLookupMaster.Form.RecordSource = "SELECT Last, Middle, First, InquiryTimeStamp, " & _
"ContactID " & _
"From tblContacts " & _
"WHERE Last Like '" & txtLastName & "*' AND First Like '" & txtFirstName & "*' " & strWhere & _
"ORDER BY Last, First, InquiryTimeStamp DESC"