filtering all forms based on a dropdown

  • Thread starter Thread starter Jared
  • Start date Start date
J

Jared

My apologies for the novel, I thought it best to provide the whole picture.

I have a 2007 accdb database to track vehicle maintenance. There are
multiple locations (depots) that the company has. At the moment, all users
see information about all depots.

I need the user to be able to select a depot from a dropdown (curr_group) on
the switchboard (frmMain) to identify their location. The default value for
the pulldown is "ALL". Whilst the field value is "ALL" the user should see
all vehicles from all depots.
If a user has changed the value to a depot, then whenever the user opens a
form or report, I need to filter the list to only show vehicles records that
belong to the current depot.

An example would be that after a user selects a depot "sydney" in the drop
down and then opens the maintenance register, they will only see maintenance
records for vehicles that have been associated with the depot "sydney"

I have a field at the vehicle level "vehicle_group" to store this data
against vehicle. The following code works perfectly:

Private Sub Form_Load()

If Not (Form_frmMain.curr_group = "All") Then
Me.Filter = "vehicle_group = '" & Form_frmMain.curr_group & "'"
Me.FilterOn = True
End If
End Sub

However it doesn't seem right to paste this on every applicable form and
report? I have /attempted to write a function to simplify/centralise the task
as such by passing the form name to the function:

Private Sub Form_Load()
groupFilter (Me.Name)
End Sub

with the following code in the function:

Function groupFilter(currForm As String)
'This function recieves a form name and if NOT "ALL" filters
'the form by the name of the depot/group passed

Dim currGroup As String

currGroup = Form_frmMain.curr_group

If Not (Form_frmMain.curr_group = "All") Then
* Form_(currForm).Filter = "vehicle_group = '" & currGroup & "'"
Form_(currForm).FilterOn = True
End If

End Function

I receive a compile error at the line with the asterix.
I would appreciate any feedback on the error in the function or whether just
I need to paste the code on every form/report that is applicable. Thankyou.
 
Jared said:
My apologies for the novel, I thought it best to provide the whole picture.

I have a 2007 accdb database to track vehicle maintenance. There are
multiple locations (depots) that the company has. At the moment, all users
see information about all depots.

I need the user to be able to select a depot from a dropdown (curr_group) on
the switchboard (frmMain) to identify their location. The default value for
the pulldown is "ALL". Whilst the field value is "ALL" the user should see
all vehicles from all depots.
If a user has changed the value to a depot, then whenever the user opens a
form or report, I need to filter the list to only show vehicles records that
belong to the current depot.

An example would be that after a user selects a depot "sydney" in the drop
down and then opens the maintenance register, they will only see maintenance
records for vehicles that have been associated with the depot "sydney"

I have a field at the vehicle level "vehicle_group" to store this data
against vehicle. The following code works perfectly:

Private Sub Form_Load()

If Not (Form_frmMain.curr_group = "All") Then
Me.Filter = "vehicle_group = '" & Form_frmMain.curr_group & "'"
Me.FilterOn = True
End If
End Sub

However it doesn't seem right to paste this on every applicable form and
report? I have /attempted to write a function to simplify/centralise the task
as such by passing the form name to the function:

Private Sub Form_Load()
groupFilter (Me.Name)
End Sub

with the following code in the function:

Function groupFilter(currForm As String)
'This function recieves a form name and if NOT "ALL" filters
'the form by the name of the depot/group passed

Dim currGroup As String

currGroup = Form_frmMain.curr_group

If Not (Form_frmMain.curr_group = "All") Then
* Form_(currForm).Filter = "vehicle_group = '" & currGroup & "'"
Form_(currForm).FilterOn = True
End If

End Function

I receive a compile error at the line with the asterix.
Click to expand...

Which error?
I would appreciate any feedback on the error in the function
Click to expand...

Without more details, my best guess is that it's because you
are using the Form_ syntax. It may work in some(?)
situations, but it really is not appropriate in general.
You should use Forms! instead.
or whether just
I need to paste the code on every form/report that is applicable. Thankyou.
Click to expand...


It would be far easier to use the OpenForm method's
WhereCondition argument. The code behind the button that
opens the form could look something like:

If Me.curr_group = "All" Then
DoCmd.OpenForm "frmMaintenance"
Else
DoCmd.OpenForm "frmMaintenance", _
WhereCondition:= "vehicle_group = '" & currGroup & "'"
End If

The same can be used for reports.

This way you don't have to touch every form and report.
Instead you only need to touch up amy code that opens the
forms and reports.
 
Back
Top