Restricting the data displayed with record navigation buttons

[Steve]

Hi
I have a form with a combobox that displays about 15 instances of data.
When the user clicks an item in this list it takes them to another
form where all the records on a particular table are displayed. At the
bottom of the form are the record navigation buttons. How can I get
the records displayed on the second form using the record navigation
buttons restricted to those records where the data in one of the fields
(called Service in this instance) is equal to the combobox value chosen
from the first form. For instance, there are 34 records in the table
but if the user chooses Service A from the combobox I want the label
next to the navigation buttons on the second form to say 1 of 9 and
only display those 9 records which have the value of Service A
contained in the field Service.

Thanks

 

[John Nurick]

Hi Steve,

There are various ways of doing this.

Perhaps the simplest is to base the second form on a parameter query
that gets the value from the combobox on the first form. The syntax of
the query (in SQL view) would be something like this:

SELECT Field1, Field2, Field3
FROM TheTable
WHERE SomeField = [Forms]![FirstForm]![ComboBox]
;

Then use VBA code in the combo box's Change event procedure to open the
form, or to requery it if it's already open. E.g. (air code):

Dim FormName As String

FormName = "Second Form"
On Error GoTo FormNotOpen
'Try to requery the form
Forms(FormName).Requery
On Error Goto 0

NormalExit:
Exit Sub

FormNotOpen:
DoCmd.OpenForm FormName
Resume NormalExit:


'If the form was open all we've done all

 

[Steve]

Thanks John, I'll try that
Steve

Hi Steve,

There are various ways of doing this.

Perhaps the simplest is to base the second form on a parameter query
that gets the value from the combobox on the first form. The syntax of
the query (in SQL view) would be something like this:

SELECT Field1, Field2, Field3
FROM TheTable
WHERE SomeField = [Forms]![FirstForm]![ComboBox]
;

Then use VBA code in the combo box's Change event procedure to open the
form, or to requery it if it's already open. E.g. (air code):

Dim FormName As String

FormName = "Second Form"
On Error GoTo FormNotOpen
'Try to requery the form
Forms(FormName).Requery
On Error Goto 0

NormalExit:
Exit Sub

FormNotOpen:
DoCmd.OpenForm FormName
Resume NormalExit:


'If the form was open all we've done all

Hi
I have a form with a combobox that displays about 15 instances of data.
When the user clicks an item in this list it takes them to another
form where all the records on a particular table are displayed. At the
bottom of the form are the record navigation buttons. How can I get
the records displayed on the second form using the record navigation
buttons restricted to those records where the data in one of the fields
(called Service in this instance) is equal to the combobox value chosen
from the first form. For instance, there are 34 records in the table
but if the user chooses Service A from the combobox I want the label
next to the navigation buttons on the second form to say 1 of 9 and
only display those 9 records which have the value of Service A
contained in the field Service.

Thanks