Okay, Brian, that's a good explanation.
From what you describe, you are expecting an error message, but not the
one
that is returned. The reference to "Forms" in the misleading error
message
is interesting, as the form's RecordSource does have such as reference.
This
suggests the possibility that Access (perhaps the Expression Service) is
not
able to interpret the error correctly. You could test if that's what's
going
on by temporarliy replacing the [Forms]![Form1]![Field1] in the
RecordSource
statement with a literal value. If the correct error is then returned,
the
issue is with Access/ES not interpreting correctly.
If that works, you might be able to workaround the issue by assigning a
string containing the literal value to the RecordSource of Form2 in the
Current event of Form1, so as to avoid the [Forms]![Form1]![Field1]
reference. (I've always found that kind of reference to have side
effects.)
HTH
I have verified that the record is not dirty, and I already had
AutoCorrect
turned off. I decompiled/recompiled to no effect.
Curiously, the correct errors fire when I attempt to delete one level
up
in
the relationship scheme. Here is the scenario:
Table1 -> Table2 -> Table3 (one-to-many left to right), referential
integrity turned on. Table 1 is the recordsource of Form1, Table2 of
Form2,
and Table3 of Form3.
When deleting a Table1 record from Form1 with cascade delete turned OFF
for
the Table1/Table2 relationship, it correctly generates a 3200 error.
When
cascade delete is turned ON, it correctly generates a 3396 error based
on
the
non-cascade-delete Table2/Table3 relationship. However, when I open
Form2
and
attempt to delete a Table2 record that has related Table3 records, I
get
the
2465 error, but only when cascade delete is turned off for the
Table2/Table3
relationship.
When cascade delete is turned on for both relationships, everything
deletes
correctly without error, but that is not what I want to do. I really
want
to
trap the 3200/3396 errors and inform the user.
FYI, here is the record source of Form2.
SELECT Table2.*
FROM Table2
WHERE (((Table2.Field2)=[Forms]![Form1]![Field1]))
ORDER BY Table2.Field1, Table2.Field2;
Having said all of this, I went through a bug hotfix process with MS
last
year on a closely-related scenariothat arose in SP3 of Office XP. The
same
2465 error occured any time the last record (based on the sort order
of
the
form, not the table's sort order) in the recordset of a form was
deleted.
MS
eventually issued a hotfix msaccess.exe for me that resolved this
issue. I
have since uninstalled Office & upgraded to Office 2003 developer in
the
hopes that this and some other issues would be resolved.
:
Brian, that's certainly odd.
Firstly, check that the record is not dirty before running the code:
If Me.Dirty Then Me.Undo
Particularly if the form is based on a multi-table query, that could
make
a
difference. (There's a related bug when the form's recordsource
contains
a
lookup table, and one of the fields in that table has a default value
set.)
If that doesn't help, make sure the Name AutoCorrect boxes are
unchecked
under:
Tools | Options | General
This "feature" is extremely buggy, and Access is likely to get
confused
about what is named what. The error message you received suggested
that
it
is confused between the Forms collection and the name of a field.
After turning off Name AutoCorrect, compact the database:
Tools | Database Utilities | Compact
Then decompile. Close Access. Make a backup copy of the file.
Decompile
the
database by entering something like this at the command prompt while
Access
is not running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"
Then compact again.
If the problem persists after that, post back for further suggestions.
More info on the Name AutoCorrect problems:
http://allenbrowne.com/bug-03.html
Access 2003 (developer)
I am getting a 2465 error ("...unable to find the field 'Forms'
referred
to...") on this code (which runs on the click of a delete button on
a
form):
ButtonDelete_Click
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
It happens only when referential integrity is enforced for the
relationship
between the table that is the recordsource of the form & a table
containing
child records but cascade delete is turned off. Enabling cascade
delete
eliminates the error. Why is it not generating the correct error
(3200
or
3396) that indicates it cannot delete the record because there are
related
records?
