Results 1 to 4 of 4
  1. #1
    NISMOJim is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2010
    Posts
    273

    If with Len not working

    Can someone please explain why this doesn't work? It's in the After Update part of cboEmpName.



    Code:
      Dim strEmp As String
      strEmp = Me.cboEmpName
      
       Me.txtEEmail = Nz(DLookup("EEmail", "qryLUEmpName", "[EName]=" & "'" & strEmp & "'"))
       Me.cboMgr = Nz(DLookup("EMgr", "qryLUEmpName", "[EName]=" & "'" & strEmp & "'"))
       Me.txtMgrE = Nz(DLookup("EMgrEmail", "qryLUEmpName", "[EName]=" & "'" & strEmp & "'"))
       
    'Place the cursor to the appropriate field
       If Len(Me.txtEEmail) < 2 Then
          Me.txtEEmail.SetFocus
        ElseIf Len(Me.cboMgr) < 2 Then
          Me.cboMgr.SetFocus
        ElseIf Len(Me.txtMgrE) < 2 Then
          Me.txtMgrE.SetFocus
        Else: Me.cboVstrName.SetFocus
       End If
    When I enter a name, I want the cursor to go to the EEmail field first if it is blank. If not, go to the EMgr field if it is blank. If not, then go to the MgrE field if it is blank. If all of these already have a value, then go to VstrName.
    The email, manager & manager email fields are all blank. When I press Enter after the Employee Name, it goes to EMgr instead of EEmail.
    If I add an address in EEmail in the table & go back to the form, the focus goes from cboEmpName to cboMgr like I want it to.
    If I add the address and a manager name in the table, the cursor jumps to cboVstrName, skipping txtMgrE.
    This seems so simple, but I am not seeing what is going wrong.
    Any ideas? Thanks!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why search on employee name instead of employee ID? Names make poor unique identifier.

    Have you step debugged?

    Why are you populating controls with DLookup? Is this duplicating data between tables?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    It would help if you said what does happen. Doesn't work could mean anything.

    Following on from June's comments
    Is the bound column of cboEmpName a string or a number field such as EmpID?

    Wherever you use Nz, it is best to define the alternative value if the DLookup is null... Nz(DLookup(……),"")
    One simple way of step debugging involves placing a different message box in each part of the If...Else...End If code, then seeing what is displayed when the code is run
    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. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If Len(Me.txtEEmail) < 2 Then

    If control value is Null, Len function returns Null, thus cannot be compared to < 2
    The solution can be Colin said, but it would be a wild guess as to where to do it given that the data types are not obvious.
    The following
    Nz(len(forms!form2.hospital),0)<2 might work, but might also balk because the default is a number and variable might be text.

    Conversely, len(forms!form2.hospital & vbnullstring)<2 might work but the data type might be numeric.
    Last edited by Micron; 07-19-2019 at 02:03 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 28
    Last Post: 01-24-2018, 05:14 PM
  2. Replies: 2
    Last Post: 05-14-2017, 10:07 AM
  3. Working query stops working after importing
    By Abacus1234 in forum Import/Export Data
    Replies: 3
    Last Post: 10-25-2015, 09:12 PM
  4. Replies: 1
    Last Post: 12-27-2014, 12:38 PM
  5. Replies: 3
    Last Post: 01-29-2013, 04:34 AM

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