Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    DblClk to open form & goto Record without filtering form?

    Hi all,
    Is there a way to DblClk a list box or button to open form to a specific record without filtering that form?


    Code:
    Private Sub LstPersonSearch_DblClick(Cancel As Integer)
    DoCmd.OpenForm "frmPerson", , , "[PersonID]=" & Me![LstPersonSearch].Column(0)
    End Sub
    Thanks
    Dave

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Dave,
    What happens using the code you posted?

    You seem to have a few similar in the Similar Threads at bottom of the page.
    Is this one relevant?

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Use OpenArgs?
    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

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by orange View Post
    Dave,
    What happens using the code you posted?

    You seem to have a few similar in the Similar Threads at bottom of the page.
    Is this one relevant?
    Jack,
    That one actually filters the form.
    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

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Paul,
    I noticed that Dave had asked similar previously. He had a few responses. Is the new request totally different from all of those earlier responses? Could be, we'll wait and see.
    It seems to me that what he quoted in post #1 should open the form to a specified record ---maybe I'm missing something obvious. Wouldn't be the first time.

    Just tried this in my database, and it works for me.
    Code:
    Private Sub List0_DblClick(Cancel As Integer)
        DoCmd.OpenForm "animal2", , , "animalid=" & Me!List0.column(0)
    End Sub

  6. #6
    d9pierce1 is offline Expert
    Windows 10 Access 2019
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi all,
    Thanks for replays and the code does work but it filters the form and I dont want to filter the form it opens~
    Guess what I am asking is there a way to open a form to specific record via dblclk or cmdbutton that does not filter the form it opens?

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Dave,
    Please explain how this is filtering.
    DoCmd.OpenForm "animal2", , , "animalid=" & Me!List0.column(0)

    This is picking 1 record out of 128 directly.

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I've used this many times:
    It uses a combobox but can be adapted to use listbox selection for criteria.

    Code:
    '---------------------------------------------------------------------------------------
    ' Method : cboSearch_AfterUpdate
    ' Author : davegri
    ' Date   : 8/20/2021
    ' Purpose: A lookup technique that removes any existing filter such as a docmd.openform
    '    with criteria executed by another form (like the Calendar calling this form).
    '    All the records in this form's recordsource are available after
    '    this lookup.
    '---------------------------------------------------------------------------------------
    Private Sub cboSearch_AfterUpdate()
        On Error GoTo Error_Handler
        Dim nSave As Long
        If IsNull(Me.cboSearch) Then Exit Sub
        nSave = cboSearch
        Me.Filter = ""
        Me.FilterOn = False
        cboSearch = nSave
        With Me.RecordsetClone
            .FindFirst "[Patient_PK] = " & Me.cboSearch
            If Not .NoMatch Then
                If Me.Dirty Then Me.Dirty = False
                Me.Bookmark = .Bookmark
            Else ' put your not found code here, but you really shouldn't need it
            End If
        End With
    Error_Handler_Exit:
        On Error Resume Next
        Exit Sub
    Error_Handler:
        Select Case Err
            Case Else
                MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboSearch_AfterUpdate" & "."
        End Select
        Resume Error_Handler_Exit
        Resume
    End Sub

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I'm curious why DoCmd.OpenForm ... isn't sufficient?

    Here's some code that you can try. Put this in a new module:
    Code:
    'modified from david fenton's code posted here:'  https://stackoverflow.com/questions/3401645/how-do-i-bring-up-a-record-based-on-a-combobox-in-access
    Public Sub OpenForm(ByVal form_name As String, ByVal where_condition As String)
    On Error GoTo ErrHandler
        Dim my_form As Form
        
        DoCmd.OpenForm form_name
        Set my_form = Forms(form_name)
        
        my_form.FilterOn = False
        
        With my_form.RecordsetClone
          .FindFirst where_condition
          If Not .NoMatch Then
             If my_form.Dirty Then my_form.Dirty = False
             my_form.Bookmark = .Bookmark
          Else
             ' put your not found code here
    
          End If
        End With
        
    ExitHandler:
        Set my_form = Nothing
        Exit Sub
    
    
    ErrHandler:
        MsgBox Err.Description, , "Error #" & Err.Number
        Resume ExitHandler
    End Sub
    Then call it from your list box's dbl click event with something like this:
    Code:
    OpenForm "frmPerson", "[PersonID]=" & Me![LstPersonSearch].Column(0)

  10. #10
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by orange View Post
    Dave,
    Please explain how this is filtering.
    DoCmd.OpenForm "animal2", , , "animalid=" & Me!List0.column(0)

    This is picking 1 record out of 128 directly.
    Orange, check out the record selector controls at the bottom of a form that has been opened in that manner. You'll find that DoCmd.OpenForm presented the 1 record by applying a form filter according to the WhereCondition parameter.

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by orange View Post
    Dave,
    Please explain how this is filtering.
    DoCmd.OpenForm "animal2", , , "animalid=" & Me!List0.column(0)

    This is picking 1 record out of 128 directly.
    When I try that I get one record?
    Code:
    docmd.OpenForm "sfrmDaily",,,"DAilyID=34"
    Attached Thumbnails Attached Thumbnails Filter.PNG  
    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

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Paul, Davegri, KD2017

    Thanks for the info. I was not aware that using open parm(where argument) on OpenForm actually filtered data. Never noticed the "filtered" info, or never paid it any attention.
    Thanks for setting me straight.

    Here is info from Microsoft:

    Click image for larger version. 

Name:	DoCmdOpenForm_M$oft.png 
Views:	24 
Size:	65.1 KB 
ID:	50239

    Edit: For anyone reading this. I took an existing form with a listbox. I added a dblClick event to the listbox to open a completely different existing form. The column(0) value is used to open the second form "Animal2". The original form was for a very different purpose. It is only the dblClick that is important to this thread.

    Click image for larger version. 

Name:	OpenForm.png 
Views:	23 
Size:	18.4 KB 
ID:	50237

    As an aside, I just tried to do a partial screen capture with snipping tool -that I have used for years. Different animal this morning -wants to do a video??? Downloaded greenshot to do the install and capture. Guess it's one of those days.
    Last edited by orange; 05-14-2023 at 09:30 AM.

  13. #13
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    It is not OpenArgs Jack?
    That is the last parameter in the open form command.

    What has been shown in the open form commands is setting the filter. Intellisense shows you that as you enter the command.

    My thoughts were to pass in the value via OpenArgs, and in the target form, if OpenArgs has a value then findfirst using that value.
    As shown by davegri and kd2017
    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

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Paul,

    You are correct. OpenArgs is the last parameter. I was confusing open with parameters Filter, whereCondition and the OpenArgs.
    However, I did not realize that the where condition was simply a filtering operation.
    As I said it's one of those days --and unfortunately, still quite early.

  15. #15
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Yes, I have never used the Filter parameter.
    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

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-30-2022, 01:34 AM
  2. DblClk to open form
    By d9pierce1 in forum Programming
    Replies: 3
    Last Post: 10-23-2021, 10:28 AM
  3. Open Form and goto record with Listbox selection
    By GaryShelton in forum Programming
    Replies: 7
    Last Post: 01-04-2019, 05:54 PM
  4. Replies: 2
    Last Post: 02-22-2017, 05:45 AM
  5. Replies: 1
    Last Post: 06-09-2012, 05:44 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