Code using list box and multi select option

T

Thorson

I am very new to writing code and know very little.

I have a form that is used to enter parameters in for multiple reports and
queries, therefore there is multiple if-then-else statements in the code. The
first query (qryCurrentInventory2) relies upon the date (cboDate) and a
selection of 1 of 3 units (lstUnit) to run the query. I would like to make
it possible for the user to select more than 1 unit and have the query then
search for both those units.

I was looking at a website on how to write this code and I couldn't get it
to work. The code below is what I currently have, the list box (lstUnit) is
not functioning as a multi-select box and therefore only allows the user to
select one unit. If someone could give me a website to look at or help me
write it that'd be great!


The current code for this query is as follows (the entire code is listed
below that):

If IsNull(cboViewReport) Then
MsgBox "Please Select Report to View"
Else
Select Case cboViewReport
Case "Current Inventory"
If IsNull(txtDate) Or IsNull(lstUnit) Then
MsgBox "Please Select Current Inventory Date and Unit"
Else
DoCmd.OpenQuery "qryCurrentInventory2"
End If


The entire code for the form with all the options is as follows:


Private Sub cmdOK_Click()
If IsNull(cboViewReport) Then
MsgBox "Please Select Report to View"
Else
Select Case cboViewReport
Case "Current Inventory"
If IsNull(txtDate) Or IsNull(lstUnit) Then
MsgBox "Please Select Current Inventory Date and Unit"
Else
DoCmd.OpenQuery "qryCurrentInventory2"
End If
Case "Open Therapeutic Cases"
If IsNull(txtDate) Or IsNull(lstUnit) Then
MsgBox "Please Select Current Inventory Date and Unit"
Else
DoCmd.OpenReport "rptOpenTherapeuticCases", acViewPreview
End If
Case "Check Withdrawal Dates"
If IsNull(txtDate) Or IsNull(lstUnit) Then
MsgBox "Please Select Current Inventory Date and Unit"
Else
DoCmd.OpenReport "rptWithdrawalCheckDates", acViewPreview
End If
Case "Monthly Protocol Report"
If IsNull(txtDate) Or IsNull(cboMonth) Or IsNull(cboYear) Then
MsgBox "Please Select Current Inventory Date, Month and Year"
Else
DoCmd.OpenReport "rptMonthlyReportPG2Protocol", acViewPreview
End If
Case "Monthly Inventory Report"
If IsNull(txtDate) Or IsNull(lstUnit) Or IsNull(cboMonth) Or
IsNull(cboYear) Then
MsgBox "Please Select Current Inventory Date, Unit, Month and Year"
Else
Select Case lstUnit
Case "DSAC"
DoCmd.RunMacro "mcrRunMonthlyInventoryReportDSAC"
Case "ORR"
DoCmd.RunMacro "mcrRunMonthlyInventoryReportORR"
Case "URB"
DoCmd.RunMacro "mcrRunMonthlyInventoryReportURB"
Case Else
MsgBox "Invalid entry in Unit"
End Select
End If
Case Else
MsgBox "Invalid entry in View Report"
End Select
End If
End Sub
 
T

Tieske

build an SQL filter statement with each of the selected names, something like
strFilter = "[UnitID] = " & Unit1 & " or [UnitID] = " & Unit2
if you don't know how many the user selects, ofcourse you'll have to write
some code to create a string holding a proper filter. btw the example assumes
that UnitID is numeric, if it is a text/string, then enclose it in quotes
(single quotes will do);
strFilter = "[UnitID] = '" & Unit1 & "' or [UnitID] = '" & Unit2 & "'"
(last chaarcters here are;
Unit2_space_ampersand_doublequote_singlequote_doublequote)

and add the filter to the DoCMD.OpenReport command (look it up in help)
 
T

Thorson

I'm sorry, I'm having trouble following what you are saying, I am very new at
code.

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"
so that if the user picks more than one Unit in "lstUnit" the query will
search for multiple units instead of just limiting it to one. Yes, my units
are text not numeric.

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.
 
T

Tieske

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"
Click to expand...

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
Click to expand...
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.
Click to expand...

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
 
T

Thorson

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 said:
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"
Click to expand...

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
Click to expand...
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.
Click to expand...

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
Click to expand...
 
T

Tieske

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 said:
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 said:
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"
Click to expand...

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
Click to expand...
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.
Click to expand...

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
Click to expand...
Click to expand...
 
T

Thorson

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
T

Tieske

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 said:
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 said:
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 said:
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

:

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
Click to expand...
Click to expand...
Click to expand...
 
T

Thorson

For now I came up with a little easier solution, I created a duplicate query
and edited it how I wanted. With 2007 it is easy to use filters to sort.

Thank you for your help, I may look into this further in the future.
--
Thorson


Tieske said:
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 said:
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 said:
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


:

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

:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Making boxes availble depending on combo box entry 9
how to do this bit of code better? 2
Using Select case and isnull 5
Validate 3 Text Fields - Urgent Need to finish by Monday 2
Form to pass strWhere to open report 2
Open Report with the parameter 1
List box 3
Add Cases 1

Top