Go to Record with Command button based off Unbound Combo Box (1 Viewer)

[Aviqq]

Hello,

I am trying to have 2 Command buttons to go to previous and next record based off an unbound combo box selections. I currently have the next command button code via VBA but I don't know how to do the opposite for a previous button. This is the code I am using for a next record:

Function AdvanceCombo(strForm As String, strControl As String)
'This function clears all entries on all listboxes on the form strForm
Dim c As Control
Dim F As Form
Dim I As Long
Dim cValue As String
Set F = Forms(strForm)
Set c = F(strControl)
cValue = c.Value
For I = 0 To c.ListCount - 1
If c.ItemData(I) = cValue Then c.Value = c.ItemData(I + 1)
Next I
End Function

And then for the Command button:

Private Sub NextLogbookRecord_Click()

    AdvanceCombo Me.Name, "TPComboBOx"
    TPComboBOx_AfterUpdate
    
End Sub

Anybody know how I could edit this code to do the opposite and go to previous record?

Also, If the combobox is null, is there anyway to go to the first record in the combobox rather than an error popping up? Currently I get Run-time error '94' Invalid use of Null. This also occurs when there is no more selections available in the combobox. Could the selections wrap around back to the begging? Thank you.

 

[Ranman256]

what about using:

me.filter = "[ID]=" & cboBox
me.FilterOn = true

 

[Aviqq]

what about using:

me.filter = "[ID]=" & cboBox
me.FilterOn = true

Sorry I am very new to VBA. Is this the code for my Command button?

 

[MajP]

Do you really want a combo or a listbox? I wrote a code for both.
1. Handles no values
2. selects first if none selected
3. wraps up and down
4. works with a combo or a listbox

Public Sub MoveListSelection(ctrl As Access.Control, Direction As AcSearchDirection)
  'Use AcUp or AcDown
  Dim cmbo As Access.ComboBox
  Dim idx As Long
  If (ctrl.ControlType = acComboBox Or ctrl.ControlType = acListBox) And ctrl.ListCount > 0 Then
    If ctrl.ControlType = acComboBox Then
     Set cmbo = ctrl
     cmbo.SetFocus
     cmbo.Dropdown
    End If
    If ctrl.ListIndex = -1 Then
      ctrl = ctrl.ItemData(0)
      Exit Sub
    End If
    If Direction = acDown Then
      If ctrl.ListIndex = ctrl.ListCount - 1 Then
        ctrl = ctrl.ItemData(0)
      Else
        ctrl = ctrl.ItemData(ctrl.ListIndex + 1)
      End If
    Else
     'going down
     If ctrl.ListIndex = 0 Then
        ctrl = ctrl.ItemData(ctrl.ListCount - 1)
      Else
        ctrl = ctrl.ItemData(ctrl.ListIndex - 1)
      End If
    End If
  Else
    MsgBox "Pass in a listbox or combobox"
  End If
End Sub

Code to call it

Private Sub cmdDown_Click()
  MoveListSelection Me.Combo1, acDown
  MoveListSelection Me.List6, acDown
End Sub

Private Sub cmdUp_Click()
  MoveListSelection Me.Combo1, acUp
  MoveListSelection Me.List6, acUp
End Sub

see frmMoveUpDown

 

[MajP]

Also I may be wrong, but I do not think your title is correct. I believe from the code you have you want to move up and down in a combobox or list box selection. Not move to a record on a form.

 

[Aviqq]

Also I may be wrong, but I do not think your title is correct. I believe from the code you have you want to move up and down in a combobox or list box selection. Not move to a record on a form.

So I am using the Combo box to select a record using:

Private Sub TPComboBOx_AfterUpdate()
      ' Find the record that matches the control.
     Dim rs As Object

 Set rs = Me.Recordset.Clone
     rs.FindFirst "[TestPoint#] = " & Str(Nz(Me![TPComboBOx], 0))
     If Not rs.EOF Then Me.Bookmark = rs.Bookmark

The combo box populates with a query. I want to move to next or previous record based off what is populating in the combo box.

 

[MajP]

If that combo box behavior is what you want then move the afterupdate code into its own procedure and now call then procedure in the afterupdate

Public Sub MoveToRecord
' Find the record that matches the control.
     Dim rs As Object

Set rs = Me.Recordset.Clone
     rs.FindFirst "[TestPoint#] = " & Str(Nz(Me![TPComboBOx], 0))
     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
end sub

Then in the code to move the buttons up and down

Private Sub cmdDown_Click()
  MoveListSelection Me.Combo1, acDown
  movetorecord
End Sub

Private Sub cmdUp_Click()
  MoveListSelection Me.Combo1, acUp
  movetorecord
End Sub

This code is needed since an afterupdate event does not occur if you change the value of the combo by code.