Prev: Word Merge Problem from Access
Next: frm.OnGotFocus = "[Event Procedure]"
From: Mat on 12 Dec 2009 08:05
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?

From: Roger on 12 Dec 2009 08:11
On Dec 12, 6:05 am, Mat <matthew....(a)optusnet.com.au> wrote:
> 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?

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
From: Mat on 12 Dec 2009 15:49
Yeah I was thinking that code was a solution. I am still hoping there
is a way to write a query though?

From: hbinc on 12 Dec 2009 16:58
On Dec 12, 9:49 pm, Mat <matthew....(a)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.
From: Mat on 12 Dec 2009 17:27
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.
 |  Next  |  Last
Pages: 1 2 3
Prev: Word Merge Problem from Access
Next: frm.OnGotFocus = "[Event Procedure]"