Checkboxes to produce report

[jmhicsupt]

I have a database that many managers access. I would like each manager to be able to click on their respective office locations to see their locations they are responsible for. Some managers have more than one location. So what I am trying to do is - if more than one checkbox is chosen, then pull the records for each of the locations. In the code below, it only allows one checkbox to be chosen. If I check more than one, it disregards the other checkboxes. Is there a way I can have all checkboxes chosen to produce the criteria.

Private Sub DistrictNew_Exit(Cancel As Integer)
'************* Code Start **************
' This code was originally written by Erika Yoxall.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Erika Yoxall
'
Dim varRegionNew, varOfcNoNew As Variant
varRegionNew = DLookup("Region", "Districts", "Districts =[DistrictNew] ")
varOfcNoNew = DLookup("OfcNo", "Districts", "Districts =[DistrictNew] ")
If (Not IsNull(varRegionNew)) Then Me![RegionNew] = varRegionNew
If (Not IsNull(varOfcNoNew)) Then Me![OfficeNew] = varOfcNoNew
End Sub
'************* Code End **************



Private Sub Form_Load()
Dim arg1 As Variant
Dim arg2 As Variant
Dim arg3 As Variant
Dim arg4 As Variant
Dim arg5 As Variant
Dim arg6 As Variant
Dim arg7 As Variant
Dim arg8 As Variant
Dim arg9 As Variant
Dim arg10 As Variant
Dim arg11 As Variant
Dim arg12 As Variant
Dim strsql As String

arg1 = Form_ViewOptions.cmbRegion.Value
arg2 = Form_ViewOptions.cmbDistrict.Value
arg3 = Form_ViewOptions.DeptMcheck
arg4 = Form_ViewOptions.cmbActiveRegion.Value
arg5 = Form_ViewOptions.cmbActiveDistrict.Value
arg6 = Form_ViewOptions.ActiveDEPTM
arg7 = Form_ViewOptions.ActiveSSCkbox
arg8 = Form_ViewOptions.hpckbox
arg9 = Form_ViewOptions.AtlStP
arg10 = Form_ViewOptions.MilwMpls
arg11 = Form_ViewOptions.PhilaNJ
arg12 = Form_ViewOptions.RichColChar


If arg1 <> "" Or arg1 <> Null Then
strsql = "Select * FROM [CustomerDB] Where RIM = '" & arg1 & "'" And DEPTM = False
GoTo loadit
End If

If arg2 <> "" Or arg2 <> Null Then
strsql = "Select * FROM [CustomerDB] Where DEPTM = False and District = '" & arg2 & "'"
GoTo loadit
End If

If arg3 <> "" Or arg3 <> Null Then
strsql = "Select * FROM [CustomerDB] Where DEPTM = True"
GoTo loadit
End If

If arg4 <> "" Or arg4 <> Null Then
strsql = "Select * FROM [CustomerDB] Where TT = False and Term = False and DEPTM = False and RIM = '" & arg4 & "'"
GoTo loadit
End If

If arg5 <> "" Or arg5 <> Null Then
strsql = "Select * FROM [CustomerDB] Where TT = False and DEPTM = False and Term = False and District = '" & arg5 & "'"
GoTo loadit
End If

If arg6 <> "" Or arg6 <> Null Then
strsql = "Select * FROM [CustomerDB] Where DEPTM = True and TT = False and Term = False"
GoTo loadit
End If

If arg7 <> "" Or arg7 <> Null Then
strsql = "Select * FROM [CustomerDB] Where DJS = True and TT = False and Term = False"
GoTo loadit
End If

If arg8 <> "" Or arg8 <> Null Then
strsql = "Select * FROM [CustomerDB] Where PRE = True and TT = False and Term = False"
GoTo loadit
End If

If arg9 <> "" Or arg9 <> Null Then
strsql = "Select * FROM [CustomerDB] WHERE ((([CustomerDB].District)='atlanta') AND (([CustomerDB].DEPTM)=False) AND (([CustomerDB].TT)=False)) OR ((([CustomerDB].District)='st. pete') AND (([CustomerDB].DEPTM)=False) AND (([CustomerDB].TT)=False))"
GoTo loadit
End If

If arg10 <> "" Or arg10 <> Null Then
strsql = "Select * FROM [CustomerDB] WHERE ((([CustomerDB].District)='minneapolis') AND (([CustomerDB].DEPTM)=False) AND (([CustomerDB].TT)=False)) OR ((([CustomerDB].District)='milwaukee') AND (([CustomerDB].DEPTM)=False) AND (([CustomerDB].TT)=False))"
GoTo loadit
End If

If arg11 <> "" Or arg11 <> Null Then
strsql = "Select * FROM [CustomerDB] WHERE ((([CustomerDB].District)='Philadelphia') AND (([CustomerDB].DEPTM)=False) AND (([CustomerDB].TT)=False)) OR ((([CustomerDB].District)='New Jersey') AND (([CustomerDB].DEPTM)=False) AND (([CustomerDB].TT)=False))"
GoTo loadit
End If

If arg12 <> "" Or arg12 <> Null Then
strsql = "Select * FROM [CustomerDB] WHERE ((([CustomerDB].District)='Charlotte') AND (([CustomerDB].DEPTM)=False) AND (([CustomerDB].TT)=False)) OR ((([CustomerDB].District)='Columbia') AND (([CustomerDB].DEPTM)=False) AND (([CustomerDB].TT)=False)) OR ((([CustomerDB].District)='Columbia') AND (([CustomerDB].DEPTM)=False) AND (([CustomerDB].TT)=False))"
GoTo loadit
End If

loadit:
If strsql = "" Then
strsql = "Select * FROM [CustomerDB]"
End If

Me.RecordSource = strsql
End Sub

 

[Remou]

Would it not be better to have a ManagerLocation table set up that stored all the manager / location combinations? Failing that, why not a multi-select listbox? It would be much easier to manage.

 

[FancyPrairie]

I agree with Remou on both points. Check out this faq181-5497 It contains a function that will build the where clause for you. It works for single and multi-select listboxes, comboboxes, checkboxes, option groups, date ranges, and textboxes. All you have to do is copy the code from the FAQ and paste it into a new module. Then set the tag properties of your controls as specified in the FAQ. Finally, open your report like this DoCmd.OpenReport "ReportName",2,,BuildWhere(Me)

For what you need, this might be a little overkill. However, I would suspect that the managers may want to limit reports to include other things (i.e. date ranges, etc)