Microsoft Access crashes when trying to add data to a complex form based on a 2-table query

I have been asked to fix a problem with an Access 2000 database.   It is one main table with several tables used to populate dropdown lists.  However, the table had too many fields, so it was broken into two tables with a one-to-one relationship.  The query joining these two tables is then used as the basis for a form that consists of 8 tabs.  There are also several computed fields, (mostly sums) which I have made into data with sources of the type "=a+c+d"  with a, c, and d being columns that occur in the query.

The form  seems to work for altering data already there, but when I try to add a new record, I key in the key (which is then copied to the key for the other table with a vb statement), the database terminates.  No error message, nothing in the event logs.  It just goes away.  

Any ideas?  
Who is Participating?
Leigh PurvisDatabase DeveloperCommented:

OK I'll say it. lol.. Have you tried a

* Compact & Repair  (Tools>Database Utilities)
* Decompile Start>Run then enter "MSAccess.exe"  "C:\Blah Blah\Path Tou Your mdb\YourApp.mdb" / decompile
* Importing your objects into a brand new mdb file?

There - I did it.  And with a bit of explanation too :-)
The fact that you're getting no error message is indeed an indication that this isn't a standard error (or at least isn't being handled standardly as it should be).
jpopowskiAuthor Commented:
I tried compact and repair, also creating a new mdb file (and while doing that, putting the vb code into a text file, then loading it from the text file to the new mdb.)  
Leigh PurvisDatabase DeveloperCommented:
"putting the vb code into a text file"

You mean saving your objects (modules/forms) to text - and importing them from text again (using the undocumented SaveAsText and LoadFromText methods?)

Have you done a decompile?  Just for completeness?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jpopowskiAuthor Commented:
No decompile yet.  Have to wait until I'm at work tomorrow to get the real database (I only have access 2003 at home.)
I will try it.

The only thing I saved as text was the vb code.  Then I told the form it had no module, imported it into the new mdb, and then changed it there to have a module, and put the text in place.  

I keep thinking there has to be something wrong with the form, as my attempts to recreate the problem do not crash the database.  However, it isn't obvious.

Leigh PurvisDatabase DeveloperCommented:
Well - the decompile is worth trying for sure.

Have you (in your new mdb) created a blank form.
Pasted the _text_ of the code into it's module.
Copied and pasted the _controls_ only in from your other form.  (i.e. in design view > select all, copy.  New form design view, paste).

Still the same?
It is entirely possible you have an error caused by something wrong in your form - but you should be informed of the error at least.
jpopowskiAuthor Commented:
I shall try copying the controls into a new form tomorrow and post back here.
Hello jpopowski

I'm reacting to this line:
> I key in the key (which is then copied to the key for the other table with a vb statement)

That line of VB is useless and potentially harmful. When you edit a query based on a one-to-one relationship, you need to supply the key and required fields of the primary table (the one on the left when you double-click the join in the relationship window). You are not required to enter  anything in the secondary table, but if you do, enter only the non-key required fields. The key of the secondary table should be left alone, and not even visible, of course (but included in the query).

Try this first. Let's say your tables are tblWide and tblWideContd, both with a key field klngID (same data type and length, naturally). Use this record source for a new  table:

    SELECT tblWide.klngID, tblWideContd.klngID As lngDummy, *
    FROM tblWide LEFT JOIN tblWideContd
    ON tblWide.klngID = tblWideContd.klngID;

This is simple left join, with an alias for the second key to avoid any naming conflict.

Enter data in this query. That should work. Progressively add your calculated fields at the end and test adding new records after each new one. That should be ok as well.

Create a form based on that, and verify that editing and adding still works.

Meanwhile, create a copy of your problem form and simplify. Start by removing the module entirely, for example. By approaching both forms to one another, you should be able  to identify the problem.

If not, take another  approach: use subforms.

Base your main form on the primary table alone, keeping all tabs that show fields from it. For the tabs showing fields from the secondary table, use subforms instead. There should be no difference to the user, as the subforms would be single-record and without any visible border.

This would simplify the task for the JetEngine, both in terms of the number of  fields in the queries and in terms of relational integrity.

Hope this helps!
jpopowskiAuthor Commented:
To LPurvis,
OK, I've tried the decompile.  When I try to add new records, access still crashes.

To harfang,
I've tried removing the second key field from the form.  That also doesn't matter.
I can enter data into the query if I just call it up in spreadsheet format.  

I'm now about to take the form and delete a few fields to see if I'm running into a an Access limit.
Leigh PurvisDatabase DeveloperCommented:
I doubt that it's a limit as such - it would just refuse.

So you have a single query where you can add records?
And if you base your form on that query?
Same results?
jpopowskiAuthor Commented:
To all of you who have made suggestions.  I've tried everything here, and still have the same issue.  I finally reached the original developer who told me it never worked (!) and the way to get around it is to put the key into the table manually, then use the form to enter other data.  

The users would rather live with that than have me create an application using mySQL which was the next step, so I'm dropping this issue for the moment.  

I still think there's an access limit we're hitting somewhere--the form has 250 controls or so (the query has 180) and 10 tabs.  However, I have to leave it for an academic puzzle for the moment.

Thanks for all your help.
Hello jpopowski

Sometimes it's a good idea to give up, but I couldn't... ;)

If you are looking for a workaround, create a button "New Record", that will ask for the new ID (if it's a manual ID key) and then create the records in both tables. A requery of the main form and a repositionning would then show the new blank record to the user.

If you need help with that, we can walk you through.

Leigh PurvisDatabase DeveloperCommented:
I wouldn't be able to stop either. ;-)
(Even when common sense says to - and if it takes ages.  Time poorly spent?  Perhaps - but when it occurs again, maybe even years down the line, you don't miss a beat :-)

(I still doubt it's an Access limit though - unless your database is about 2GB!).
jpopowskiAuthor Commented:
I also don't like to give up, but my boss says it's time to move on.  Therefore, I can only look at this as an academic exercise on my own time--which I may do.

How can I award points to both of you to express appreciation for all the help so far?  Will the accept button work twice?  
No, you will need to use the "split" link right above the comment entry box.
Good luck with your form!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.