473,322 Members | 1,241 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Table already open through user interface and cannot be manipulated programmatically

Hi,
I have a form which uses table 'Patients' as Record Source and type Dynaset. I have a 8 digit text id field which starts with 'PT' (PT000001). I wrote the following code to get the next maximum ID on a new record.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     Dim db As DAO.Database
  3.     Dim rs As DAO.Recordset
  4.     Dim sql As String
  5.     Dim strNextID As String
  6.  
  7.     If Me.NewRecord Then
  8.         sql = "SELECT TOP 1 CInt(Mid([Id],3,8)) AS MaxID " & _
  9.                     "FROM Patients " & _
  10.                     "ORDER BY CInt(Mid([Id],3,8)) DESC ;"
  11.         Set db = CurrentDb
  12.         Set rs = db.OpenRecordset(sql, dbOpenDynaset, dbOptimistic)
  13.         strNextID = "PT" & String(6 - Len(rs!maxid), "0") & rs!maxid + 1
  14.         Me.Id = strNextID
  15.         Me.Id.SetFocus
  16.     End If
  17.  
  18. End Sub
But when I try to add new record, I get the following error.

"The table Patients is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically. (Error 3008)"

Could anyone suggest a work around on this issue?

Thanks,
Robin
Sep 12 '10 #1

✓ answered by patjones

I apologize for all the replies. I duplicated your situation in my test database and found that this works nicely:

Expand|Select|Wrap|Line Numbers
  1. Dim rs As DAO.Recordset
  2. Dim strSQL As String
  3. Dim lngMaxID As Long
  4.  
  5. strSQL = "SELECT MAX(ID) AS MaxID FROM (SELECT CInt(Mid(productID, 3, 8)) AS ID FROM tblTest)"
  6. Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
  7.  
  8. lngMaxID = rs("MaxID")
  9. lngMaxID = lngMaxID + 1
  10.  
  11. Set rs = Nothing

I eliminated the dbOptimistic option. I also used slightly different SQL that utilizes a sub-query, but your SQL should work fine also. Additionally, it is good practice to clear the recordset object by using the Nothing keyword.

Pat

11 6999
patjones
931 Expert 512MB
What line is the error occurring on? You can find this out by setting a break point on the first executable line of the sub then stepping through it until the error is raised.

Also, what exactly is the format of your ID values? Maybe you could give us a short list of examples. Thanks.

Pat
Sep 13 '10 #2
Pat,
The error occurs at line 12.
Expand|Select|Wrap|Line Numbers
  1.         Set rs = db.OpenRecordset(sql, dbOpenDynaset, dbOptimistic)
Basically the ID values have PT prefix and rest six digit increases i.e. PT000001,PT000002,PT000003.

I used a numeric ID and used dmax which works fine.
Expand|Select|Wrap|Line Numbers
  1.         lngNextID = DMax("Id", "Patients") + 1
I guess its an issue with opening a recordset on a bound table. I tried a query instead of the table but same error.

Robin
Sep 13 '10 #3
patjones
931 Expert 512MB
I was going to suggest using DMax in some form. DMax is the usual way of handling this problem. This is not to say that SELECT TOP can't work...but as you can see it has the disadvantage of needing to use a recordset.

So your situation is resolved then?

Pat
Sep 13 '10 #4
...deleted.
Sep 13 '10 #5
...deleted.
Sep 13 '10 #6
Sorry, I am on a slow connection, so same reply posted several times erroneously.

Dmax would not work on a text field and I might encounter similar situation in future. So I would rather want to know how to work with a recordset, if possible. Any suggestion?

Robin
Sep 13 '10 #7
patjones
931 Expert 512MB
Can you try changing line 12 to the following and let me know if you still get the error (I suspect the error will continue but want to rule out a simple possibility first):

Expand|Select|Wrap|Line Numbers
  1. Set rs = db.OpenRecordset(sql, dbOpenTable, dbOptimistic)

Pat
Sep 13 '10 #8
patjones
931 Expert 512MB
Also, the option dbOptimistic is meant to be used in the fourth argument of OpenRecordset...so you should really have:

Expand|Select|Wrap|Line Numbers
  1. Set rs = db.OpenRecordset(sql, dbOpenTable, , dbOptimistic)

Since you are opening the recordset to retrieve data only you could even try:

Expand|Select|Wrap|Line Numbers
  1. Set rs = db.OpenRecordset(sql, dbOpenTable, dbReadOnly, dbOptimistic)

Pat
Sep 13 '10 #9
patjones
931 Expert 512MB
I apologize for all the replies. I duplicated your situation in my test database and found that this works nicely:

Expand|Select|Wrap|Line Numbers
  1. Dim rs As DAO.Recordset
  2. Dim strSQL As String
  3. Dim lngMaxID As Long
  4.  
  5. strSQL = "SELECT MAX(ID) AS MaxID FROM (SELECT CInt(Mid(productID, 3, 8)) AS ID FROM tblTest)"
  6. Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
  7.  
  8. lngMaxID = rs("MaxID")
  9. lngMaxID = lngMaxID + 1
  10.  
  11. Set rs = Nothing

I eliminated the dbOptimistic option. I also used slightly different SQL that utilizes a sub-query, but your SQL should work fine also. Additionally, it is good practice to clear the recordset object by using the Nothing keyword.

Pat
Sep 13 '10 #10
Both of your suggestion gave the 'invalid argument' error. However the following statement worked.

Expand|Select|Wrap|Line Numbers
  1.         Set rs = db.OpenRecordset(sql, dbOpenDynaset, dbReadOnly)

Thanks for the clue Pat.

Robin
Sep 13 '10 #11
patjones
931 Expert 512MB
No problem, glad it worked out.
Sep 13 '10 #12

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

Similar topics

1
by: ST | last post by:
This is my other error when I click on Immunoflourescence. I believe this is related to the other error I just posted (Input string was not in a correct format.) Please let me know if you have...
18
by: Roger Withnell | last post by:
I open a new window from the current window to display maps. Several maps of different sizes can be displayed. The function is given the size of the map and adjusts the window size accordingly....
6
by: Stefania Scott | last post by:
Hi, I need to find how can I know that a program I am going to shell it is already open. This program uses an csv file that I need to refresh before shelling the program, however if the csv file...
3
by: trellow | last post by:
Hello, I am writing an application that needs to read a file that is already open by another process for writing. When I do the following: FileStream fs = new FileStream(fileName,...
3
by: PontiMax | last post by:
Hi, in my opinion the user interface designers of the Microsoft CRM solution have done a very good job. But I'm afraid I don't understand how certain elements of that design were implemented......
2
by: Stewart Rogers | last post by:
Hi all, I have been working on an ASP.NET application that is a kind of wizard ( a list of sequential pages ). We built that application for the CLIENT-A and it worked fine. After six months...
4
by: simchajoy2000 | last post by:
Hi, I am working on a user interface where I have made it possible for users to open a new panel in the main form. However, since this occurs after the main form has already loaded, the new...
1
by: Martin | last post by:
Hi all, We're planning to rewrite a VB6 Windows app as a VB.Net Web app. This VB6 app had an MDI user interface (more accurately: a TDI interface). This interface allowed the user to have...
1
by: Neha Jain | last post by:
i have a table 'Tab1' with some fields. i want to create a screen consisting one of the fields in Table 'Tab1'.user must be able to select any of the data stored in the table for that field and any...
0
by: dreamer247 | last post by:
hii all i am working with mssql 2000 and after restoring a databas from device the warning is .. Warning: mssql_connect() : message: Cannot open user default database. Using master database...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.