#Name? errors when using query as data source for form

I have a simple query that retrieves a few fields from an MSDE database that
is connected via linked tables. When I run this query in datasheet view, it
works fine, no problems. However, when I use this query as the data source
for a form (the form has a text box for every field in the table) I get a
#Name? error for every field that is not included in the query.

I suppose I could use the datasheet view, but I have yet to find a way to
keep users from copying data in datasheet view. I have checked, and the
fields are not null, so I think the problem is simply that the form can't
identify a data source for those fields.

I was thinking it might make sense to select all the text boxes on the form
and simply set them to invisible rather than having the user see #Name?
errors. Does anyone know a way to select only the controls on the form that
are not being used by my query (or just to get rid of the #Name? errors)?

Any help would be appreciated.

Thanks,

Chris

The following code loops through the Controls collection of the form, and
for each control loops through the Properties collection of that control
looking for a ControlSource property (this is to avoid trying to reference
the ControlSource property of control types, such as line or label, that
don't have a ControlSource property). If the code finds a ControlSource
property, it checks to see if the ControlSource is an expression (begins
with "="). If not, it then loops through the fields in the form's recordset
looking for a field name that matches the ControlSource property. If it
doesn't find one, it sets the ControlSource property to an empty string.

Private Sub Form_Open(Cancel As Integer)

Dim ctl As Control
Dim prp As DAO.Property
Dim boolFound As Boolean
Dim fld As DAO.Field

For Each ctl In Me.Controls
boolFound = False
For Each prp In ctl.Properties
If prp.Name = "ControlSource" Then
If Left$(prp.Value, 1) "=" Then
For Each fld In Me.RecordsetClone.Fields
If fld.Name = prp.Value Then
boolFound = True
Exit For
End If
Next fld
If boolFound = False Then
prp.Value = ""
End If
Exit For
End If
End If
Next prp
Next ctl

End Sub


--
Brendan Reynolds


"Chris Burnette" wrote in message
...
I have a simple query that retrieves a few fields from an MSDE database
that
is connected via linked tables. When I run this query in datasheet view,
it
works fine, no problems. However, when I use this query as the data
source
for a form (the form has a text box for every field in the table) I get a
#Name? error for every field that is not included in the query.

I suppose I could use the datasheet view, but I have yet to find a way to
keep users from copying data in datasheet view. I have checked, and the
fields are not null, so I think the problem is simply that the form can't
identify a data source for those fields.

I was thinking it might make sense to select all the text boxes on the
form
and simply set them to invisible rather than having the user see #Name?
errors. Does anyone know a way to select only the controls on the form
that
are not being used by my query (or just to get rid of the #Name? errors)?

Any help would be appreciated.

Thanks,

Chris

Every Field you want to use as the ConntrolSources for the Controls on the
Form (or part of the ControlSource in case of Calculated Control) must be
include in the Select clause of the Query being used as the RecordSource for
the Form.

Remember that the Form get the data via the Query so if the data is not in
the Query, the Form won't find it.

--
HTH
Van T. Dinh
MVP (Access)



"Chris Burnette" wrote in message
...
I have a simple query that retrieves a few fields from an MSDE database
that
is connected via linked tables. When I run this query in datasheet view,
it
works fine, no problems. However, when I use this query as the data
source
for a form (the form has a text box for every field in the table) I get a
#Name? error for every field that is not included in the query.

I suppose I could use the datasheet view, but I have yet to find a way to
keep users from copying data in datasheet view. I have checked, and the
fields are not null, so I think the problem is simply that the form can't
identify a data source for those fields.

I was thinking it might make sense to select all the text boxes on the
form
and simply set them to invisible rather than having the user see #Name?
errors. Does anyone know a way to select only the controls on the form
that
are not being used by my query (or just to get rid of the #Name? errors)?

Any help would be appreciated.

Thanks,

Chris

Thanks guys. It seems like the simplest solution is just to create a new
form for each query, rather than trying to use one form to do everything.

Thanks,

Chris

"Van T. Dinh" wrote:

Every Field you want to use as the ConntrolSources for the Controls on the
Form (or part of the ControlSource in case of Calculated Control) must be
include in the Select clause of the Query being used as the RecordSource for
the Form.

Remember that the Form get the data via the Query so if the data is not in
the Query, the Form won't find it.

--
HTH
Van T. Dinh
MVP (Access)



"Chris Burnette" wrote in message
...
I have a simple query that retrieves a few fields from an MSDE database
that
is connected via linked tables. When I run this query in datasheet view,
it
works fine, no problems. However, when I use this query as the data
source
for a form (the form has a text box for every field in the table) I get a
#Name? error for every field that is not included in the query.

I suppose I could use the datasheet view, but I have yet to find a way to
keep users from copying data in datasheet view. I have checked, and the
fields are not null, so I think the problem is simply that the form can't
identify a data source for those fields.

I was thinking it might make sense to select all the text boxes on the
form
and simply set them to invisible rather than having the user see #Name?
errors. Does anyone know a way to select only the controls on the form
that
are not being used by my query (or just to get rid of the #Name? errors)?

Any help would be appreciated.

Thanks,

Chris

It's certainly possible to re-use one form or report to display the result
of a group of very similar queries. But if there are significant differences
between the queries, then yes, it becomes simpler to use different forms and
reports.

--
Brendan Reynolds


"Chris Burnette" wrote in message
...
Thanks guys. It seems like the simplest solution is just to create a new
form for each query, rather than trying to use one form to do everything.

Thanks,

Chris

"Van T. Dinh" wrote:

Every Field you want to use as the ConntrolSources for the Controls on
the
Form (or part of the ControlSource in case of Calculated Control) must be
include in the Select clause of the Query being used as the RecordSource
for
the Form.

Remember that the Form get the data via the Query so if the data is not
in
the Query, the Form won't find it.

--
HTH
Van T. Dinh
MVP (Access)



"Chris Burnette" wrote in
message
...
I have a simple query that retrieves a few fields from an MSDE database
that
is connected via linked tables. When I run this query in datasheet
view,
it
works fine, no problems. However, when I use this query as the data
source
for a form (the form has a text box for every field in the table) I get
a
#Name? error for every field that is not included in the query.

I suppose I could use the datasheet view, but I have yet to find a way
to
keep users from copying data in datasheet view. I have checked, and
the
fields are not null, so I think the problem is simply that the form
can't
identify a data source for those fields.

I was thinking it might make sense to select all the text boxes on the
form
and simply set them to invisible rather than having the user see #Name?
errors. Does anyone know a way to select only the controls on the form
that
are not being used by my query (or just to get rid of the #Name?
errors)?

Any help would be appreciated.

Thanks,

Chris