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. - Private Sub Form_Current()
-
Dim db As DAO.Database
-
Dim rs As DAO.Recordset
-
Dim sql As String
-
Dim strNextID As String
-
-
If Me.NewRecord Then
-
sql = "SELECT TOP 1 CInt(Mid([Id],3,8)) AS MaxID " & _
-
"FROM Patients " & _
-
"ORDER BY CInt(Mid([Id],3,8)) DESC ;"
-
Set db = CurrentDb
-
Set rs = db.OpenRecordset(sql, dbOpenDynaset, dbOptimistic)
-
strNextID = "PT" & String(6 - Len(rs!maxid), "0") & rs!maxid + 1
-
Me.Id = strNextID
-
Me.Id.SetFocus
-
End If
-
-
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
I apologize for all the replies. I duplicated your situation in my test database and found that this works nicely: - Dim rs As DAO.Recordset
-
Dim strSQL As String
-
Dim lngMaxID As Long
-
-
strSQL = "SELECT MAX(ID) AS MaxID FROM (SELECT CInt(Mid(productID, 3, 8)) AS ID FROM tblTest)"
-
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
-
-
lngMaxID = rs("MaxID")
-
lngMaxID = lngMaxID + 1
-
-
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
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
Pat,
The error occurs at line 12. - 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. - 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
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
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
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): - Set rs = db.OpenRecordset(sql, dbOpenTable, dbOptimistic)
Pat
Also, the option dbOptimistic is meant to be used in the fourth argument of OpenRecordset...so you should really have: - Set rs = db.OpenRecordset(sql, dbOpenTable, , dbOptimistic)
Since you are opening the recordset to retrieve data only you could even try: - Set rs = db.OpenRecordset(sql, dbOpenTable, dbReadOnly, dbOptimistic)
Pat
I apologize for all the replies. I duplicated your situation in my test database and found that this works nicely: - Dim rs As DAO.Recordset
-
Dim strSQL As String
-
Dim lngMaxID As Long
-
-
strSQL = "SELECT MAX(ID) AS MaxID FROM (SELECT CInt(Mid(productID, 3, 8)) AS ID FROM tblTest)"
-
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
-
-
lngMaxID = rs("MaxID")
-
lngMaxID = lngMaxID + 1
-
-
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
Both of your suggestion gave the 'invalid argument' error. However the following statement worked. - Set rs = db.OpenRecordset(sql, dbOpenDynaset, dbReadOnly)
Thanks for the clue Pat.
Robin
No problem, glad it worked out.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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....
|
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...
|
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,...
|
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......
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
| |