Table Already Opened by Another User (1 Viewer)

[gray]

Hi

WinXPPro
Access 2002

There a couple of threads on here mentioning the following error but none seem to apply to my situation?

I have a form where a user can create an Estimate for a job. Once the job is completed they can click a button on the Estimate Form to create an Invoice based on that Estimate. I want the new Invoice to pop up to the user immediately after it has been created. However I get the following error.

"Run-time error '3008': The table 'Invoices' is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically."

My code looks like this.

-------------------------------------------------------
Dim rsWrite As New Recordset

rsWrite.Open "Invoices", CurrentProject.AccessConnection, adOpenDynamic, adLockOptimistic
rsWrite.AddNew
rsWrite!Invoice_Number = New_Rec_No
rsWrite!Date_Invoice_Added_to_System = Date & " " & Time
rsWrite.Update
New_Rec_ID = rsWrite!Invoice_ID
rsWrite.Close


stDocName = "Invoice Details Form"

If Not IsLoaded(stDocName) = True Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Forms("Invoice Details Form").Recordset.FindFirst "Invoice_ID = " & New_Rec_id

Forms("Invoice Details Form").SetFocus
-----------------------------------------------------------

It seems I have to "close" the Invoices table somehow? I tried CurrentProject.AccessConnection.close after the rsWrite.close but that did not work.

Can anyone help me please?

Thanks

 

[Rabbie]

Can you indicate which line is generating the error?

 

[gray]

Hi

Sorry, should've included that before.... it is the form open command

DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks

 

[Rabbie]

It is possible that you are opening the form before the table has been closed properly. I think it would be worth while putting some sort of delay between rswrite.close and docmd.Openform. You could test this by putting a breakpoint in and seeing if it works then.

 

[gray]

Hi

Thanks for helping out.

I placed a breakpoint and left it a good 30 seconds before PF8 ing. But I still get the same error.

I wonder if there'e some way to "refresh" the active connection to release the table?

P.S.

my Tools/Options/Advanced/Default Open Mode = Exclusive

I've tried = Shared as well but no joy... get the same problem

 

[gray]

Hi

Has anyone else seen this problem? or might have suggestions for a resolution.... I'm struggling with it a little....

"Run-time error '3008': The table 'Invoices' is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically."

Thanks

 

[Rabbie]

This is just a shot in the dark but has the Invoice table been opened from the database screen.

Can you post a version of your DB so we can look at it to help solve this problem

 

[gray]

Hi

Thanks very much for your assistance... I'll chop out the relevant bits and get it posted onto here asap... It might take me a day or so as I'm out of the office for a couple of days. Thanks

 

[boblarson]

Try adding this to it:

Dim rsWrite As New ADO.Recordset

 

[gray]

Hi Rabbie and Bob

First, apologies for the... ahem... schoolboy error "Dim rsWrite As New Recordset" and thanks for pointing it out. I've corrected it but still get the same 3008 error.

I'm pretty sure it's the locking I'm using but I've tried all sorts of permutations without success.

If you still have a few minutes to look at my problem, I've stripped out the Db to the bare bones and could forward it to you. Would it be OK if I to send it to you as a Private Message rather than a post (not sure about the etiquette there)? I'll add in some brief notes about how to generate the error.

Obviously, I'd be happy to post the resolution for the benefit of others If I can crack it.

Thanks for your help.

 

[boblarson]

You can PM me if you wish.

 

[boblarson]

Okay, here's your problem -

On the Quotations form you have opened the invoices table by using this:

SELECT Invoices.Invoice_ID, Invoices.Full_Invoice_Number, Invoices.Date_Invoice_Added_to_System FROM Invoices WHERE (((Invoices.Quotation_ID)=Forms![Quotation Form]!Quotation_Id_TextBox));

in the INVOICES CREATED FROM THIS QUOTATION on the RECORD DETAILS tab. If you get rid of it, the code works. Instead of assigning a SQL statement to it, why don't you load it in the form's On Load event with code and then update it after converting a quote.

 

[gray]

Hi

Thanks very much to Bob for helping out with this... I spent hours looking for the problem.

For other newbies: I think the reason I was getting this error was because I had a listbox in one form populated by a "SELECT From Invoices" etc, set in the Rowsource property of its listbox. This appears to create a "permanent" connection to the table ("Invoices" in my case). The form I was trying to open was bound to the same [Invoices] table and so when I tried to open it, a conflict arose.

I amended this by populating the listbox using the sub-routine below and by calling that sub-routine from the Form's Open and Current events (or wherever reqd) :-

------------------------------------------------------------
Private Sub Populate_My_ListBox()
Dim rsRead As New ADODB Recordset

rsRead.Open "SELECT Field1, Field2 FROM My_Table WHERE Feild =" & Me!Value, CurrentProject.AccessConnection, adOpenForwardOnly, adLockOptimistic

With Me!My_ListBox
.RowSourceType = "table/query"
.BoundColumn = 0
Set .Recordset = rsRead
End With

rsRead.Close
-------------------------------------------------------
Works great!...no more 3008 errors when I open the other form. I gather that . Recordset property for listboxes is only available in Access 2002/3 upwards?

By the way, the other thing I learned was that if you want to use the dblClick event to "sense" which row the user has clicked and to grab a column's details on that row, thus :-

Rec_Id = Me.My_ListBox.Column(0)

then the form's AllowEdits property must be set to True. I set / unset this in the Listbox' got focus and lostFocus events respectively.

Hope this helps someone!

 

[boblarson]

Thanks for posting back with the final resolution. It is good to do so, so HOPEFULLY, people will use the search feature here and find the answer.

I'm glad we finally figured out what was happening. it was starting to get me frustrated too until I finally found the rowsource item that was causing the problem.