Open a specific record after login

A

aym

Registered User.
Local time
Today, 05:49
Joined
Aug 20, 2017
Messages
47
Hello house

Hello experts

I have been trying for months to get my login form to open up to a specific record on the main form(NOTE PLS my main form is called "Navigation Form"). I have a table that store (ID, name, sex, country, Address etc) which i used to create my main form.

I also have a table called tblUser which stores (ID, password and login name.) I built a login form that allows the user to type in their password and login name.

I am attaching the file. My aim is to get user login
form to display a particular record on the main form
The code only display first record ID (1) of the main form when login.

PLS what will be added to the code to enable a specific form to open after login.
Am not that good in writing vba code. Pls help me out


Option Compare Database

Private Sub Command1_Click()
Dim User As String
Dim UserLevel As Integer
Dim TempPass As String
Dim ID As Integer
Dim UserName As String
Dim TempID As String

If IsNull(Me.txtUserName) Then
MsgBox "Please enter UserName", vbInformation, "Username required"
Me.txtUserName.SetFocus
ElseIf IsNull(Me.txtPassword) Then
MsgBox "Please enter Password", vbInformation, "Password required"
Me.txtPassword.SetFocus
Else
If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin = '" & Me.txtUserName.Value & "' And UserPassword = '" & Me.txtPassword.Value & "'"))) Then
MsgBox "Invalid Username or Password!"
Else
TempID = Me.txtUserName.Value
UserName = DLookup("[UserName]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
UserLevel = DLookup("[UserType]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
TempPass = DLookup("[UserPassword]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
UserLogin = DLookup("[UserLogin]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
DoCmd.Close
If (TempPass = "password") Then
MsgBox "Please change Password", vbInformation, "New password required"
DoCmd.OpenForm "frmUserinfo", , , "[UserLogin] = " & UserLogin
Else
'open different form according to user level
If UserLevel = 1 Then ' for admin
DoCmd.OpenForm "Admin Form"
Else
DoCmd.OpenForm "Navigation Form"
End If

End If
End If
End If
End Sub

Private Sub Form_Load()
Me.txtUserName.SetFocus
End Sub
 
It looks like you are closing the form before the code will finish running - I've tidied it up a bit to make the logical flow a bit more obvious.
Code: 
Private Sub Command1_Click()
    Dim User             As String
    Dim UserLevel        As Integer
    Dim TempPass         As String
    Dim ID               As Integer
    Dim UserName         As String
    Dim TempID           As String

    If IsNull(Me.txtUserName) Then
        MsgBox "Please enter UserName", vbInformation, "Username required"
        Me.txtUserName.SetFocus
        Exit Sub     [COLOR="Green"]'May as well exit here - nothing more to see[/COLOR]
    End If
    If IsNull(Me.txtPassword) Then
        MsgBox "Please enter Password", vbInformation, "Password required"
        Me.txtPassword.SetFocus
        Exit Sub     [COLOR="green"]'Ditto - leave now[/COLOR]
    End If

    If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin = '" & Me.txtUserName.Value & "' And UserPassword = '" & Me.txtPassword.Value & "'"))) Then
        MsgBox "Invalid Username or Password!"
        Exit Sub     [COLOR="green"]'And again leave the code as we aren't doing anything else[/COLOR]
    End If

    TempID = Me.txtUserName.Value
    UserName = DLookup("[UserName]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
    UserLevel = DLookup("[UserType]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
    TempPass = DLookup("[UserPassword]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
    UserLogin = DLookup("[UserLogin]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")

    DoCmd.Close    [COLOR="Red"] 'This must close the form you are on - I think it will stop processing the code at this point?[/COLOR]

    If (TempPass = "password") Then
        MsgBox "Please change Password", vbInformation, "New password required"
        DoCmd.OpenForm "frmUserinfo", , , "[UserLogin] = " & UserLogin
    Else
      [COLOR="green"]  'open different form according to user level[/COLOR]
        If UserLevel = 1 Then        ' for admin
            DoCmd.OpenForm "Admin Form"
        Else
            DoCmd.OpenForm "Navigation Form"
        End If

    End If

End Sub
 
here is a little clean up of your code. If there other concerns, please let us know.

Code: 
Option Compare Database
Option Explicit

Private Sub Command1_Click()
    Dim User As String
    Dim UserLevel As Integer
    Dim TempPass As String
    Dim ID As Integer
    Dim UserName As String
    Dim TempID As String
    
    If IsNull(Me.txtUserName) Then
        MsgBox "Please enter UserName", vbInformation, "Username required"
        Me.txtUserName.SetFocus
        Exit Sub
    End If
    If IsNull(Me.txtPassword) Then
        MsgBox "Please enter Password", vbInformation, "Password required"
        Me.txtPassword.SetFocus
        Exit Sub
    End If
    If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin = '" & Me.txtUserName.Value & "' And UserPassword = '" & Me.txtPassword.Value & "'"))) Then
        MsgBox "Invalid Username or Password!"
        Me.txtPassword.SetFocus
        Exit Sub
    End If
    
    TempID = Me.txtUserName.Value
    UserName = DLookup("[UserName]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
    UserLevel = DLookup("[UserType]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
    TempPass = DLookup("[UserPassword]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
    UserLogin = DLookup("[UserLogin]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
    
    ' You don't close this form when there are codes below that need
    ' to be processed. Otherwise it will fail.
    'DoCmd.Close

    ' Just hide the form for a while instead of closing it
    Me.Visible = False
    
    If (TempPass = "password") Then
        MsgBox "Please change Password", vbInformation, "New password required"
        DoCmd.OpenForm "frmUserinfo", , , "[UserLogin] = " & UserLogin
    End If
    
    'open different form according to user level
    If UserLevel = 1 Then ' for admin
        DoCmd.OpenForm "Admin Form"
    Else
        DoCmd.OpenForm "Navigation Form"
    End If
    ' Now we can close this form
    DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_Load()
    Me.txtUserName.SetFocus
End Sub
 
here is a sample Login form that I am using.
It might not be useful to you, but take a look
maybe it will change your mind.

use "common" for both username and password.
instruction on Home form.
Enjoy.
 

Attachments

FYI, I moved the thread out of the introductions forum.
 
arnelgp said:
here is a little clean up of your code. If there other concerns, please let us know.

Code: 
Option Compare Database
Option Explicit

Private Sub Command1_Click()
    Dim User As String
    Dim UserLevel As Integer
    Dim TempPass As String
    Dim ID As Integer
    Dim UserName As String
    Dim TempID As String
    
    If IsNull(Me.txtUserName) Then
        MsgBox "Please enter UserName", vbInformation, "Username required"
        Me.txtUserName.SetFocus
        Exit Sub
    End If
    If IsNull(Me.txtPassword) Then
        MsgBox "Please enter Password", vbInformation, "Password required"
        Me.txtPassword.SetFocus
        Exit Sub
    End If
    If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin = '" & Me.txtUserName.Value & "' And UserPassword = '" & Me.txtPassword.Value & "'"))) Then
        MsgBox "Invalid Username or Password!"
        Me.txtPassword.SetFocus
        Exit Sub
    End If
    
    TempID = Me.txtUserName.Value
    UserName = DLookup("[UserName]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
    UserLevel = DLookup("[UserType]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
    TempPass = DLookup("[UserPassword]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
    UserLogin = DLookup("[UserLogin]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
    
    ' You don't close this form when there are codes below that need
    ' to be processed. Otherwise it will fail.
    'DoCmd.Close

    ' Just hide the form for a while instead of closing it
    Me.Visible = False
    
    If (TempPass = "password") Then
        MsgBox "Please change Password", vbInformation, "New password required"
        DoCmd.OpenForm "frmUserinfo", , , "[UserLogin] = " & UserLogin
    End If
    
    'open different form according to user level
    If UserLevel = 1 Then ' for admin
        DoCmd.OpenForm "Admin Form"
    Else
        DoCmd.OpenForm "Navigation Form"
    End If
    ' Now we can close this form
    DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_Load()
    Me.txtUserName.SetFocus
End Sub
Click to expand...


Good day sir,
Thanks for answering my question, am very grateful.
I have enter the code in my vba, but an error message Variable not define and when i click on OK It take me to this line "[UserLogin] = '" & Me.txtUserName.Value & "'") highlightedd "[UserLogin]= to blue

Please sir what is the problem. I really need your help sir.
Thanks
 
Minty said:
It looks like you are closing the form before the code will finish running - I've tidied it up a bit to make the logical flow a bit more obvious.
Code: 
Private Sub Command1_Click()
    Dim User             As String
    Dim UserLevel        As Integer
    Dim TempPass         As String
    Dim ID               As Integer
    Dim UserName         As String
    Dim TempID           As String

    If IsNull(Me.txtUserName) Then
        MsgBox "Please enter UserName", vbInformation, "Username required"
        Me.txtUserName.SetFocus
        Exit Sub     [COLOR="Green"]'May as well exit here - nothing more to see[/COLOR]
    End If
    If IsNull(Me.txtPassword) Then
        MsgBox "Please enter Password", vbInformation, "Password required"
        Me.txtPassword.SetFocus
        Exit Sub     [COLOR="green"]'Ditto - leave now[/COLOR]
    End If

    If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin = '" & Me.txtUserName.Value & "' And UserPassword = '" & Me.txtPassword.Value & "'"))) Then
        MsgBox "Invalid Username or Password!"
        Exit Sub     [COLOR="green"]'And again leave the code as we aren't doing anything else[/COLOR]
    End If

    TempID = Me.txtUserName.Value
    UserName = DLookup("[UserName]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
    UserLevel = DLookup("[UserType]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
    TempPass = DLookup("[UserPassword]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
    UserLogin = DLookup("[UserLogin]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")

    DoCmd.Close    [COLOR="Red"] 'This must close the form you are on - I think it will stop processing the code at this point?[/COLOR]

    If (TempPass = "password") Then
        MsgBox "Please change Password", vbInformation, "New password required"
        DoCmd.OpenForm "frmUserinfo", , , "[UserLogin] = " & UserLogin
    Else
      [COLOR="green"]  'open different form according to user level[/COLOR]
        If UserLevel = 1 Then        ' for admin
            DoCmd.OpenForm "Admin Form"
        Else
            DoCmd.OpenForm "Navigation Form"
        End If

    End If

End Sub
Click to expand...

Thanks for your respond sir, i really i appreciate your respond sir.

Sir i have a problem with the code, after entry the code an error message "The expression you entered refers to an object that is closed or doesn't exit". And when i click on debug it highlighted this statement line YELLOW " 'This must close the form you are on - I think it will stop processing the code at this point?" which mean there is a problem. Please help me to look into it sir. Am not that good in vba code.

thanks
 
hi Ma'am!

can you please check table tblUser if it has
a fieldname of UserLogin.
if you do, tell me what is the datatype (text, number, etc.)

if you dont have, then replace the UserLogin in the code
to the actual fieldname you have.

i am sure Me.txtUserName.Value is correct
because it passed the other IF statement there
before it reaches here and got error.

let me know.
 
Thanks for your respond sir i really appreciate your effort sir.

I love the code codes sir, but my question now is that how do i open my form using Your Login Form. and i want for example if a student login it should bring out such student record.

Am not that good when it comes to vba code

I really need your help sir.

thanks
 
you have variable ID there in your code, does this mean
this is the student ID?

do you have same field that holds student ID in tblUser?

how about the Navigation form, does it have student ID there?

if there is a Field in tblUser for student ID then we
have to extract that first from tblUser.

varStudentID = DLookup("[StudentIDField]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")

And if Navigation form also (on its recordsource) has student ID, we can open it with Where clause:

DoCmd.OpenForm "Navigation Form",,,"StudentID='" & varStudentID & "'"

----------
anyway, if it is possible to zip your database and post it,
we can understand it better, than merely explaining fragment
by fragment. if you want to resolve this the soonest you can
post it now.
 
arnelgp said:
you have variable ID there in your code, does this mean
this is the student ID?

do you have same field that holds student ID in tblUser?

how about the Navigation form, does it have student ID there?

if there is a Field in tblUser for student ID then we
have to extract that first from tblUser.

varStudentID = DLookup("[StudentIDField]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")

And if Navigation form also (on its recordsource) has student ID, we can open it with Where clause:

DoCmd.OpenForm "Navigation Form",,,"StudentID='" & varStudentID & "'"

----------
anyway, if it is possible to zip your database and post it,
we can understand it better, than merely explaining fragment
by fragment. if you want to resolve this the soonest you can
post it now.
Click to expand...


Thanks for your usual assistant

Am finding it difficult to upload the zip file here sir.
 
If you would like pm me and I'll give you my email. You can send it there and your dB is safe with me.
 
Click on my picture and send me a msg.
 
I pm u my email address, email me and attach the dB.
 
Still no mail.
 
You must log in or register to reply here.

Similar threads

C
User Login
Replies
8
Views
601
Isaac
Isaac
D
Solved Change password using tempvar not passing to following form
Replies
4
Views
801
Gasman
Gasman
M
Solved Password Validation sub
2
Replies
29
Views
2,906
mikenyby
M
D
Clearing data in a form after clicking print report
Replies
9
Views
1,247
Ken Sheridan
Ken Sheridan
D
Solved Highlighting Next Record After Current Record Filtered Out of Datasheet
Replies
17
Views
2,075
cheekybuddha
cheekybuddha

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom