viewing data from the database

[nawaray]

hello,

i have a form3 which consists of text boxes(txtno, txtname, txtcpr and txtdate) and buttons next and previous

the table is called table2 which consists of (viewno, name,cpr ,date).

when the user enter his username and password and his authority is a user the above form3 will be displayed, this works fine with me.
but when i press the next button or the previous button i can't see the data it will not go next or previous.

the second how can i display the date automaticaly?
the third is i want to add a text box for a username which will automatically display the name of the user who logged in .

below is my code for the login:

Private Sub Command1_Click()
    '~~> Check if Username has been put
    If Len(Trim(Text1.Text)) = 0 Then
        MsgBox "Please Enter the User Name"
        Text1.SetFocus
        Exit Sub
    End If
    '~~> Check if password has been put
    If Len(Trim(Text2.Text)) = 0 Then
        MsgBox "Please Enter the Password"
        Text2.SetFocus
        Exit Sub
    End If
    '~~> Connect to the Access database
    Set cn = New ADODB.Connection
    
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Users\nooras\Desktop\revenue\rev system\LoginExample\Users.mdb;"
    
    '~~> Open a recordset
    Set rs = New ADODB.Recordset
    '~~> Open the recordset
    rs.Open "SELECT * FROM Table1", cn, , , adCmdText
    
    Do While Not rs.EOF
        If UCase(rs("username")) = UCase(Trim(Text1.Text)) Then
            If UCase(rs("Password")) = UCase(Text2.Text) Then
                strUserType = rs("authority")
                boolAllow = True '<~~ Authenticated
            End If
        End If
        rs.MoveNext
    Loop

    '~~> Close and clean up
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    
    If boolAllow = False Then
        MsgBox "Either the username is incorrect or the password is incorrect"
        Text1.SetFocus
    ElseIf strUserType = "admin" Then
      Form2.Show
      Form1.Hide
    Else
    Form3.Show
    Form1.Hide
    
    
       '~~> User found
       ' MsgBox "User " & Text1.Text & " Found. Authority Type of user is " & strUserType
    End If
End Sub

and this is my code for the next and previous button:

Private Sub nextcmd_Click()
If rs.RecordCount > 0 Then
        rs.MoveNext
        If (Not rs.EOF) Then
            
            Prevcmd.Enabled = True
            fill_data
        Else
            nextcmd.Enabled = False
            Prevcmd.Enabled = True
            fill_data
        End If
    End If
End Sub

previous button:

Private Sub Prevcmd_Click()
If (rs.RecordCount > 0) Then
        rs.MovePrevious
        If (Not rs.BOF) Then
            
            nextcmd.Enabled = True
            fill_data
        Else
            Prevcmd.Enabled = False
            nextcmd.Enabled = True
            fill_data
        End If
    End If
End Sub

Private Sub fill_data()

If rs.BOF Or rs.EOF Then txtNo.Locked = True

txtname.Locked = True
txtcpr.Locked = True
txtdate.Locked = True

Set cn = New ADODB.Connection
    
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Users\nooras\Desktop\revenue\rev system\LoginExample\Users.mdb;"
    
    '~~> Open a recordset
    Set rs = New ADODB.Recordset
    '~~> Open the recordset
    rs.Open "SELECT * FROM  Table2", cn, , , adCmdText
txtNo = rs!ViewNo

txtname = rs!Name
txtcpr = rs!cpr
txtdate = rs!Date

txtNo = ""
    txtname = ""
    txtcpr = ""
    txtdate = ""  
Else
 If IsNull(rs!ViewNo) Then txtNo = "" Else txtNo = rs!ViewNo
     If IsNull(rs!Name) Then txtname = "" Else txtname = rs!Name
    If IsNull(rs!cpr) Then txtcpr = "" Else txtcpr = rs!cpr
    If IsNull(rs!Date) Then txtdate = "" Else txtdate = rs!Date

End If

txtNo.Locked = True

txtname.Locked = True
txtcpr.Locked = True

End Sub

Private Sub Form_Load()
txtNo.Locked = True
txtname.Locked = True
txtcpr.Locked = True
txtdate.Locked = True




Set cn = New ADODB.Connection
    
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Users\nooras\Desktop\revenue\rev system\LoginExample\Users.mdb;"
    
    '~~> Open a recordset
    Set rs = New ADODB.Recordset
    '~~> Open the recordset
    rs.Open "SELECT * FROM  Table2", cn, , , adCmdText



txtNo = rs!ViewNo

txtname = rs!Name
txtcpr = rs!cpr
txtdate = rs!Date

End Sub

Thank you

[Hack]

but when i press the next button or the previous button i can't see the data it will not go next or previous.

Display your whole recordset in a ListView or something.

the second how can i display the date automaticaly?

Private Sub Form_Load()
Label1.Caption = Format(Now, "mm/dd/yyyy")
End Sub

the third is i want to add a text box for a username which will automatically display the name of the user who logged in .

Private Sub Form_Load()
Label1.Caption = Format(Now, "mm/dd/yyyy")
Text1.Text = Environ("username")
End Sub

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

but when i press the next button or the previous button i can't see the data it will not go next or previous.

Based on your fill_data code that is entirely expected - because you explicitly re-load exactly the same data each time.

I recommend that you take a look at the "ADO Tutorial" from the "Classic VB:ADO" section of our Database Development FAQs/Tutorials (at the top of this forum), as it deals with that kind of thing correctly.

[Wen Lie]

agree with si_the_geek....
Seeing your code, i think you only do the same thing. No matter which button you press, your code will generate the same thing.

You could declare a global recordset, and you open the recordset 1 time only on your form_load, and you close it only at your form_unload event.

Then you can generate your recordset movement on your button press.

hope will help.

Regards,
Willy