Query to pull value from unbound form control?

[CW]

Is it possible to use a query to pull the data from an unbound control on a
form (i.e. a text box)? If so, what does the syntax need to be? (Let's assume
the form is Form 1 and the control is Text 1)
Many thanks
CW

 

[elbyc]

Is it possible to use a query to pull the data from an unbound control ona
form (i.e. a text box)? If so, what does the syntax need to be? (Let's assume
the form is Form 1 and the control is Text 1)
Many thanks
CW

You should be able to reference the text box directly as [text 1]. Are
you trying to access the text box through VBA or just from another
control on the form?

 

[elbyc]

I did a lot of research on this last week in this usenet group, so I
thought I'd give back. Hope this example will be useful:

I've got three controls on my form:
Ownercmb - a combo box (could be bound or unbound, does not matter)
where the row source type is Table and the row source selects three
columns - Initials, Name, Group
GPM_Grouptxt - a text box bound to the field [Group] in my query
gpm_group_holder - a hidden text box used to capture the value of the
selection in Ownercmb
Job_Typecmb - another combo box used to select the type of job
(dropdown menu depends on the Group value)

Here are some is some of the queries I use on the form that reference
other textboxes or combo boxes:

GPM_Grouptxt is bound to =[Ownercmb].[Column](2) - so that when the
user selects an entry from the combo box, the result from column one
appears in Ownercmb and the result from column 3 appears in
GPM_Grouptxt.

For Job_Typecmb I want to filter by the results in the GPM_Grouptxt
textbox. I have another table called job_type_table with columns
[job_type] and [group].
I set the row source to:
SELECT [job_type_table].[job_type] FROM [job_type_table] WHERE [group]=
[gpm_grouptxt];
This references both the other table and the value of the field
gpm_grouptxt, even though gpm_grouptxt is not in my other table. In
fact if you try to preview that query it won't work, it only works
when you run the form.

That's on the form. In VBA I reference the textbox as Me.GPM_Grouptxt
as in Me.GPM_Grouptxt = Me.GPM_Group_holder

I do that when I open the form so that I'm sure that the GPM_Grouptxt
has a value.

 

[CW]

Many thanks for all those examples.
In my case, the data is held on one particular form which is a job record,
and then I have another (completely unrelated) form onto which I want to pull
various bits of data.
I will want to use this particular value in a number of other areas too, so
I thought it best to use a query to pull it from the original form and then I
can re-use it wherever I need to.
CW

 

[CW]

No I am not trying to access the textbox value either through VBA or through
another control.
I am trying to access it from a query.
I want to create a query that will grab the values from this particular
unbound textbox on each of my job record forms.

Is it possible to use a query to pull the data from an unbound control on a
form (i.e. a text box)? If so, what does the syntax need to be? (Let's assume
the form is Form 1 and the control is Text 1)
Many thanks
CW

You should be able to reference the text box directly as [text 1]. Are
you trying to access the text box through VBA or just from another
control on the form?

 

[CW]

That's very helpful Ken, all clear now!
Many thanks
CW

If you simply want to include the value of the control in a column returned
by the query then put a fully qualified reference to the control in the
'field' row of a blank column in the query design grid. Precede this by the
name you want the column returned as and a colon, e.g.

MyField:Forms![Form 1]![Text 1]

The form has to be open when the query is opened and the query will need to
include at least one table which contains at least one row. If you wanted
the query to return only one row with just the one column then you can use
the DISTINCT option and any table, e.g. in SQL View:

SELECT DISTINCT Forms![Form 1]![Text 1] AS [MyField]
FROM [MyTable];

However, doing that would not make a lot of sense as you can directly
reference the control in the same way e.g. as the ControlSource of a control
on another form or in a report, or in VBA code.

Ken Sheridan
Stafford, England

Many thanks for all those examples.
In my case, the data is held on one particular form which is a job record,
and then I have another (completely unrelated) form onto which I want to pull
various bits of data.
I will want to use this particular value in a number of other areas too, so
I thought it best to use a query to pull it from the original form and then I
can re-use it wherever I need to.
CW

I did a lot of research on this last week in this usenet group, so I
thought I'd give back. Hope this example will be useful:

[quoted text clipped - 33 lines]

I do that when I open the form so that I'm sure that the GPM_Grouptxt
has a value.