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 = ""
 
  
Else
  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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:


Dim LogonValid As Integer, accType as string
 
  LogonValid = Nz(DCount("*", "tblLogIn", "([UserName] = '" & Me("txtUserName") & "') and ([Password] = '" & Me("txtPassword") & "')"), 0)
 
If LogonValid > 0 Then
 
  accType= Nz(DLookup("AcctType", "tblLogIn", "([UserName] = '" & Me("txtUserName") & "') and ([Password] = '" & Me("txtPassword") & "')"), "")

  DoCmd.OpenForm "frmNavigation"
  Forms!frmNavigation!txtUser = Me.txtUserName
  Forms!frmNavigation!txtAcctType='" & accType & "'"
  Me.txtUserName = ""
  Me.txtPassword = ""
 
 
Else
  MsgBox "Invalid Login"
End If

End Sub
0
Tony HungateDirector of TrainingAuthor Commented:
I am not very good with syntax beyond the very basic, and the solution provided is throwing a syntax error on this line.

Forms!frmNavigation!txtAcctType='" & accType & "'"

Open in new window


Thanks for the quick response.
0
mbizupCommented:
Okay - it looks like you are deleting the duplicate question, so I'll post my suggestion here...


1. Set the recordsource property of frmNavigation to tblLogIn (if it doesn't have a recordource property already.

2. On frmNavigation, set the control source properties of txtAcctType to AccountType and txtUser to UserName (from tblLogin)

3. Change your validation code as shown below.

With this setup, you can refer to txtAcctType, txtUser and any other fields from tblLogin at any time, and from any form in your database, as long as frmNavigation remains open.

Validation code:

Dim LogonValid As Integer
  
  LogonValid = Nz(DCount("*", "tblLogIn", "([UserName] = '" & Me("txtUserName") & "') and ([Password] = '" & Me("txtPassword") & "')"), 0)
  
If LogonValid > 0 Then

  ' Open the navigation form, filtered to the specific user
  DoCmd.OpenForm "frmNavigation",,, "UserName = '" & Me.txtUserName "'"

  '''*** Take this line out - with this setup it is not needed
  '''' Forms!frmNavigation!txtUser = Me.txtUserName
  ''''*****************************************

  Me.txtUserName = ""
  Me.txtPassword = ""
 
  
Else
  MsgBox "Invalid Login"
End If

End Sub 

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Tony HungateDirector of TrainingAuthor Commented:
mbizup,

I have attempted to use utilize your solution.  I have set the recordsoruce for frmNavigation to tblLogIn, and set the rowsource for both txtUser and txtAcctType to their respective fields within the table.

I am still getting a syntax error on my login forms login button click event, from the following line.

  DoCmd.OpenForm "frmNavigation",,, "UserName = '" & Me.txtUserName "'"

Open in new window


Thanks for your assistance.

~:TLH:~
0
mbizupCommented:
What is the exact error message you are getting?
0
mbizupCommented:
Ooops...

Try this (I missed an '&' at the end):

  DoCmd.OpenForm "frmNavigation",,, "UserName = '" & Me.txtUserName & "'" 

Open in new window

0
Tony HungateDirector of TrainingAuthor Commented:
I have attached two screen shots to eliminate any miscommunicaiton.

 Error Msg scrsht Code Highlighted scrsht
0
Rey Obrero (Capricorn1)Commented:
try this


Dim LogonValid As Integer, accType as string
 
  LogonValid = Nz(DCount("*", "tblLogIn", "([UserName] = '" & Me("txtUserName") & "') and ([Password] = '" & Me("txtPassword") & "')"), 0)
 
If LogonValid > 0 Then
 
 
  DoCmd.OpenForm "frmNavigation"
  Forms!frmNavigation!txtUser = Me.txtUserName
  Forms!frmNavigation!txtAcctType= Nz(DLookup("AcctType", "tblLogIn", "([UserName] = '" & Me("txtUserName") & "') and ([Password] = '" & Me("txtPassword") & "')"), "")

  Me.txtUserName = ""
  Me.txtPassword = ""
 
 
Else
  MsgBox "Invalid Login"
End If

End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tony HungateDirector of TrainingAuthor Commented:
capricorn1

That did it.  Thanks for your help.

mbizup,

I will award you partial points at the final solution was a combination of works.

Thanks all.
0
mbizupCommented:
t__hungate,

I'm not objecting to the point split - cap1 has also posted a working solution. but my post here should have resolved the error in my method: http:#a37364799


The point is, however, that in your application you should use one solution or the other (they are different methods).  With a bound form, you dont need the code to put values in the boxes after opening the form.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.