Scrolling through records with a next / previous buttons

[mattster1010]

Afternoon all,

I have a combo box on a form that finds a record based on the value selected within the combo box (i.e company name). All subforms on the form then show the details for that customer.

The SQL behind the combobox is as follows:

[VBA]SELECT [Customers Query].[CustomerID], [Organisation_Name] AS Name FROM [Customers Query]; [/VBA]

The reordsource of the form does not point to the 'Customers' table.

Is there anyway that I could use two commands buttons (one for a previous and one for a next record) to scroll through the list of customers contained with the combo box list?

Regards,

Matt

[CreganTur]

It's quite easy:
[vba]DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious[/vba]

However, I would suggest adding in a Sub to handle enabling/disabling these buttons based on where you are in the recordset. This will keep your users from seeing errors if they hit the Previous button when they're on the first record. You would call it right after the GoToRecord command, like this:

[vba]DoCmd.GoToRecord , , acPrevious
UpdateNavigationButtons[/vba]

Here's the Sub:
[vba]Private Sub UpdateNavigationButtons()

'move focus to a different control so all navigation buttons are free for changes
Me.OtherObject.SetFocus
'enable all navigation buttons
Me.btnGoFirst.Enabled = True
Me.btnGoLast.Enabled = True
Me.btnGoNext.Enabled = True
Me.btnGoPrevious.Enabled = True

'set button enable properties for special first and last records
If CurrentRecord = 1 Then
Me.btnGoFirst.Enabled = False
Me.btnGoPrevious.Enabled = False
ElseIf CurrentRecord = RecordsetClone.RecordCount Then
Me.btnGoLast.Enabled = False
Me.btnGoNext.Enabled = False
End If

Exit Sub

End Sub[/vba]

[OBP]

Randy, that is nice code, not quite the same as the one that I use, which is like this
[VBA] Dim rs As Object, Records As Integer
Set rs = Me.Recordset.Clone
If Not Me.NewRecord Then
rs.Bookmark = Me.Bookmark
rs.MoveNext
Me.Next.Visible = Not (rs.EOF) ' next record
rs.Bookmark = Me.Bookmark
rs.MovePrevious
Me.Command36.Visible = Not (rs.BOF) ' Previous Record
Me.Command37.Visible = Not (rs.BOF) ' First Record
rs.MoveNext
Me.Command38.Visible = True ' Last Record
me.command39.visible = True ' new record
else
me.command39.visible = False ' new record

End If
rs.Close
Set rs = Nothing[/VBA]

However, I don't think that is what Matt is asking, he wants to step through the Customer Records, which are in the Combo, but not in the Form.
So it would need a Recordset.

[mattster1010]

Thanks both of you for posting replies.

However, I don't think that is what Matt is asking, he wants to step through the Customer Records, which are in the Combo, but not in the Form.
So it would need a Recordset.

Would the code above step through the list of customers in a combo box? How would I refer to the list of customer contained within the combo box?

Cheers,

Matt

[OBP]

Matt, you have to use the same Recordset as the Combo and then use
movenext and moveprevious as in
rs.movenext

[mattster1010]

Morning,

Going back to this issue, I have used the following code to scroll through the records:

[VBA]Private Sub Command12_Click()
Dim rs As Object, rst As Object, count As Integer, counter As Integer, SQL As String
SQL = "SELECT CustomersQuery.CustomerID, CustomersQuery.Organisation_Name " & _
"AS Name " & _
"FROM CustomersQuery "
Set rs = CurrentDb.OpenRecordset(SQL)
rs.MoveNext
Set rs = Nothing
End Sub[/VBA]

This doesn't work however. Can you see where I might be going wrong?

Cheers,

Matt

[OBP]

In what way doesn't it work?

[mattster1010]

Hi OBP,

It does not scroll through the 'CustomersQuery' recordset. It doesn't populate an error message of any kind either, when the command button is clicked.

Regards,

Matt

[OBP]

That is probably because it starts at record one and only moves one record, but how would you see it?
You are not displaying Organisation_name in any way.
You could use a msgbox to display it, but is that what you want to do/