Table lock - Run-time error 3211 ????

[Pan]

I have a form with a combo box that contains a list of customers. Select a customer from the combo box and the form fields populate with general customer info. The form has a subform on it with a combo box where you can select products. Select a product from the combo box and the subform populates with customer-specific product info.
If I open the main form and select a customer, then without doing anything else I select another customer, everything works ok. It’s when I select a product on the subform, and then try to select a different customer on the main form that I get “Run-time error 3211: The database engine could not lock table tblCustomer because it is in use by another person or process.”
Can anybody shed some light on this? I’ve been confounded by it for days.

 

[quest4]

What is, very simply put, happening is you opened one record, via the Customer selection and you have not save it and closed it and then you are trying to open another. Is that kind of clear to you? A simple cure is to add a cmdButton to the form, cal it Save and then add a second cmdButton and call it New or Add. Use the wizard to build these and they should be close to what you need. As you get better at this there are some real cute ways to improve this, but I don't know how good you are at code. hth.

 

[Pan]

I'm very thankful to you for replying. However, I knew I ran the risk of miscommunicating by way oversimplifying the issue.

I can edit the current record on the main form as much as I want and still successfully select a different customer from the customer combo box without having to create a "Save" and "Add" or "New" command button to the form, as long as I don't select a product from the product combo box on the subform. I can even edit pre-existing records on the product subform without a problem. I've commented out the code behind the product combo box on the subform so it does nothing except display the name of the product I selected, but it still locks the table for the main form so I can't select a different customer.
I've been programming for some time now. I've never run into a problem that I haven't been able to devise a fix for. I'm approaching hopelessness with this issue though. If you have any more suggestions, I'm all ears, and deeply appreciative.

 

[quest4]

Have you check to make sure that the master/child link is properly set up. hth.

 

[Pan]

As best as I can tell, the links are set up correctly. If they weren't, I wouldn't think I'd be able to edit a product record on the subform and then successfully select a different customer on the main form. Again, it seems that the problem occurs when I use the Product combo box on the subform (even though there's no code behind it), and then select a different customer on the main form.
This is giving me the Exorcist head spins.

 

[quest4]

I think the problem should probably be in the master/child or in the table relationship. Because you can change customers, but the trouble is in the subform area. In the table relationship, is the referental integrety checked? How about cascade update and delete? I am assuming there is a relationship between those tables. If not that, could you attach a stripped down version of the dbase? Sometimes it is easier to see what is being talked about. hth.

 

[Pan]

It seems like it'd be easier for me to go jump off a bridge and end it all than to strip down this database. ;-)
It's a front end for an Oracle data warehouse.
The tables are created at run-time, so I'm not sure how to deal with the relationships.
I can edit data on the subform and then change customers on the main form just fine. It's when I use the evil combo box on the subform (and again, I've commented out all of the code for the combo box), then try to change customers that I run into the issue.
It's curious how there's little to no information that I've been able to find on this or any other forum about this issue. I've even consulted all of my reference books and not found a thing.
How does Access lock tables? If I had more information on that, maybe I could figure this out.
I'm baffled...

 

[quest4]

I have never heard of locking tables, but I have heard of and use locking records. just create a moule with this:
Public Sub SetRecLocks() 'This command set Pessismistic locking.
'If someone is poking aroundin a given record no on else can edit it.
Application.SetOption "Default Record Locking", 2

End Sub
Then in the forms OnLoad call it. Record is locked for your use only. You can lock individual contols also, Me!YourContolName.Locked=True. I understand why you can not strip the dbase, but I still think try is something wrong in the master/child set-up or the tables relationships. The subform should not be behaving like that. I am sorry I can not figure this one out, but maybe someone else here can do that for you.

 

[Pan]

Thank you very much for your time. I've figured it out. I was setting a couple of variables to the values in certain columns of the Product combo box on the subform and I had referenced them incorrectly. For some reason it was locking a table that is created by the main form when I select a customer from the Customer combo box.
I hope you aren't too frustrated by this. Perhaps if I had just pasted the code for the Product combo box here, you might have seen the incorrect reference sooner than I did.
Thanks again.

 

[quest4]

The table will do the when it can not figure out what to do with data, aka type-o's and other things. Yes the code would have helped a little. Glad to see that it is now working for you, and feel free to use that module above to keep two users from working on the same record at the same time. Good luck with the rest of you project and remember, there are never any problems only solutions.