Use Set statement to reference a form

[Guest]

I am trying to concatenate some values and then set the result of the string
to a variable of the Form type.

The form name I am attempting to set the variable to is stored in a field in
a table.
I reference this text value through a combo box on a form.

The code below is from a class module of a form attached to a label.

Here is what I have done:
Dim FilterForm As Form

Set FilterForm = "Form!" & Me!ReportName.Column(4)

Can this be done or am I missing something real obvious?


Thank you,

Seth

 

[Terry Kreft]

A form is of type form not string.

I suspect you are trying to do something like this

Dim FilterForm As Form

Set FilterForm = Forms(Me.ReportName.Column(4))

note that this will only work if th form is already open, if it isn't you'll
need to do something like

Dim FilterForm As Form

DoCmd.OpenForm Me.ReportName.Column(4)
Set FilterForm = Forms(Me.ReportName.Column(4))

 

[Guest]

Terry:

Your solution fixed my problem.

You were right, I was attempting to set the variable before having the form
opened first.

Question -

Why do you enclose the reference to the field in parentheses? What does
that do or tell Access? Where can I read about this?

Thank you,

Seth

 

[Terry Kreft]

Forms, as in Forms(Me.ReportName.Column(4)), is the collection of forms
which are open. You can reference a form in the Forms collection in a
number of ways. Say for example you have a form called fSeth which is open
and is the only one open, you could reference it in the following ways
Forms![fSeth]
Forms(0)
Forms("fSeth")

or if you had a variable which contained the value fSeth you could use the
variable in the brackets e.g.
Dim strFormName as String
Dim f as Form

strFormName = "fSeth"
' ... other code here to open the form etc.

Set f = Forms(strFormName)

This latter method is similar to what is happening in the code I posted,
except in that case the "variable" is a control on a form which supplies a
valid form name. We could expand that code to something like:-.

Dim FilterForm As Form
Dim strFormName as String

strFormName = Me.ReportName.Column(4)

DoCmd.OpenForm strFormName
Set FilterForm = Forms(strFormName)

The forms collection is a zero based collection, that means when the first
form is opened it has an ordinal of zero (you can refer to it using the
syntax Forms(0)) the second form opened has an ordinal of 1 (Forms(1)), and
so on.

 

[Guest]

Terry:

I will ponder this. Thank you for taking the time to detail the response to
my question. I am sure you are busy and that makes it all the more valuable.

Seth

Forms, as in Forms(Me.ReportName.Column(4)), is the collection of forms
which are open. You can reference a form in the Forms collection in a
number of ways. Say for example you have a form called fSeth which is open
and is the only one open, you could reference it in the following ways
Forms![fSeth]
Forms(0)
Forms("fSeth")

or if you had a variable which contained the value fSeth you could use the
variable in the brackets e.g.
Dim strFormName as String
Dim f as Form

strFormName = "fSeth"
' ... other code here to open the form etc.

Set f = Forms(strFormName)

This latter method is similar to what is happening in the code I posted,
except in that case the "variable" is a control on a form which supplies a
valid form name. We could expand that code to something like:-.

Dim FilterForm As Form
Dim strFormName as String

strFormName = Me.ReportName.Column(4)

DoCmd.OpenForm strFormName
Set FilterForm = Forms(strFormName)

The forms collection is a zero based collection, that means when the first
form is opened it has an ordinal of zero (you can refer to it using the
syntax Forms(0)) the second form opened has an ordinal of 1 (Forms(1)), and
so on.


--

Terry Kreft

Terry:

Your solution fixed my problem.

You were right, I was attempting to set the variable before having the form
opened first.

Question -

Why do you enclose the reference to the field in parentheses? What does
that do or tell Access? Where can I read about this?

Thank you,

Seth