Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 48
  1. #16
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737

    I like a challenge sometimes, so I figured out the syntax for assigning the controlsource property to a textbox on form load. It's convoluted but works (note that in this case the criteria value is a number and I just assigned it rather than trying to get a value from some other place):

    Code:
    Private Sub Form_Load()
    Dim lngEntry As Long
    
    lngEntry = 1
    
    Me.Text3.ControlSource = "=DLookup(""[fieldNameHere]"",""[tableNameHere]"",""[criteriaFieldNameHere]=" & lngEntry & """)"
    
    End Sub
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  2. #17
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Here is what I had for my Login button
    Code:
    Private Sub cmdLogin_Click()
    Dim strFormName As String
    
    strFormName = Me.Name
    ' Check the password
    If Nz(Me.txtUserPassword, "InvalidPassword") <> Me.cboEmployeeID.Column(2) Then
        MsgBox "Invalid Password "
        ilogins = ilogins + 1
        Me.txtUserPassword = ""
        Me.txtUserPassword.SetFocus
        If ilogins > 2 Then
            DoCmd.Quit
        End If
        
    Else
        TempVars("EmployeeID").Value = Me.cboEmployeeID.Column(0)
        TempVars("Employee").Value = Me.cboEmployeeID.Column(1)
        TempVars("UserLevel").Value = DLookup("DataOrder", "tblLookup", "LookupID = " & Me.cboEmployeeID.Column(3))
        DoCmd.OpenForm "Switchboard"
        DoCmd.Close acForm, strFormName
    End If
    End Sub
    Here is what I have for the Employee combo rowsource
    Code:
    SELECT tblUser.EmployeeID, [Forename] & " " & [Surname] AS FullName, tblUser.UserPassword, tblUser.UserLevel, tblUser.UserActive
    FROM tblEmployee INNER JOIN tblUser ON tblEmployee.EmployeeID=tblUser.EmployeeID
    WHERE (((tblUser.UserActive)=True));
    HTH
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #18
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    @Micron
    I know you like a challenge but it really doesn't need to be that complicated. See item 1 in post #15. It works!

    @Welshgasman
    Thanks for giving a TempVars example so the OP has an alternative. I still only rarely use TempVars myself
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #19
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    @ Micron

    Can you insert my field names into this so I can just copy the code?

    Am seriously not understanding all the fields

  5. #20
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What comes before the equals sign?
    = DLookup("FirstName", "tbl_login", "UserName = '" & GetUserName() & "'")

    If you want to assign that to the control's controlsource property it won't work as you have written? If you are setting the value property of the control that will work. My example sets the actual controlsource property, which is what I thought was being suggested. Or am I wrong, because I don't see you assigning the controlsource property in your example? Either way, it's not something I would do for this.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #21
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    @isladogs
    I created the module as you said
    And I used the code in the control source but it gives me a blank textbox when logged in.

    How will I apply it if I want to use the form load event?

  7. #22
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    @Emmanuel
    See post #15

    @Micron
    I'm assigning the control value in the Form_Load event
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #23
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    @isladogs
    When code is added to the on load event, the code turns red.

    Also how will the First name appear in the textbox since am not seeing the textbox name in the onload event code

  9. #24
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Code:
    me.yourtextboxname=DLookup(....etc) as in item 1 of post #15
    If you are still stuck, please post all the relevant code you are using ...or upload a cut down version of your database with all confidential data removed or replaced.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #25
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That's what I would do. However, I took this as a reference to the controsource property:
    The control source of your textbox will be something like
    =DLookup("FirstName", "tbl_Users", "UserName = '" & strUserName & "'")
    My mistake.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #26
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Can you insert my field names into this so I can just copy the code?
    I could but I don't suggest you do it that way. What I posted seems to be based on a misinterpretation of what Isladogs wrote about control source vs controlsource. I'd do it either of the other 2 ways suggested.

    I concur, copy, compact, zip and post a db that contains just enough to replicate what you want if you're still stuck.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #27
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    How do I upload file here?

  13. #28
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    See "how to attach files" at the top of the forum menubar.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #29
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    Seriously
    Am not finding difficulty locating it

  15. #30
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think that was supposed to be "am having difficulty locating it" - it being the link for attaching files?

    Click image for larger version. 

Name:	attachFiles.jpg 
Views:	16 
Size:	24.1 KB 
ID:	45192

    the link for that button is

    https://www.accessforums.net/showthread.php?t=70301
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Page 2 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 10-02-2014, 09:29 AM
  2. Replies: 3
    Last Post: 03-17-2014, 10:23 AM
  3. Replies: 6
    Last Post: 02-21-2013, 10:52 AM
  4. Username and passwod login using Form
    By Tom1 in forum Programming
    Replies: 4
    Last Post: 07-04-2012, 12:29 PM
  5. Replies: 3
    Last Post: 05-31-2012, 02:49 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums