multiple choice in a combo box

[Ra]

Hi,

I have a combo box on a form showing a list of plants.
I need to select from the drop down list two plants at one time. Any way I
can select more than one plant at a time?

Thank you,

 

[Douglas J. Steele]

No. Combo boxes are single-select. For multiselect, your only choices are a
list box or a subform.

 

[Ra]

So if I replace the combo box with a list box, any way you can give a hint on
how to make multiple selection?

 

[Douglas J. Steele]

You set the list box's MultiSelect property to something other than None.

That having been said, though, what do you intend to do with the selections?
You cannot bind a control to a multiselect list box.

 

[Ra]

My data base is linked thru Oracle to a data server.
In my list box a have a number of two plants that feed info from the same
data pool. Normally I select one plant first, run the queries, and get a
table1.
I than select the second plant, run the queries, and get a table that I
append on top of table1.
I am trying to run with one query both plants at the same time.

Thank you,

 

[Douglas J. Steele]

How do you currently run the queries?

With a multiselect list box, you need code along the lines of the following
to refer to each selected item:

Dim varSelected As Variant

For Each varSelected In Me![NameOfListbox].ItemsSelected
' At this point, Me![NameOfListbox].ItemData(varSelected) will
' refer to the bound column for one of the selected items in the list box.
' To refer to the value in a different column than the bound one, you'd use
' Me![NameOfListbox].Column(n, varSelected), where n is the column
' number (numbering starts at 0, not 1)
Next varSelected

 

[Ra]

I have:
Query1(make table) that runs the first plant[AMT]

SELECT TPD30_PRODUCTION_RUN.PART_ID, TPD30_PRODUCTION_RUN.PLANT_ID,
TPD30_PRODUCTION_RUN.GROSS_PIECES, TPD30_PRODUCTION_RUN.REPORTING_DATE,
TPD30_PRODUCTION_RUN.REPORTING_DATE INTO [*TPD30_PRODUCTION_RUN_AMT]
FROM TPD30_PRODUCTION_RUN
GROUP BY TPD30_PRODUCTION_RUN.PART_ID, TPD30_PRODUCTION_RUN.PLANT_ID,
TPD30_PRODUCTION_RUN.GROSS_PIECES, TPD30_PRODUCTION_RUN.REPORTING_DATE,
TPD30_PRODUCTION_RUN.REPORTING_DATE
HAVING (((TPD30_PRODUCTION_RUN.PLANT_ID)=[Forms]![MAIN MENU]![SET_PLANT1])
AND ((TPD30_PRODUCTION_RUN.REPORTING_DATE) Between [Forms]![MAIN MENU]![SET
DATE-MAIN MENU] And [Forms]![MAIN MENU]![Ending Date]));

Query2 (make table) that runs the second plant [PFM]:

SELECT TPD30_PRODUCTION_RUN.PART_ID, TPD30_PRODUCTION_RUN.PLANT_ID,
TPD30_PRODUCTION_RUN.GROSS_PIECES, TPD30_PRODUCTION_RUN.REPORTING_DATE,
TPD30_PRODUCTION_RUN.REPORTING_DATE INTO [*TPD30_PRODUCTION_RUN_PON]
FROM TPD30_PRODUCTION_RUN
GROUP BY TPD30_PRODUCTION_RUN.PART_ID, TPD30_PRODUCTION_RUN.PLANT_ID,
TPD30_PRODUCTION_RUN.GROSS_PIECES, TPD30_PRODUCTION_RUN.REPORTING_DATE,
TPD30_PRODUCTION_RUN.REPORTING_DATE
HAVING (((TPD30_PRODUCTION_RUN.PLANT_ID)=[Forms]![MAIN MENU]![SET_PLANT])
AND ((TPD30_PRODUCTION_RUN.REPORTING_DATE) Between [Forms]![MAIN MENU]![SET
DATE-MAIN MENU] And [Forms]![MAIN MENU]![Ending Date]));

Query3(append query)that append AMT to PFM

INSERT INTO [*TPD30_PRODUCTION_RUN_PON] ( PART_ID, PLANT_ID, GROSS_PIECES,
REPORTING_DATE )
SELECT [*TPD30_PRODUCTION_RUN_AMT].PART_ID,
[*TPD30_PRODUCTION_RUN_AMT].PLANT_ID,
*TPD30_PRODUCTION_RUN_AMT].GROSS_PIECES,
[*TPD30_PRODUCTION_RUN_AMT].REPORTING_DATE
FROM [*TPD30_PRODUCTION_RUN_AMT]
GROUP BY [*TPD30_PRODUCTION_RUN_AMT].PART_ID,
[*TPD30_PRODUCTION_RUN_AMT].PLANT_ID,
[*TPD30_PRODUCTION_RUN_AMT].GROSS_PIECES,
[*TPD30_PRODUCTION_RUN_AMT].REPORTING_DATE;

What I would like to do is deal with one list box that selects both plants
the same time, instead of me running three queries to get the data.

How do you currently run the queries?

With a multiselect list box, you need code along the lines of the following
to refer to each selected item:

Dim varSelected As Variant

For Each varSelected In Me![NameOfListbox].ItemsSelected
' At this point, Me![NameOfListbox].ItemData(varSelected) will
' refer to the bound column for one of the selected items in the list box.
' To refer to the value in a different column than the bound one, you'd use
' Me![NameOfListbox].Column(n, varSelected), where n is the column
' number (numbering starts at 0, not 1)
Next varSelected



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

My data base is linked thru Oracle to a data server.
In my list box a have a number of two plants that feed info from the same
data pool. Normally I select one plant first, run the queries, and get a
table1.
I than select the second plant, run the queries, and get a table that I
append on top of table1.
I am trying to run with one query both plants at the same time.

Thank you,

 

[Douglas J. Steele]

Is there a reason for Make Table queries, rather than just using a simple
Union query?

Assuming you name the list boxes on the form SET_PLANT and SET_PLANT1, one
approach to maintaining your current set-up would be something like:

Dim strSQL As String
Dim strPlant As String
Dim strPlant1 As String
Dim varSelected As Variant

For Each varSelected In [Forms]![MAIN MENU]![SET_PLANT].ItemsSelected
strPlant = strPlant & [Forms]![MAIN
MENU]![SET_PLANT].ItemData(varSelected) & ", "
Next varSelected

For Each varSelected In [Forms]![MAIN MENU]![SET_PLANT1].ItemsSelected
strPlant1 = strPlant1 & [Forms]![MAIN
MENU]![SET_PLANT1].ItemData(varSelected) & ", "
Next varSelected

strPlant = Left(strPlant, Len(strPlant)-2)
strPlant1 = Left(strPlant1, Len(strPlant1)-2)

strSQL = "SELECT PART_ID, PLANT_ID, " & _
"GROSS_PIECES, REPORTING_DATE, " &
"REPORTING_DATE " & _
"INTO [*TPD30_PRODUCTION_RUN_AMT] " & _
"FROM TPD30_PRODUCTION_RUN " & _
"WHERE PLANT_ID IN (" & strPlant1 & ") " & _
"AND REPORTING_DATE BETWEEN " & _
Format([Forms]![MAIN MENU]![SET DATE-MAIN MENU], "\#yyyy\-mm\-dd\#") & _
" AND " & _
Format([[Forms]![MAIN MENU]![Ending Date], "\#yyyy\-mm\-dd\#") & _
"GROUP BY PART_ID, PLANT_ID, " & _
"GROSS_PIECES, REPORTING_DATE, " & _
"REPORTING_DATE"
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "PART_ID, PLANT_ID, " & _
"GROSS_PIECES, REPORTING_DATE, " & _
"REPORTING_DATE " & _
"INTO [*TPD30_PRODUCTION_RUN_PON] " & _
"FROM TPD30_PRODUCTION_RUN "

"WHERE PLANT_ID IN (" & strPlant & ") " & _
"AND REPORTING_DATE BETWEEN " & _
Format([Forms]![MAIN MENU]![SET DATE-MAIN MENU], "\#yyyy\-mm\-dd\#") & _
" AND " & _
Format([[Forms]![MAIN MENU]![Ending Date], "\#yyyy\-mm\-dd\#") & _
"GROUP BY PART_ID, PLANT_ID, " & _
"GROSS_PIECES, REPORTING_DATE, " & _
"REPORTING_DATE "

CurrentDb.Execute strSQL, dbFailOnError

and then run your Append query.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have:
Query1(make table) that runs the first plant[AMT]

SELECT TPD30_PRODUCTION_RUN.PART_ID, TPD30_PRODUCTION_RUN.PLANT_ID,
TPD30_PRODUCTION_RUN.GROSS_PIECES, TPD30_PRODUCTION_RUN.REPORTING_DATE,
TPD30_PRODUCTION_RUN.REPORTING_DATE INTO [*TPD30_PRODUCTION_RUN_AMT]
FROM TPD30_PRODUCTION_RUN
GROUP BY TPD30_PRODUCTION_RUN.PART_ID, TPD30_PRODUCTION_RUN.PLANT_ID,
TPD30_PRODUCTION_RUN.GROSS_PIECES, TPD30_PRODUCTION_RUN.REPORTING_DATE,
TPD30_PRODUCTION_RUN.REPORTING_DATE
HAVING (((TPD30_PRODUCTION_RUN.PLANT_ID)=[Forms]![MAIN MENU]![SET_PLANT1])
AND ((TPD30_PRODUCTION_RUN.REPORTING_DATE) Between [Forms]![MAIN
MENU]![SET
DATE-MAIN MENU] And [Forms]![MAIN MENU]![Ending Date]));

Query2 (make table) that runs the second plant [PFM]:

SELECT TPD30_PRODUCTION_RUN.PART_ID, TPD30_PRODUCTION_RUN.PLANT_ID,
TPD30_PRODUCTION_RUN.GROSS_PIECES, TPD30_PRODUCTION_RUN.REPORTING_DATE,
TPD30_PRODUCTION_RUN.REPORTING_DATE INTO [*TPD30_PRODUCTION_RUN_PON]
FROM TPD30_PRODUCTION_RUN
GROUP BY TPD30_PRODUCTION_RUN.PART_ID, TPD30_PRODUCTION_RUN.PLANT_ID,
TPD30_PRODUCTION_RUN.GROSS_PIECES, TPD30_PRODUCTION_RUN.REPORTING_DATE,
TPD30_PRODUCTION_RUN.REPORTING_DATE
HAVING (((TPD30_PRODUCTION_RUN.PLANT_ID)=[Forms]![MAIN MENU]![SET_PLANT])
AND ((TPD30_PRODUCTION_RUN.REPORTING_DATE) Between [Forms]![MAIN
MENU]![SET
DATE-MAIN MENU] And [Forms]![MAIN MENU]![Ending Date]));

Query3(append query)that append AMT to PFM

INSERT INTO [*TPD30_PRODUCTION_RUN_PON] ( PART_ID, PLANT_ID, GROSS_PIECES,
REPORTING_DATE )
SELECT [*TPD30_PRODUCTION_RUN_AMT].PART_ID,
[*TPD30_PRODUCTION_RUN_AMT].PLANT_ID,
*TPD30_PRODUCTION_RUN_AMT].GROSS_PIECES,
[*TPD30_PRODUCTION_RUN_AMT].REPORTING_DATE
FROM [*TPD30_PRODUCTION_RUN_AMT]
GROUP BY [*TPD30_PRODUCTION_RUN_AMT].PART_ID,
[*TPD30_PRODUCTION_RUN_AMT].PLANT_ID,
[*TPD30_PRODUCTION_RUN_AMT].GROSS_PIECES,
[*TPD30_PRODUCTION_RUN_AMT].REPORTING_DATE;

What I would like to do is deal with one list box that selects both plants
the same time, instead of me running three queries to get the data.

How do you currently run the queries?

With a multiselect list box, you need code along the lines of the
following
to refer to each selected item:

Dim varSelected As Variant

For Each varSelected In Me![NameOfListbox].ItemsSelected
' At this point, Me![NameOfListbox].ItemData(varSelected) will
' refer to the bound column for one of the selected items in the list
box.
' To refer to the value in a different column than the bound one, you'd
use
' Me![NameOfListbox].Column(n, varSelected), where n is the column
' number (numbering starts at 0, not 1)
Next varSelected



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

My data base is linked thru Oracle to a data server.
In my list box a have a number of two plants that feed info from the
same
data pool. Normally I select one plant first, run the queries, and get
a
table1.
I than select the second plant, run the queries, and get a table that I
append on top of table1.
I am trying to run with one query both plants at the same time.

Thank you,




:

You set the list box's MultiSelect property to something other than
None.

That having been said, though, what do you intend to do with the
selections?
You cannot bind a control to a multiselect list box.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


So if I replace the combo box with a list box, any way you can give
a
hint
on
how to make multiple selection?

:

No. Combo boxes are single-select. For multiselect, your only
choices
are
a
list box or a subform.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

I have a combo box on a form showing a list of plants.
I need to select from the drop down list two plants at one time.
Any
way I
can select more than one plant at a time?

Thank you,