Solved

vba subform won't reload on change in recordsource

Posted on 2014-10-24
6
775 Views
Last Modified: 2014-11-07
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) & "# "
        End If
 
       '--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"
            

Open in new window

0
Comment
Question by:isurgyn
6 Comments
 
LVL 10

Assisted Solution

by:Anthony Berenguel
Anthony Berenguel earned 167 total points
ID: 40402361
Have you already tried me.refresh or me.requery from the parent form after you change the record source?
0
 

Author Comment

by:isurgyn
ID: 40402435
The subform requery is implicit in the change recordsource method.   So a requery or refresh on the subform makes no difference.  Don't believe I have tried a requery on the main form mainly because I don't want to requery the main form as that will requery all of the subforms on the main form and screw up those recordsets.

That said, I have used the same type method to change the recordsource on another main form with no subforms and it had the identical problem.  The only way to get it to show the new recordsource was to close the form and reopen it.
0
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 40402489
I want to say i've got around the same problem you're having with the subform by refreshing or requerying the main form. I'll have to dig into some old applications to see if I can find an example. But in the meantime I think it would be something worth trying -- if it's not a problem to also requery the main form.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 166 total points
ID: 40402641
Do you have the Master and Child linkfields set on the Subform? If you do, and if those links are not valid after resetting the Recordsource, then the subform wouldn't show records.

Note too that SQL Server using the percent sign ( % ) as the wildcard, not the asterisk ( * ). If you're hitting a sql database, you should change that in your SQL statement.
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 167 total points
ID: 40404260
I have had similar problems, and found that clearing the record source, then doing whatever you need to do to recreate the record source, then re-assigning the record source to the subform, works well.  Here is some sample code:

      Echo False
      
      Me.RecordSource = ""
      CurrentDb.Execute strSQL
      Me.RecordSource = "qryContactCodes"
      strSearch = "[ContactCode] = " & lngCode _
         & " AND [ContactID] = " & lngContactID
      Me.Recordset.FindFirst strSearch

      Echo True

Open in new window


The point of the search is to get back to the record where you started out (it may change when requerying).
0
 

Author Comment

by:isurgyn
ID: 40421527
Hi,

Sorry for taking so long to get back on this.  Other db crisis to deal with.

I have tried all of the suggested solutions and none solve the problem.  The requery and refresh I have tried on both main and subforms to no avail.  The subform is a lookup form and the criteria are entered into unbound fields on the main form.  Because we are using Like we want to be able to bring back any similar records.  That said, I did try linking master and child fields an no change.

I thought that Helen had it nailed but again, no change in the subform.  The SQL statement works great so the query criteria are not a problem.  

Any other suggestions?  Thanks
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Running sum query 6 35
DBF to ... Converter 5 49
can't find file error on web browser 1 18
word 2016 1 8
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now