I need to say first that I don't really know anything about VBA. I know how to cut and paste. I used a tutorial at Martin Green's website on creating dynamic reports: .
I have several tables all linked to the main Person table with a foreign key. I want to have a multiple multi-select boxes eg. List 1: Profession (eg, physician, RN, EMT, etc) List 2: Occupation-really means experience (eg, Administration, Research, Clinical, etc) that will then open a report with these criteria.
I'm not sure how to deal with null values. With the data already entered none of the physicians listed work activities. So if I select "physician" in List 1 no records appear because they have no records in the "Occupation" List. I decided to add a multi-select list of all the people in the database thinking everyone is of course in that list and that then all physicians would appear whether or not any other criteria were in the other list boxes. It doesn't seem to work. I know have 4 lists. It seems to work ok otherwise. Here is the code (with some comments):
Private Sub cmdOK_Click()
Dim varItem As Variant
Dim strLAST_NAME As String
Dim strCREDENTIAL_TYPE As String
Dim strOCCUPATION_TYPE As String
Dim strTRAINING_TYPE As String
Dim strFilter As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rptMulti") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string from lstName listbox
For Each varItem In Me.lstName.ItemsSelected
strLAST_NAME = strLAST_NAME & ",'" & Me.lstName.ItemData(varItem) _
& "'"
Next varItem
If Len(strLAST_NAME) = 0 Then
strLAST_NAME = "Like '*'"
Else
strLAST_NAME = Right(strLAST_NAME, Len(strLAST_NAME) - 1)
strLAST_NAME = "IN(" & strLAST_NAME & ")"
End If
' Build criteria string from lstCredentails listbox
For Each varItem In Me.lstCredentials.ItemsSelected
strCREDENTIAL_TYPE = strCREDENTIAL_TYPE & ",'" & Me.lstCredentials.ItemData(varItem) _
& "'"
Next varItem
If Len(strCREDENTIAL_TYPE) = 0 Then
strCREDENTIAL_TYPE = "Like '*'"
Else
strCREDENTIAL_TYPE = Right(strCREDENTIAL_TYPE, Len(strCREDENTIAL_TYPE) - 1)
strCREDENTIAL_TYPE = "IN(" & strCREDENTIAL_TYPE & ")"
End If
' Build criteria string from lstOccupations listbox
For Each varItem In Me.lstOccupations.ItemsSelected
strOCCUPATION_TYPE = strOCCUPATION_TYPE & ",'" & Me.lstOccupations.ItemData(varItem) _
& "'"
Next varItem
If Len(strOCCUPATION_TYPE) = 0 Then
strOCCUPATION_TYPE = "Like '*'"
Else
strOCCUPATION_TYPE = Right(strOCCUPATION_TYPE, Len(strOCCUPATION_TYPE) - 1)
strOCCUPATION_TYPE = "IN(" & strOCCUPATION_TYPE & ")"
End If
' Build criteria string from lstTraining listbox
For Each varItem In Me.lstTraining.ItemsSelected
strTRAINING_TYPE = strTRAINING_TYPE & ",'" & Me.lstTraining.ItemData(varItem) _
& "'"
Next varItem
If Len(strTRAINING_TYPE) = 0 Then
strTRAINING_TYPE = "Like '*'"
Else
strTRAINING_TYPE = Right(strTRAINING_TYPE, Len(strTRAINING_TYPE) - 1)
strTRAINING_TYPE = "IN(" & strTRAINING_TYPE & ")"
End If
' Build filter string
strFilter = "[LAST_NAME] " & strLAST_NAME & _
" AND [CREDENTIAL_TYPE] " & strCREDENTIAL_TYPE & _
" AND [OCCUPATION_TYPE] " & strOCCUPATION_TYPE & _
" AND [TRAINING_TYPE] " & strTRAINING_TYPE
' Apply the filter and switch it on
With Reports![rptMulti]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Re DAO. I followed Martin Green's preference for DAO code (I couldn't do it myself otherwise) and set the options in tools. However, does this mean the report won't run on other computers with Access 2000 that don't have that set? If so, I need to change it because this may be run on several computers. Then I may be up a crick since I don't really understand what it is. I know I'm getting in over my head but I'm pretty good at this cutting an pasting.
I have several tables all linked to the main Person table with a foreign key. I want to have a multiple multi-select boxes eg. List 1: Profession (eg, physician, RN, EMT, etc) List 2: Occupation-really means experience (eg, Administration, Research, Clinical, etc) that will then open a report with these criteria.
I'm not sure how to deal with null values. With the data already entered none of the physicians listed work activities. So if I select "physician" in List 1 no records appear because they have no records in the "Occupation" List. I decided to add a multi-select list of all the people in the database thinking everyone is of course in that list and that then all physicians would appear whether or not any other criteria were in the other list boxes. It doesn't seem to work. I know have 4 lists. It seems to work ok otherwise. Here is the code (with some comments):
Private Sub cmdOK_Click()
Dim varItem As Variant
Dim strLAST_NAME As String
Dim strCREDENTIAL_TYPE As String
Dim strOCCUPATION_TYPE As String
Dim strTRAINING_TYPE As String
Dim strFilter As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rptMulti") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string from lstName listbox
For Each varItem In Me.lstName.ItemsSelected
strLAST_NAME = strLAST_NAME & ",'" & Me.lstName.ItemData(varItem) _
& "'"
Next varItem
If Len(strLAST_NAME) = 0 Then
strLAST_NAME = "Like '*'"
Else
strLAST_NAME = Right(strLAST_NAME, Len(strLAST_NAME) - 1)
strLAST_NAME = "IN(" & strLAST_NAME & ")"
End If
' Build criteria string from lstCredentails listbox
For Each varItem In Me.lstCredentials.ItemsSelected
strCREDENTIAL_TYPE = strCREDENTIAL_TYPE & ",'" & Me.lstCredentials.ItemData(varItem) _
& "'"
Next varItem
If Len(strCREDENTIAL_TYPE) = 0 Then
strCREDENTIAL_TYPE = "Like '*'"
Else
strCREDENTIAL_TYPE = Right(strCREDENTIAL_TYPE, Len(strCREDENTIAL_TYPE) - 1)
strCREDENTIAL_TYPE = "IN(" & strCREDENTIAL_TYPE & ")"
End If
' Build criteria string from lstOccupations listbox
For Each varItem In Me.lstOccupations.ItemsSelected
strOCCUPATION_TYPE = strOCCUPATION_TYPE & ",'" & Me.lstOccupations.ItemData(varItem) _
& "'"
Next varItem
If Len(strOCCUPATION_TYPE) = 0 Then
strOCCUPATION_TYPE = "Like '*'"
Else
strOCCUPATION_TYPE = Right(strOCCUPATION_TYPE, Len(strOCCUPATION_TYPE) - 1)
strOCCUPATION_TYPE = "IN(" & strOCCUPATION_TYPE & ")"
End If
' Build criteria string from lstTraining listbox
For Each varItem In Me.lstTraining.ItemsSelected
strTRAINING_TYPE = strTRAINING_TYPE & ",'" & Me.lstTraining.ItemData(varItem) _
& "'"
Next varItem
If Len(strTRAINING_TYPE) = 0 Then
strTRAINING_TYPE = "Like '*'"
Else
strTRAINING_TYPE = Right(strTRAINING_TYPE, Len(strTRAINING_TYPE) - 1)
strTRAINING_TYPE = "IN(" & strTRAINING_TYPE & ")"
End If
' Build filter string
strFilter = "[LAST_NAME] " & strLAST_NAME & _
" AND [CREDENTIAL_TYPE] " & strCREDENTIAL_TYPE & _
" AND [OCCUPATION_TYPE] " & strOCCUPATION_TYPE & _
" AND [TRAINING_TYPE] " & strTRAINING_TYPE
' Apply the filter and switch it on
With Reports![rptMulti]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Re DAO. I followed Martin Green's preference for DAO code (I couldn't do it myself otherwise) and set the options in tools. However, does this mean the report won't run on other computers with Access 2000 that don't have that set? If so, I need to change it because this may be run on several computers. Then I may be up a crick since I don't really understand what it is. I know I'm getting in over my head but I'm pretty good at this cutting an pasting.