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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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.
 
Back
Top