Can someone explain what this piece of code is doing?

[mvital]

Hi All,
I have the following piece of code. Can someone tell me what it is doing in words?

For Each varItem In Me.lstInvestigation.ItemsSelected
strInvestigation = strInvestigation & ",'" & Me.lstInvestigation.ItemData(varItem) _
& "'"
Next varItem

If Len(strInvestigation) = 0 Then
strInvestigation = "Like '*'"
Else
strInvestigation = Right(strInvestigation, Len(strInvestigation) - 1)
strInvestigation = "IN(" & strInvestigation & ")"
End If


thanks in advance,

Maribel

 

[SgtJarrow]

    For Each varItem In Me.lstInvestigation.ItemsSelected
    strInvestigation = strInvestigation & ",'" & Me.lstInvestigation.ItemData(varItem) _
    & "'"
    Next varItem

This is looping through all the selected items in the lstInvestigation ListBox and adding them into the string variable strInvestigation, single quote wrapped and separated by a comma.

    If Len(strInvestigation) = 0 Then
    strInvestigation = "Like '*'"
    Else
    strInvestigation = Right(strInvestigation, Len(strInvestigation) - 1)
    strInvestigation = "IN(" & strInvestigation & ")"
    End If

This is modifying a Like clause for a query (most likely) based on the selected items found in the ListBox and which were placed in the strInvestigation variable.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[mvital]

Thanks Robert.

I asked because I do not get an error. What I have is a form that applies various filters on a report based on the criteria chosen. Some of the filters are working and not others. I have verified that the list boxes contain the information on the report for that field. When I make a sample id selection in the list boxt and then click on the 'apply filter' button, the report opens and I should see the results for the sample id, but I don't. I get a blank page. What could be happening? How can some selections work and not others? I can see the information I want in the report preview I make that selection and apply filter and it does not come up.

thanks again!

 

[SgtJarrow]

Maribel,

Do all the "filters" use this same structure and are only applied one at a time? This emans, you can make selections from on list box at a time only....the others don't matter. If that is the case, I would like to the sources of the listboxes as the trouble. You may be displaying the "name" but the "id" is the primary field...so the strInvestigation in the case would be filled with ids and not names. That may or may not be the case.

If there are multiple listboxes that all filter the report, then it could be a relational problem. Again this could be related to ids vs display values.

If you are having trouble finding the issue, feel free to post a sample of table structures and data, as well as the query (SQL view copy paste is usually wasiest) and any other important information so I can review what you have and what might be the causes.

Good luck.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[mvital]

Robert,

Thanks for your time and willingness to work this through with me. It's frustrating when you have an issue like this and there is no error. So, thank you!

All the filters use the same structure. You can select to filter the report by one of the listboxes or all of them.
I have pasted all the code. The add filter and remove filter as well. Like I said, right now I can filter by sample_id '043'and see all the results for that sample. When I preview the report I see a sample_id '0001', but when I select it from my listbox and click on the add filter I get a blank page. The list boxes are populated by querying for the distinct values for that field in the query that builds the report. So, that I only get the values that make up the report. I think it might be a filter issue somehow when I click 'remove filter' it keeps it or doesn't clear it completely somehow. You know similar to when you do a filter by form or filter by selection or advance filter in datasheet view.

Code Below:

Private Sub cmdApplyFilter_Click()
Dim varItem As Variant
Dim strAnalyte As String
Dim strLocation As String
Dim strTask As String
Dim strEmployee As String
Dim strFilter As String
Dim strSortOrder As String
Dim strSource As String
Dim strLimitType As String
Dim strInvestigation As String
Dim strCompany As String
Dim strSample As String


' Check that the report is open
Dim Response As VbMsgBoxResult
If SysCmd(acSysCmdGetObjectState, acReport, "rpt_ContaminantandExposureLimit") <> acObjStateOpen Then
Response = MsgBox("The report is not open." _
& vbCrLf & "Do you want to open it now?" _
, vbQuestion + vbYesNoCancel)
Select Case Response
Case vbYes
DoCmd.OpenReport "rpt_ContaminantandExposureLimit", acViewPreview
Case vbNo
Exit Sub
Case vbCancel
DoCmd.Close acForm, Me.Name
Exit Sub
End Select
End If

' Build criteria string from lstSample listbox
For Each varItem In Me.lstSample.ItemsSelected
strSample = strSample & ",'" & Me.lstSample.ItemData(varItem) _
& "'"
Next varItem
If Len(strSample) = 0 Then
strSample = "Like '*'"
Else
strSample = Right(strSample, Len(strSample) - 1)
strSample = "IN(" & strSample & ")"
End If

'Build criteria string from Investigation listbox

For Each varItem In Me.lstInvestigation.ItemsSelected
strInvestigation = strInvestigation & ",'" & Me.lstInvestigation.ItemData(varItem) _
& "'"
Next varItem

If Len(strInvestigation) = 0 Then
strInvestigation = "Like '*'"
Else
strInvestigation = Right(strInvestigation, Len(strInvestigation) - 1)
strInvestigation = "IN(" & strInvestigation & ")"
End If

' Build criteria string from lstLocation listbox
For Each varItem In Me.lstLocation.ItemsSelected
strLocation = strLocation & ",'" & Me.lstLocation.ItemData(varItem) _
& "'"
Next varItem
If Len(strLocation) = 0 Then
strLocation = "Like '*'"
Else
strLocation = Right(strLocation, Len(strLocation) - 1)
strLocation = "IN(" & strLocation & ")"
End If


' Build criteria string from lstAnalyte listbox
For Each varItem In Me.lstAnalyte.ItemsSelected
strAnalyte = strAnalyte & ",'" & Me.lstAnalyte.ItemData(varItem) _
& "'"
Next varItem
If Len(strAnalyte) = 0 Then
strAnalyte = "Like '*'"
Else
strAnalyte = Right(strAnalyte, Len(strAnalyte) - 1)
strAnalyte = "IN(" & strAnalyte & ")"
End If


' Build criteria string from lstTask listbox
For Each varItem In Me.lstTask.ItemsSelected
strTask = strTask & ",'" & Me.lstTask.ItemData(varItem) _
& "'"
Next varItem
If Len(strTask) = 0 Then
strTask = "Like '*'"
Else
strTask = Right(strTask, Len(strTask) - 1)
strTask = "IN(" & strTask & ")"
End If

' Build criteria string from lstEmployee listbox
For Each varItem In Me.lstEmployee.ItemsSelected
strEmployee = strEmployee & ",'" & Me.lstEmployee.ItemData(varItem) _
& "'"
Next varItem
If Len(strEmployee) = 0 Then
strEmployee = "Like '*'"
Else
strEmployee = Right(strEmployee, Len(strEmployee) - 1)
strEmployee = "IN(" & strEmployee & ")"
End If

' Build criteria string from lstCompany listbox
For Each varItem In Me.lstCompany.ItemsSelected
strCompany = strCompany & ",'" & Me.lstCompany.ItemData(varItem) _
& "'"
Next varItem
If Len(strCompany) = 0 Then
strCompany = "Like '*'"
Else
strCompany = Right(strCompany, Len(strCompany) - 1)
strCompany = "IN(" & strCompany & ")"
End If


' Build criteria string from fraSource option group
Select Case Me.fraSource.value
Case 1
strSource = "='NIOSH'"
Case 2
strSource = "='OSHA'"
Case 3
strSource = "='ACGIH'"
End Select

' Build criteria string from fraType option group
Select Case Me.fraType.value
Case 1
strLimitType = "='Ceiling'"
Case 2
strLimitType = "='PEL'"
Case 3
strLimitType = "='REL'"
Case 4
strLimitType = "='STEL'"
Case 5
strLimitType = "='TLV'"
End Select

' Build filter string
strFilter = " [SAMPLE_ID] " & strSample & _
" AND [Sampling_Event] " & strInvestigation & _
" AND [Location] " & strLocation & _
" AND [Analyte] " & strAnalyte & _
" AND [Work_Task] " & strTask & _
" AND [Employee_Name] " & strEmployee & _
" AND [Company_Name] " & strCompany & _
" AND [Source] " & strSource & _
" AND [Limit_type] " & strLimitType

' Apply filter and sort to report
With Reports![rpt_ContaminantandExposureLimit]
.Filter = strFilter
.FilterOn = True
.OrderBy = strSortOrder
.OrderByOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
Dim varItem As Variant
Dim i As Integer

' Remove filter and sort from report
On Error Resume Next
With Reports![rpt_ContaminantandExposureLimit]
.FilterOn = False
.OrderByOn = False
End With
On Error GoTo 0

fraType.value = 0
fraSource.value = 0

' Reset form to original values
For Each varItem In Me.lstSample.ItemsSelected
Me.lstSample.Selected(varItem) = False
Next varItem
For Each varItem In Me.lstAnalyte.ItemsSelected
Me.lstAnalyte.Selected(varItem) = False
Next varItem
For Each varItem In Me.lstLocation.ItemsSelected
Me.lstLocation.Selected(varItem) = False
Next varItem
For Each varItem In Me.lstTask.ItemsSelected
Me.lstTask.Selected(varItem) = False
Next varItem
For Each varItem In Me.lstEmployee.ItemsSelected
Me.lstEmployee.Selected(varItem) = False
Next varItem
For Each varItem In Me.lstInvestigation.ItemsSelected
Me.lstInvestigation.Selected(varItem) = False
Next varItem
For Each varItem In Me.lstCompany.ItemsSelected
Me.lstCompany.Selected(varItem) = False
Next varItem
End Sub

thanks,

MV

 

[SgtJarrow]

Here's the first thing that comes to mind....

Since you have a complicated filter criteria, try placing a MSGBOX or some such after each portion of the build happens ro step through the code. You can watch to make sure it is building properly. Once you have confirmed the build is correct, then you can look as to what else could be the issue.

  ' Build criteria string from lstSample listbox
    For Each varItem In Me.lstSample.ItemsSelected
        strSample = strSample & ",'" & Me.lstSample.ItemData(varItem) _
        & "'"
    Next varItem
    If Len(strSample) = 0 Then
        strSample = "Like '*'"
    Else
        strSample = Right(strSample, Len(strSample) - 1)
        strSample = "IN(" & strSample & ")"
    End If

MSGBOX(STRSAMPLE
   
'Build criteria string from Investigation listbox

For Each varItem In Me.lstInvestigation.ItemsSelected
    strInvestigation = strInvestigation & ",'" & Me.lstInvestigation.ItemData(varItem) _
    & "'"
    Next varItem

If Len(strInvestigation) = 0 Then
    strInvestigation = "Like '*'"
Else
    strInvestigation = Right(strInvestigation, Len(strInvestigation) - 1)
    strInvestigation = "IN(" & strInvestigation & ")"
End If

MSGBOX(STRINVESTIGATION)
.
.
.
.
.
.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[mvital]

Robert,

I tried the msgbox code and it looks like everything is working fine. I just can't figure it out.

Any other ideas? Otherwise, thank you SO much for your help!

MV