By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,889 Members | 1,358 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,889 IT Pros & Developers. It's quick & easy.

Help! Error Message when inserting 2 sbf's

P: 99
Hey. I have a mainform, based off a tblClient. Then 2 sbfs based off queries. Im trying to insert the sbfs into the mainform to allow for data entry (using the Tab Control). I am linking the mainform and sbfs together through the master and child fields based off the clientID. I can get 1 of the 2 sbfs to show its information, but when I try adding the second 1, it gives me an error message:

You tried to lock table while opening it, but the table cannot be locked because it is currently in use. Wait a moment, then try the operation again.

I can insert either 1 of the sbfs first and that 1 will work, then the 2nd 1 ends up with this message and doesnt display anything. Each sbf is based off its own qry, which is based off the same table.

Any suggestions?
Dec 11 '06 #1
Share this Question
Share on Google+
11 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Robert,

What are you using the second subform for. The records are probably locked by the first subform. If you are using it to try to separate out the questions I don't think this will work.

I'm not sure of the solution but post more detail on the composition of each subform and why you are trying to separate the records and some of the other experts my be able to help.

I will point them in this direction once you've posted more details.

Mary
Dec 11 '06 #2

P: 99
Mary, sorry for the lack of details. You guessed right, I am trying to separate the questions for the sbfs. Some of the sbfs have a lot of questions, or have a few questions that are not directly related, but I would like to display on a single form.

For example, I have 3 queries which return about 10 question/answers total. Each query is unrelated to anything else, so instead of having 3 forms with 2-3 fill-ins, I thought Id display them all in 1 form.

The reason I did not have them all in a query (ex: Between 101 And 103 OR ) was because I didnt want them all displayed in the datasheet view together. I guess Im just being picky, but I thought I would break them up and then type a text label for each section to distinguish them. (I would like to continue using the datasheet view, since that is what my other forms use).

If this is not possible, then I will try to live with them all in the same list.

Thanks, Robert
Dec 11 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
I am just adding the table structures for the other experts.

tblQuestions
QuestionNumber (Primary Key)
Question (Text Field)

tblAnswers
AnswerAutoID (Primary Key)
ClientAutoID (FK) to tblClient
QuestionNumber (FK) to tblQuestions
Answer (Currancy)

Robert currently has a subform based on tblAnswers on a main form based on tblClient with a master/child relationship based on ClientAutoID.

His problem is that there are potentially hundreds of questions (prepopulated) and answers per client and he would like to separate them out into various subforms but when he tries to add a second subform he is given a record locking error.

Mary
Dec 11 '06 #4

P: 99
I am just adding the table structures for the other experts.

tblQuestions
QuestionNumber (Primary Key)
Question (Text Field)

tblAnswers
AnswerAutoID (Primary Key)
ClientAutoID (FK) to tblClient
QuestionNumber (FK) to tblQuestions
Answer (Currancy)

Robert currently has a subform based on tblAnswers on a main form based on tblClient with a master/child relationship based on ClientAutoID.

His problem is that there are potentially hundreds of questions (prepopulated) and answers per client and he would like to separate them out into various subforms but when he tries to add a second subform he is given a record locking error.

Mary
Guess I didn't add enough details, sorry about that. I actually just opened 2 of my forms at the same time that do not have and sbf's in them and I got the same message. I'm wondering if this is because they are all based off the same table. TblAnswers. If this is the case, then I'm not sure that anything can be done. Thanks again for all the help Mary.
Dec 11 '06 #5

NeoPa
Expert Mod 15k+
P: 31,613
I suspect you may find a way around this if you got to Tools / Options / Advanced and experiment with the Open Mode and Record Locking options.
No guarantees, but that's what I'd look at.
Dec 11 '06 #6

100+
P: 1,646
Guess I didn't add enough details, sorry about that. I actually just opened 2 of my forms at the same time that do not have and sbf's in them and I got the same message. I'm wondering if this is because they are all based off the same table. TblAnswers. If this is the case, then I'm not sure that anything can be done. Thanks again for all the help Mary.
I haven't used bound controls, even in Access, for many years. Too difficult to control. This can be done in a non-bound form using static client-side cursors. Maybe you can change the type of cursor for the recordsets bound to the subforms so that they are client side (don't know if that is possible) and change the type of locking.
Just a guess.
Dec 11 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
I suspect you may find a way around this if you got to Tools / Options / Advanced and experiment with the Open Mode and Record Locking options.
No guarantees, but that's what I'd look at.
I agree changing the record locking options to No Locks may solve the problem but it always opens up the danger of corruption in a multi user environment.

Robert, will there be more than one concurrent user on the database?

Mary
Dec 11 '06 #8

P: 99
There will be several users on the DB. I think it will probably be around 2-4 people using it at once. I guess it's better to make sure that people can't make mistakes than to have a cool looking form. I may just go ahead and leave this stuff alone, (i.e. split it all onto separate forms, instead of having it all on 1. Thanks for the help. Ill be sure to post my next question soon :) .
Dec 11 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
There will be several users on the DB. I think it will probably be around 2-4 people using it at once. I guess it's better to make sure that people can't make mistakes than to have a cool looking form. I may just go ahead and leave this stuff alone, (i.e. split it all onto separate forms, instead of having it all on 1. Thanks for the help. Ill be sure to post my next question soon :) .
No problem Robert.

Mary
Dec 11 '06 #10

P: 1
Hi,

i have a similar problem.

I have a table that lists equipments. For each equipment, there can be from one to four certificates (ex. CA 2062 or CA 2525,...), and in the table i put four field, certif_1, certif_2, certif_3 and certif_4.

table equipments
[...]
certif_1
certif_2
certif_3
certif_4
[...]

Each certificate has a validity period. There is another table with, for each certificate, its validity period.

table certif
id
certif_number
validity_period

Now i want to use a beautiful form... It is based on the table "equipments". I have four drop-down lists to choose a certificate from the existing ones. I would like to display the validity period of each of them...

I tried to use four trivial subforms, each one based on a query that selects the right certificate (with certif_n as parameter).
But i get the same error as above when i try to display the form. It seems that Access wants to lock the "certif" table once for each subform. It works when i only put one subform, but as soon as i put two of them, i get the error.

I know that i'm not exactly doing this as i should. There is something silly in using subforms.

It's my first Access database, so please don't blame me. I hope i'm being clear enough... and someone can help.

Thanks
Jan 9 '07 #11

NeoPa
Expert Mod 15k+
P: 31,613
If this answer isn't the answer to your problem then this is not the thread to post your question in.
You need to create your own new thread. You may include a link to this one if required.
Jan 9 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.