.movenext 1

[kimtp]

Would like to move through my database using .movenext/.moveprevious buttons. However, my code is not cooperating. The error I am getting 'Current Recordset does not support updating' which is error 3251. Haven't a clue on how to cure.

My code:

Public Sub NextRecord(ByVal lngID As Long)
On Error GoTo HandleError
sSql = "SELECT * FROM Members WHERE Members.ID =" & lngID
If rs.State = adStateOpen Then
rs.Close
End If
rs.Open sSql, cData
rs!ID = lngID
With rs
.MoveNext
If .EOF Then
.MoveFirst
End If
End With

hangs up with rs!ID = lngID and returns the error msg.

The connection and recordset are all dimmed in option explicit. No problem opening the recordset.

Any help is appreciated.

Thanx.

Kim

 

[rkinrade]

rem out rs!ID = IngID and see if it runs correctly

 

[CajunCenturion]

I suspect that you may have opened the Recordset with a read only lock type. That would lead to the error when you attempt to make a change to the data in the recordset, which is what you're doing with the offending assignment statement. I would look at the lock parameters on the Recordset open, and choose one more suited to your application. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[kimtp]

Here is the info on locktype:

With rs
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open "SELECT * FROM " & TBLMem, cData, , , adCmdText
End With

Don't think that is the problem since I have updated other records.

Rem out the rs!Id = lngID and it returns the same record. The next time I get a 'Bof/Eof' error msg. I have noticed that the record number returned is -1. Is that correct?

Thanx for the continued help.

Kim

 

[CassandraR]

Hi Kim:

May I suggest the following?

Public Sub NextRecord(ByVal lngID As Long)
    On Error GoTo HandleError
    sSql = "SELECT * FROM Members WHERE Members.ID =" & lngID,

    ' If the recordset is not open, open it.  (Don't close it.)

    If rs.State <> adStateOpen Then rs.Open sSql, cData
    rs!ID = lngID
    With rs

        '  Check to see if EOF is true before using MoveNext

        If Not .EOF Then .MoveNext  
        If .EOF Then .MoveFirst
    End With

Personnally, I prefer ADO over DAO.

Cassie

 

[kimtp]

Thanx for the input. In both cases, the results are the same 'eof/bof is true' msg appears. Doesn't either method, Cassie, do the same thing?

Thanx.

Kim

 

[johnwm]

Do all your members have the same ID? If their IDs are different then your string

< sSql = &quot;SELECT * FROM Members WHERE Members.ID =&quot; & lngID>

will only return 1 record, so a .Movenext is bound to fail.
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[kimtp]

Thanx, Johnwm. Took out the WHERE. Now the .movenext takes me to the second record and then stops with the next .movenext. What I was trying to do was to use the selected record as the starting point and then move forward or backward from there.

Any help is great.

Thanx.

Kim

 

[kimtp]

Finally worked out the Next Record conundrum by using the lastname as a starting point, looping through the database and then .movenext. Works.

Now on the .moveprevious portion, I keep getting 'Operation not allowed in this context' error 3219. Here is the code that I am using:

sSql = &quot;SELECT * FROM Members ORDER BY Lastname&quot;
If rs.State = adStateOpen Then
rs.Close
End If
rs.Open sSql, cData
Do Until rs!Lastname = sLast
rs.MoveNext
Loop
rs.MovePrevious
If rs.BOF Then
rs.MoveLast
If rs.EOF Then
Exit Sub
End If
End If

Hangs up on the rs.moveprevious.

Any help is appreciated.

Thanx.

Kim

 

[johnwm]

I think your problem is with your connection. You use:
With rs
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open &quot;SELECT * FROM &quot; & TBLMem, cData, , , adCmdText
End With

By specifying default cursortype and locktype in the .Open statement you are overriding the previously set values.

As you don't actually need the adCommandText option set in the Open command you can reduce that line to:

.Open &quot;SELECT * FROM &quot; & TBLMem, cData

If you want to use adCmdText then specify cursor and lock types in the open statement:

.Open &quot;SELECT * FROM &quot; & TBLMem, cData, adOpenDynamic, adLockOptimistic, adCmdText
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[kimtp]

johwm, many thanx. I changed the rs.open ssql, cdata to
rs.open ssql, cdata,,adlockoptimistic

and it works the way it should.

The original

With rs
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open &quot;SELECT * FROM &quot; & TBLMem, cData, , , adCmdText
End With

was in the class initialize. If removed how will it affect the other open statements?

 

[johnwm]

I generally choose a specific connection type to suit the particular circumstances...
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[CCLINT]

Just a few words to the above:

Cursor location: Because the Cursor Location is, in this case, the Client, the setting of the cursor type will have no affect - a client static cursor (updatable) will always be used.

Options: CommandType - To optimize the Open method, it is always best to set the command type, in this case adCmdText.
The default is adOptionUnspecified (-1). This means ADO has to determine what the command type is (saved recordset file (adCmdFile), stored proceedure (adCmdStoredProc), table (adCmdTable), sql statement (adCmdText).
Not setting this value will slow down the Open method.

Open statement: All arugments are named arguments. So, it may be easier to use them as such. Then, you do not have to worry about whether you have the arguments in the right order or are using the correct number of comma seperators.
It is very easy to make a mistake like this:

.Open &quot;SELECT * FROM &quot; & TBLMem, cData, , adCmdText

adCmdText has the value of 1. But by mistake, it was put in the wrong place, namely, where the argument for the LockType...in which case the recordset is now read only (adLockReadOnly-1).
So better is:

.Open Source:=&quot;SELECT * FROM &quot; & TBLMem, ActiveConnection:=cData, CursorType:=adOpenStatic, LockType:=adLockOptimistic, Options:=adCmdText

Which could be written like (regardless or the order of the arguments):
.Open ActiveConnection:=cData, CursorType:=adOpenStatic, LockType:=adLockOptimistic, Options:=adCmdText,Source:=&quot;SELECT * FROM &quot; & TBLMem

Or, when leaving out arguments:
.Source = :=&quot;SELECT * FROM &quot; & TBLMem
.Open ActiveConnection:=cData, CursorType:=adOpenStatic, LockType:=adLockOptimistic, Options:=adCmdText

And most visible is:
.Source = :=&quot;SELECT * FROM &quot; & TBLMem
.CursorType = adOpenStatic
.ActiveConnection = cData
.LockType = adLockOptimistic

.Open Options:=adCmdText

[/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[kimtp]

CClint, thanx for the response. Let's see if I can digest all of what has been offered.

1. I should not be using the general open statement in the frm load/class initialize?

2. If I have many public subs in a class that use the open method for interrogating the database then I should use one of the open methods that you suggest for each time the recordset is talking to the database?

Thanx.

Kim