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

[Guest]

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

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

 

[Guest]

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)

 

[Guest]

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]

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

 

[Guest]

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

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

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]

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

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

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

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?

:

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


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.

 

[John Spencer]

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

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

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

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?

:

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


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.

 

[Guest]

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.

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

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

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

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?

:

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


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.

 

[Guest]

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

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

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

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

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?

:

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


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

Hi Ron,

Haven't been ignoring you. Just got home.
Are you saying that List59 is on one form and List12 is on another?

Brian

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

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

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

:

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

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?

:

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


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.

 

[Guest]

Hi
That's correct. List59 is #1 and List12 is #2.
I'm leaving now for a little while myself.
Thanks

Hi Ron,

Haven't been ignoring you. Just got home.
Are you saying that List59 is on one form and List12 is on another?

Brian

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

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


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

:

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

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?

:

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


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.

 

[Guest]

Brian
I just took a look at that SQL statement I sent you for List12 (Listbox#2)
That is not right. Here is the rowsource fo that:
SELECT [Customer Query].CustomerID, [Customer Query].Expr1, [Customer
Query].Phone, [Customer Query].OrderID, [Customer Query].StartDate
FROM [Customer Query]
WHERE ((([Customer Query].CustomerID)=[Forms]![OrderDateForm]![Listbox59]));
I don't know where that came from. SORRY

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

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?

 

[Guest]

Just wanted to say Thanks for your response. I am going to try the combo.