viewing data from the database
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:
Code:
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:
Code:
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:
Code:
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
Code:
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
Code:
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
Re: viewing data from the database
Quote:
Originally Posted by
nawaray
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.
Quote:
Originally Posted by nawaray
the second how can i display the date automaticaly?
Code:
Private Sub Form_Load()
Label1.Caption = Format(Now, "mm/dd/yyyy")
End Sub
Quote:
Originally Posted by nawaray
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 .
Code:
Private Sub Form_Load()
Label1.Caption = Format(Now, "mm/dd/yyyy")
Text1.Text = Environ("username")
End Sub
Re: viewing data from the database
Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)
Quote:
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.
Re: viewing data from the database
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