RE: Code using list box and multi select option

Tech-Archive recommends: Fix windows errors by optimizing your registry

So you are saying instead of creating a form that selects the "C-Unit" for
the field to just open the query without a selection and use a filter to
narrow it down? Is that correct or did I mis-understand?

If that is what you are suggesting that would work fine for that query but
the form also requires the field "C-Unit" to be selected for other purposes,
other reports that the form is used to generate. So it is necessary to have
the user select a specific "C-Unit" on that form. I couldn't create a
separate form for those other functions because they run through the query I
am originally working with above; a filter wouldn't work for these purposes.


"Tieske" wrote:

Hi,

OK I understand you make a selection in your query that refers to a field on
the form. You can't use this method if you want to use the multiselect option.

Loose the selection on the unit type in the query (any other criteria can
remain). This will make the query select all units. Now the code I provided
creates a filtertext, that performs the same selection you now have in your
query, but now with the multiselect option.

Probably didn't work because of the double selection, once in the query and
once in using the filter in the code.

The reason for making a form is that a query cannot be opened with an
additional filter (which is required for the multiselect option as explained
above). On the other hand, a form can be opened with an additional filter.
Also, if you open a form in data*** mode, it looks pretty much the same as
an opened query. So the end result should be the same. (lookup the
docmd.openquery and the docmd.openform function in Access help and compare
the parameters for both)

regards
Tieske


"Thorson" wrote:

The code does work... but I don't think that it is what I want. I'm sorry
maybe I just don't understand. Why did I need to create a form
"frmcurrentInventory2"? I want it to run a query "qryCurrentInventory2" this
query is already created, it has a field "C-Unit" I would like to limit to
whatever the user selects in this form... I'm not sure how to change the code
to make this work.

This is what I ended up putting in, it asked for an End Select and another
End IF:

Dim strFltr As String
Dim varLstItem As Variant
' Build a unit list
strFltr = vbNullString
If Me.lstUnit.ItemsSelected.Count > 0 Then
For Each varLstItem In Me.lstUnit.ItemsSelected
strFltr = strFltr & Chr(34) & Me.lstUnit.ItemData(varLstItem) & Chr(34)
& ", "
' if the data is a numeric value then use: (not enclosed in double quotes)
' strFltr = strFltr & Me.lstUnit.ItemData(varLstItem) & ", "
Next varLstItem
strFltr = Left(strFltr, Len(strFltr) - 2) ' remove the final comma and
Space
' set whatever field you need to align with the data selected in the
listbox in the line below
strFltr = "[C-Unit] IN (" & strFltr & ")"
End If

' from here on check the: Me.lstUnit.ItemsSelected.Count for the number
of items selected or
' use 'strFltr = vbNullString' to test whether there is none selected
' Further more; from here on when opening reports or forms 'strFltr' can
be used for the filter setting

If IsNull(cboViewReport) Then
MsgBox "Please Select Report to View"
Else
Select Case cboViewReport
Case "Current Inventory"
' replaced 'IsNull(lstUnit)' by 'strFltr = vbNullString' on thefollowing
Line
If IsNull(txtDate) Or strFltr = vbNullString Then
MsgBox "Please Select Current Inventory Date and Unit(s)" 'added '(s)'
Else
' Open form based upon your query in data*** mode, using the Filter
DoCmd.OpenForm "frmCurrentInventory2", acFormDS, , strFltr
End If
End Select
End If

"Tieske" wrote:

Hi Thorson,

I think I misread your post also, but let me explain;

My first question is why would I add it to the DoCmd.OpenReport Command?
The reason I want to change the code is for the query "qryCurrentInventory2"

Lookup the help for DoCMD.OpenForm and DoCMD.OpenReport, you'll see they
both have a parameter "Filter", where you would set an SQL type filter that
would be applied to the form/report you're opening. The filter usually is the
text following the 'WHERE' statement in an SQL. Take some of your queries and
select the SQL-view and you'll see what I mean. An example could be; "[Month]
6" to select anything in a table/query that has the month field somewhere
in the second half of the year.
The DoCMD.OpenQuery doesn't allow you to pass a filter. Hence, make a report
or form showing your data to be able to use that functionality.


The part of the code I'm assuming would change is the first if-then-else
statement, that is the part of the code dealing with the query
"qryCurrentInventory2" the rest of the code is for other actions depending on
what the user selects on the form.

Yes and No, if you set it to multiselect, you'll have to test the other
if/thens as well. Do you want the ones that test for lstUnit IsNull to allow
multiple units as well?
Once you set the multiselect, use the lstUnit.ItemsSelected.Count to check
the number of items selected. So the other if thens need adjustment to.
Additionaly if you want to use the filter elsewhere as well, it needs to be
created before the if then of this query.

4 things to do;
1) Set the multiselect property of the listbox to TRUE
2) Set the correct fieldname at the bottom of the 'Build a unit list' part
3) create a form frmCurrentInventory2 that has all the fields you want
to show (formatting isn't an issue, because the code below will open it
in data*** mode, so don't bother)
4) copy the code to the module

Here's your code adjusted (haven't tested it, but it should work or be easy
to fix)

Option Compare Database
Option Explicit

Private Sub cmdOK_Click()
Dim strFltr As String
Dim varLstItem As Variant
' Build a unit list
strFltr = vbNullString
If Me.lstUnit.ItemsSelected.Count > 0 Then
For Each varLstItem In Me.lstUnit.ItemsSelected
strFltr = strFltr & Chr(34) & Me.lstUnit.ItemData(varLstItem) &
Chr(34) & ", "
' if the data is a numeric value then use: (not enclosed in double
quotes)
' strFltr = strFltr & Me.lstUnit.ItemData(varLstItem) & ", "
Next varLstItem
strFltr = Left(strFltr, Len(strFltr) - 2) ' remove the final comma and
space
' set whatever field you need to align with the data selected in the
listbox in the line below
strFltr = "[Here goes your fieldname] IN (" & strFltr & ")"
End If

' from here on check the: Me.lstUnit.ItemsSelected.Count for the
number of items selected or
' use 'strFltr = vbNullString' to test whether
there is none selected
' Further more; from here on when opening reports or forms 'strFltr' can be
used for the filter setting
'

If IsNull(cboViewReport) Then
MsgBox "Please Select Report to View"
Else
Select Case cboViewReport
Case "Current Inventory"
' replaced 'IsNull(lstUnit)' by 'strFltr = vbNullString' on the
following line
If IsNull(txtDate) Or strFltr = vbNullString Then
MsgBox "Please Select Current Inventory Date and Unit(s)" '
added '(s)'
Else
' Open form based upon your query in data*** mode, using
the filter
DoCmd.OpenForm "frmCurrentInventory2", acFormDS, , strFltr
End If

Somewhere in the past I got this code from an example I found here;
http://www.accessmvp.com/DJSteele/SmartAccess.html
Check the: "March 2006: Let me check my list... " at the bottom


Hope this helps, let me know the results.

regards,
Tieske

.

Quantcast