Need selection in one list box to pull query info into another

Hi, this is my first project and everyone has been very helpful to me. Thank
you.
I have one more hurdle, if someone can help. I have a list box which pulls
customer orders as the result of a date search. When I select a customer in
this list, I would like to have the option to push a command button and have
this list box, or a second list box display all customer orders with that
name. I have created a query for this process, with the fields in it I need,
but I don't know where to go from here.

Hi Ron,

I'd use a second listbox filtered on the customers id. Then you'd just need
to issue a requery on the second listbox in the AfterUpdate event of the
first listbox.

Brian


"Ron Weaver" wrote in message
...
Hi, this is my first project and everyone has been very helpful to me.
Thank
you.
I have one more hurdle, if someone can help. I have a list box which pulls
customer orders as the result of a date search. When I select a customer
in
this list, I would like to have the option to push a command button and
have
this list box, or a second list box display all customer orders with that
name. I have created a query for this process, with the fields in it I
need,
but I don't know where to go from here.

First, is your list box a multiselect list box? If not, I suggest you change
it to a combo. It is easier to use as a parameter for the query you will
need to pull the data you want. If it is a multiselect listbox, then the way
to do that is to build a Where condition for your query based on the
selections in the list box. This, however, is pretty code intensive. First
you have to determine which rows in the list box are selected, and build a
string that can be used as an SQL WHERE clause.

Here is a function I use for that. It will seem a little strange, because
in the form I am using it, there are six list boxes and none or all of them
may have selections made:

'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

Now, I have to add it to the query to do the filtering. Notice in this
case, I am using HAVING because it is a Totals query. What I do here is use
a stored query, read in its SQL string, and modify it with the Replace
function, and store it to another query so I don't muck up the original, and
the report uses this query as its recordsource:

Set dbf = CurrentDb
Set qdfs = dbf.QueryDefs
Set qdfXl = CurrentDb.QueryDefs(strXlQuery)
strSQL = qdfXl.SQL

'Delete the old query in case an error left it hanging
For Each qdf In qdfs
If qdf.Name = "_BPOTemp" Then
qdfs.Delete qdf.Name
Exit For
End If
Next qdf

If Len(strWhere) 0 Then
'This keeps the HAVING clause that is common to all versions of the report.
strWhere = "HAVING " & strWhere & " AND "
strSQL = Replace(strSQL, "HAVING ", strWhere)

Set qdf = dbf.CreateQueryDef("_BPOTemp", strSQL)


"Ron Weaver" wrote:

Hi, this is my first project and everyone has been very helpful to me. Thank
you.
I have one more hurdle, if someone can help. I have a list box which pulls
customer orders as the result of a date search. When I select a customer in
this list, I would like to have the option to push a command button and have
this list box, or a second list box display all customer orders with that
name. I have created a query for this process, with the fields in it I need,
but I don't know where to go from here.

Brian, I tried to fiqure out this filter thing. Can you help me with some
code and where to put it. Now the second part: If I put a command button on
the first list and tie it to a requery macro, and put that macro into the
AfterUpdate event of the first list box, does that sound right?

"Brian Bastl" wrote:

Hi Ron,

I'd use a second listbox filtered on the customers id. Then you'd just need
to issue a requery on the second listbox in the AfterUpdate event of the
first listbox.

Brian


"Ron Weaver" wrote in message
...
Hi, this is my first project and everyone has been very helpful to me.
Thank
you.
I have one more hurdle, if someone can help. I have a list box which pulls
customer orders as the result of a date search. When I select a customer
in
this list, I would like to have the option to push a command button and
have
this list box, or a second list box display all customer orders with that
name. I have created a query for this process, with the fields in it I
need,
but I don't know where to go from here.

Hi Ron,

Basically, if you have the customerid as the bound column in the rowsource
for the first listbox, your rowsource for the second listbox would be
something like:

SELECT OrderID, OrderDate, Whatever else
FROM [Orders Table]
WHERE ((([Orders Table].[CustomerID]) = Forms!MyForm!Listbox1))

Then in the After Update event procedure of Listbox1, you'd requery
Listbox2:

Private Sub Listbox1_AfterUpdate()
Me.Listbox2.Requery
End Sub

There will be no need for macros or command buttons.

If this example doesn't help, can you copy and paste the rowsources for both
listboxes as well as the form name?

HTH,
Brian

"Ron Weaver" wrote in message
...
Brian, I tried to fiqure out this filter thing. Can you help me with some
code and where to put it. Now the second part: If I put a command button
on
the first list and tie it to a requery macro, and put that macro into the
AfterUpdate event of the first list box, does that sound right?

"Brian Bastl" wrote:

Hi Ron,

I'd use a second listbox filtered on the customers id. Then you'd just
need
to issue a requery on the second listbox in the AfterUpdate event of the
first listbox.

Brian


"Ron Weaver" wrote in message
...
Hi, this is my first project and everyone has been very helpful to me.
Thank
you.
I have one more hurdle, if someone can help. I have a list box which
pulls
customer orders as the result of a date search. When I select a
customer
in
this list, I would like to have the option to push a command button
and
have
this list box, or a second list box display all customer orders with
that
name. I have created a query for this process, with the fields in it I
need,
but I don't know where to go from here.

Brian
Here is the SQL statement entered into the rowsource of ListBox2. You will
notice I am using Expr1 instead of CustomerID, this is because I want to pull
all customers with the same name. When I try to save the following statement
I get the message: "Characters found after the end of SQL statement". Do you
see a problem with it?
SELECT [Customer Query].Expr1, [Customer Query].StartDate, [Customer
Query].CustPhone, [Customer Query].OrderID
FROM [Customer Query];
WHERE ((([Customer Query].[Expr1])=Forms!OrderDateForm!ListBox0 ))

"Brian Bastl" wrote:

Hi Ron,

Basically, if you have the customerid as the bound column in the rowsource
for the first listbox, your rowsource for the second listbox would be
something like:

SELECT OrderID, OrderDate, Whatever else
FROM [Orders Table]
WHERE ((([Orders Table].[CustomerID]) = Forms!MyForm!Listbox1))

Then in the After Update event procedure of Listbox1, you'd requery
Listbox2:

Private Sub Listbox1_AfterUpdate()
Me.Listbox2.Requery
End Sub

There will be no need for macros or command buttons.

If this example doesn't help, can you copy and paste the rowsources for both
listboxes as well as the form name?

HTH,
Brian

"Ron Weaver" wrote in message
...
Brian, I tried to fiqure out this filter thing. Can you help me with some
code and where to put it. Now the second part: If I put a command button
on
the first list and tie it to a requery macro, and put that macro into the
AfterUpdate event of the first list box, does that sound right?

"Brian Bastl" wrote:

Hi Ron,

I'd use a second listbox filtered on the customers id. Then you'd just
need
to issue a requery on the second listbox in the AfterUpdate event of the
first listbox.

Brian


"Ron Weaver" wrote in message
...
Hi, this is my first project and everyone has been very helpful to me.
Thank
you.
I have one more hurdle, if someone can help. I have a list box which
pulls
customer orders as the result of a date search. When I select a
customer
in
this list, I would like to have the option to push a command button
and
have
this list box, or a second list box display all customer orders with
that
name. I have created a query for this process, with the fields in it I
need,
but I don't know where to go from here.

Hi Ron,

if you in fact pasted the rowsource below, then I see a trailing space after
ListBox0. Beyond that, I can't tell without more info. If that doesn't solve
it, then perhaps you can post the SQL for ListBox0.

I do see one potential problem:
If [Customer Query].Expr1 aliases the customer id, then you still won't get
all of the customers with the same name, since an id is supposed to be
unique to each customer.

Brian


"Ron Weaver" wrote in message
...
Brian
Here is the SQL statement entered into the rowsource of ListBox2. You will
notice I am using Expr1 instead of CustomerID, this is because I want to
pull
all customers with the same name. When I try to save the following
statement
I get the message: "Characters found after the end of SQL statement". Do
you
see a problem with it?
SELECT [Customer Query].Expr1, [Customer Query].StartDate, [Customer
Query].CustPhone, [Customer Query].OrderID
FROM [Customer Query];
WHERE ((([Customer Query].[Expr1])=Forms!OrderDateForm!ListBox0 ))

"Brian Bastl" wrote:

Hi Ron,

Basically, if you have the customerid as the bound column in the
rowsource
for the first listbox, your rowsource for the second listbox would be
something like:

SELECT OrderID, OrderDate, Whatever else
FROM [Orders Table]
WHERE ((([Orders Table].[CustomerID]) = Forms!MyForm!Listbox1))

Then in the After Update event procedure of Listbox1, you'd requery
Listbox2:

Private Sub Listbox1_AfterUpdate()
Me.Listbox2.Requery
End Sub

There will be no need for macros or command buttons.

If this example doesn't help, can you copy and paste the rowsources for
both
listboxes as well as the form name?

HTH,
Brian

"Ron Weaver" wrote in message
...
Brian, I tried to fiqure out this filter thing. Can you help me with
some
code and where to put it. Now the second part: If I put a command
button
on
the first list and tie it to a requery macro, and put that macro into
the
AfterUpdate event of the first list box, does that sound right?

"Brian Bastl" wrote:

Hi Ron,

I'd use a second listbox filtered on the customers id. Then you'd
just
need
to issue a requery on the second listbox in the AfterUpdate event of
the
first listbox.

Brian


"Ron Weaver" wrote in message
...
Hi, this is my first project and everyone has been very helpful to
me.
Thank
you.
I have one more hurdle, if someone can help. I have a list box
which
pulls
customer orders as the result of a date search. When I select a
customer
in
this list, I would like to have the option to push a command
button
and
have
this list box, or a second list box display all customer orders
with
that
name. I have created a query for this process, with the fields in
it I
need,
but I don't know where to go from here.

The error is generated because you have a Semi-colon in the third line after
FROM [Customer Query]. Delete the Semi-colon and your query should be clear
as far as syntax goes. Access adds a semi-colon at the very end of queries,
although it is not required.

SELECT [Customer Query].Expr1, [Customer Query].StartDate, [Customer
Query].CustPhone, [Customer Query].OrderID
FROM [Customer Query];
WHERE ((([Customer Query].[Expr1])=Forms!OrderDateForm!ListBox0 ))

"Ron Weaver" wrote in message
...
Brian
Here is the SQL statement entered into the rowsource of ListBox2. You will
notice I am using Expr1 instead of CustomerID, this is because I want to
pull
all customers with the same name. When I try to save the following
statement
I get the message: "Characters found after the end of SQL statement". Do
you
see a problem with it?
SELECT [Customer Query].Expr1, [Customer Query].StartDate, [Customer
Query].CustPhone, [Customer Query].OrderID
FROM [Customer Query];
WHERE ((([Customer Query].[Expr1])=Forms!OrderDateForm!ListBox0 ))

"Brian Bastl" wrote:

Hi Ron,

Basically, if you have the customerid as the bound column in the
rowsource
for the first listbox, your rowsource for the second listbox would be
something like:

SELECT OrderID, OrderDate, Whatever else
FROM [Orders Table]
WHERE ((([Orders Table].[CustomerID]) = Forms!MyForm!Listbox1))

Then in the After Update event procedure of Listbox1, you'd requery
Listbox2:

Private Sub Listbox1_AfterUpdate()
Me.Listbox2.Requery
End Sub

There will be no need for macros or command buttons.

If this example doesn't help, can you copy and paste the rowsources for
both
listboxes as well as the form name?

HTH,
Brian

"Ron Weaver" wrote in message
...
Brian, I tried to fiqure out this filter thing. Can you help me with
some
code and where to put it. Now the second part: If I put a command
button
on
the first list and tie it to a requery macro, and put that macro into
the
AfterUpdate event of the first list box, does that sound right?

"Brian Bastl" wrote:

Hi Ron,

I'd use a second listbox filtered on the customers id. Then you'd
just
need
to issue a requery on the second listbox in the AfterUpdate event of
the
first listbox.

Brian


"Ron Weaver" wrote in message
...
Hi, this is my first project and everyone has been very helpful to
me.
Thank
you.
I have one more hurdle, if someone can help. I have a list box
which
pulls
customer orders as the result of a date search. When I select a
customer
in
this list, I would like to have the option to push a command button
and
have
this list box, or a second list box display all customer orders
with
that
name. I have created a query for this process, with the fields in
it I
need,
but I don't know where to go from here.

Thanks John
Per Brian's suggestion, I am changing my search field. I'm finding something
that simple causes other challenges. Soon as I get back to that point I will
do what you have suggested.

"John Spencer" wrote:

The error is generated because you have a Semi-colon in the third line after
FROM [Customer Query]. Delete the Semi-colon and your query should be clear
as far as syntax goes. Access adds a semi-colon at the very end of queries,
although it is not required.

SELECT [Customer Query].Expr1, [Customer Query].StartDate, [Customer
Query].CustPhone, [Customer Query].OrderID
FROM [Customer Query];
WHERE ((([Customer Query].[Expr1])=Forms!OrderDateForm!ListBox0 ))

"Ron Weaver" wrote in message
...
Brian
Here is the SQL statement entered into the rowsource of ListBox2. You will
notice I am using Expr1 instead of CustomerID, this is because I want to
pull
all customers with the same name. When I try to save the following
statement
I get the message: "Characters found after the end of SQL statement". Do
you
see a problem with it?
SELECT [Customer Query].Expr1, [Customer Query].StartDate, [Customer
Query].CustPhone, [Customer Query].OrderID
FROM [Customer Query];
WHERE ((([Customer Query].[Expr1])=Forms!OrderDateForm!ListBox0 ))

"Brian Bastl" wrote:

Hi Ron,

Basically, if you have the customerid as the bound column in the
rowsource
for the first listbox, your rowsource for the second listbox would be
something like:

SELECT OrderID, OrderDate, Whatever else
FROM [Orders Table]
WHERE ((([Orders Table].[CustomerID]) = Forms!MyForm!Listbox1))

Then in the After Update event procedure of Listbox1, you'd requery
Listbox2:

Private Sub Listbox1_AfterUpdate()
Me.Listbox2.Requery
End Sub

There will be no need for macros or command buttons.

If this example doesn't help, can you copy and paste the rowsources for
both
listboxes as well as the form name?

HTH,
Brian

"Ron Weaver" wrote in message
...
Brian, I tried to fiqure out this filter thing. Can you help me with
some
code and where to put it. Now the second part: If I put a command
button
on
the first list and tie it to a requery macro, and put that macro into
the
AfterUpdate event of the first list box, does that sound right?

"Brian Bastl" wrote:

Hi Ron,

I'd use a second listbox filtered on the customers id. Then you'd
just
need
to issue a requery on the second listbox in the AfterUpdate event of
the
first listbox.

Brian


"Ron Weaver" wrote in message
...
Hi, this is my first project and everyone has been very helpful to
me.
Thank
you.
I have one more hurdle, if someone can help. I have a list box
which
pulls
customer orders as the result of a date search. When I select a
customer
in
this list, I would like to have the option to push a command button
and
have
this list box, or a second list box display all customer orders
with
that
name. I have created a query for this process, with the fields in
it I
need,
but I don't know where to go from here.

Brian
Ok, I have changed the field from Expr1 to CostomerID. I got by the WHERE
statement problem (had a semi colon in there). This where I am now. I believe
everything is in there correctly, But when I select a customer in Listbox1 I
get the following: Compile Error, Method or Data Member Not Found. The
problem is in the after update statement in listbox1 (List59).In the
Me.List12.Requery the .List12 is highlighted. List12 is my Listbox2. As you
requested earlier I have included the rowsource for both listboxes and there
form names.
ListBox1(59) Form "OrderDateForm"
SELECT [Order Date Query].CustomerID, [Order Date Query].StartDate, [Order
Date Query].Expr1, [Order Date Query].CustPhone, [Order Date Query].OrderID
FROM [Order Date Query];
ListBox2 (List12) Form "CustomerOrders"
SELECT Customer.CustomerID, Customer.FirstName, Customer.Phone,
Orders.OrderID, Orders.StartDate
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID;
I hope this helps.
Thanks

"Brian Bastl" wrote:

Hi Ron,

if you in fact pasted the rowsource below, then I see a trailing space after
ListBox0. Beyond that, I can't tell without more info. If that doesn't solve
it, then perhaps you can post the SQL for ListBox0.

I do see one potential problem:
If [Customer Query].Expr1 aliases the customer id, then you still won't get
all of the customers with the same name, since an id is supposed to be
unique to each customer.

Brian


"Ron Weaver" wrote in message
...
Brian
Here is the SQL statement entered into the rowsource of ListBox2. You will
notice I am using Expr1 instead of CustomerID, this is because I want to
pull
all customers with the same name. When I try to save the following
statement
I get the message: "Characters found after the end of SQL statement". Do
you
see a problem with it?
SELECT [Customer Query].Expr1, [Customer Query].StartDate, [Customer
Query].CustPhone, [Customer Query].OrderID
FROM [Customer Query];
WHERE ((([Customer Query].[Expr1])=Forms!OrderDateForm!ListBox0 ))

"Brian Bastl" wrote:

Hi Ron,

Basically, if you have the customerid as the bound column in the
rowsource
for the first listbox, your rowsource for the second listbox would be
something like:

SELECT OrderID, OrderDate, Whatever else
FROM [Orders Table]
WHERE ((([Orders Table].[CustomerID]) = Forms!MyForm!Listbox1))

Then in the After Update event procedure of Listbox1, you'd requery
Listbox2:

Private Sub Listbox1_AfterUpdate()
Me.Listbox2.Requery
End Sub

There will be no need for macros or command buttons.

If this example doesn't help, can you copy and paste the rowsources for
both
listboxes as well as the form name?

HTH,
Brian

"Ron Weaver" wrote in message
...
Brian, I tried to fiqure out this filter thing. Can you help me with
some
code and where to put it. Now the second part: If I put a command
button
on
the first list and tie it to a requery macro, and put that macro into
the
AfterUpdate event of the first list box, does that sound right?

"Brian Bastl" wrote:

Hi Ron,

I'd use a second listbox filtered on the customers id. Then you'd
just
need
to issue a requery on the second listbox in the AfterUpdate event of
the
first listbox.

Brian


"Ron Weaver" wrote in message
...
Hi, this is my first project and everyone has been very helpful to
me.
Thank
you.
I have one more hurdle, if someone can help. I have a list box
which
pulls
customer orders as the result of a date search. When I select a
customer
in
this list, I would like to have the option to push a command
button
and
have
this list box, or a second list box display all customer orders
with
that
name. I have created a query for this process, with the fields in
it I
need,
but I don't know where to go from here.