Form Entry Error: "DB could not lock...."

[Steven547]

Hi there... hope everyone had a great holiday!

I have a form that is used for data entry. There are 3 combo's which are Company, SubCompany, Department. They are all in relation to each other. (Pick one, the other combo requeries...)

However, there might be a time when the user wants to add a NEW company, subcomp, dept to the form and it is not listed in the combo boxes. So, I developed another form which allows the user to enter that new information. That command link is on the SAME form as the data entry.

When they click that "add new" button, the form pops up, and they can enter the information, but when they click Save, I get the "halt" error message and another message saying:
"The database engine could not lock table 'tblXREFcompany' because it is already in use by another person or process."

What does this mean and how can I avoid this or fix this? Thanks.

 

[randy700]

One possible solution...

In the second form's open event, close the first form because it is using the tables you want to update.

In the second form's close event, open the first form - the combo boxes will include the new data.



Randy

 

[Steven547]

I was thinking that as well, and I believe that is the issue. I would like to let the user stay in the form though without closing out. My one thought is to have an entry into a "temp" table, which then populates the main tables through a macro.

update qry: grabs info from form -- update to(forms.frmCFR.company)
Then append the XREF table...(which contains comp, subcomp, dept)
Then use the XREF table to create 3 other tables (this way they can select the company and the appropriate sub company / department will be displayed based on previous selection.)

not working so far...

 

[randy700]

Not entirely sure, but I believe that, as long as you keep the first form, with the combo boxes, open, you will be unable to update the table from another form.

Have you considered using the combo boxes NotInList events?


Randy

 

[Steven547]

NotInList ? I'm looking into this now, but what would you suggest?

The main problem is, we are not connected to SQL yet. So the tables are all through access, which makes loading the forms real slow. I could have them log out, enter the company info, then back in, but that is a tedious task.

 

[Steven547]

Ok...here is what I did so far:
If the user is entering a new company, sub, dept, they enter those into 3 text boxes.
Those text boxes are stored in 3 different fields in my main table (tempComp, tempSub, tempDept).

I have a macro that does the following:
setwarnings = NO
1: Creates a table based on the main table but only pulling the temp fields.
2: Appends the XREF table (contains the relationships between the comp, sub, dept)...so company is appended with tempCompany, etc...
3: Create the xrefCompany Table from the table created in step 2 above.
4: Create the xrefSub Table from the table created in step 2 above.
5: Create the xrefDept Table from the table created in step 2 above.
setwarnings = YES

So, they enter the info into the text boxes, then click on the command button which runs the above macro. However, it doesn't work. It also only seems to work if I take off the "setwarnings"...

Any thoughts?

 

[Steven547]

Ok..think I figured this out. It seems that the "setwarnings" in the macro is not allowing the tables to be updated. So, on the click event of the command button (that runs the macro), I have the docmd.setwarnings (on) before the docmd.run my macro then I have it set to (off). Also, BEFORE any of that happens on the CLICK event, I save the record (this takes the emphasis out of the table). So far, it works... phew!