Copying Forms From One Database to Another and Back Changes Them

  • Thread starter Thread starter Stewart Berman
  • Start date Start date
S

Stewart Berman

Simplified Version:

Access 2007

Create a table with an automumber ID Primary Key and three text fields: Field1, Field2, Field3
Add a few records to the table.

Use the wizard to create a datasheet form (call it form1) based on the table.

In the Form_Current event put:
Private Sub Form_Current()

MsgBox "Field1: " & Me.Field1 & ", Field2: " & Me.Field2 & ", Field3: " & Me.Field3

End Sub

When you open the form you get the message box for the first record and if you click on another
record you get the message box for that record.

Now open the form in design mode and delete the Field3 textbox (and label). Save the form and open
it in form mode. You still see the message box for each record. If you go into the immediate
window and type Me. you will see all three fields listed.

Now close the form.

Open another database and import form1 form the first. Now import form1 back into the first
database from the second. It will come in as Form11. Open the form and it will throw a compile
error flagging Me.Field2 as missing. But if you go into the immediate window and type Me. you will
find Field2 but not Field3.

My questions are:
1. Should Form1 have still worked after deleting Field3 (the textbox and label) from the detail
section?
2. If Form1 should still work then why doesn't Form11 (the one that was copied back and forth
between database1 and databse2) work?
3. If the forms shouldn't work because the Field3 textbox was deleted why is Field2 flagged as
missing in form11?

We hit some of these problems because we are doing distributed development on a large Access 2007
application. To minimize collisions each developer "owns" the objects they are working on. We
integrate them back together by having each developer copy the objects that are ready for
integration from their development database to a new small database that then only has those
objects. That database is then sent to the person responsible for integrating the changes.
Periodically, the integrated database is sent out to the developers to use as a new baseline.
Unfortunately, this results in some odd behavior where an object works fine before being copied but
not after. It appears that the only way to safely transport objects is to use saveastext and
loadfromtext as that appears to stop the objects from changing by being copied.

By the way a comparison of form1 and form11 text files produced by saveastext shows differences in
GUIDs and in one line in the NameMap.
 
I believe you've struck a bug in the way Access handles objects of type
AccessField. It's not uncommon.

To see what's going on, open the Immediate Window (Ctrl+G) and enter:
? TypeName(Forms!Form1!Field2)
Assuming Form1 is open and has a text box named Field2, Access will respond
with:
TextBox

Now delete the text box. The field named Field2 still exists in the form's
RecordSource, but there's no text box with that name. Repeat the same
question in the Immediate Window. This time Access will respond with:
AccessField

In my experience, Access handles objects of text box reliably, but objects
of type AccessField are unreliable. You gave an example, where the form
imported into another database fails to recognise the reference. The same
thing can happen when you convert to a different version of Access. In fact,
code such as:
Me.Field2
may suddenly stop compiling! (Me!Field2 will always compile as it is checked
at runtime, not compile time.)

But the bug is worse than that. In some cases if you name an object of type
AccessField in the LinkMasterFields/LinkChildFields properties of a subform
control, it can actually crash Access ("shut down by Windows...")

The problem is also exacerbated by Name AutoCorrect, where the actual name
of fields can get quite muddled (with altered field names still appearing
under old names, and JET confusing captions with existing/past fieldnames,
and ....)

My recommendation is:
a) Avoid using the buggy AccessField type. Always put a text box on the form
if you need to refer to it.

b) Make sure Name AutoCorrect is off.

Some developers advise to use a different name for your text boxes and for
the fields they are bound to (e.g. txtCity for the text box bound to the
City field), and then use the text box name, not the field name. Personally,
I don't find that necessary.

For other suggestions on avoiding corruption see:
http://allenbrowne.com/ser-25.html
The point about the AccessField appears under #3 of "Preventing corruption
during development." [The bug a bit more complex than the article describes:
seems to be more prevalent when the subform is bound to a multi-table query,
occurs only in Access 2002 and later, etc.)

HTH.
 
Thanks for the background.
a) Avoid using the buggy AccessField type. Always put a text box on the form
if you need to refer to it.
Click to expand...

Can you can around that by referencing the field directly in the form's recordset object:
Form1.RecordSet.Fields("Field3").value
b) Make sure Name AutoCorrect is off.
Click to expand...

That is the first thing that gets turned off in a new database. I think that was added to Access as
a way of identifying novice developers. If you open an Access database and find that switched on
you know you are going to have to deal with code written by someone with very little real world
experience.
For other suggestions on avoiding corruption see:
http://allenbrowne.com/ser-25.html
Click to expand...

Again, thanks.

Allen Browne said:
I believe you've struck a bug in the way Access handles objects of type
AccessField. It's not uncommon.

To see what's going on, open the Immediate Window (Ctrl+G) and enter:
? TypeName(Forms!Form1!Field2)
Assuming Form1 is open and has a text box named Field2, Access will respond
with:
TextBox

Now delete the text box. The field named Field2 still exists in the form's
RecordSource, but there's no text box with that name. Repeat the same
question in the Immediate Window. This time Access will respond with:
AccessField

In my experience, Access handles objects of text box reliably, but objects
of type AccessField are unreliable. You gave an example, where the form
imported into another database fails to recognise the reference. The same
thing can happen when you convert to a different version of Access. In fact,
code such as:
Me.Field2
may suddenly stop compiling! (Me!Field2 will always compile as it is checked
at runtime, not compile time.)

But the bug is worse than that. In some cases if you name an object of type
AccessField in the LinkMasterFields/LinkChildFields properties of a subform
control, it can actually crash Access ("shut down by Windows...")

The problem is also exacerbated by Name AutoCorrect, where the actual name
of fields can get quite muddled (with altered field names still appearing
under old names, and JET confusing captions with existing/past fieldnames,
and ....)

My recommendation is:
a) Avoid using the buggy AccessField type. Always put a text box on the form
if you need to refer to it.

b) Make sure Name AutoCorrect is off.

Some developers advise to use a different name for your text boxes and for
the fields they are bound to (e.g. txtCity for the text box bound to the
City field), and then use the text box name, not the field name. Personally,
I don't find that necessary.

For other suggestions on avoiding corruption see:
http://allenbrowne.com/ser-25.html
The point about the AccessField appears under #3 of "Preventing corruption
during development." [The bug a bit more complex than the article describes:
seems to be more prevalent when the subform is bound to a multi-table query,
occurs only in Access 2002 and later, etc.)

HTH.
Click to expand...
 
Stewart Berman said:
Can you can around that by referencing the field directly in the form's
recordset object:
Form1.RecordSet.Fields("Field3").value
Click to expand...

Certainly worth investigating. You're then dealing with an object of type
Field, so (if I'm right and the bug is with the AccessField handling) you
may well circumvent the problem.
 
Your web site is quite impressive and contains some interesting items.

You might want to add the problem in message:
(e-mail address removed)
To your knew bug list. I eventually got a response from someone in
Microsoft that they agreed it was a bug but I think that was in one of the
Microsoft online forums and I can never find my way back in those. Not that
it matters as they didn't provide anyway to track the bug.

Also,
See the Errors section of http://support.microsoft.com/ph/11265
The page doesn't have an errors section.
 
Back
Top