Discussion Locked

Access 2000: Bad event procedures

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Here's the type of error that really makes me hate Access 2000 (or rather my knowledge of Access 2000): I'm working on a database that keeps track of computers that are built along with their serial numbers, components, who they were sold to etc. Since I am modifying an existing database, I made a copy of the original and started working on it. Everything was working just fine and dandy, and all my procedures worked just fine. However, when I tried to copy the newly finished form (with all its related code) into the original database, none of the form events (on Load, on Click, etc) would work. I now receive errors that say, "The expression On Click [or any other event] you entered as the event property setting produced the following error: User-type not defined". This puzzles me because all the VB code is there, exactly as it is in the previous copy of the database (where it worked seemingly flawlessly). I also made sure to copy all relevant module files into the original database soall the code would work correctly.

Any ideas anyone? Thanks.

5 replies

by sl-campbell

VBA is sensitive in this matter.
1. You can try to click each event new and see if the code window goes to the correct proceedure. As long as it does, do this for each event, close Access, and the re-open it again. Try this for one (smaller) form first to see if it works.

OR

2. You can remove the form from the original MDB and IMPORT the form from the copy MDB. This should work the best and easiest.

by shanghai sam

Thanks. Although your methods didn't work directly, it did help me to remember that I can import tables from one database into another, so that's what I did. Rather than import the form (which I tried and didn't work) into the original, I imported the tables from the original into the new database, rebuilt the relationships, and PRESTO! it worked. Thanks for the reminder about importing things.

by bob sellman

The first question I have is: is your application separate from your database? Based on your description of the effort you are going through, it sounds like you have not followed an important design rule in setting up your database application: Separate the database (call it the back end) and the application (call it the front end).

You can do this easily by following these steps:

1. Copy the original database (with everyone off of it) to two new files, (I'll call one MydbBE.mdb and theother Mydb.FE.mdb.)

2. Delete everything (reports, forms, macros, modules, etc.) from MydbBE.mdb EXCEPT the tables. (Obviously, compact the database.)

3. Delete all tables from MydbFE.mdb. Then click on Files | Get External Data | Link and link to the tables in MydbBE.mdb. (And compact this database.)

Since you have made changes, take your modified database, rename it to MydbFE.mdb, delete the tables, and link to the tables in MydbBE.mdb (and compact the database). Then, after renaming the working version of MydbFE.mdb, just copy your new version of MydbFE.mdb over the location of the previously working version of MydbFE.mdb.

(When you are doing your development effort using a copy of the current MydbFE.mdb, just link to a copy of MydbBE.mdb for testing.)

This method is much cleaner and easier than trying to copy a new form, report, etc., into an existing application database. At the same time, you avoid modifying any "real" data, yet you can fully test the newversion before letting the users use it. Best of all, if you keep a copy of the previous version that worked, you also have an emergency fall back in case of unexpected problems.

One other item: If your application has control tables that contain data used to control the application, rather than containing data entered or viewed by users, those tables might be appropriate for the front end application database.

by shanghai sam

I really like your method in terms of program design, and so I will definitely consider this approach in the future. I like having the two separated like that and I may yet try this construction (once I get over the fear of figuring out what connection string to use when calling data from MydbBE.mdb using ADO) in this one. However, at the moment my database and application are not separate and reside in a single database file in Access. Clumsy, I know, but it is an acceptable temporary solution. I inherited this database and so have to make it work as best as I can in the short-term while I add functionalities and shift the focus from Access-controlled actions to VBA-controlled actions. Thanks again for your thoughtful answer.

This discussion is closed to new replies.