MS Access - Subform causes Access to Abort.

[ajaac]

Has anyone else experienced this problem? Or have a suggustions?
I've been building system for a long time and never seen this before!

Problem description:

I have an access form with a subform, the main form displays a
contract recrod and has a unique field named contractID, the subform
is a list of records associated with this contract, each record
contains a contractID field.
If I set the Link Child Field to ContractID and the Link Master Field
to ContractID, This is the normal way to link a subform to the main
form. But I'm experiencing an abort on some contract records.

First thought was a corrupt app or backend db.
I've created a new frontend app and imported all of the objects, then
compact and repaired the backend, with no change.

To verify the that it was corrupt data, I did the following.

I then created an unbound field (Test) on the main form and changed
the subform Link Master Field to use this field.
I then opened the form, moved to an offending contract record and
manually entered the contractID into the test field, and it worked!.
So the data is not corrupt.

So I added a statement to the on current event: Me!Test = me!
ContractID.
And this works OK, So now I have a workaround.

But I would really like to know what's happening behind the scenes.

 

[Guest]

Is the problem happening on the same records every time? If so it must be
something to do with the data.
If you remove the subform, do you get any errors?
Open the tables to see if there is anything unusual about the offending
records.
Have you checked how the relationships are defined between the two tables?
On a record which gives an error, are there corresponding records which
should appear in the subform?
What is the error message?
Have you tried doing a ctrl-break at the error to see where the problem is
happening?
Not sure if any of these are the correct path.

James

 

[ajaac]

Thanks for the reply James.

The error was the crash screen, "Do you want to send your error to
microsoft."
There was no way to break on the error. No error message.

The work around I thought worked, didn't. It would allow me to
display a record that kept crashing when the master link field was
contractid. but when i move to any other record, after displaying said
record, it would crash.

I checked the data, I found 2 records that it would crash on, and I
eyeballed them, could see nothing unusual.

If I removed the subform it worked fine. If I just removed the link
it worked.
I could edit the records in question in the subform when opened as a
standalone form with out a problem.
I could view the records in question in the main form as long as there
was no link to the subform.

I recreated the main form, and the subform, still had the problem.

I upgraded the db from 2K to 2003, still had the problem.

I decompiled the db. Same problem.

The subform data source was a table. I changed this to use a sql
statement instead and it works Great!!!
My question now is why? Why would a sql statement work when a table
wouldn't? Nothing special about the sql statement. Just a Select *
from table.

I spent about 3 hours on this today and since the sql statement
worked, I'm giving up.

I would like to know what's going on, but I just don't have the time
to play with it.

I would guess the problem has to be in the table, a little unusal when
you can view all the records in the subform when opened as a form, but
not when linked as a subform. And when the datasouce is a query it
work, would lead one to believe that something strange is going on
with the internal linking that exposes some issue with an index or
something. I will rebuild the table before I deliver the application,
just in case.

 

[Guest]

Your response makes me think the problem has something to do with the primary
key of the table in the subform.

If the relationship is not defined correctly or something, or your query may
correct a Null and allow it to show fine. It would also open fine in the
subform only if this were the problem.

If the Link Child Fields/Link Master Fields are incorrect.

Have you checked the primary key and the foreign key to make sure they have
the same Data Type for the primary/foreign key.

Maybe something here amongst the haystack.
James