Next record button based on combo box

I am putting together a database form.  I have a next record button.  Right now the next record button goes to the next record from a table.  But,  I need the next record button to be based on the records shown in the right combo box.  

At the top of the contact form.  There are three combo boxes.  I need the next record button to go to the next record of the records in the third right combo box.  

See attached sample database.
1.accdb
lehi52Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
GozrehConnect With a Mentor Commented:
Just change to this
Private Sub Command51_Click()
   On Error Resume Next
   If Me.display.ListIndex <> Me.display.ListCount - 1 Then
      Me.display.SetFocus
      Me.display.ListIndex = Me.display.ListIndex + 1
   Else
      Me.display.ListIndex = 0
   End If
   DoCmd.SearchForRecord , , acFirst, "[ContactID] = " & Nz(Me.display, 0)
End Sub

Open in new window

4.accdb
0
 
GozrehCommented:
you can use ListIndex example (you should change it to your field names)

to go next
ComboName.SetFocus
If ComboName.ListIndex <> ComboName.ListCount - 1 Then
        ComboName.ListIndex = ComboName.ListIndex + 1
      Else
        ComboName.ListIndex = 0
      End If
End Sub

Open in new window

and to go back
ComboName.SetFocus
If ComboName.ListIndex <> 0 Then
        ComboName.ListIndex = ComboName.ListIndex - 1
      Else
        ComboName.ListIndex = ComboName.ListCount - 1
      End If
End Sub

Open in new window

0
 
lehi52Author Commented:
I get an error when I change the name in the code to the name of the combo box.  This code refers to the "display" combo box.   I am sure I did something wrong. I posted the code into the VBA code.  Does the code need to be put into a specific event procedure to work?

display.SetFocus
If display.ListIndex <> display.ListCount - 1 Then
        display.ListIndex = display.ListIndex + 1
      Else
        display.ListIndex = 0
      End If
End Sub
------------------------------------------------------------
display.SetFocus
If display.ListIndex <> 0 Then
        display.ListIndex = display.ListIndex - 1
      Else
        display.ListIndex = display.ListCount - 1
      End If
End Sub

Open in new window

0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
GozrehCommented:
Where did you put this code ?
Private Sub Command51_Click()
   Me.display.SetFocus
   If Me.display.ListIndex <> Me.display.ListCount - 1 Then
      Me.display.ListIndex = Me.display.ListIndex + 1
   Else
      Me.display.ListIndex = 0
   End If
End Sub

Open in new window

Private Sub Command52_Click()
   Me.display.SetFocus
   If Me.display.ListIndex <> 0 Then
      Me.display.ListIndex = Me.display.ListIndex - 1
   Else
      Me.display.ListIndex = display.ListCount - 1
   End If
End Sub

Open in new window

0
 
lehi52Author Commented:
I just put it into the VBA code.  no where specifically.  I just added it into there.   It did not work with those changes.
0
 
lehi52Author Commented:
It does work but it spits out an error.  Run-Time Error '7777' You've used the List Index property incorrectly.

It goes to the next record and cycles through only those names,  but the issue is after I click next each time the error pops up.
0
 
GozrehCommented:
please upload your sample
0
 
lehi52Author Commented:
Here you go.
2.accdb
0
 
GozrehCommented:
Go to the VBA (Press Alt+F11 from main application), then select debug / compile database.
when you will get an error comment that line, and compile again.
then test the code again.
0
 
lehi52Author Commented:
Now it cycles through, but it only changes the name in the combo box,  and does not change the entire record displayed on the form.
0
 
GozrehCommented:
add after the code display_AfterUpdate
Private Sub Command51_Click()
   Me.display.SetFocus
   If Me.display.ListIndex <> Me.display.ListCount - 1 Then
      Me.display.ListIndex = Me.display.ListIndex + 1
   Else
      Me.display.ListIndex = 0
   End If
   display_AfterUpdate 
End Sub

Open in new window

0
 
lehi52Author Commented:
Gives an error again after adding that.  Same error as before.
3.accdb
0
 
GozrehCommented:
Just to let you know, you should compact your database regularly, the file you uploaded is 4.9MB and the file i uploaded is 1.5MB.
http://office.microsoft.com/en-us/access-help/compact-and-repair-an-access-file-HP005187449.aspx
0
 
lehi52Author Commented:
Great solution.   Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.