Sub Form recordsource = pass thru query won't open - How to reset it with code

[SteveApa]

Hi All.
I found this info on this forum, and can confirm, if a subform's recordsource is set to a pass thru query, it won't load. Infact, it won't even kick off the subform's form_open event (where I tried to reset the recordsource). I must have inadvertently saved the subform with the passthru recordsource.

The only way I have found to reset the recordsource is in the form design view and manually change the recordsource to a non-pass thru. Then in the subform's form_open, set it back to the passthru. It seems that the form must be open to change the recordsource with vb.



NOTE: I have in my app the choice to set a passthru or not... Speed thing with sql server - I either recordsource to a non-passthru to allow direct edit or I use a passthru and have vb code edit the data. I am doing this because the db will be used globally and I am not sure of speed.

So, How do I reset it? The form won't open if the recordsource is set to a passthru. I am looking for something like changing a query's sql... dim qd as dao.querydef then set the qd.sql = "bla""
Is there a dim td = dao.formdef... then I could td.recordsource = "bla" without opening the form??

Thanks
Steve
Harrisburg, PA

[June7]

Even if that were a thing, how would that resolve issue? Would still change RecordSource to passthrough and then form would not open.

[SteveApa]

Sorry dude... You are totally wrong, Totally! To get a subform to work with a passthru (or sql server procedure), you 1st assign a different recordsource in access's design view. Then prior to subform open, you change the subform recordsource to the passthru.... on the main form's open event. It just hit me... My issue was that I assigned a global variable to the subform's on open, set SUBFORM_FORM (Global variable) = me (SUBFORM_FORM dim'd as form). And, we all know that the subform open 1st before the main form. This should cause the subform's global variable to be defined. But, since the subform was saved with the recordsource as a passthru, access never got to the subform's open event, so my global variable was never assigned. I referenced the global variable as SUBFORM_FORM.recordsource = "passthru query". so... I fix is, in the main form's open event, I must explicitly assign the recordsource to the subform by name, not subform assigned global variable.

Mainform's open event:
Forms!Mainformname!"subformname".form.recordsource = "Passquery"
instead of:
SUBFORM_FORM.recordsource = "passquery"

[SteveApa]

Ok.. so that did not work because the subform is not open yet on the open event of the main form. The only solution I see is to set the subform recordset in the design view to a non-pass thru (so it will load), then once it is loaded, change the recordsource to the passthru (from the main form's open event"

[SteveApa]

Here is the solution i came up with... In code, I open the subform 1st, just as a check. In the Form_open event, I check to see if the main form is open, if not, then I set the subform recordsource to the non-passthru. If the main form is open, I bypass resetting the recordset and set it in the main form's Open event!
Solved!

[SteveApa]

Summary. If a mainform has a subform with a recordsource set to a passthru, the subform will not open... So, before the main form is opened (the on_click that opens the form before the docmd.Open main_form, I run the initialize subform routine to set the recordsource of the subforms to a dummy recordset. Then, on open of the main for, the subform's Form_Open event, resets the recordsource to the passthru. Solved.

Public Sub Initialize_Sub_Form_Recordsources_For_Passthru_Change_Later()
Dim form_name As String


    'Global variable set in code to control recordsources of subforms.
    'Global Const USE_PASS_THRU_AS_SOURCE = True ' this variable set if forms are pass thru queries, non-direct edit.  SQL Code to edit records
    'Global Const USE_PASS_THRU_AS_SOURCE = False ' this variable set if forms are direct edit (dao queries)


    'set recordsource for all subforms, before the main form opens, to a dummy non-passthru recordset.
    'Must be done before the Main Form On_Open event.  Subforms will not open from main form is subform is set to a passthru.
    'Change the subform's recordsource, to what you want, pass or not, in the subform's form_open event
    
Status_Message_Open True, "Local: Initializing Subforms"
    form_name = "ews 51 4b) Sub Form - Work Elements"
    DoCmd.OpenForm form_name, acDesign, , , , acHidden
    Forms(form_name).RecordSource = "ews 1) Dummy Table - Initial Recordsource for Subforms"
    DoCmd.Close acForm, form_name, acSaveYes
    'pass thru query: ews 2 b2) Work Elements LZ Project Current Gate XX - sql server procedure
    'non pass thru query: ews 51 3b) Work Elements - Global Project - Current - dao query using linked sql server ables
    
    form_name = "ews 54 2b) Action - Update sub main"
    DoCmd.OpenForm form_name, acDesign, , , , acHidden
    Forms(form_name).RecordSource = "ews 1) Dummy Table - Initial Recordsource for Subforms"
    DoCmd.Close acForm, form_name, acSaveYes
    'pass thru query:  not created yet
    'non pass thru query: ews 54 2b) Action - Update sub main - qry


    
    
    form_name = "ews 55 2b) Update - Update sub main"
    DoCmd.OpenForm form_name, acDesign, , , , acHidden
    Forms(form_name).RecordSource = "ews 1) Dummy Table - Initial Recordsource for Subforms"
    DoCmd.Close acForm, form_name, acSaveYes
    'pass thru query: not created yet
    'non pass thru query: ews 53 3b) Action Updates - Global Project - Current
Status_Message_Open False
    
End Sub

subform's Form_Open Event:

Private Sub Form_Open(Cancel As Integer) ' subform of Main form
    ' reset recordset to passthru or not based on GLOBAL variable USE_PASS_THRU_AS_SOURCE.  Before Main form is open, this form's
    ' recordset was set to a dummy recordsource.
    Set EWS_MAIN_ELEMENT_FORM = Me ' set form to global variable of type form... this makes it easier to reference later.  Like EWS_MAIN_ELEMENT_FORM.requery
    If USE_PASS_THRU_AS_SOURCE Then ' Global variable set (either by code or by cmdbtn click)
        EWS_MAIN_ELEMENT_FORM.RecordSource = "ews 2 b2) Work Elements LZ Project Current Gate XX"
    Else
        EWS_MAIN_ELEMENT_FORM.RecordSource = "ews 51 3b) Work Elements - Global Project - Current"
    End If
End Sub

[Minty]

Just store the pass through and then create a normal stored query against it.
Use the stored query in your sub form.