Combo box on an instance of a form [Help]

Prev: Word Merge Problem from Access
Next: frm.OnGotFocus = "[Event Procedure]"

From: Bob Quintal on 13 Dec 2009 08:21

Mat <matthew.kay(a)optusnet.com.au> wrote in news:0e5e0be0-8fea-4d02-
bbf6-9a5cc08c8523(a)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.

From: hbinc on 13 Dec 2009 11:56

On Dec 12, 11:27 pm, Mat <matthew....(a)optusnet.com.au> wrote:
> 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.

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.

From: Mat on 15 Dec 2009 06:49

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;

From: hbinc on 15 Dec 2009 07:31

From: hbinc on 15 Dec 2009 08:52

On Dec 15, 12:49 pm, Mat <matthew....(a)optusnet.com.au> wrote:
> 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;

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.

First | Prev | Next | Last
Pages: 1 2 3
Prev: Word Merge Problem from Access
Next: frm.OnGotFocus = "[Event Procedure]"