Select name in one listbox and pull all matching names to second

R

Ron Weaver

I have a form with two listboxes. At this point I can select a name in
listbox 1 and it just pulls that name (CustomerID) to the listbox 2. I need
all matching names (Whether they are the same person or not), in listbox 2.
List59: (listbox 1)
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];
List111: (listbox 2)
SELECT [Customer Query].CustomerID, [Customer Query].Expr1 AS Expr2,
[Customer Query].StartDate, [Customer Query].Phone, [Customer Query].OrderID
FROM [Customer Query]
WHERE ((([Customer Query].CustomerID)=[Forms]![OrderDateForm].[list59]));
Thanks for everyones help.
 
B

Brian Bastl

Ron,

Since you want the second listbox to show 'all' matching names, then you
can't filter on CustomerID. You'll have to incorporate the Customer's name
into the rowsource for List59, so you'll have to modify the SQL (below) for
both listboxes to reflect the actual CustomerName and Customers Table.

There may be better ways to do it, but hopefully this will give a decent
jumping off point.

Brian

..List59: (listbox 1)
SELECT [tblCustomers].CustomerName, [Order Date Query].StartDate, [Order
Date Query].Expr1, [Order Date Query].CustPhone, [Order Date Query].OrderID
FROM [Order Date Query]
INNER JOIN tblCustomers ON
[Order Date Query].[CustomerID] = [tblCustomers].[CustomerID];


List111: (listbox 2)
SELECT [Customer Query].CustomerID, [Customer Query].[CustomerName],
[Customer Query].StartDate, [Customer Query].Phone, [Customer Query].OrderID
FROM [Customer Query]
WHERE ((([Customer Query].CustomerName) Like "*" &
[Forms]![OrderDateForm].[list59] & "*"));
 
S

Steve Schapel

Ron,

Does [Order Date Query] include the customer's name? If not, can it be
modified so that it does include the customer's name? And then for this
field to be included in the Row Source of List59? How about [Customer
Query], does that include the customer's name? If not, can it be
modified so that it does include the customer's name? If you can set
that up, then you will be able to modify the Where criteria for the
query you use for List111, so that it points to the customer name
instead of the CustomerID.
 
R

Ron Weaver

Steve, the customer's name is in "Expr1". What do I do next?

Steve Schapel said:
Ron,

Does [Order Date Query] include the customer's name? If not, can it be
modified so that it does include the customer's name? And then for this
field to be included in the Row Source of List59? How about [Customer
Query], does that include the customer's name? If not, can it be
modified so that it does include the customer's name? If you can set
that up, then you will be able to modify the Where criteria for the
query you use for List111, so that it points to the customer name
instead of the CustomerID.

--
Steve Schapel, Microsoft Access MVP


Ron said:
I have a form with two listboxes. At this point I can select a name in
listbox 1 and it just pulls that name (CustomerID) to the listbox 2. I need
all matching names (Whether they are the same person or not), in listbox 2.
List59: (listbox 1)
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];
List111: (listbox 2)
SELECT [Customer Query].CustomerID, [Customer Query].Expr1 AS Expr2,
[Customer Query].StartDate, [Customer Query].Phone, [Customer Query].OrderID
FROM [Customer Query]
WHERE ((([Customer Query].CustomerID)=[Forms]![OrderDateForm].[list59]));
Thanks for everyones help.
Click to expand...
Click to expand...
 
R

Ron Weaver

Brian
The customers name is in "Expr1" Please show me how the SQLs should read.
Thanks
 
B

Brian Bastl

Ron,

I just reversed the rowsource order for List59 so that Expr1 (customer name)
is the first column. Assuming that the Listbox does NOT have a control
source, set the bound column to 1. You'll need to requery List111 in the
After Update event of List59.

..List59: (listbox 1)
SELECT [Order Date Query].Expr1, [Order Date Query].StartDate, [Order
Date Query].CustomerID, [Order Date Query].CustPhone, [Order Date
Query].OrderID
FROM [Order Date Query];


List111: (listbox 2)
SELECT [Customer Query].CustomerID, [Customer Query].[CustomerName],
[Customer Query].StartDate, [Customer Query].Phone, [Customer Query].OrderID
FROM [Customer Query]
WHERE ((([Customer Query].CustomerName) Like "*" &
[Forms]![OrderDateForm].[list59] & "*"));

Brian



Ron Weaver said:
Brian
The customers name is in "Expr1" Please show me how the SQLs should read.
Thanks

Brian Bastl said:
Ron,

Since you want the second listbox to show 'all' matching names, then you
can't filter on CustomerID. You'll have to incorporate the Customer's name
into the rowsource for List59, so you'll have to modify the SQL (below) for
both listboxes to reflect the actual CustomerName and Customers Table.

There may be better ways to do it, but hopefully this will give a decent
jumping off point.

Brian

..List59: (listbox 1)
SELECT [tblCustomers].CustomerName, [Order Date Query].StartDate, [Order
Date Query].Expr1, [Order Date Query].CustPhone, [Order Date Query].OrderID
FROM [Order Date Query]
INNER JOIN tblCustomers ON
[Order Date Query].[CustomerID] = [tblCustomers].[CustomerID];


List111: (listbox 2)
SELECT [Customer Query].CustomerID, [Customer Query].[CustomerName],
[Customer Query].StartDate, [Customer Query].Phone, [Customer Query].OrderID
FROM [Customer Query]
WHERE ((([Customer Query].CustomerName) Like "*" &
[Forms]![OrderDateForm].[list59] & "*"));
Click to expand...
Click to expand...
 
R

Ron Weaver

Brian
It doesn't work. I left listbox 1 alone because it had all the fields. On
listbox 2, I changed CustomerName to Expr1 in two places. The listbox remains
blank. Before I put this code in it was pulling a single name to listbox 2
based on CustomerID. I have checked for miss spelling, etc. The way this form
works is: When the form is opened, the listboxes are blank. There are two
text fields. One for StartDate and one for EndDate. After these are filled, I
click on a control which refreshes the form and populates list 1. When I
select a record in listbox 1, that populates listbox 2. Both listboxes are
bound to one column.
I hope this helps.
Brian Bastl said:
Ron,

I just reversed the rowsource order for List59 so that Expr1 (customer name)
is the first column. Assuming that the Listbox does NOT have a control
source, set the bound column to 1. You'll need to requery List111 in the
After Update event of List59.

..List59: (listbox 1)
SELECT [Order Date Query].Expr1, [Order Date Query].StartDate, [Order
Date Query].CustomerID, [Order Date Query].CustPhone, [Order Date
Query].OrderID
FROM [Order Date Query];


List111: (listbox 2)
SELECT [Customer Query].CustomerID, [Customer Query].[CustomerName],
[Customer Query].StartDate, [Customer Query].Phone, [Customer Query].OrderID
FROM [Customer Query]
WHERE ((([Customer Query].CustomerName) Like "*" &
[Forms]![OrderDateForm].[list59] & "*"));

Brian



Ron Weaver said:
Brian
The customers name is in "Expr1" Please show me how the SQLs should read.
Thanks

Brian Bastl said:
Ron,

Since you want the second listbox to show 'all' matching names, then you
can't filter on CustomerID. You'll have to incorporate the Customer's name
into the rowsource for List59, so you'll have to modify the SQL (below) for
both listboxes to reflect the actual CustomerName and Customers Table.

There may be better ways to do it, but hopefully this will give a decent
jumping off point.

Brian

..List59: (listbox 1)
SELECT [tblCustomers].CustomerName, [Order Date Query].StartDate, [Order
Date Query].Expr1, [Order Date Query].CustPhone, [Order Date Query].OrderID
FROM [Order Date Query]
INNER JOIN tblCustomers ON
[Order Date Query].[CustomerID] = [tblCustomers].[CustomerID];


List111: (listbox 2)
SELECT [Customer Query].CustomerID, [Customer Query].[CustomerName],
[Customer Query].StartDate, [Customer Query].Phone, [Customer Query].OrderID
FROM [Customer Query]
WHERE ((([Customer Query].CustomerName) Like "*" &
[Forms]![OrderDateForm].[list59] & "*"));
Click to expand...
Click to expand...
Click to expand...
 
B

Brian Bastl

Ron,

Are you saying that your List59 is being filtered by date? I don't see a
where clause in its rowsource.

Tell you what: if you want me to take a quick look at your db, you'll need
to compact it, save a copy of it in A2000 file format or earlier, change the
file extension from .mdb to .xxx, and zip it up. You can send it to me at:

bastelER<at>alltelER<dot>net
'remove capitalized letters and change punctuation appropriately

Otherwise, I can't help without knowing alot more about your form, its
recordsource, the names of the pertinent tables and respective fieldnames
pertaining to the listboxes, etc.

Brian



Ron Weaver said:
Brian
It doesn't work. I left listbox 1 alone because it had all the fields. On
listbox 2, I changed CustomerName to Expr1 in two places. The listbox remains
blank. Before I put this code in it was pulling a single name to listbox 2
based on CustomerID. I have checked for miss spelling, etc. The way this form
works is: When the form is opened, the listboxes are blank. There are two
text fields. One for StartDate and one for EndDate. After these are filled, I
click on a control which refreshes the form and populates list 1. When I
select a record in listbox 1, that populates listbox 2. Both listboxes are
bound to one column.
I hope this helps.
Brian Bastl said:
Ron,

I just reversed the rowsource order for List59 so that Expr1 (customer name)
is the first column. Assuming that the Listbox does NOT have a control
source, set the bound column to 1. You'll need to requery List111 in the
After Update event of List59.

..List59: (listbox 1)
SELECT [Order Date Query].Expr1, [Order Date Query].StartDate, [Order
Date Query].CustomerID, [Order Date Query].CustPhone, [Order Date
Query].OrderID
FROM [Order Date Query];


List111: (listbox 2)
SELECT [Customer Query].CustomerID, [Customer Query].[CustomerName],
[Customer Query].StartDate, [Customer Query].Phone, [Customer Query].OrderID
FROM [Customer Query]
WHERE ((([Customer Query].CustomerName) Like "*" &
[Forms]![OrderDateForm].[list59] & "*"));

Brian



Ron Weaver said:
Brian
The customers name is in "Expr1" Please show me how the SQLs should read.
Thanks

:

Ron,

Since you want the second listbox to show 'all' matching names, then you
can't filter on CustomerID. You'll have to incorporate the
Click to expand...
Customer's
name
into the rowsource for List59, so you'll have to modify the SQL
Click to expand...
(below)
for
both listboxes to reflect the actual CustomerName and Customers Table.

There may be better ways to do it, but hopefully this will give a decent
jumping off point.

Brian

..List59: (listbox 1)
SELECT [tblCustomers].CustomerName, [Order Date Query].StartDate, [Order
Date Query].Expr1, [Order Date Query].CustPhone, [Order Date Query].OrderID
FROM [Order Date Query]
INNER JOIN tblCustomers ON
[Order Date Query].[CustomerID] = [tblCustomers].[CustomerID];


List111: (listbox 2)
SELECT [Customer Query].CustomerID, [Customer Query].[CustomerName],
[Customer Query].StartDate, [Customer Query].Phone, [Customer Query].OrderID
FROM [Customer Query]
WHERE ((([Customer Query].CustomerName) Like "*" &
[Forms]![OrderDateForm].[list59] & "*"));
Click to expand...
Click to expand...
Click to expand...
 
R

Ron Weaver

Thanks Brian
I may take you up on that. I just looked at the Customer Query. It is in the
SQL statement along with formatting for the phone. I don't know why it's not
in the row source in listbox 2. Should it be?
Thanks for hanging in there with me.

Brian Bastl said:
Ron,

Are you saying that your List59 is being filtered by date? I don't see a
where clause in its rowsource.

Tell you what: if you want me to take a quick look at your db, you'll need
to compact it, save a copy of it in A2000 file format or earlier, change the
file extension from .mdb to .xxx, and zip it up. You can send it to me at:

bastelER<at>alltelER<dot>net
'remove capitalized letters and change punctuation appropriately

Otherwise, I can't help without knowing alot more about your form, its
recordsource, the names of the pertinent tables and respective fieldnames
pertaining to the listboxes, etc.

Brian



Ron Weaver said:
Brian
It doesn't work. I left listbox 1 alone because it had all the fields. On
listbox 2, I changed CustomerName to Expr1 in two places. The listbox remains
blank. Before I put this code in it was pulling a single name to listbox 2
based on CustomerID. I have checked for miss spelling, etc. The way this form
works is: When the form is opened, the listboxes are blank. There are two
text fields. One for StartDate and one for EndDate. After these are filled, I
click on a control which refreshes the form and populates list 1. When I
select a record in listbox 1, that populates listbox 2. Both listboxes are
bound to one column.
I hope this helps.
Brian Bastl said:
Ron,

I just reversed the rowsource order for List59 so that Expr1 (customer name)
is the first column. Assuming that the Listbox does NOT have a control
source, set the bound column to 1. You'll need to requery List111 in the
After Update event of List59.

..List59: (listbox 1)
SELECT [Order Date Query].Expr1, [Order Date Query].StartDate, [Order
Date Query].CustomerID, [Order Date Query].CustPhone, [Order Date
Query].OrderID
FROM [Order Date Query];


List111: (listbox 2)
SELECT [Customer Query].CustomerID, [Customer Query].[CustomerName],
[Customer Query].StartDate, [Customer Query].Phone, [Customer Query].OrderID
FROM [Customer Query]
WHERE ((([Customer Query].CustomerName) Like "*" &
[Forms]![OrderDateForm].[list59] & "*"));

Brian



Brian
The customers name is in "Expr1" Please show me how the SQLs should read.
Thanks

:

Ron,

Since you want the second listbox to show 'all' matching names, then you
can't filter on CustomerID. You'll have to incorporate the Customer's
name
into the rowsource for List59, so you'll have to modify the SQL (below)
for
both listboxes to reflect the actual CustomerName and Customers Table.

There may be better ways to do it, but hopefully this will give a decent
jumping off point.

Brian

..List59: (listbox 1)
SELECT [tblCustomers].CustomerName, [Order Date Query].StartDate, [Order
Date Query].Expr1, [Order Date Query].CustPhone, [Order Date
Query].OrderID
FROM [Order Date Query]
INNER JOIN tblCustomers ON
[Order Date Query].[CustomerID] = [tblCustomers].[CustomerID];


List111: (listbox 2)
SELECT [Customer Query].CustomerID, [Customer Query].[CustomerName],
[Customer Query].StartDate, [Customer Query].Phone, [Customer
Query].OrderID
FROM [Customer Query]
WHERE ((([Customer Query].CustomerName) Like "*" &
[Forms]![OrderDateForm].[list59] & "*"));
Click to expand...
Click to expand...
Click to expand...
 
R

Ron Weaver

Sorrty
I got that backwards. I meant "Order Date Query" and "Listbox 1"

Ron Weaver said:
Thanks Brian
I may take you up on that. I just looked at the Customer Query. It is in the
SQL statement along with formatting for the phone. I don't know why it's not
in the row source in listbox 2. Should it be?
Thanks for hanging in there with me.

Brian Bastl said:
Ron,

Are you saying that your List59 is being filtered by date? I don't see a
where clause in its rowsource.

Tell you what: if you want me to take a quick look at your db, you'll need
to compact it, save a copy of it in A2000 file format or earlier, change the
file extension from .mdb to .xxx, and zip it up. You can send it to me at:

bastelER<at>alltelER<dot>net
'remove capitalized letters and change punctuation appropriately

Otherwise, I can't help without knowing alot more about your form, its
recordsource, the names of the pertinent tables and respective fieldnames
pertaining to the listboxes, etc.

Brian



Ron Weaver said:
Brian
It doesn't work. I left listbox 1 alone because it had all the fields. On
listbox 2, I changed CustomerName to Expr1 in two places. The listbox remains
blank. Before I put this code in it was pulling a single name to listbox 2
based on CustomerID. I have checked for miss spelling, etc. The way this form
works is: When the form is opened, the listboxes are blank. There are two
text fields. One for StartDate and one for EndDate. After these are filled, I
click on a control which refreshes the form and populates list 1. When I
select a record in listbox 1, that populates listbox 2. Both listboxes are
bound to one column.
I hope this helps.
:

Ron,

I just reversed the rowsource order for List59 so that Expr1 (customer name)
is the first column. Assuming that the Listbox does NOT have a control
source, set the bound column to 1. You'll need to requery List111 in the
After Update event of List59.

..List59: (listbox 1)
SELECT [Order Date Query].Expr1, [Order Date Query].StartDate, [Order
Date Query].CustomerID, [Order Date Query].CustPhone, [Order Date
Query].OrderID
FROM [Order Date Query];


List111: (listbox 2)
SELECT [Customer Query].CustomerID, [Customer Query].[CustomerName],
[Customer Query].StartDate, [Customer Query].Phone, [Customer Query].OrderID
FROM [Customer Query]
WHERE ((([Customer Query].CustomerName) Like "*" &
[Forms]![OrderDateForm].[list59] & "*"));

Brian



Brian
The customers name is in "Expr1" Please show me how the SQLs should read.
Thanks

:

Ron,

Since you want the second listbox to show 'all' matching names, then you
can't filter on CustomerID. You'll have to incorporate the Customer's
name
into the rowsource for List59, so you'll have to modify the SQL (below)
for
both listboxes to reflect the actual CustomerName and Customers Table.

There may be better ways to do it, but hopefully this will give a decent
jumping off point.

Brian

..List59: (listbox 1)
SELECT [tblCustomers].CustomerName, [Order Date Query].StartDate, [Order
Date Query].Expr1, [Order Date Query].CustPhone, [Order Date
Query].OrderID
FROM [Order Date Query]
INNER JOIN tblCustomers ON
[Order Date Query].[CustomerID] = [tblCustomers].[CustomerID];


List111: (listbox 2)
SELECT [Customer Query].CustomerID, [Customer Query].[CustomerName],
[Customer Query].StartDate, [Customer Query].Phone, [Customer
Query].OrderID
FROM [Customer Query]
WHERE ((([Customer Query].CustomerName) Like "*" &
[Forms]![OrderDateForm].[list59] & "*"));
Click to expand...
Click to expand...
Click to expand...
 
B

Brian Bastl

Ron,

Like I said before, I have no idea what your db looks like. What is the
purpose of the form on which the listboxes reside? Is the form bound or
unbound? Are the listboxes bound or unbound? If the form is bound, what is
its recordsource? What are the pertinent tables and fields relating to your
listboxes? The SQL for the first listbox doesn't explain much, although I'm
not sure why you need the telephone number in the first listbox, since it is
being displayed in the second listbox.

Brian


Ron Weaver said:
Sorrty
I got that backwards. I meant "Order Date Query" and "Listbox 1"

Ron Weaver said:
Thanks Brian
I may take you up on that. I just looked at the Customer Query. It is in the
SQL statement along with formatting for the phone. I don't know why it's not
in the row source in listbox 2. Should it be?
Thanks for hanging in there with me.

Brian Bastl said:
Ron,

Are you saying that your List59 is being filtered by date? I don't see a
where clause in its rowsource.

Tell you what: if you want me to take a quick look at your db, you'll need
to compact it, save a copy of it in A2000 file format or earlier, change the
file extension from .mdb to .xxx, and zip it up. You can send it to me at:

bastelER<at>alltelER<dot>net
'remove capitalized letters and change punctuation appropriately

Otherwise, I can't help without knowing alot more about your form, its
recordsource, the names of the pertinent tables and respective fieldnames
pertaining to the listboxes, etc.

Brian



Brian
It doesn't work. I left listbox 1 alone because it had all the fields. On
listbox 2, I changed CustomerName to Expr1 in two places. The listbox
remains
blank. Before I put this code in it was pulling a single name to listbox 2
based on CustomerID. I have checked for miss spelling, etc. The way this
form
works is: When the form is opened, the listboxes are blank. There are two
text fields. One for StartDate and one for EndDate. After these are
filled, I
click on a control which refreshes the form and populates list 1. When I
select a record in listbox 1, that populates listbox 2. Both listboxes are
bound to one column.
I hope this helps.
:

Ron,

I just reversed the rowsource order for List59 so that Expr1 (customer
name)
is the first column. Assuming that the Listbox does NOT have a control
source, set the bound column to 1. You'll need to requery List111 in the
After Update event of List59.

..List59: (listbox 1)
SELECT [Order Date Query].Expr1, [Order Date Query].StartDate, [Order
Date Query].CustomerID, [Order Date Query].CustPhone, [Order Date
Query].OrderID
FROM [Order Date Query];


List111: (listbox 2)
SELECT [Customer Query].CustomerID, [Customer Query].[CustomerName],
[Customer Query].StartDate, [Customer Query].Phone, [Customer
Query].OrderID
FROM [Customer Query]
WHERE ((([Customer Query].CustomerName) Like "*" &
[Forms]![OrderDateForm].[list59] & "*"));

Brian



Brian
The customers name is in "Expr1" Please show me how the SQLs should
read.
Thanks

:

Ron,

Since you want the second listbox to show 'all' matching names, then
you
can't filter on CustomerID. You'll have to incorporate the
Customer's
name
into the rowsource for List59, so you'll have to modify the SQL
(below)
for
both listboxes to reflect the actual CustomerName and Customers
Table.

There may be better ways to do it, but hopefully this will give a
decent
jumping off point.

Brian

..List59: (listbox 1)
SELECT [tblCustomers].CustomerName, [Order Date Query].StartDate,
[Order
Date Query].Expr1, [Order Date Query].CustPhone, [Order Date
Query].OrderID
FROM [Order Date Query]
INNER JOIN tblCustomers ON
[Order Date Query].[CustomerID] = [tblCustomers].[CustomerID];


List111: (listbox 2)
SELECT [Customer Query].CustomerID, [Customer Query].[CustomerName],
[Customer Query].StartDate, [Customer Query].Phone, [Customer
Query].OrderID
FROM [Customer Query]
WHERE ((([Customer Query].CustomerName) Like "*" &
[Forms]![OrderDateForm].[list59] & "*"));
Click to expand...
Click to expand...
Click to expand...
 
S

Steve Schapel

Ron,

Use this as the Row Source query for List111:

SELECT [Customer Query].CustomerID, [Customer Query].Expr1 AS Expr2,
[Customer Query].StartDate, [Customer Query].Phone, [Customer
Query].OrderID
FROM [Customer Query]
WHERE ((([Customer
Query].Expr1)=[Forms]![OrderDateForm]![list59].[Column](2)));
 
R

Ron Weaver

Hi Steve
I tried this , but I don't think you can use the column expression in a
query. I got the error:
Undefined Function'Forms!OrderDateForm!List59.column' in expression.
It is strange, but when I use: WHERE ((([Customer
Query].CustomerID)=[Forms]![OrderDateForm].[list59])), my form works
perfectly, unfortunately it just returns the one record. I sure appreciate
your thoughts.
Steve Schapel said:
Ron,

Use this as the Row Source query for List111:

SELECT [Customer Query].CustomerID, [Customer Query].Expr1 AS Expr2,
[Customer Query].StartDate, [Customer Query].Phone, [Customer
Query].OrderID
FROM [Customer Query]
WHERE ((([Customer
Query].Expr1)=[Forms]![OrderDateForm]![list59].[Column](2)));

--
Steve Schapel, Microsoft Access MVP


Ron said:
Steve, the customer's name is in "Expr1". What do I do next?
Click to expand...
Click to expand...
 
R

Ron Weaver

Brian
I have emailed you the database per your instructions. Let me know if for
some reason you don't get it.
Thanks for your help.
Brian Bastl said:
Ron,

Like I said before, I have no idea what your db looks like. What is the
purpose of the form on which the listboxes reside? Is the form bound or
unbound? Are the listboxes bound or unbound? If the form is bound, what is
its recordsource? What are the pertinent tables and fields relating to your
listboxes? The SQL for the first listbox doesn't explain much, although I'm
not sure why you need the telephone number in the first listbox, since it is
being displayed in the second listbox.

Brian


Ron Weaver said:
Sorrty
I got that backwards. I meant "Order Date Query" and "Listbox 1"

Ron Weaver said:
Thanks Brian
I may take you up on that. I just looked at the Customer Query. It is in the
SQL statement along with formatting for the phone. I don't know why it's not
in the row source in listbox 2. Should it be?
Thanks for hanging in there with me.

:

Ron,

Are you saying that your List59 is being filtered by date? I don't see a
where clause in its rowsource.

Tell you what: if you want me to take a quick look at your db, you'll need
to compact it, save a copy of it in A2000 file format or earlier, change the
file extension from .mdb to .xxx, and zip it up. You can send it to me at:

bastelER<at>alltelER<dot>net
'remove capitalized letters and change punctuation appropriately

Otherwise, I can't help without knowing alot more about your form, its
recordsource, the names of the pertinent tables and respective fieldnames
pertaining to the listboxes, etc.

Brian



Brian
It doesn't work. I left listbox 1 alone because it had all the fields. On
listbox 2, I changed CustomerName to Expr1 in two places. The listbox
remains
blank. Before I put this code in it was pulling a single name to listbox 2
based on CustomerID. I have checked for miss spelling, etc. The way this
form
works is: When the form is opened, the listboxes are blank. There are two
text fields. One for StartDate and one for EndDate. After these are
filled, I
click on a control which refreshes the form and populates list 1. When I
select a record in listbox 1, that populates listbox 2. Both listboxes are
bound to one column.
I hope this helps.
:

Ron,

I just reversed the rowsource order for List59 so that Expr1 (customer
name)
is the first column. Assuming that the Listbox does NOT have a control
source, set the bound column to 1. You'll need to requery List111 in the
After Update event of List59.

..List59: (listbox 1)
SELECT [Order Date Query].Expr1, [Order Date Query].StartDate, [Order
Date Query].CustomerID, [Order Date Query].CustPhone, [Order Date
Query].OrderID
FROM [Order Date Query];


List111: (listbox 2)
SELECT [Customer Query].CustomerID, [Customer Query].[CustomerName],
[Customer Query].StartDate, [Customer Query].Phone, [Customer
Query].OrderID
FROM [Customer Query]
WHERE ((([Customer Query].CustomerName) Like "*" &
[Forms]![OrderDateForm].[list59] & "*"));

Brian



Brian
The customers name is in "Expr1" Please show me how the SQLs should
read.
Thanks

:

Ron,

Since you want the second listbox to show 'all' matching names, then
you
can't filter on CustomerID. You'll have to incorporate the
Customer's
name
into the rowsource for List59, so you'll have to modify the SQL
(below)
for
both listboxes to reflect the actual CustomerName and Customers
Table.

There may be better ways to do it, but hopefully this will give a
decent
jumping off point.

Brian

..List59: (listbox 1)
SELECT [tblCustomers].CustomerName, [Order Date Query].StartDate,
[Order
Date Query].Expr1, [Order Date Query].CustPhone, [Order Date
Query].OrderID
FROM [Order Date Query]
INNER JOIN tblCustomers ON
[Order Date Query].[CustomerID] = [tblCustomers].[CustomerID];


List111: (listbox 2)
SELECT [Customer Query].CustomerID, [Customer Query].[CustomerName],
[Customer Query].StartDate, [Customer Query].Phone, [Customer
Query].OrderID
FROM [Customer Query]
WHERE ((([Customer Query].CustomerName) Like "*" &
[Forms]![OrderDateForm].[list59] & "*"));
Click to expand...
Click to expand...
Click to expand...
 
B

Brian Bastl

Ron,

nope, didn't get it.

I double-checked the address I gave you. It is correct.

bastel
<at>
alltel
<dot>
net

Brian
 
S

Steve Schapel

Ron,

I think you might be right... it's been a while since I've done anything
like this. I think what you need to do is put a hidden unbound textbox
on the form, and set its Control Source to...
=[list59].[Column](2)
Let's say you name this textbox CustomerRef. Then write your query like...
... WHERE (([Customer Query].Expr1)=[Forms]![OrderDateForm]![CustomerRef])
 
R

Ron Weaver

Steve
I tried that. The selected name in Listbox 1 shows up in the textbox
"CustomerRef", but not in ListBox 2. I still have the "After Update" property
in listbox 1 as:'Me.List70.Requery' pointing to listbox 2. I need the name
selected in listbox 1 to show all instances of that name in listbox 2.
Thanks

Steve Schapel said:
Ron,

I think you might be right... it's been a while since I've done anything
like this. I think what you need to do is put a hidden unbound textbox
on the form, and set its Control Source to...
=[list59].[Column](2)
Let's say you name this textbox CustomerRef. Then write your query like...
... WHERE (([Customer Query].Expr1)=[Forms]![OrderDateForm]![CustomerRef])

--
Steve Schapel, Microsoft Access MVP


Ron said:
Hi Steve
I tried this , but I don't think you can use the column expression in a
query. I got the error:
Undefined Function'Forms!OrderDateForm!List59.column' in expression.
It is strange, but when I use: WHERE ((([Customer
Query].CustomerID)=[Forms]![OrderDateForm].[list59])), my form works
perfectly, unfortunately it just returns the one record. I sure appreciate
your thoughts.
Click to expand...
Click to expand...
 
S

Steve Schapel

Ron,

What happened to List111? Is this now List70? What does it in fact
show in Listbox 2/111/70? Is the Expr1 field in the [Customer Query]
query the name of the customer, and matches the value of the customer
name in [list59].[Column](2)?
 
R

Ron Weaver

I changed out the listbox 2, trying to fix the problem. It is now list70.
Nothing else has changed. Expr1 is in both listboxes. Listbox 1 is still
list59. If you would like the listbox SQLs I will be happy to send them.

Steve Schapel said:
Ron,

What happened to List111? Is this now List70? What does it in fact
show in Listbox 2/111/70? Is the Expr1 field in the [Customer Query]
query the name of the customer, and matches the value of the customer
name in [list59].[Column](2)?

--
Steve Schapel, Microsoft Access MVP


Ron said:
Steve
I tried that. The selected name in Listbox 1 shows up in the textbox
"CustomerRef", but not in ListBox 2. I still have the "After Update" property
in listbox 1 as:'Me.List70.Requery' pointing to listbox 2. I need the name
selected in listbox 1 to show all instances of that name in listbox 2.
Thanks
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add <ALL> to combo box 9
Need ListBox1 to deliver results to ListBox2 15
Add Numbers To A Listbox 3
I will never get this program!!!! 4
Need selection in one list box to pull query info into another 13
Query to find the sum of fields... 2
Query to Pull Most Recent Call with a Callback Date Scheduled 10
Pass selection in multi-field listbox to query-based subform (repo 7

Top