variable Recordset

[Lanason]

Final stupid question on my nested loop

How do I put a variable into the form name to replace frmABC (as I get this from my data)
myrec2 = Forms!?????.Recordset

 

[Ranman256]

normally, you have a permanent form name for the recordset.
frmClients, show client recordset
frmCustomers ,show customers,
BUT if like the above , the form has the same datafields ,you can swap recordsets

docmd.OpenForm "myForm"
forms!myForm.Recordsource = "qsClients"

 

[Frothingslosh]

The long version for referring to a form's recordset is:

Forms.Item(FormName).Recordset

You can replace FormName with either a string inside quotes or with a variable.

If the form isn't open you'll receive an error.

Basically, the ! in Forms!FormName.Recordset tells Access to pull the default property for whatever it follows. With the Forms collection, that's Item. The rest just tells it that the Item is named FormName and to access the Recordset property.

 

[isladogs]

Following on from the last response, you can test if the form is open using:

SysCmd(acSysCmdGetObjectState, acForm, "FormName")

This returns 0 if closed and rather oddly 1 (not -1) if true

So you could add this code to avoid the error:

If SysCmd(acSysCmdGetObjectState, acForm, "FormName")=0 Then DoCmd.OpenForm "FormName"

 

[Frothingslosh]

Actually, I find it easier to test via CurrentProject.AllForms("FormName").IsLoaded. It returns TRUE of the form is open (in ANY view) and FALSE otherwise.