VBA code for Log In Form

[Breanna]

I am very new to access using 2013.

I have a log in form which has user name text box, password text box, login button. On the event procedure for the button I used VBA code to ensure correct username and password in order to open the main page(form). I am now trying to set it up so that when an admin user enters the correct info it opens a main page(form) and when a regular user enters their info it opens a different form. I used a video to follow the steps but I keep getting an error.


Run Time error 13 Type mismatch

I assume this means I have spelled something incorrectly. can someone please confirm thats what that error means or not? Below is the whole code and where the error is Bold. Am I missing any commas or things like that? This is actually the first code I have done so I am stuck. (it was working fine without the user level parts but I do need this to be working)

Any Help is greatly appreciated.


Private Sub LoginButton_Click()

Dim UserLevel As Integer




If IsNull(Me.txtUserName) Then
MsgBox "Please Enter User Name", vbinformation, "UserName Required"
Me.txtUserName.SetFocus

Else
If IsNull(Me.txtPassword) Then
MsgBox "Please Enter Password", vbinformation, "Password Required"
Me.txtPassword.SetFocus

Else
'process the job
If (IsNull(DLookup("[UserName]", "UserID", "[UserName]='" & Me.txtUserName.Value & " ' and password='" & Me.txtPassword.Value & "'"))) Then
MsgBox "Incorrect UserName or Password"

Else
UserLevel = DLookup("SecurityLevel", "UserID", "UserName = '" & Me.txtUserName.Value & "'")


DoCmd.Close
If UserLevel = 1 Then
MsgBox "UserName and Password Correct"
DoCmd.OpenForm "Main Page"
Else
DoCmd.OpenForm "UserPage"
End If


End If
End If

End If
End Sub

[Movian]

Personally I structure situations like this a little differently to keep things easier to understand. I do this by removing nests where possible when they are caused by multiple logic checks

for example I would do



'Check username is not blank
If IsNull(Me.txtUserName) and not me.txtusername = ""Then
MsgBox "Please Enter User Name", vbinformation, "UserName Required"
Me.txtUserName.SetFocus
exit sub
end if

'checkpassword is not blank
If IsNull(Me.txtPassword) and not me.txtpassword = "" Then
MsgBox "Please Enter Password", vbinformation, "Password Required"
Me.txtPassword.SetFocus
exit sub
end if

'process authentication


However this is a personal preference but it breaks up these logic checks into their own individual entity and removes nesting confusion from the main function of comparing the username and password

onto your specific issue, Type mismatch indicates that your are comparing or trying to put data of one type into a variable of a different type. For example in this instance you have a variable named user level defined as an integer type

an integer can only support or work with whole numbers, its possible that the value that is being returned from your query is of a different type such as a string (with characters in it), thats where I would start looking next.

another quick question you have a docmd.close what is it you are trying to close ? if its the local form I would suggest specifying that explicitly docmd.close acform, me.name


also I am unsure how secure your login needs to be but it looks like your storing your password in plain text, you may want to look at adding a function for generating a hash, there is a lot of information available on creating and using hashes for password systems.

[Breanna]

Oh man after searching for this answer for a few days now I finally posted on the forum as a last result because I needed more hands on outside help. After I posted I had an idea pop in my head and it worked. I feel silly now lol. But in case anyone wanted the solution here it is.


Private Sub LoginButton_Click()







If IsNull(Me.txtUserName) Then
MsgBox "Please Enter User Name", vbinformation, "UserName Required"
Me.txtUserName.SetFocus

Else
If IsNull(Me.txtPassword) Then
MsgBox "Please Enter Password", vbinformation, "Password Required"
Me.txtPassword.SetFocus

Else
'process the job
If (IsNull(DLookup("[UserName]", "UserID", "[UserName]='" & Me.txtUserName.Value & " ' and password='" & Me.txtPassword.Value & "'"))) Then
MsgBox "Incorrect UserName or Password"

Else
UserLevel = DLookup("[SecurityLevel]", "UserID", "UserName = '" & Me.txtUserName.Value & "'")

DoCmd.Close
If UserLevel = "Admin" Then
MsgBox "UserName and Password Correct"
DoCmd.OpenForm "Main Page"
Else
DoCmd.OpenForm "UserPage"
End If


End If
End If

End If
End Sub




Originally I was using one table with user login, password, security level then another table with id and security level and trying to get the vba code to lookup from a relationship between the two. But after thinking about it it didnt make sence to have both so I took away the second table and instead of using a number assocatied with the level I just used the level. This actually worked I am excited. :)

[Movian]

ok it looks like what you did is remove the deceleration of the variable.

I would not recommend this solution as you still using the variable


UserLevel = DLookup("[SecurityLevel]", "UserID", "UserName = '" & Me.txtUserName.Value & "'")

This still makes a variable called UserLevel however now instead of you telling the computer what sort of variable to use (String, Integer etc ) the system tries to guess for you and its not allways very good at doing that.

I would suggest at the very top of your form just under where it says

Option Compare Database

add a second line that says

Option Explicit

This is a best practice, it forces you to declare your variables however it improves program efficiency and I would always recommend doing this.

[Breanna]

....
onto your specific issue, Type mismatch indicates that your are comparing or trying to put data of one type into a variable of a different type. For example in this instance you have a variable named user level defined as an integer type

an integer can only support or work with whole numbers, its possible that the value that is being returned from your query is of a different type such as a string (with characters in it), thats where I would start looking next.

another quick question you have a docmd.close what is it you are trying to close ? if its the local form I would suggest specifying that explicitly docmd.close acform, me.name


also I am unsure how secure your login needs to be but it looks like your storing your password in plain text, you may want to look at adding a function for generating a hash, there is a lot of information available on creating and using hashes for password systems.

Thanks! what you explained about the user level integer makes sense with what I did the change the code so it works. I have already figured out how to fix it (I know silly I had an idea to try right after I posted this) From what you said though my changes actually make sense to me instead of just changing to see it if worked.

The close is referring to the login form. After the correct login is issued the form closes before opening the main page. I will try adding what you said tho to ensure no confusion in the future that is a good idea.

As for the log in form when you type in a password it does use *** instead of plain text. This is set up in the text box of password and not the on click procedure of the button. I do need to change that in the table with the passwords as well. It just has not been my top priority since I only have two stored in there right now to get this part working.


Thank You So Much! this has given me things to consider and remember.

[Breanna]

ok it looks like what you did is remove the deceleration of the variable.

I would not recommend this solution as you still using the variable


UserLevel = DLookup("[SecurityLevel]", "UserID", "UserName = '" & Me.txtUserName.Value & "'")

This still makes a variable called UserLevel however now instead of you telling the computer what sort of variable to use (String, Integer etc ) the system tries to guess for you and its not allways very good at doing that.

I would suggest at the very top of your form just under where it says

Option Compare Database

add a second line that says

Option Explicit

This is a best practice, it forces you to declare your variables however it improves program efficiency and I would always recommend doing this.

After I enter option explicit then what? add back the dim Userlever as then what. I cant use integer since it is not a number. would I use string?

[jonh]

String sounds right. Yes.

[Breanna]

String sounds right. Yes.

I just tried it and it worked. Thanks for confirming :)

[jonh]

Cool. :)

Datatypes are generaly a string, date, number or boolean.

String is anything containing text.
Date is a date or maybe time.
Number (byte,integer,double,long,currency) numbers with differnt precisions.
And boolean is true/false or in english yes or no.