Reference forms and fields with variables

  • Thread starter Thread starter Seth Schwarm
  • Start date Start date
S

Seth Schwarm

How can I reference a form and field with variables?

My current example is this:

my form is: fdlgSubmission

my field name is: NEPoint

I have dimensioned my variables as follows:

frm1 as Form
fld as Field

I set the value to the form variable as follows:

Set frm1 = Forms!fdlgSubmission
It works great when referencing the form and when
referencing a field such as frm1!NEPoint.

However, I want a variable to store a field by this
method:

Set fld = frm1!NEPoint

When I attempt this, I get a Run-time error '13' Type
mismatch error.

I have also tried this method:

Set fld = Forms!fdlgSubmission!NEPoint

It does not work either.

I am writing a Select statement and need to assign this
field value to a variable to have code following this
Select statement use it and complete my process.

Thanks,

Seth
 
It may seem ambiguous, but you have controls on your form (text boxes, etc),
and fields in its Dynaset or RecordsetClone.

The reference:
frm1!NEPoint
is probably referring to a TextBox object, not a Field object, and so you
receive the type mismatch.

You could solve that by dimensioning:
Dim txt As Textbox
and then:
Set txt = frm1!NEPoint

However, you may not need to create variables for all your controls. Once
you have a reference to the form, you also have a reference to all its
fields and controls and properties and methods and ... So you can assign a
value to a text box just by entering:
frm1!NEPoint = 999

The next example loops through all the controls on a form:
Dim frm1 As Form
Dim ctl As Control
Set frm1 = Forms!fdlgSubmission
For each ctl in frm1.Controls
Debug.Print ctl.Name, ctl.ControlType
Next
Set frm1 = Nothing
 
Allen:

What exactly does my reference of Field refer to? You
make good sense about the Textbox and my code now works.
I just don't understand why anyone would ever use the
Field reference.

Thanks,

Seth
 
There are so many ambiguities here, that it may not really help to follow
them all.

If Field2 is a field in the underlying recordset, and it is NOT represented
by a control on a form, then:
Me.Field2
is an object of type AccessField. Can't tell you much about this object as
it is undocumented, and it has only one property, Value, so it is not very
useful. The implementation of this object seems to be buggy: occassionally
Access just loses the object, and Me.Field2 won't compile. Me!Field2 (which
is not checked by the compiler) still works, so the object is still there
but the reference is unstable. If you reassign the form's RecordSource after
it is open, it messes things up also, but the scenario is not limited to
that. (There is a possibly related bug in Access 2002 and 2003, where they
crash (closed by Windows) intermittently if a field nominated in the
LinkChildFields property of a subform is NOT represented by a control in the
subform, i.e. it can crash if it refers to an AccessField not a Control.)

More useful is the Dynaset of the form, which contains a collection of all
the Fields. So:
Me.Dynaset.Fields!Field2
is a useful reference. It will tell you properties about the data type of
the field, which table it is written to (useful if the form is based on a
multi-table query), whether the field is Required and so on.

You can also get some information on the fields in the form's recordset
through its RecordsetClone, i.e.:
Me.RecordsetClone.Fields!Field2

In reports, the reference:
Me.Field2
often fails to work if Field2 is not a control on the report. I've not been
able to make any sense of the conditions when this occurs.

All objects in Access have a default property or collection, and Access will
refer to that if the context demands it. For example, the default property
for text boxes is Value, so if you code:
If IsNull(Me.txt1) Then
Access recognises that IsNull() does not apply to text boxes, and so
automatically interprets the code as:
If IsNull(Me.txt.Value) Then

This means that when you type a simple reference such as:
Me.XXXX
Access can interpret the "XXXX" in *many* different ways, e.g.:
- a member of the form's default collection - Controls, e.g. a text box;
- the default property of the control (i.e. its Value);
- an AccessField (when there is no control by that name);
- the default property of the AccessField (i.e. its Value);
- a property of the form itself (e.g. .if you have a text box called Name,
but Me.Name refers to the name of the form);
- a variable in the form's module;
- an event procedure in the form's module;
- ...
and it gets worse if you omit "Me." because the ambiguity extends to
everything else in scope.
 
Back
Top