Referencing Forms in VBA code

[hughesai]

Hi all,

I need to be able to reference a form in VBA. The form may be different depending on user selections. I want to be able to set a variable to reference the appropriate form.

The code is in a module, the form name being passed in as an attribute, stFrm.

I can use
DoCmd.OpenForm stFrm
to open the form, but how do I set, say, a variable frm to reference that opened form?

I need to be able to manipulate various parts of the form design as below:
frm.RecordSource = stFormSQL
frm.Area.Visible = False
and also change some information later as below:
frm!lvMan = inLevelManager

I can achieve all this by using
Forms!frmCumCharts......
instead of using the frm variable, but just can not set the variable.

Anybody point me in the right direction?

Aidan Hughes

 

[grnzbra]

Try playing with somehting like:
Forms.Form.FormName(FormNameVariable)

Or perhaps

Forms.Form.Name(FormNameVariable)

Or perhaps


Forms.Formname(FormNameVariable)

 

[GHolden]

You can dim a variable as type form and pass this to your functions.

eg.

Private Sub mChangeRecordSource(byval frmForm as Form)
frmForm.RecordSource = whatever
End Sub

Call from form with

mChangeRecordSource Me




There are two ways to write error-free programs; only the third one works.

 

[GHolden]

Er...

Just to clarify. If you want the functions available to all forms you will have to make them Public and put them in a module. And unless your strFormSQL was public you would have to pass that too.


Public Sub mChangeRecordSource(byval vfrmForm as Form, byval vstrFormSQL as String)

vfrmForm.RecordSource = vstrFormSQL

End Sub

There are two ways to write error-free programs; only the third one works.

 

[hughesai]

Thanks for your replys.
I'm going to try them out later.

Aidan.

 

[hughesai]

Hi again.

I had limited sucess with these answers.
I could not get
Forms.Form.FormName(FormNameVariable)
and various combinations to work.

I finally found a work around in that the form I want to reference this time is the last one I've opened, so
Set frm = Forms.Item(Forms.Count - 1)
works for this scenario.

However, I would still like to know how to do this properly, because I'm sure to come across it again. You would think it should be something like
Set frm = Forms.Item.Name(FormNameVariable)
but if it is, I just have not tried (guessed) the right combination yet.

GHolden - I'm not finished with your suggestions yet. I currently have all my code in a module, so using Me to reference the form will not work, but I'm now considering moving the code to the form instead.

Any further ideas ? and thanks again -

Aidan Hughes