Re: How to populate a list box based on user input?

In news:653FA27F-F4C7-49D5-97C5-3809DC790397@xxxxxxxxxxxxx,
worksfire1 <worksfire1@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
An employee needs to be able to enter an account # in a text box on a
form, then click the "Submit" button and then see a list of orders
associated with that account pop up in a list box. They then need to
be able to highlight and select multiple orders from that list with
the CTRL or SHIFT key (courtesy of using the Extended List Box
feature.) Then once selecting all desired orders in the list, they
click "View" and a query come up showing all those orders on separate
records with miscellaneous information from other fields in the order
table.

All this can be done without too much trouble.

At the very beginning, how do you get the specific orders for the
account the user typed in the text box on the form to pop up in a
list box without having to make a separate query that passes a form
value paramater and putting that query in the Row Source. I think it
can be done another way. Maybe enter a SQL statement in the Control
Source for the list box? If so, what should the statement look like?
SELECT Order from tblOrder where ...pass the account # in text box.
I get stuck on what the SQL should look like in the Control Source
section to just show the list of orders associated with the account #
typed in by the employee on the form.

You can set the RowSource (not ControlSource) of the list box to a SQL
statement that references the text box. For example,

SELECT [Order] FROM tblOrder
WHERE AccountNo = [Forms]![YourFormName]![txtAccountNo]

Then all you have to do is use the AfterUpdate event of the text box to
requery the list box. For example,

Private Sub txtAccountNo_AfterUpdate()

Me!lstOrders.Requery

End Sub

The next step, opening a query on the selected orders, is the tricky
bit. There are several ways to go about it. The simplest is to use a
stored query for this, but update the query's SQL on the fly. That
would look something like this (assuming the command button to trigger
this is called "cmdViewOrders"):

'----- start of example code -----
Private Sub cmdViewOrders_Click()

Dim strOrders As String
Dim varItem As Variant

With Me!lstOrders
If .ItemsSelected.Count = 0 Then
MsgBox "No orders selected for display."
Else
' Make delimited list of orders.
For Each varItem In .ItemsSelected
strOrders = strOrders & "," & .ItemData(varItem)
' Note: this assumes that the field Order is numeric.
' If it's text, use this:
' strOrders = strOrders & _
' ",'" & .ItemData(varItem) & "'"
Next varItem

strOrders = Mid$(strOrders, 2) ' drop leading comma

' Add an operator to the list.
If .ItemsSelected.Count > 1 Then
strOrders = "In (" & strOrders & ")"
Else
strOrders = "= " & strOrders
End If

' Update the SQL property of the query we'll
' use to display the orders.
CurrentDb.QueryDefs("qryDisplayOrders").SQL = _
"SELECT * FROM tblOrder WHERE [Order] " & strOrders

' Open the query to display the orders.
DoCmd.OpenQuery "qryDisplayOrders"

End If
End With

End Sub
'----- end of example code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.