Multiple select list box

[ScottXe]

Currently I am using a multiple select list box in a form to select the engineer names for a report. I am looking for some enhancement.

Firstly when I do not select any name, there is no records displayed. I need to show all records if I do not select any name in the dropdown list.

I would like to create anther format of report to suit other purpose. Can I add the selection of these two reports apart from the selection of engineer name.

After I complete the selection on the form, I click a command button that has On Click event that consists of following code.

Private Sub Command15_Click()

'Create a string that we can apply to the filter criteria
Dim strWhere As String
strWhere = "" 'Initialize string variable

Dim varSelection As Variant

'Iterate the selections in the listbox
'and apply the results to frmByState's filter property
If Me.List0.ItemsSelected.Count = 0 Then

Exit Sub 'Go ahead and bail, no items selected

ElseIf Me.List0.ItemsSelected.Count = 0 Then 'There is no need for OR operator with a single selection

For Each varSelection In Me.List0.ItemsSelected

strWhere = "[Engineer] = " & "'" & Me.List0.Column(1, varSelection) & "'"

Next varSelection

Else

For Each varSelection In Me.List0.ItemsSelected

strWhere = strWhere & "[Engineer] = " & "'" & Me.List0.Column(1, varSelection) & "'" & " OR "

Next varSelection

strWhere = Left(strWhere, InStrRev(strWhere, " OR ")) 'Use the left function to trim the last 4 characters

End If

'Open the report and filtered to the selection
DoCmd.OpenReport "rptPerformanceByEngineer", acViewReport, , strWhere

'Close this form
DoCmd.Close acForm, "frmSearchCriteria"

End Sub

 

[Ranman256]

Wouldnt be easier to make a SINGLE pick list box and when the user dbl-clicks the item , runs an append query to put the item in a 'pick' table.
Once all the items are selected, the picked items are joined to your data table to pull the data?

With zero code.
You can also pick a report. Both can use the pick table.

 

[ScottXe]

Hi Ranman256,

Thanks for your suggestion. I have not set up the pick table yet. Is it complicated to it setup? Any further reference to set up this feature. Thanks!

 

[Ranman256]

You make a form with a list box of choices.
The user dbl-clicks an item, this fires DBL-CLICK event that runs an append query.
this item is appended to the pick table.

(you may need a query to delete all from the table too)


It uses these queries:
qsAvail: the available list to pick from
select [ST], [state] from tSTates

qs1State:
select [ST], [state] from tSTates where [ST] = forms!frmPick!lstState

qsPicked:
select * from tPicked

qaAddPickedPerson:
INSERT INTO tPicked ( ST, State )
SELECT [ST] ,[State] FROM tStates
WHERE ((ST)=[Forms]![frmPick]![lstAvail)

qdEmptyPikTbl:
DELETE * FROM tPicked

qdDel1Person:
DELETE * FROM tPicked where [ST] = forms!frmPick!lstPicked

 

[spikepl]

@OP

Was there a question there somewhere? I could not really see what you wanted help with.

@Ranman256

You have added a lot of complexity, and I do not quite see the reason. Further, a local selection (that would also work for a multitude of users) has now become global (since it is in a table) that would mess up multiple users like that.

Further, you'll have db bloat, because you create/delete tables.

Sorry but that is a not a good recommendation. Why should all that be better than what OP had going? What problem does it fix?

 

[ScottXe]

You make a form with a list box of choices.
The user dbl-clicks an item, this fires DBL-CLICK event that runs an append query.
this item is appended to the pick table.

(you may need a query to delete all from the table too)
View attachment 59093

It uses these queries:
qsAvail: the available list to pick from
select [ST], [state] from tSTates

qs1State:
select [ST], [state] from tSTates where [ST] = forms!frmPick!lstState

qsPicked:
select * from tPicked

qaAddPickedPerson:
INSERT INTO tPicked ( ST, State )
SELECT [ST] ,[State] FROM tStates
WHERE ((ST)=[Forms]![frmPick]![lstAvail)

qdEmptyPikTbl:
DELETE * FROM tPicked

qdDel1Person:
DELETE * FROM tPicked where [ST] = forms!frmPick!lstPicked

Thanks for your suggestion. I will try it later.

 

[ScottXe]

@OP

Was there a question there somewhere? I could not really see what you wanted help with.

@Ranman256

You have added a lot of complexity, and I do not quite see the reason. Further, a local selection (that would also work for a multitude of users) has now become global (since it is in a table) that would mess up multiple users like that.

Further, you'll have db bloat, because you create/delete tables.

Sorry but that is a not a good recommendation. Why should all that be better than what OP had going? What problem does it fix?

Hi spikepl,

I need the help to show all records when I do not select any names in the listbox and select other report format based on current listbox form.

 

[MarkK]

Well, if nothing is selected in the list, your code exits the routine without opening the report. Change that.

Private Sub Command15_Click()
    Dim strWhere As String
    Dim varSelection As Variant
    
    If Me.List0.ItemsSelected.count = 0 Then
[COLOR="Green"]        'when nothing selected, your code exits here[/COLOR]
        Exit Sub
    ElseIf Me.List0.ItemsSelected.count = 0 Then
[COLOR="Green"]        'this block will never run.  see why?[/COLOR]
        For Each varSelection In Me.List0.ItemsSelected
            strWhere = "[Engineer] = " & "'" & Me.List0.column(1, varSelection) & "'"
        Next varSelection
    Else
        For Each varSelection In Me.List0.ItemsSelected
            strWhere = strWhere & "[Engineer] = " & "'" & Me.List0.column(1, varSelection) & "'" & " OR "
        Next varSelection
        strWhere = Left(strWhere, InStrRev(strWhere, " OR "))
    End If
    
    DoCmd.OpenReport "rptPerformanceByEngineer", acViewReport, , strWhere
    DoCmd.Close acForm, "frmSearchCriteria"
End Sub