I am totally new to Access 2003 and am trying to build a form that will allow other users to see only the records by filtering using a combo box. I know that this topic has been addressed time and time again but being new I am having trouble following others advice. I have tried many of the suggestions here but seem to be missing something. So here is what I have and I hope someone will help me get my form going.
I have a table [CurrentDraw_Route_Detail] that contains about 600000 records. Each record contains the following fields:
MetroState ((MS) 2 possibilities (Metro or State) I built a query against this table to make a DISTINCT table [DISTINCTMetroState] for the fields so that should another choice be added that it will automatically be updated for selection.
Classification ((BC) 13 possibilities (for now, more to follow) I built a query against this table to make a DISTINCT table [DISTINCTClassification] just like the one for MetroState.
ABC Class
District
Zone
Sort (field for sorting the abc class)
Date
Route
Draw
Returns
Net
There are a few other fields but these are the main one.
So I built a form called FORM1 with the record source tied to [CurrentDraw_Route_Detail]. In this form I put two combo boxes in the Form Header, one for MetroState and one for Classification. I tied the Row Source of each to the [DISTINCTMetroState] or [DISTINCTClassification] tables. I put in the AfterUpdate event field the following code:
Private Sub BC_AfterUpdate()
On Error GoTo ErrHandler
'DoCmd.OpenForm Me.Name, , , Me!BC.Column(0)
Me.Filter = "[Classification]=""" & Me.BC & Me.FilterOn = True
Exit Sub
ErrHandler:
MsgBox "Error"
Err.Clear
End Sub
In the Detail section I added the other fields for that I wanted displayed and set the form to continuous. When I ran the form I get all records regardless of what is changed in the combo boxes. Just for kicks I build a second form called FORM2 that contains the same fields that I wanted displayed and inserted it as a subform. Regardless again I get every record. Somehow that filter is not being applied but I do not understand where. Is there a certain way to tie the filter to the table? ANY and ALL assistance will be appreciated!!!
I have a table [CurrentDraw_Route_Detail] that contains about 600000 records. Each record contains the following fields:
MetroState ((MS) 2 possibilities (Metro or State) I built a query against this table to make a DISTINCT table [DISTINCTMetroState] for the fields so that should another choice be added that it will automatically be updated for selection.
Classification ((BC) 13 possibilities (for now, more to follow) I built a query against this table to make a DISTINCT table [DISTINCTClassification] just like the one for MetroState.
ABC Class
District
Zone
Sort (field for sorting the abc class)
Date
Route
Draw
Returns
Net
There are a few other fields but these are the main one.
So I built a form called FORM1 with the record source tied to [CurrentDraw_Route_Detail]. In this form I put two combo boxes in the Form Header, one for MetroState and one for Classification. I tied the Row Source of each to the [DISTINCTMetroState] or [DISTINCTClassification] tables. I put in the AfterUpdate event field the following code:
Private Sub BC_AfterUpdate()
On Error GoTo ErrHandler
'DoCmd.OpenForm Me.Name, , , Me!BC.Column(0)
Me.Filter = "[Classification]=""" & Me.BC & Me.FilterOn = True
Exit Sub
ErrHandler:
MsgBox "Error"
Err.Clear
End Sub
In the Detail section I added the other fields for that I wanted displayed and set the form to continuous. When I ran the form I get all records regardless of what is changed in the combo boxes. Just for kicks I build a second form called FORM2 that contains the same fields that I wanted displayed and inserted it as a subform. Regardless again I get every record. Somehow that filter is not being applied but I do not understand where. Is there a certain way to tie the filter to the table? ANY and ALL assistance will be appreciated!!!