Multi Select List Box

[Wendy]

I have been at this a long time. I have reviewed all prior posts regarding
multi select list boxes to filter data for a report. I am just not "getting"
it.

I have a query "qryF2GLSummary" that includes "EffectiveDate" and "Step"
parameters. I have created a form that opens when the report rptF2 is run to
input these criteria. What I would like to do is include a multi select list
box where the user can select one or more or all GL Accounts from the
tblGL_Account. (This table has 8 records and two fields, GL_Account and
Description, both text fields)

I am trying to modify the examples I have seen, but am running into some
snags. I am working in 2003 but using 2000 file format. I have included the
code below.

Any help would be appreciated. Thank you

Private Sub OK_Click()
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "qryF2GLSummary"
With Me.lstGLAccount
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """&.Column (1, varItem) & "","
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[GL_Account] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "GL_Account" & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition: strWhere
Exit Sub

End Sub

 

[Nikos Yannacopoulos]

Wendy,

I can see a possible problem with your code. To begin with, insert a line:

Debug.Print strWhere

right before the DoCmd.OpenReport, and look at the expression that you
created in the immediate window. Is it what you expected? I guess not.

What is the story with strDelim? You are using it in your loop without
having assigned a value to it (so it returns null). What did you intend
it to do? My guess is your GL_Account field is text (which is the right
type for this use), so strDelim was supposed to insert the text
qualifier? If yes, then assign it a value:
strDelim = "'"
before the For loop.

Also, I was wondering, why are you creating strDescrip? You don't seem
to be using it anywhere, but you are trying to insert the text
qualifiers directly there, as opposed to using strDelim (if my guess was
correct).
Tip: when needing text qualifiers within string expressions in VBA, it
is easier to use the single quote instead of the double one, so VBA
doesn't get confused with double quotes within strings.

HTH,
Nikos

 

[Wendy]

Thank you for your advice. I was using the code that Allen Browne referes to
for these types of questons.

I think I am having issues with my definitions...if I define strDoc as the
query I will use for the report then I get an error telling me it cannot find
report named qryF2GLSummary...maybe I am not undersatnding this concept well.
I also think my strWhere definition is also incorrect.I have attached the
revised code below:

Thank you...Wendy

Private Sub OK_Click()
Dim varItem As Variant
Dim strWhere As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "qryF2GLSummary"
strDelim = "'"
With Me.lstGLAccount
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[GL_Account] IN (" & Left$(strDelim, lngLen) & ")"
End If

DoCmd.OpenReport strDoc, acViewPreview, strWhere


Exit Sub


End Sub

 

[Wendy]

Got most of it to work... The only problem I am having is that the character
of the data returned is deleted...I was using code from
http://allenbrowne.com/ser-50.html...in there is code to remove trailing
comma. I think this is what is causing the last character to be deleted.
Could you help me identify the part of the code doing that?

Thank you for your advice. I was using the code that Allen Browne referes to
for these types of questons.

I think I am having issues with my definitions...if I define strDoc as the
query I will use for the report then I get an error telling me it cannot find
report named qryF2GLSummary...maybe I am not undersatnding this concept well.
I also think my strWhere definition is also incorrect.I have attached the
revised code below:

Thank you...Wendy

Private Sub OK_Click()
Dim varItem As Variant
Dim strWhere As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "qryF2GLSummary"
strDelim = "'"
With Me.lstGLAccount
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[GL_Account] IN (" & Left$(strDelim, lngLen) & ")"
End If

DoCmd.OpenReport strDoc, acViewPreview, strWhere


Exit Sub


End Sub

I have been at this a long time. I have reviewed all prior posts regarding
multi select list boxes to filter data for a report. I am just not "getting"
it.

I have a query "qryF2GLSummary" that includes "EffectiveDate" and "Step"
parameters. I have created a form that opens when the report rptF2 is run to
input these criteria. What I would like to do is include a multi select list
box where the user can select one or more or all GL Accounts from the
tblGL_Account. (This table has 8 records and two fields, GL_Account and
Description, both text fields)

I am trying to modify the examples I have seen, but am running into some
snags. I am working in 2003 but using 2000 file format. I have included the
code below.

Any help would be appreciated. Thank you

Private Sub OK_Click()
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "qryF2GLSummary"
With Me.lstGLAccount
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """&.Column (1, varItem) & "","
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[GL_Account] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "GL_Account" & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition: strWhere
Exit Sub

End Sub

 

[Nikos Yannacopoulos]

Wendy,

Your first problem, which I missed the previous time, but you seem to
have resolved by now, is that variable strDoc needs to be assigned the
report name, not the query name.

Apart from that, the variable in your Left$() function should be
strWhere - cause this is the one you are trying to truncate and apply -
not strDelim! Once you fix this, it should be fine.

HTH,
Nikos