Table already open through user interface and cannot be manipulated programmatically

[robin27th]

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 programmaticall y. (Error 3008)"

Could anyone suggest a work around on this issue?

Thanks,
Robin

[patjones]

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

[robin27th]

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,PT0000 02,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

[patjones]

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

[robin27th]

...deleted.

[robin27th]

...deleted.

[robin27th]

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

[patjones]

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

[patjones]

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

[patjones]

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

[robin27th]

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

[patjones]

No problem, glad it worked out.