Passing Recordset to new form before Load 1

[papadilbert]

I've read other posts but I'm having a problem with the solution pasted below:
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "bForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Set Forms(stDocName).Recordset = Me.Recordset

The above code opens the form. The Load subroutine of bForm begins to execute but the Recordset has not been set yet.

The above code seems to set the aForm.Recordset after the bForm has been loaded.

The above code specifies stLinkCriteria but it's not set. What's that about?

How do I set the bForm.Recordset before bForm Load is executed?

 

[MajP]

That is correct the form opens and does all of these before giving control back to the calling code:
Open → Load → Resize → Activate → Current

Why do you need it before?

One work around would be a global variable in a standard module

public glblRecordset

then in the calling code
glblRecordset = me.recordset
DoCmd.OpenForm "bForm"

then in the forms open event
me.recordset = glblRecordset

 

[papadilbert]

bForm is a popup that contains a listbox that I want to populate based on the recordset being passed. I wanted to use the bForm Load function to populate the listbox. It seems that I need to take another approach.

One way would be to code the bForm Load to get the parent's recordset but I can't figure out how to code that. But because the bForm is a popup created from executing a button on aForm that does an OpenForm on bForm....maybe there isn't a parent/child relationship? I get a runtime error that the parent form can't be found.

So the work around I have coded now is: I created in bForm a public sub LoadtheListbox() that populates the listbox. I just moved the code that I had written in the Sub Form_Load()

I call bForm.LoadtheListbox right after the Set Forms(stDocName).Recordset = Me.Recordset

Now my code looks like this:
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "bForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Set Forms(stDocName).Recordset = Me.Recordset

Call Forms("bForm").LoadtheListbox

MajP .. Thanks for the Global suggestion!

 

[MajP]

One way would be to code the bForm Load to get the parent's recordset but I can't figure out how to code that. But because the bForm is a popup created from executing a button on aForm that does an OpenForm on bForm....

in the formb open event you can directly reference the form a and then load the listbox. This would avoid the need of the global

me.recordset = forms("formA").recordset
me.loadthelistbox

 

[papadilbert]

The format for the Me.Recordset statement is exactly what I needed. No work around required. I just put that at the beginning of the Form_Load() and followed it with LoadtheListbox code.

Thanks!

 

[papadilbert]

Well, this worked ... but didn't.

When I open aForm as the first form (clicking on it from the list of forms) and then I click the button to open bForm, the reference Forms("aForm).Recordset works.

However, aForm is normally displayed (opened) on a tab of tabbed form. So, while on the tabForm, aForm, I click the button to open bForm, the reference to Forms("aForm").Recordset fail with the error that aForm can't be found.

 

[MajP]

This issue is the subform and not the tab. A form instantiated in a subform cannot be referenced by its name. When you open a form as a mainform that form is added to the forms collection. Subforms are not added. You have to reference it through its control.

so instead of this
me.recordset = forms("formA").recordset

me.recordset = forms("MainFormName").subformcontrolName.Form.recordset

You may then want to go back to the global variable construct, because then you could pass the recordset from either formA as a mainform or FormA as a subform.

 

[papadilbert]

I need a bit more explanation of the reference to subformcontrolName.

Let's say my main form is "MainForm".
It has a tab control called "TabCtl0"
It has several pages ... called Page1, Page2, etc
It has several subforms.. one called "aForm"

Does the subform control name equal the name of the subform?

I tried:

Set Me.Recordset = Forms("MainForm").aForm.Form.Recordset

This failed.

How do I determine the name of the subform control?

 

[papadilbert]

I figured it out. The answer to my own question is no, the form name is not equal to the subform control name.

The subform control name can be found by opening the main form in design view, and opening the code view (Alt-F11), then scrolling through the Code View Properties window combobox to locate the entry associated with the particular subform. The name field is the first property listed under the Alphabetic tab.

MagP, I still like your global suggestion.
Again...Thanks!