Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access VBA select a record on a form (2000, 2003)

    Hi,

    I have a form based on a query.
    The form's recordset is filtered by setting its Filter property through some code, which works fine.

    What is the fastest way to find (and select) a record with a specific Id within the filtered recordset?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access VBA select a record on a form (2000, 2003)

    You can use code like this:

    Dim lngID As Long
    Dim rst As DAO.Recordset

    lngID = ...
    Set rst = Me.RecordsetClone
    rst.FindFirst "ID=" & lngID
    If rst.NoMatch Then
    MsgBox "ID " & lngID & " not found.", vbExclamation
    Else
    Me.Bookmark = rst.Bookmark
    End If
    Set rst = Nothing

    The code requires that you have set a reference to the Microsoft DAO 3.6 Object Library.
    I have assumed that the field you're looking for is named ID and that it is a number field. If it is a text field, use

    rst.FindFirst "ID=" & Chr(34) & lngID & Chr(34)

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access VBA select a record on a form (2000, 2003)

    Thanks Hans, looks simple enough.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access VBA select a record on a form (2000, 2003)

    It's basically the code generated by the Combo Box Wizard if you select the option "Find a record on my form based on the value I selected in my combo box".

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access VBA select a record on a form (2000, 2003)

    Ah. Followup: if I use the scroll wheel to scroll down so the record goes out of view, how do I ensure it comes back when I click my button?

    I tried:

    Me.SelTop = Me.CurrentRecord

    but that doesn't seem to do the trick?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access VBA select a record on a form (2000, 2003)

    Setting SelTop isn't very dependable. You could try something like

    Dim n As Long
    n = Me.CurrentRecord
    Me.SelTop = Me.RecordsetClone.RecordCount
    Me.SelTop = 1
    Me.SelTop = n

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access VBA select a record on a form (2000, 2003)

    Looks promising, thanks.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access VBA select a record on a form (2000, 2003)

    Hi Hans, what's the purpose of setting Me.SelTop = Me.RecordsetClone.RecordCount and then Me.SelTop = n?
    I've tried it on an unfiltered continuous form where the current record on opening is 24 rows from the bottom and Me.RecordsetClone.RecordCount and Me.CurrentRecord give me the same number.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access VBA select a record on a form (2000, 2003)

    In a form with only a few records, setting SelTop has little effect anyway. In a form with more records, setting SelTop to RecordCount and then to 1 forces Access to jump. The value of n is the current record number before the jumps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •