log in program (vb6 + access)

[yeeitsneo]

uhm. im coding a simple log in program in vb 6 and im using access as the database.
i already coded the username and password verification part successfully.
but our instructor requires us to include a "Privilege" whereas, all registered usernames in the database have corresponding privilege or rights to change their password. i declared privilege as int. a privilege of 1 means the logged in user can change his/her password. if not 1 then the logged in user cannot edit or change his/password.

in my database. i have created two tables. one for the users which i renamed "userstbl" which includes the ff fields
1. userid (pk)
2. username
3. userpass

the second table is called "privtbl" which includes the ff fields.
1. privilege (not auto number. i assign 1's randomly)
2. userid (fk)

sooo my problem is that, i have no idea how to code such condition that checks if the logged in user has a privilege of 1. if so, the user may change his password. if not the user cannot.

heres my code:

'codes for the module 
Public db As ADODB.Connection
Public rec As ADODB.Recordset


Sub connect()
    Set db = New Connection
    db.CursorLocation = adUseClient
    db.Open "Provider = Microsoft.jet.OLEDB.4.0;data source = letmein.mdb;persist security info = false"
    
End Sub

Private Sub login_Click()
   sql = "SELECT COUNT (*) FROM Usertbl WHERE " & _
          "UserName='" & Replace(Text1.Text, "'", "''") & "' AND " & _
          "userpass='" & Replace(Text2.Text, "'", "''") & "'"
      Set rec = db.Execute(sql)


      Dim a As Integer
      If CLng(rec.Fields(0)) < 1 Then 'checks if the input username and pass is found in the database
          Unload Me
          a = MsgBox("Invalid user name/password.", vbExclamation + vbYesNo)
          If a = vbYes Then
             Text1.Text = ""
             Text2.Text = ""
             Form1.Show
          End If
      Else
      ' a condition should be written here. or perhaps another elseif

            Form2.Show
            Unload Me
            
      End If

 End Sub


Private Sub Command2_Click()
End
End Sub

Private Sub Form_Load()
connect
sql = "select * from usertbl"
Set rec = New Recordset
rec.Open sql, db, adOpenDynamic, adLockBatchOptimistic
End Sub

a response would highly be appreciated.
thanks you! :)

[debasisdas]

After successful log in, you need to check the privilege of the same user from database. If the user has desired privileges then enable the password change form else disable that.

[ChrisPadgham]

why are you storing the priveleges in a separate table, you could have another field in the Usertbl to hold them.

Then change your select to read the priveleges, rather than the count that is there are the moment.

[yeeitsneo]

After successful log in, you need to check the privilege of the same user from database. If the user has desired privileges then enable the password change form else disable that.

sir, i really dont have any idea on how to code that part.
im new to vb.
so could you please write that line of code for me.
thank you so much.

[ChrisPadgham]

first you will need to change your database table to store the privilege.
second change the SELECT statement to something like

sql = "SELECT Privilege FROM Usertbl WHERE ......

you can use the rec.recordcount to determine whether any records were returned.

rec!privelege to retrieve their privileges

[AndreRet]

As Chris has stated, the following -

You currently have

in my database. i have created two tables. one for the users which i renamed "userstbl" which includes the ff fields
1. userid (pk)
2. username
3. userpass

the second table is called "privtbl" which includes the ff fields.
1. privilege (not auto number. i assign 1's randomly)

Add privilege to your "userstbl". This save you plenty of extra coding just to get the value from the second table "privtbl"

Now, add a module to your app and declare the variable that will hold the value of the user -

''In your module below Option explicit

Public xUser As Integer

''In your Form1 part, where the username and password is true the following -

Else
      ' a condition should be written here. or perhaps another elseif
         xUser = db!privilege
            Form2.Show
            Unload Me

This value will now be available throughout your application. Lets say that on form2 you have a button that will enable the user to edit his username or password, the following -

Private Sub Form_Load()

if xUser = 1 Then
  ''User can edit data...
  cmdEdit.Enabled = True
     Else
  cmdEdit.Enabled = False
End if
End Sub