Results 1 to 11 of 11
  1. #1
    JRodko is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2018
    Posts
    127

    Add filter to ELookup in VBA

    Hello,



    I have attached my code. I would like to have the ELookup only search for results where a certain criteria are met (inner join between tables).

    I would like it to find the "SAMP_NUM" given the sorting in the ELookup statement, but only do so where "Geochemistry - Samples".'SAMP_Hole_NUM" is the same as "tblSelectedDrillHoleandDeclination".'hole_NUM '.

    Any help would be appreciated.

    Code:
    Private Sub Form_BeforeInsert(Cancel As Integer)
    If Me.SAMP_Num = "(Auto)" Then
            Dim PrevValue As Variant
            PrevValue = ELookUp("SAMP_NUM", "[Geochemistry - Samples]", , "datModified Desc")
            
            If Not IsNull(PrevValue) Then
                'Increment the value from last time
                Me.SAMP_Num = Succ(PrevValue)
            Else
                'No previous records were found so start from the bottom
                Me.SAMP_Num = "SRC"
            End If
        End If
    End Sub

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Assuming hole_NUM is a field in form RecordSource and is text type:

    ELookUp("SAMP_NUM", "[Geochemistry - Samples]", "SAMP_Hole_NUM='" & Me!hole_NUM & "'" , "datModified Desc")

    If this isn't working, provide sample of tables data and relationship.
    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
    Any particular reason why you are using Allen Browne's ELookup rather than the built in DLookup function?
    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
    JRodko is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2018
    Posts
    127
    It was recommended to me to be honest, I think on this forum. I don't know enough to know the difference. It is part of a larger function that auto-increments an alphanumeric code, if that makes sense. You can PM me if you want to see more

  5. #5
    JRodko is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2018
    Posts
    127
    Quote Originally Posted by June7 View Post
    Assuming hole_NUM is a field in form RecordSource and is text type:

    ELookUp("SAMP_NUM", "[Geochemistry - Samples]", "SAMP_Hole_NUM='" & Me!hole_NUM & "'" , "datModified Desc")

    If this isn't working, provide sample of tables data and relationship.

    Sorry, hole_NUM is a field from a separate table. The main form is linked to it, however the sub form does not have it. The two forms are linked using this field. Does that help?

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by JRodko View Post
    It was recommended to me to be honest, I think on this forum. I don't know enough to know the difference. It is part of a larger function that auto-increments an alphanumeric code, if that makes sense. You can PM me if you want to see more
    No worries. I was just curious.
    For anyone interested, ELookup can be found at http://allenbrowne.com/ser-42.html

    June
    I think you have omitted the optional third Criteria argument. Should there be two commas before datModified Desc?
    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

  7. #7
    JRodko is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2018
    Posts
    127
    Quote Originally Posted by isladogs View Post
    No worries. I was just curious.
    For anyone interested, ELookup can be found at http://allenbrowne.com/ser-42.html

    June
    I think you have omitted the optional third Criteria argument. Should there be two commas before datModified Desc?
    I need a sort using datModified Desc but also a criteria where SAMP_Hole_Num = the single record for tblSelectedDrillHoleandDeclination.hole_NUM. June's code gives an error, but I am not skilled enough to know why.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Colin,
    No, there are 4 arguments and I have 4 parameters.

    JRodko,
    Domain aggregate cannot reference another table. It has no way to know which record of table has the criteria. The parameter must come from the form. If forms are linked on hole_NUM then both forms have the value.

    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.

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    You're trying to get an equivalent elookup for this sql?
    Code:
    SELECT TOP 1 [Geochemistry - Samples].SAMP_NUM 
    FROM   tblSelectedDrillHoleandDeclination 
           INNER JOIN [Geochemistry - Samples] 
                   ON tblSelectedDrillHoleandDeclination.hole_NUM = 
                      [Geochemistry - Samples].SAMP_Hole_NUM 
    ORDER  BY [Geochemistry - Samples].datModified DESC;
    copy and paste the above sql into the elookup function's params would look like
    Code:
    ELookUp("[Geochemistry - Samples].SAMP_NUM", "tblSelectedDrillHoleandDeclination INNER JOIN [Geochemistry - Samples] ON tblSelectedDrillHoleandDeclination.hole_NUM = [Geochemistry - Samples].SAMP_Hole_NUM", , "[Geochemistry - Samples].datModified DESC")

  10. #10
    JRodko is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2018
    Posts
    127
    This seems to have worked masterfully, I did not know I could try that approach. Thanks very much.

    Quote Originally Posted by kd2017 View Post
    You're trying to get an equivalent elookup for this sql?
    Code:
    SELECT TOP 1 [Geochemistry - Samples].SAMP_NUM 
    FROM   tblSelectedDrillHoleandDeclination 
           INNER JOIN [Geochemistry - Samples] 
                   ON tblSelectedDrillHoleandDeclination.hole_NUM = 
                      [Geochemistry - Samples].SAMP_Hole_NUM 
    ORDER  BY [Geochemistry - Samples].datModified DESC;
    copy and paste the above sql into the elookup function's params would look like
    Code:
    ELookUp("[Geochemistry - Samples].SAMP_NUM", "tblSelectedDrillHoleandDeclination INNER JOIN [Geochemistry - Samples] ON tblSelectedDrillHoleandDeclination.hole_NUM = [Geochemistry - Samples].SAMP_Hole_NUM", , "[Geochemistry - Samples].datModified DESC")

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi June
    Sorry. You are indeed correct. I misread it on a small screen!
    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

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

Similar Threads

  1. DLookup and ELookup errors
    By MadTom in forum Forms
    Replies: 4
    Last Post: 09-06-2019, 12:50 PM
  2. Replies: 20
    Last Post: 02-16-2019, 07:51 AM
  3. I need an ELookUp Example
    By BLFOSTER in forum Forms
    Replies: 16
    Last Post: 06-12-2017, 04:38 AM
  4. Error using ELookup but not DLookup
    By Rawb in forum Programming
    Replies: 12
    Last Post: 10-18-2010, 07:09 AM
  5. Replies: 3
    Last Post: 10-06-2009, 02:11 PM

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