asked on

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.

Rey Obrero (Capricorn1)

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

Tony Hungate

ASKER

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.

SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tony Hungate

ASKER

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:~

mbizup

What is the exact error message you are getting?

mbizup

Ooops...

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

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

Open in new window

Tony Hungate

ASKER

I have attached two screen shots to eliminate any miscommunicaiton.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tony Hungate

ASKER

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.

mbizup

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.