Results 1 to 9 of 9
  1. #1
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496

    combobox with focus set to a specific record on drop-down open

    I'm being asked if i can make it so that when a combo box is opened, the choice is already at a particular record

    when a user is editing a specific record, they often need to refer to another that is typically only a few records away (but not always). the combo box gets them to the info they need. consequently, when they open the combo list, and the focus is at the first record, they find themselves having to scroll through the (occasionally longer


    -ish) list until they get to the range of records that would the choice would be in (but sometimes, the record they want may be early in the list, or sometimes towards the end ...usually not)

    the question is:
    can i make it so that when the drop-down list opens, a specific record (in this case with a value equal to a field on the current form, is selected?

    with thnx in advance,
    mark

  2. #2
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    If you know the record that you need, why use a combobox? Why not just have a command button that is tied to the record?

  3. #3
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    they don't always know the record, and it could be one of any number they need to get to, varying from instance to instance

  4. #4
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    I assume the records are in another table. If you have the second table setup with a foreign key from the form table, use that to find all records associated with that.

    Dave

  5. #5
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    they don't always know the record they need to get to (the pull-down includes a couple of fields that assist their choice), and it could be one of any number records they need to get to, varying from instance to instance

    the records are in the same table; they are navigating from one record to another

  6. #6
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    I would need to see the database to understand more of what your trying to do.

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    This is something I have used. The first line would be in the form_current event. The function could be in the form's code or a module.

    Code:
    Me.cboEvent = Me.cboEvent.ItemData(fcnEventPosition([tblEvent_FK]))
       
    Public Function fcnEventPosition(arg As Long) As Long
      Dim rst As DAO.Recordset
      Dim sSQL As String
      Dim ncnt As Long
      sSQL = "SELECT Event_PK, Event from tblEvent ORDER BY Event;"
      Set rst = CurrentDb.OpenRecordset(sSQL)
      rst.MoveLast
      rst.MoveFirst
      With rst
        Do Until !Event_PK = arg
           ncnt = ncnt + 1
           .MoveNext
        Loop
      End With
      fcnEventPosition = ncnt
      set rst = nothing
    End Function  

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think that one of the biggest concerns here would be whether or not you have other events on the combo, such as before update. You can set the value as you wish (assuming the goal is to effect a selection then user drops down the list and you're there) but doing so will likely update the combo, potentially firing other events. Might that be a problem here?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Here's a method that works for me but doesn't require recordsets. The key things to note are:
    - whichever event you use (possibly Current event, I used form Open) it makes use of the timer event to turn the trick because it apparently cannot be done on form open or load. If you need the timer event for some other purpose, then it would not work without some kind of logic test to decide whether or not to run the section of code for this.
    - the selection code turns the timer off
    - an initial value must be set for the control
    - the control must receive focus before setting selection
    - don't try to substitute the Me.cmbComboName (this is the name of your combo) with an object variable (e.g. ctl)
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
    Me.TimerInterval = 1
    
    End Sub
    
    Private Sub Form_Timer()
    Dim n As Long
    
    Me.TimerInterval = 0
    For n = 0 To Me.cmbComboName.ListCount - 1
      If Me.cmbComboName.ItemData(n) = "your text value from other form field" Then 'if numeric, no quotes. This value must be in the bound column of the combo
         Exit For
      End If
    Next
    
     With Me.cmbComboName
       .Value = Me.cmbComboName.ItemData(n)
       .SetFocus
       .Selected(n) = True
       .Dropdown
    End With
      
    End Sub
    That should make the selection AND drop the combo down to it.
    Last edited by Micron; 08-23-2019 at 07:32 PM. Reason: code comment

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

Similar Threads

  1. Replies: 3
    Last Post: 06-24-2019, 11:20 AM
  2. Replies: 2
    Last Post: 07-10-2017, 12:30 AM
  3. Replies: 5
    Last Post: 10-18-2016, 06:00 AM
  4. Close one form open another and set focus to a specific field
    By Derrick T. Davidson in forum Programming
    Replies: 12
    Last Post: 08-01-2014, 05:16 AM
  5. Replies: 1
    Last Post: 05-31-2012, 01:01 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