Issue pulling a user type from table at login

I have set up a simple login validation via this bit of VB

Dim LogonValid As Integer
  LogonValid = Nz(DCount("*", "tblLogIn", "([UserName] = '" & Me("txtUserName") & "') and ([Password] = '" & Me("txtPassword") & "')"), 0)
If LogonValid > 0 Then
  DoCmd.OpenForm "frmNavigation"
  Forms!frmNavigation!txtUser = Me.txtUserName
  Me.txtUserName = ""
  Me.txtPassword = ""
  MsgBox "Invalid Login"
End If

End Sub

Open in new window

Now I find that I need to pull another value from the tblLogIn.  I need to lookup the AcctType from tblLogIn and place it on my frmNavigation in a textbox named txtAcctType.

The navigation form is always open and would provide the mechanism for me to reach back and verify that the user that is currently logged in has access to different parts of the db and forms later.

Thanks in advance.
LVL 10
Tony HungateDirector of TrainingAsked:
Who is Participating?
Tony HungateDirector of TrainingAuthor Commented:
Sorry all, I somehow posted this questions twice.

Please see this link for remainder of the solution.  Thank you everyone for your quick response.
Hi, you should look at DLookup, eg

dim sAcctType as sstring

sAcctType=dlookup("AcctType", "tblLogin","([UserName] = '" & Me("txtUserName") & "') and ([Password] = '" & Me("txtPassword") & "')")
if isnull(sAcctType) then
 MsgBox "Invalid Login"
 ... go off and do something eg
end if
Is frmNavigation bound to anything (its recordsource property)?

If not, you could bind it to tblLogin, and have hidden textboxes to hold AcctType  and any other info that you need from tblLogin.

Like that, you could refer to these things from anywhere else in your database like this:

If Forms!frmNavigation.txtAcctType = "Admin" Then '<--- refers to a textbox on frmNavigation named txtAcctType

Open in new window

Just a note, if you do what I suggested in my previous comment, you would have to open your navigation form filtered for the specific user like this:

  DoCmd.OpenForm "frmNavigation",,,"[UserName] = '" & Me("txtUserName") & "'"

Open in new window

Tony HungateDirector of TrainingAuthor Commented:
Duplicated Question.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.