referencing through a procedure

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

Guest

I have a sub that opens a table gets a number of form names and open each
form to add a new record in that form. So I do this

docmd.openacform(stdoc1)..... which works

I then want to get to the form and change a field ie.
forms(stdoc1).contactid = X

this does not happen WHY?
Is there a better way to make it happen
 
I'll take a cut at this one but you may have to post back with a more
complete explanation of what you're doing and why.

My first thought is "Why bother with the form at all"? Your procedure
is already gathering the data. The frorm is going to be based on one
or more tables. Why not just open the affected recordset and add the
record with code?

That being said, it seems that what you are doing shouldn't really be
done in an RDBMS and most specifically not in Access. You are using
data from somewhere in your database to produce a
predictable/calculated result in another part of your database. By
doing that you increase your storage requirements and, more
impotantly, you risk losing synchronism between the data in different
parts of your database. Instead you should always calculate each time
you need to display the result rather than store it.

HTH
 
I then want to get to the form and change a field ie.
forms(stdoc1).contactid = X

this does not happen WHY?
Is there a better way to make it happen
Click to expand...

First of all, I'd like to reinforce much of what Larry says: this is not
a very database-y sort of thing to want to do.

Nevertheless, there is a reall programming problem here. VBA has two ways
of reaching an object's members. The dot operator simply goes to a
property by the name that follows: AForm.Caption or
MyClass.PublicProperty and so on. It also has a shriek operator, which
accesses a member of the *default collection* of the object. For example,
the default collection of the Forms object is .Item, so
Worksheets!Eric
is the same as
Worksheets.Item("Eric") or simply Worksheets("Eric")

Now, Access forms do some funny things. First of all, the Controls
collection is the default collection of the Form object, so
MyForm!MyComboBox
is the same as
MyForm.Controls("MyComboBox") or (theoretically) MyForm("MyComboBox")

A bound form also exposes form *properties* for the fields in its
recorsource, so
MyForm.AverageAge
is the same as
MyForm.Recordset.Fields("AverageAge") or MyForm.Recordset!AverageAge

Starting to make sense? Wait for it! What happens when a control is bound
to a data field and both of them have the same name? You might think that
MyForm!AverageAge
and
MyForm.AverageAge
would both point to the same value, which is the value in the field
referenced by the control. Or the other way round... Well, so they do,
except sometimes when they don't. I really don't understand why the
relationship breaks down, but the bottom line is that every now and then
you don't get what you thought you would.

The workround is always to give your controls different names from the
data fields they apply to. If the field is AverageAge, then make the
textbox txtAverageAge. If it's ExternalColourCode, then make the listbox
lstExternalColourCode, and so on. At least then you know exactly what you
mean when you write either MyForm!ExternalColourCode or
MyForm.txtAverageAge. Perversely, although they are wrong these still
seem to parse and lead you to the table field or the text box
respectively, which just goes to show how forgiving the Access platform
is. And not a little bit confusing!

In your case, I'd make everything very explicit and change the whole
assignment to something like

Forms(stDoc1).Controls("ContactID").Value = "X"

just to make absolutely sure. And don't forget to do a record save
afterwards.
Forms(stDoc1).Dirty = False

But I still think I'd do whatever you need to do by direct data access so
that the user does not get a migraine from all the numbers jumping about
outside his or her control. Or just check the design and avoid the
copying in the first place.

Hope that helps


Tim F
 
Back
Top