How to Open a Form using If-then-Else condition

[Brad_Newman]

Hi All,

I am pretty new to access so i am using a very basic function structure to open a navigation form using if-then-else condition,

Below is my VB coding;
-----------------------------------------------------------------------------------------------

Option Compare Database
Private intLogonAttempts As Integer

Private Sub Emp_Exit_Click()
DoCmd.Quit
End Sub

Private Sub EmpLogin_Click()

'Check to see if data is entered into the UserName combo box

    If IsNull(Me.Username) Or Me.Username = "" Then
            MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
            Me.Username.SetFocus
        Exit Sub
    End If

'Check to see if data is entered into the password box

    If IsNull(Me.TextPassword) Or Me.TextPassword = "" Then
            MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
            Me.TextPassword.SetFocus
        Exit Sub
    End If

'Check value of password in tblEmployees to see if this matches value chosen in combo box

    If Me.TextPassword.Value = DLookup("Password", "AgentDetails", "[ID]=" & Me.Username.Value) Then

        MyID = Me.Username.Value

'Close logon form and open splash screen
               
        DoCmd.Close acForm, "Login Form", acSaveNo
        
        If Me.Username.Value = Akbar Then
        
        DoCmd.OpenForm "Akbar_Nav_Form", acPreview
           
        ElseIf Me.Username = Badri Then
        DoCmd.OpenForm "Badri_Nav_Form", acPreview
       
        
        End If
        MsgBox "Password Invalid.  Please Try Again", vbCritical + vbOKOnly, "Invalid Entry!"
        Me.TextPassword.SetFocus
    
     End If
'If User Enters incorrect password 3 times database will shutdown
    
    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
        MsgBox "You do not have access to this database.  Please contact your system administrator.", vbCritical, "Restricted Access!"
        Application.Quit
    End If
    
End Sub

---------------------------------------------------------------------------------------------
I am getting an run time error '2467': "The Expression you entered refers to an object that is closed or doesn't exist"

Can anyone help me out ??

 

[Mihail]

Highlight the line where error occur.

 

[RainLover]

First add this line.

Option Explicit

Immediately after the line Option Compare Database

You haven't given us much to go by.

Suggest you Rem out most of your code. Then add back in a section at a time. When you find what errors copy and past that so we can have a look.

BTW It is your choice but most people do not use a prefix like "int" for integer. A bit of old school.

 

[Brad_Newman]

HI Mihail,

Thanks for replying back....Pls see below is the colored statement where I am getting this error:

Option Compare Database
Private intLogonAttempts As Integer

Private Sub Emp_Exit_Click()
DoCmd.Quit
End Sub

Private Sub EmpLogin_Click()

'Check to see if data is entered into the UserName combo box

    If IsNull(Me.Username) Or Me.Username = "" Then
            MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
            Me.Username.SetFocus
        Exit Sub
    End If

'Check to see if data is entered into the password box

    If IsNull(Me.TextPassword) Or Me.TextPassword = "" Then
            MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
            Me.TextPassword.SetFocus
        Exit Sub
    End If

'Check value of password in tblEmployees to see if this matches value chosen in combo box

    If Me.TextPassword.Value = DLookup("Password", "AgentDetails", "[ID]=" & Me.Username.Value) Then

        MyID = Me.Username.Value

'Close logon form and open splash screen
               
        DoCmd.Close acForm, "Login Form", acSaveNo
        
        [COLOR=Red][B]If Me.Username.Value = Akbar Then[/B][/COLOR] :banghead:
        
        DoCmd.OpenForm "Akbar_Nav_Form", acPreview
           
        ElseIf Me.Username = Badri Then
        DoCmd.OpenForm "Badri_Nav_Form", acPreview
       
        
        End If
        MsgBox "Password Invalid.  Please Try Again", vbCritical + vbOKOnly, "Invalid Entry!"
        Me.TextPassword.SetFocus
    
     End If
'If User Enters incorrect password 3 times database will shutdown
    
    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
        MsgBox "You do not have access to this database.  Please contact  your system administrator.", vbCritical, "Restricted Access!"
        Application.Quit
    End If
    
End Sub

so as you can see the above coding, not sure if it's right....what I am trying to do is, if the Username taken from the combo box is = the username I have mentioned in the employee table then it should open the employees respective form.....

I hope this gives a better picture now... pls help...

 

[Brad_Newman]

Hi Rain Lover,

Thanks for your suggestion.

I added the code and now I am getting a error which says " Variable Not Defined".
Please see the attachment for reference and pls help...

 

[GinaWhipp]

First, replace...

If Me.Username.Value = Akbar Then

with

If Me.Username.Value = "Akbar" Then

Text my be enclosed within quotations. As for you other issue, add...

Dim MyID As Long 'If numeric

OR

Dim MyID As String 'If Text

...under Private Sub EmpLogin_Click()

 

[Mihail]

if the Username taken from the combo box is = the username I have mentioned in the employee table then it should open the employees respective form.....

I think that still will not working because the combo is a bound control that should store the ID (number), not the name (text).
If will work, means that your employee table has incorrect structure . No mistake here: INcorrect structure.

 

[RainLover]

Hi Rain Lover,

Thanks for your suggestion.

I added the code and now I am getting a error which says " Variable Not Defined".
Please see the attachment for reference and pls help...

I did expect that error so don't panic.

Option Exolicit forces you to declare every Variable as to what type it is. Namely Number, Currency, Text etc. If you don't it will become a Variant which means it takes on what type it thinks it should be. So standard pracctice should be to include this in every module.

DoCmd.Close acForm, "Login Form", acSaveNo

Your use of acSaveNo is incorrect. acSave refers to the design of the form not the data it contains. In your case it does not do any harm as the Record will be save when it looses focus. However it would be better not to use redundant code.

You also use ".Value" This is also redundant in your situation. If you want to know more place your cursor over it and press F1 for help.

Other than coding errors which are currently being fix you do have a serious problem with your method. You are "Hard Coding"

This is where you have named just two of your users and have special forms for each. When it comes time to add a few more users then things get confusing because only you can add them in the code.

It would be better if these users were stored in a Table and to reference them with DLookup.

Hope this helps.

 

[nanscombe]

I am getting an run time error '2467': "The Expression you entered refers to an object that is closed or doesn't exist"

Would Me.Username happen to be on the "Login Form" that you've just closed?

[B]DoCmd.Close[/B] acForm, "Login Form", acSaveNo
        
        [COLOR="Red"]If Me.Username.Value = Akbar Then[/COLOR] :banghead:

 

[nanscombe]

I've created a mockup of what I think your Login Form looks like and have made a few little tweaks to it.

I have also added an extra column called NavForm to your AgentDetails table which would allow you to assign a custom Navigation Form name to any given user, or groups of user.

Feel free to have a look around and see what you think.

 

[Brad_Newman]

I've created a mockup of what I think your Login Form looks like and have made a few little tweaks to it.

I have also added an extra column called NavForm to your AgentDetails table which would allow you to assign a custom Navigation Form name to any given user, or groups of user.

Feel free to have a look around and see what you think.

Thanks A TON man ...this is what exactly i was looking for....Now I came to know that the coding I was giving was so stupid....I really need to look into this more n more...

i am a beginner, can you suggest me any site where I can find Tutorials to learn VB?

 

[bonniebelle200]

I've created a mockup of what I think your Login Form looks like and have made a few little tweaks to it.

I have also added an extra column called NavForm to your AgentDetails table which would allow you to assign a custom Navigation Form name to any given user, or groups of user.

Feel free to have a look around and see what you think.

I just want to say thank you Brad. I was :banghead: But I checked before making a post about my problem and I found this reply you gave that is exactly on point. I've downloaded the zip file for reference.
Now I'm . BonnieBelle

 

[nanscombe]

Pssst. Did you mean Brad or Nigel?

 

[RainLover]

Pssst. Did you mean Brad or Nigel?

I think he must mean you as Brad is the name of the Database.

Nigel you have an incorrect use of Dirty on each form

 

[nanscombe]

Nigel you have an incorrect use of Dirty on each form

Whoops, have I?

Do you mean

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click


    [COLOR="Red"]If Me.Dirty Then Me.Dirty = False[/COLOR]
    DoCmd.Close

Exit_cmdClose_Click:
    Exit Sub

Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
    
End Sub

The code was actually written by Access 2010's inbuilt Command button Wizard, I just amended the labels.

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click


    [COLOR="Red"]If Me.Dirty Then Me.Dirty = False[/COLOR]
    DoCmd.Close

Exit_Command0_Click:
    Exit Sub

Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click
    
End Sub

I usually just use "DoCmd.Close acForm, Me.name, acSaveNo" but I wanted the embedded picture on the button.