help with combo box and list box population

[incagold]

Hi guys,

Newbie back again with a question.

We have a form which contains a combo box and a list box.

Our objective is to populate the list box based on the selection from the combo box. One of the selections of the combo box is "(All)". We get the desired results if we select any single value but we do not know how to construct the rowsource to bring in all records if the selection is (All) or a single record if that is selected.

Is there a way to do this? If so could you provide an example of how to approach this.

Thank you very much in advance,

BEF

 

[HarleyQuinn]

Try something like this:

Private Sub Combo0_Click()
Dim i As Integer

List2.RowSource = "" 'use if the listbox needs to be cleared before an entry is added

Select Case Combo0.Value
Case "All" ' select all values
For i = 0 To Combo0.ListCount - 1
If Combo0.ItemData(i) <> "All" Then List2.AddItem Combo0.ItemData(i)
Next i
Case Else 'if it's not all that's selected
List2.AddItem Combo0.Value ' add the selected value
End Select
End Sub

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[incagold]

Hi HarleyQuinn,

Thank you for your response. This gave us food for thought, but I believe we have understated what we are trying to accomplish. Let me try again.

The combo box (cboSelIPT) has a Row Source:
SELECT * FROM TMP_tblIPT ORDER BY [IPT];

The list box (lstList) has a Row Source:
SELECT [ID_NO], [ASSGN_IPT], [POS_NO], [NAME], [FIRST] FROM tblEMPLOYEES WHERE ASSGN_IPT=[Forms]![frmForm2]![cboSelIPT] ORDER BY [ASSGN_IPT], [POS_NO];

We would like to be able to make a single or "all" selection in the combo box. Having made this selection, we would like to use the value to populate the list information using the combo box value as our criteria for what is displayed.

We would also like to display "all" information from tblEMPLOYEES if the selection is (All).

Once this information is in the list box, we would like to re-order the position of entries in the list box (we have a routine that does this now). The purpose for "all" is to allow our clerical personel to alter (1) list if the number of changes is significant. The event works fine when we select (1) entry from the combo box but we don't know the means to alter the row source so that when "all" is selected, the routine will bring back all table data.

Just a side note, the list is a priority ranking of contract employees on a government project. They are placed on teams and their roles with those teams change, hence we move there listing around to satisfy the priority needs. Up until now this has been a manual and tedious task. We are trying to minimize this efort.

Once again, thank you very much for replying and trying to help. We sincerely appreciate the time and effort you have shown us. I hope the above will give a better understanding of where we would like to go.

BEF

 

[HarleyQuinn]

Hi,

How about something like:

Private Sub Combo0_Click()

lstList.RowSource = "" 'use if the listbox needs to be cleared before an entry is added

Select Case cboSelIPT.Value
Case "All" ' select all values
lstList.RowSource = "SELECT [ID_NO], [ASSGN_IPT], [POS_NO], [NAME], [FIRST] FROM tblEMPLOYEES ORDER BY [ASSGN_IPT], [POS_NO];"
Case Else 'if it's not all that's selected
lstList.RowSource = "SELECT [ID_NO], [ASSGN_IPT], [POS_NO], [NAME], [FIRST] FROM tblEMPLOYEES WHERE ASSGN_IPT=[Forms]![frmForm2]![cboSelIPT] ORDER BY [ASSGN_IPT], [POS_NO];"
End Select
End Sub

Using this will set the rowsource at runtime using a case statement to either the selection you already have (when a value is selected) or to all (the same query but with no criteria) when you select "All".

From what I have understood this is something like what you are after. If it is not please post back and I will try and modify to suit your needs.

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[HarleyQuinn]

Hi,

How about something like:

Private Sub cboSelPT_Click()

lstList.RowSource = "" 'use if the listbox needs to be cleared before an entry is added

Select Case cboSelIPT.Value
Case "All" ' select all values
lstList.RowSource = "SELECT [ID_NO], [ASSGN_IPT], [POS_NO], [NAME], [FIRST] FROM tblEMPLOYEES ORDER BY [ASSGN_IPT], [POS_NO];"
Case Else 'if it's not all that's selected
lstList.RowSource = "SELECT [ID_NO], [ASSGN_IPT], [POS_NO], [NAME], [FIRST] FROM tblEMPLOYEES WHERE ASSGN_IPT=[Forms]![frmForm2]![cboSelIPT] ORDER BY [ASSGN_IPT], [POS_NO];"
End Select
End Sub

Using this will set the rowsource at runtime using a case statement to either the selection you already have (when a value is selected) or to all (the same query but with no criteria) when you select "All".

From what I have understood this is something like what you are after. If it is not please post back and I will try and modify to suit your needs.

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[HarleyQuinn]

Please ignore my first reply today as it was posted in error.

Thanks

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[incagold]

Hi HarleyQuinn,

Thank you very much. That did exactly what we wanted. Once again you guys bailed us newbies out.

Sorry for the delay in reply, but was out of town Friday and did not get to my computer until this A.M.

Once again, thank you very much.

BEF

 

[HarleyQuinn]

Gald I could help

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.