Populating unbound text box based on login form - Code breaking

[gori1084]

Hi everyone,

though the title makes it look way more complex than it, I think it won't be as hard for someone with coding knowledge.

Dummy DB.zip

I have this database that opens with a login form, whereby the user accesses it. I have a table that stores the login information of customers and a code that validates that the right info is entered. Now I would like to filter the the results displayed by the form that open upon successful login, by populating an unbound textbox in the form with the "UserRegion" of the person who just logged in. This information is also stored in the table with the users login information. I can't get the code to work so I'm attaching the DB to this thread, hoping that some of you could take a look.

Please login by just clicking on the button without changing user/password info.

The piece of code I'm struggling with is in the form Sponsors and is the following:

Form("sponsors").Controls("USERREGION").Value = DLookup("[Region]", "Users", "[LoginID] =" & Employee.Value)

Thank you all so much.



B

[ranman256]

Simply:
forms!sponsors!USERREGION= DLookup...

(use the builder!)

[gori1084]

Sorry but what do you mean the builder? there is no builder in code view!

[Wher]

This slightly modified version of your code seems to solve the issue:

Private Sub Command9_Click()
Dim lgID As Long
'Check to see if data is entered into the UserName combo box


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


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


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


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


    If Password.Value = DLookup("[Password]", "Users", _
            "[LoginID] =" & Employee.Value) Then


        LoginID = Employee.Value
        lgID = LoginID
        'Hide logon form and open splash screen
        Visible = True
        DoCmd.OpenForm "Sponsors"


    Else
      MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
            "Invalid Entry!"
        Password.SetFocus
    End If


    DoCmd.Close acForm, "LoginForm", acSaveNo


    'Filter by region
    
    Form_Sponsors.Counterparties_Subform1.Controls(8) = DLookup("[Region]", "Users", "[LoginID] =" & lgID)
    
  
    '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 admin.", _
               vbCritical, "Restricted Access!"
        Application.Quit
    End If


    
End Sub

[gori1084]

Thank you but I tried and it keeps breaking. If you look at the formula, there are things I don't understand. Did you modify some names in the database?

Form_Sponsors.Counterparties_Subform1.Controls(8) = DLookup("[Region]", "Users", "[LoginID] =" & lgID)

What is the (8)?
What is the LgID at the end?

Are these new fields/names you used?

[Wher]

When i open your example database and modify the code as mentioned, it works for me.
In the Immediate Window i got this result (without modifying any form name):

?Form_Sponsors.Counterparties_Subform1.Controls(8).name
UserRegion

See attachment.

[gori1084]

Well, ok that may be due to some naming issues. But still I'm surprised you are picking up that result. If you log in to the database with the credentials that are in the login box by default, you should not have UserRegion as a result. That is the header of the field in the table. You should pick up the region associated with the Login ID you used at the beginning. Namely User 332211 who is associated with EMEA as UserRegion. Am I misreading something here?

[Wher]

After logging in i get the result as seen in the attachment:

[June7]

That is a form/subform arrangement. The code is behind main form but UserRegion is behind subform. Must reference subform container control.

You are closing the Login form too soon.

This works for me:

Forms!sponsors.[Counterparties Subform1]!UserRegion = DLookup("[Region]", "Users", "[LoginID] =" & Employee.Value)
DoCmd.Close acForm, "LoginForm", acSaveNo


Setting a variable named lgID as Wher shows works also.


Also, remove Users table from the main form RecordSource.


Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[gori1084]

Thanks so much to both of you. I have used June7 explanations to figure out what I was doing wrong and used pretty much the same version of the code that Wher gave me. Thank you, thank you, thank you. I seriously thought I was gonna go insane on this.

B