How to populate a list box based on user input?

[Guest]

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.

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.

 

[Dirk Goldgar]

In

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

 

[Guest]

Awesome!! Thank you!!

Now I know how to make a list box do a SQL statement! How do I make a text
box do a SQL Statement I specify? It doesn't have the option to put the SQL
in the Row Source. It only has Control Source. I'm afraid I don't
understand the difference between Control Source and Row Source.

In

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)

 

[Dirk Goldgar]

In

Awesome!! Thank you!!

Now I know how to make a list box do a SQL statement! How do I make
a text box do a SQL Statement I specify?

You can't do that directly. What you can do is set the text box's
Control Source to an expression that calls one of the domain aggregate
functions, such as DLookup, DMin, Dmax, or DCount (those are the most
useful ones). For example, you could have this for a text box's Control
Source:

=DLookup("SomeField", "MyTable", "ID=" & [ID])

That would be the equivalent of the SQL statement

SELECT TOP 1 SomeField FROM MyTable
WHERE ID=[the value of the ID field on your form]

Bear in mind that binding a text box to a domain aggregate function is
not a good substitute for binding the control to a field in the form's
RecordSource query. If you can build a query, likely involving multiple
tables, that gives you all the fields you want to display on your form
*and is updatable (if this form is to allow edits and additions)*, then
binding the form to that query is usually a better solution than using a
domain aggregate function to retrieve the data.

It doesn't have the option
to put the SQL in the Row Source. It only has Control Source. I'm
afraid I don't understand the difference between Control Source and
Row Source.

A Control Source (or ControlSource property) is the field or expression
to which the control is bound; that is, where it gets the value it
displays, and where it will store the updated value. Of course using a
function expression as the ControlSource automatically makes it
non-updatable.

The Row Source (RowSource property) only exists for controls such as
list boxes and combo boxes, which display multiple rows of data. It's
the table or query from which the rows to be displayed are drawn.

There's one more "Source" property worth mentioning here: the Record
Source (RecordSource) property. This is the table or query where a form
or report gets the data to be displayed and edited.

 

[Guest]

Dirk, that makes perfect sense to build a query that gets info from all the
tables that will be in use on the form. I had actually done that on a
previous database when I could not get the control source to do it! It had
tons of tables and I thought it was a work-around, but apparently others have
done it before. That makes me feel better.

I am trying to speed speed up querying of the 1 million records in the order
table? I know websites do this efficiently all the time with their data. How
else does one of a million bank customers log in to their Bank of America
account in a matter of a second (or less!)

I want an employee to be able to type an account # in a text box on a Access
form then immediately see a list box pop up with all of the order #s
associated with the order specified. Your advice worked perfectly and quick.

But then as you know in my case, the employee selects/highlights one or
several of the order #s that pop up in the list box. Then the click "View
Order Details" and that is where things get really slow.

I have the piece of code that you helped me write to shows a query that
contains the order details for all of the selected orders. It works, but
really slowly. We are talking several minutes to retrieve and display the
Order details for the selected orders. What can I do to speed the retrieval
up when the users clicks the button to view the rental order details? ROE is
short for rental order entry (aka an order)

Private Sub cmdViewROEs_Click()
Dim strROEs As String
Dim varItem As Variant

With Me!lstROEs
If .ItemsSelected.Count = 0 Then
MsgBox "You have not selected any ROEs for display."
Else
' Make delimited list of orders.
For Each varItem In .ItemsSelected
' Note: this assumes that the field ROE is numeric.
' strROEs = strROEs & "," & .ItemData(varItem)

' If it's text, use this:
strROEs = strROEs & _
",'" & .ItemData(varItem) & "'"

Next varItem

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

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

' Update the SQL property of the query we'll
' use to display the orders.
CurrentDb.QueryDefs("qryDisplayROEsInfo").SQL = _
"SELECT * FROM orderdata WHERE [HeroRO] " & strROEs

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


End If
End With

End Sub

In

Awesome!! Thank you!!

Now I know how to make a list box do a SQL statement! How do I make
a text box do a SQL Statement I specify?

You can't do that directly. What you can do is set the text box's
Control Source to an expression that calls one of the domain aggregate
functions, such as DLookup, DMin, Dmax, or DCount (those are the most
useful ones). For example, you could have this for a text box's Control
Source:

=DLookup("SomeField", "MyTable", "ID=" & [ID])

That would be the equivalent of the SQL statement

SELECT TOP 1 SomeField FROM MyTable
WHERE ID=[the value of the ID field on your form]

Bear in mind that binding a text box to a domain aggregate function is
not a good substitute for binding the control to a field in the form's
RecordSource query. If you can build a query, likely involving multiple
tables, that gives you all the fields you want to display on your form
*and is updatable (if this form is to allow edits and additions)*, then
binding the form to that query is usually a better solution than using a
domain aggregate function to retrieve the data.

It doesn't have the option
to put the SQL in the Row Source. It only has Control Source. I'm
afraid I don't understand the difference between Control Source and
Row Source.

A Control Source (or ControlSource property) is the field or expression
to which the control is bound; that is, where it gets the value it
displays, and where it will store the updated value. Of course using a
function expression as the ControlSource automatically makes it
non-updatable.

The Row Source (RowSource property) only exists for controls such as
list boxes and combo boxes, which display multiple rows of data. It's
the table or query from which the rows to be displayed are drawn.

There's one more "Source" property worth mentioning here: the Record
Source (RecordSource) property. This is the table or query where a form
or report gets the data to be displayed and edited.

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

(please reply to the newsgroup)

 

[Dirk Goldgar]

In

Dirk, that makes perfect sense to build a query that gets info from
all the tables that will be in use on the form.

Well, that depends on how many tables are going to be used on the form,
and their relationships, and what the form is going to be used for.
Often building a "master query" that joins lots of tables together is
*not* the best solution. Combo boxes do have their uses, as I said, as
do subforms. One problem with "giant master queries" is that they are
often not updatable. But judicious use of table joins can get the
information you need onto your form.

I am trying to speed speed up querying of the 1 million records in
the order table? I know websites do this efficiently all the time
with their data. How else does one of a million bank customers log
in to their Bank of America account in a matter of a second (or less!)

You're going about it the right way.

I want an employee to be able to type an account # in a text box on a
Access form then immediately see a list box pop up with all of the
order #s associated with the order specified. Your advice worked
perfectly and quick.

So far, so good.

But then as you know in my case, the employee selects/highlights one
or several of the order #s that pop up in the list box. Then the
click "View Order Details" and that is where things get really slow.

I have the piece of code that you helped me write to shows a query
that contains the order details for all of the selected orders. It
works, but really slowly. We are talking several minutes to retrieve
and display the Order details for the selected orders. What can I do
to speed the retrieval up when the users clicks the button to view
the rental order details?

Is the field HeroRO in table OrderDetails indexed? If you want decent
performance with this many records, it must be.

 

[Guest]

Well tblOrderdata is a linked table. That might explain the slowness which
is exacerbated by the sheer quantity of records. It links into the
tblOrderdata from another developer's database. I think what I may do is
develop a daily process to make a local copy of the linked tblOrderdata each
early AM. Then put it in a pre-defined table with indexes on the appropriate
fields. Then query against it. Shouldn't that run much quicker? Maybe
there is a simpler way but that's my wild idea for the day! Now if only I
could automate the early, early AM process before employees want to use fresh
data in the new database.

 

[Dirk Goldgar]

In

Well tblOrderdata is a linked table. That might explain the slowness
which is exacerbated by the sheer quantity of records. It links into
the tblOrderdata from another developer's database.

The fact that tblOrderData is a linked table should not in itself make
the query slow. Considerations are:

1. Is the field indexed or not? If it's not indexed, any query against
it is going to be slow. If you don't have control of the table, then
you may not be able to create an index on the field, in which case your
idea of copying the table to a local version may be a satisfactory
workaround. But whether you go against a local table or a linked one,
the field must be indexed for decent performance.

2. Assuming the linked table is on a network share somewhere, is your
network really slow? A normal modern network should be plenty fast
enough to bring back a small number of records quickly. But Access
needs to be able to use the table's index to select and request just the
records you want to display.

3. Is there a delay involved in connecting to this other database? That
can often be resolved by keeping some form open that is bound to some
table linked from the external database, or by keeping a global
recordset open on that database. However, I wouldn't even begin to
attack the problem this way until I've verified that it's a connection
delay, not a missing index, that is causing the slowdown.

I think what I
may do is develop a daily process to make a local copy of the linked
tblOrderdata each early AM. Then put it in a pre-defined table with
indexes on the appropriate fields. Then query against it. Shouldn't
that run much quicker?

That could work, but it shouldn't be necessary.

Now if only I could automate the early, early
AM process before employees want to use fresh data in the new
database.

That can be done by a scheduler task. We can talk about that if it
turns out to be necessary.

 

[Guest]

Our network isn't slow at all. So I guess that is not it. The table does
not have any indexes applied to it in the database that originally obtains
it. Ofcourse I don't have control of linked table, so don't I have to make a
copy of the table to use so that I have the ability to apply some indexes to
it? When I made a copy and applied an index to HeroRO, it became lighting
fast to query. But if I have to go this route, I'll have to schedule an
early AM process to go grab the recently updated order table from the other
database (which got the order table from someone doing an import into the db
for them).

 

[Dirk Goldgar]

In

Our network isn't slow at all. So I guess that is not it. The table
does not have any indexes applied to it in the database that
originally obtains it.

Argh! Why the heck not?

Ofcourse I don't have control of linked
table,

Can you not convince the person who controls the database to index that
field?

so don't I have to make a copy of the table to use so that I
have the ability to apply some indexes to it?

Would you be allowed to modify the database yourself to index that
field? Is the table in question regularly deleted and recreated (by
import, for example)? In that case you might have to recreate the index
every time the table is recreated. Would you be allowed to do that,
seeing as it involves modifying the source database?

When I made a copy and
applied an index to HeroRO, it became lighting fast to query.

Strongly suggesting that it's the lack of an index that's causing the
problem.

But if
I have to go this route, I'll have to schedule an early AM process to
go grab the recently updated order table from the other database
(which got the order table from someone doing an import into the db
for them).

If you have to schedule a task to do this, sure you can. All you have
to do is define a macro in your database that uses the RunCode action to
execute your copy routine, which exits Access when it's done copying and
indexing the table. Then you can set up the task scheduler to execute a
command line that starts Access and passes your database's path and the
/x command-line option to execute your macro.

 

[Guest]

I looked more at the source table I am linking to from another database.
Basically, the person who controls that database is importing a text file
from an ftp side that national IS headquarters sticks out there each early
AM. It is imported using a import spec file that labels the fields, field
types and indexes. According to the import spec file she uses, she doesn't
put ANY indexes on the info coming in!!!! Nor are there indexes on the table
the info is appended into. Couldn't I just modify the import spec file to
indicate indexes for the appropriate fields? Then when I link to the table,
provided network speed isn't an issue, it will be FASTER.

 

[Dirk Goldgar]

In

I looked more at the source table I am linking to from another
database. Basically, the person who controls that database is
importing a text file from an ftp side that national IS headquarters
sticks out there each early AM. It is imported using a import spec
file that labels the fields, field types and indexes. According to
the import spec file she uses, she doesn't put ANY indexes on the
info coming in!!!! Nor are there indexes on the table the info is
appended into. Couldn't I just modify the import spec file to
indicate indexes for the appropriate fields? Then when I link to the
table, provided network speed isn't an issue, it will be FASTER.

That depends on the nature of this "import spec file", and how it is
processed. I'm not sure, but it doesn't sound like you're talking about
the normal Access import/output specifications, which are stored in
hidden system tables. You may or may not be talking about a schema.ini
file that is processed automatically by Access.

If this import spec file you mention is part of some home-grown import
function, then it's entirely possible that you can solve your problem by
modifying it as you suggest -- assuming you have the authority to do
that -- but I can't give you any advice as to how.