Opening concurrently two forms based on same tables crash the database

Question

The two major entry forms in my database are based on two different queries from the same set of tables (note: mostly the same, except for there is one forecast result table for each of the scenarios below):

One form is for forecasting by customer, the other for forecasting by product.

On the ByCustomer form, a forecaster has to choose Region;

On the ByProduct form, a forecast has to choose region first, then there are two more cascade combo boxes for them to choose Division and ProfitCenter.

I just completed both form, and now each of the two works well individually. But I realize that when I have the two forms open at the same time (which will be the case for the end users), and after I start to pick an item from the combo box on whatever is the 2nd form, the database crashes right away (closes out and restarts by itself).

I tried this many times, each time the same thing happen. Also, somehow the system created a "Backup" file, and a "Record-Lock Information" file.

How can I solve this? Intuitively I don't think this should happen - the two queries should do their job separately, then be reflected on the two forms separately. Why do they conflict?

Thanks very much in advance.

p.s. More information: 1) the "Region" combo box on both form share the same query for sorting out regions. 2) I made one form first, then copied and pasted that form to make the 2nd form, by changing the control source for the subform on the form and child/master links. In case the info helps.

Answer 1

Hmmm, never had that happen BUT also never ran two seperate forms trying to update both at the same time on the same fields and perhaps that is the problem?  You are, in effect, trying to change the Region in one form one way and then changing it another form the other way.  Let's start with this...

1. What is the RecordSource of each Form?  Query or are you basing both off the entire Table?
2. What is your Table(s) like?  Need to know the breakdown

     a. Did you set SubDataSheet to [NONE]? 

     b. Are you using Look-Up Fields (Table based) or seperate Tables to provide the data for you *look-up* fields*?

As for the *backup* file, yes, Access will automatially create one upon locking up.  It will *hold* the lock file because it was not closed *properly*.  You should be able to open Access with no problem, if you can't then try deleting the lock file.  (Make sure you keep a back-up of your own on hand.)

Answer 2

While I am thrilled you solved it by yourself what you did orginally shouldn't have caused a crash... a freeze perhaps but not a crash.  Anyway, alls well that ends well, problem solved and you're a happy camper!

Answer 3

Hmmm, never had that happen BUT also never ran two seperate forms trying to update both at the same time on the same fields and perhaps that is the problem?  You are, in effect, trying to change the Region in one form one way and then changing it another form the other way.  Let's start with this...

 

1. What is the RecordSource of each Form?  Query or are you basing both off the entire Table?
2. What is your Table(s) like?  Need to know the breakdown

     a. Did you set SubDataSheet to [NONE]? 

     b. Are you using Look-Up Fields (Table based) or seperate Tables to provide the data for you *look-up* fields*?

 

As for the *backup* file, yes, Access will automatially create one upon locking up.  It will *hold* the lock file because it was not closed *properly*.  You should be able to open Access with no problem, if you can't then try deleting the lock file.  (Make sure you keep a back-up of your own on hand.)

Wow, finally I did something even a MVP never did before! :P

Thanks Gina for your reply. The RecordSource for each form is a different query. One query is based on tblActualByProduct and tblForecastByProduct, the other is based on tblActualByCustomer and tblForecastByCustomer (these are not exactly the names in my database, but you probably get what I mean from these names). The two actual tables are data from two sets of queries that based off the same set of downloaded ERP data (as you can imagine, our business wants to see the sales results from two different views, by product and by customer).

In my database I do not have look up fields, and the Subdatasheet property is never modified.

I haven't got any reply for a long while after I posted this question, so I tried a few routes by myself. One thing I did actually seems having solved the problem?! To share with you here and you probably understand why that is since know Access inside out. I got the result for now but I can't explain the reason:

I made a copy of the query that is the RecordSource for the "region" combo (as you remember I have that combo on both forms). Then I made that copy to be the recordsource of the 2nd form. And now the problem is solved magically, heehee????!!!!

The "region" combo box was the only element on these two forms that originally shared the same recordsource. Seems like the lesson I learned from here is that don't use same recordsource if you need two or more forms open and modified at the same time.....