Combo box on an instance of a form

[Mat]

When I open the form normally, ie: From the database window, the combo
box works correctly. The query behind the combo box references a field
on the form and shows the data. For example:

SELECT tblStates.State_name, tblCountries.Country_name
FROM tblCountries INNER JOIN tblStates ON tblCountries.ID =
tblStates.tblCountriesID
WHERE (((tblCountries.Country_name)=[forms]![frmPeople]!
[Nationality]))
ORDER BY tblStates.State_name;

But when I open an instance of the form the combo box can't find the
parameter:
[forms]![frmPeople]![Nationality] and it displays an input box.

I suspect this is because of the nature of the instance of the form.
Is there another way to write the query so that it works for when I
create an instance of the form?

[Roger]

in the form's current event, use vba, to create the sql string,
allowing to refer to the 'nationality' field
and assign it to the combox box rowSource

strsql = "select ...."
comboBox.rowsource = strsql

[Mat]

Yeah I was thinking that code was a solution. I am still hoping there
is a way to write a query though?

[hbinc]

On Dec 12, 9:49 pm, Mat <matthew....@optusnet.com.au> wrote:
> Yeah I was thinking that code was a solution. I am still hoping there
> is a way to write a query though?

Hi Mat,

I do not understand the problem because I do not know what "the
instance of the form" is.
I searched in the Help and I searched in my dictionary, but no clue
how I can relate this to my knowledge of Access on this moment. Can
you explain this for me?

HBinc.

[Mat]

Sure!

'put this in a module
public collForms as new Collection

'put this code in a button on a form
dim frm as Form
set frm = new Form1 'form1 is the name of a form you have in your
database.
frm.visible = true
collforms.add frm, cstr(frm.hwnd)
set frm = nothing

The above will create multiple copies (instances) of form1.

[Bob Quintal]

Mat <matth...@optusnet.com.au> wrote in news:0e5e0be0-8fea-4d02-
bbf6-9a5...@x5g2000prf.googlegroups.com:

> Yeah I was thinking that code was a solution. I am still hoping there
> is a way to write a query though?
>

You are creating the form instance via code.
Code is the only solution to make reference to that form
Any attempt to reference the textbox will fail because the query has no
knowledge of the code that created the instance of the form.

Imagine you have 3 open instances of the form, and try to requery one
of those instances. How would the query know which instance to use as
the filter?

--
Bob Quintal

PA is y I've altered my email address.

[hbinc]

Hi Mat,

I understand. Until now I have never used this kind of things, so I
would like to learn from you for what kind of applications you can use
this feature.

Back to your problem, in your example you created a new form as object
variable frm.
So, in my opinion, you can refer to this variable in your query:

SELECT tblStates.State_name, tblCountries.Country_name
FROM tblCountries INNER JOIN tblStates ON tblCountries.ID =
tblStates.tblCountriesID

WHERE (((tblCountries.Country_name)=frm![Nationality]))
ORDER BY tblStates.State_name;

I am not sure, because I never used it, but give it a try.

HBInc.

[Mat]

I wrote this function, it needs error checking code of course:

Public Function GetControlsValue(strCtl As String) As Variant
Dim frm As Form
GetControlsValue = -1
Set frm = Application.Screen.ActiveForm
GetControlsValue = frm.Controls(strCtl)
End Function

Now I can re-write my queries:

SELECT tblStates.State_name, tblCountries.Country_name
FROM tblCountries INNER JOIN tblStates ON tblCountries.ID =
tblStates.tblCountriesID

WHERE (((tblCountries.Country_name)=cstr(GetControlsValue
("FieldName"))))
ORDER BY tblStates.State_name;

[hbinc]

Hi Mat,

And what if you use instead of

cstr(GetControlsValue("FieldName")

just

Screen.ActiveForm("FieldName")

In that case GetControlsValue is not necessary, and also not an
instance of the form.

HBInc.

[hbinc]

Hi Mat,

In your example you used Screen.ActiveForm.
Personally I do not like "Screen". It is the_active_screen, whatever
it is. In almost all cases you can use the variable of current form:
Me.

Sometimes you need to refer to the previous from.
I do that by using the OpenArgs argument in the Open statement of the
new form. The OpenArgs argument has the name of the from_where_form,
i.e. Me.Name.
In the new opened form I then can define

Dim prev_form as Form
Set prev_form = Forms(Me.OpenArgs).

Now I have access to all Controls or Properties of the prev_form.
You can even do this many levels deep.

HBInc.

[Mat]

I should add that having to rewrite a lot of queries to get around
this issue is a major drawback to using instances of forms.

I have numerous combo and list boxes that refer to a control on the
form that I am having to re-write.