referencing multiple forms???

  • Thread starter Thread starter Jeff Klein
  • Start date Start date
J

Jeff Klein

I have a query that references filds on a form eg.
[forms]![frm_calendar]![cbo_Name]. I want to create another form that will
also use the same query. Is there a way to reference multiple forms instead
of copying the query and changing the reference?
 
There is no way that I know of to directly reference the control(s) on a
different form.

You can do this by using a VBA function to return the value you want. If it is
just two forms and they will never be open at the same time, then you can test
for which form is open and set the function to return that value from the
appropriate form and control.

Another way, if you have another form that is always open, is to send the
value(s) to (invisible) control(s0 on that form and then use the control(s) on
that form to grab the values from. AIR CODE Follows. You will need a function
to detect if a form is open or not.

Public Function fReturnStartDate() as Variant

If IsFormOpen("frm_calendar") Then
fReturnStartDate = [forms]![frm_calendar]![cbo_Name]
ElseIf IsFormOpen("SecondForm") then
fReturnStartDate = Forms("SecondForm").txtStartDate
End If

End Function

Then in your query, you can replace [forms]![frm_calendar]![cbo_Name] with fReturnStartDate()
 
John,
Thank for the reply. Hmmm....I never thought of a function. Does the
function have to be defined in a module, or can it be defined in the form?
This way each form can set its own value. I have tried to define it in the
form but I am getting a error "Object Required".


John Spencer (MVP) said:
There is no way that I know of to directly reference the control(s) on a
different form.

You can do this by using a VBA function to return the value you want. If
it is
just two forms and they will never be open at the same time, then you can
test
for which form is open and set the function to return that value from the
appropriate form and control.

Another way, if you have another form that is always open, is to send the
value(s) to (invisible) control(s0 on that form and then use the
control(s) on
that form to grab the values from. AIR CODE Follows. You will need a
function
to detect if a form is open or not.

Public Function fReturnStartDate() as Variant

If IsFormOpen("frm_calendar") Then
fReturnStartDate = [forms]![frm_calendar]![cbo_Name]
ElseIf IsFormOpen("SecondForm") then
fReturnStartDate = Forms("SecondForm").txtStartDate
End If

End Function

Then in your query, you can replace [forms]![frm_calendar]![cbo_Name] with
fReturnStartDate()



Jeff said:
I have a query that references filds on a form eg.
[forms]![frm_calendar]![cbo_Name]. I want to create another form that
will
also use the same query. Is there a way to reference multiple forms
instead
of copying the query and changing the reference?
Click to expand...
Click to expand...
 
No, you need the function in a module. I must admit I haven't tested it using a
public function in a class module.

Jeff said:
John,
Thank for the reply. Hmmm....I never thought of a function. Does the
function have to be defined in a module, or can it be defined in the form?
This way each form can set its own value. I have tried to define it in the
form but I am getting a error "Object Required".

John Spencer (MVP) said:
There is no way that I know of to directly reference the control(s) on a
different form.

You can do this by using a VBA function to return the value you want. If
it is
just two forms and they will never be open at the same time, then you can
test
for which form is open and set the function to return that value from the
appropriate form and control.

Another way, if you have another form that is always open, is to send the
value(s) to (invisible) control(s0 on that form and then use the
control(s) on
that form to grab the values from. AIR CODE Follows. You will need a
function
to detect if a form is open or not.

Public Function fReturnStartDate() as Variant

If IsFormOpen("frm_calendar") Then
fReturnStartDate = [forms]![frm_calendar]![cbo_Name]
ElseIf IsFormOpen("SecondForm") then
fReturnStartDate = Forms("SecondForm").txtStartDate
End If

End Function

Then in your query, you can replace [forms]![frm_calendar]![cbo_Name] with
fReturnStartDate()



Jeff said:
I have a query that references filds on a form eg.
[forms]![frm_calendar]![cbo_Name]. I want to create another form that
will
also use the same query. Is there a way to reference multiple forms
instead
of copying the query and changing the reference?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top