> I assume you have other selection criteria in the query that also point to
> controls listed on your form. So in that case you'll get;
>
> Query performing all selections, except UNIT ===>> Filter to strip the
> data of all UNITS not selected (performing the UNIT selection) ====>> Show
> the report/form (which now includes all selections you needed)
>
> In your original code, you only had 1 query, the one I told you to change to
> a form. All the others are already forms or reports.
> You now say that you can't modify the query because the other forms would
> then show to much data (the UNIT selection is gone).
> You can simply resolve this by applying the same filter to those reports.
>
> Another way would be to create a copy of your query, remove the UNIT
> selection from that query, and make the new form based upon this new query
> (and use the filter on that).
> Instead of all forms being based upon the same query, you would have 1 form
> based on the new query (without UNIT selection), and all the other
> reports/forms on the old query (with UNIT selection).
>
> does this help?
>
>
> "Thorson" wrote:
>
> > 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 datasheet 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 datasheet 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 datasheet 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 datasheet 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
> > > > >