help help

[hyper88]

Private Sub Command1_Click()
getRecordSet
connRs.Source = "Select * From Users"
connRs.Open
If connRs.EOF = True Then
connRs.MoveFirst
Else
connRs.MoveNext
Text1.Text = connRs!UserID
Text2.Text = connRs!password
Text3.Text = connRs!userRole
End If
End Sub

This is a MoveNext command button but whenever i click it will only move to the second row of the database and will not proceed on.

getRecordSet is a sub in my module that opens a connection and set the recordset

i closes the recordset after loading the data from db into a listbox

[Edneeis]

Probably because you keep reopening the recordset, which takes it back to the start.

[abhijit]

IMHO,
If the recordset was being reopened, it would have generated an error. I do not think that is the case over here.


Could you just post the getRecordset Sub?

Cheers!
Abhijit

[hyper88]

eh...

i have a listbox and 3 textboxes and 1 MoveNxt command button.

1) i open connection and recordset when i load the form to load the details into the listbox then close it. (In sub form_load)

2) i closed the recordset in #1 because i want to change the recordset.source with some conditions and display what i clicked on to the textboxes.(In sub list1_click())

3) i reopen the recordset so i can change the record.source get a "SELECT * FROM xxx" and moveNext the record. (In the cmd button).

anyways i can achieve these?? without closing and reopening of record?

thanks

[hyper88]

Public Sub getRecordSet()
Call connect 'connection string setup
Set connRs = New ADODB.Recordset
With connRs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockPessimistic
.ActiveConnection = connWTC

End With

this is the getrecordSet sub

[Edneeis]

No error would be generated unless the RS is STILL open.

I only said REopen because if it is a move next function used more than once then it keeps opening the RS a new, hence the phrase REopen (as in open again).

What you could do is record the Absolute page number before you close it then when you open it the second time just add one to that go there and then close.

[MarkT]

I would change it to something like this instead.

Private Sub Command1_Click()
 
    If connRs Is Nothing Then
        getRecordSet
    End If
    
    Text1.Text = connRs!UserID
    Text2.Text = connRs!password
    Text3.Text = connRs!userRole
    
    connRs.MoveNext
End Sub
 
 
Public Sub getRecordSet()
    Call Connect 'connection string setup
    Set connRs = New ADODB.Recordset
    
    With connRs
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockPessimistic
        .ActiveConnection = connWTC
        .Source = "Select * From Users"
        .Open
        .MoveFirst
    End With
End Sub