How do I communciate between my list and button

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a form that has a list box and a button.

The list box is populated by a query that has two fields that have been
selected so that there are no duplicates in the list. The command button is
there to print a report.

Once they have picked one of the items from the list, they will click on the
button to have a report printed.

The item picked in the list must be used to create a query to select items
in the database that are associated with the item picked. The report will
print out the items selected.

I need some help in knowing how to pass the item picked to the query that
will gather the items and pass that on to the report. Naturally I want to
use the same report for each time a different item is picked.

Can someone please help me start in the right direction. I can develop the
queries and the report but I don't know how to setup communicating to the
button and sending the query to the report.

Thank you for your help.
 
I need some help in knowing how to pass the item picked to the query that
will gather the items and pass that on to the report. Naturally I want to
use the same report for each time a different item is picked.
Click to expand...

If your form is named frmMyForm, and the listbox is named lstPickItem,
use a Query with a criterion

=[Forms]![frmMyForm]![lstPickItem]

as the Recordsource for your report.

John W. Vinson[MVP]
 
John,

Where do I find the Recordsource in the Report properties?

I did use your command in the query and when I run the query, it asks me to
input the number. In my report I used that query for the bases of my report.
So if I can get the lstPickItem in the right place, it should work okay.

So please advise and thank you for your assistance.
Regards
--
SailFL


John Vinson said:
I need some help in knowing how to pass the item picked to the query that
will gather the items and pass that on to the report. Naturally I want to
use the same report for each time a different item is picked.
Click to expand...

If your form is named frmMyForm, and the listbox is named lstPickItem,
use a Query with a criterion

=[Forms]![frmMyForm]![lstPickItem]

as the Recordsource for your report.

John W. Vinson[MVP]
Click to expand...
 
John,

When I inserted the parameters you gave, though they were wrong, an input
box came up asking for input.

When I use the correct name for the fields, it comes up with an error
message stating that the expression is incorrect or to complicated to be
evalutated.

This is the SQL.

SELECT ClientRDAFile.Group, ClientRDAFile.[Distributor Name],
ClientRDAFile.[Store name], ClientRDAFile.Street, ClientRDAFile.City,
ClientRDAFile.State, ClientRDAFile.[Zip code], ClientRDAFile.Contact
FROM ClientRDAFile
WHERE (((ClientRDAFile.Group)=[Forms]![Form1]![lstPickItem]));

It appears that the group is not getting popluated.

Thanks
 
John,

When I inserted the parameters you gave, though they were wrong, an input
box came up asking for input.

When I use the correct name for the fields, it comes up with an error
message stating that the expression is incorrect or to complicated to be
evalutated.

This is the SQL.

SELECT ClientRDAFile.Group, ClientRDAFile.[Distributor Name],
ClientRDAFile.[Store name], ClientRDAFile.Street, ClientRDAFile.City,
ClientRDAFile.State, ClientRDAFile.[Zip code], ClientRDAFile.Contact
FROM ClientRDAFile
WHERE (((ClientRDAFile.Group)=[Forms]![Form1]![lstPickItem]));

It appears that the group is not getting popluated.

Thanks
Click to expand...

What's the datatype of the field Group? And what's the RowSource and
Bound Column of the listbox? Does the listbox in fact return the right
value?

Bear in mind that if your ClientRDAFile field Group is a Lookup field,
you'll need the listbox to return the numeric ID value, rather than
the looked-up text. This is one reason to dislike lookup fields (one
among many).

John W. Vinson[MVP]
 
John,
Thanks for the quick reply.

Datatype of the field group = Number

RowSource =
SELECT [Find duplicates for ClientRDAFile].[Group Field], [Find duplicates
for ClientRDAFile].[Distributor Name Field] FROM [Find duplicates for
ClientRDAFile] ORDER BY [Group Field];

BoundColumn = 2

How do I tell what the listbox is returning? Where do I look to determine
that?

How do I determine if the ClientRDAFile field Group is a Lookup field?

How do I lset the return for the listbox to be a numeric ID Value? I could
not find the return in Properties.

Thanks


--
SailFL


SailFL said:
John,

Where do I find the Recordsource in the Report properties?

I did use your command in the query and when I run the query, it asks me to
input the number. In my report I used that query for the bases of my report.
So if I can get the lstPickItem in the right place, it should work okay.

So please advise and thank you for your assistance.
Regards
--
SailFL


John Vinson said:
I need some help in knowing how to pass the item picked to the query that
will gather the items and pass that on to the report. Naturally I want to
use the same report for each time a different item is picked.
Click to expand...

If your form is named frmMyForm, and the listbox is named lstPickItem,
use a Query with a criterion

=[Forms]![frmMyForm]![lstPickItem]

as the Recordsource for your report.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
John

Thanks for the quick reply..

Datatype of the field group = Number
RowSource =
SELECT [Find duplicates for ClientRDAFile].[Group Field], [Find duplicates
for ClientRDAFile].[Distributor Name Field] FROM [Find duplicates for
ClientRDAFile] ORDER BY [Group Field];

BoundColumn = 2

How do I tell what the listbox is returning? Where do I look to determine
that?

How do I determine if the ClientRDAFile field Group is a Lookup field?

Thank you for your help.
--
SailFL


John Vinson said:
John,

When I inserted the parameters you gave, though they were wrong, an input
box came up asking for input.

When I use the correct name for the fields, it comes up with an error
message stating that the expression is incorrect or to complicated to be
evalutated.

This is the SQL.

SELECT ClientRDAFile.Group, ClientRDAFile.[Distributor Name],
ClientRDAFile.[Store name], ClientRDAFile.Street, ClientRDAFile.City,
ClientRDAFile.State, ClientRDAFile.[Zip code], ClientRDAFile.Contact
FROM ClientRDAFile
WHERE (((ClientRDAFile.Group)=[Forms]![Form1]![lstPickItem]));

It appears that the group is not getting popluated.

Thanks
Click to expand...

What's the datatype of the field Group? And what's the RowSource and
Bound Column of the listbox? Does the listbox in fact return the right
value?

Bear in mind that if your ClientRDAFile field Group is a Lookup field,
you'll need the listbox to return the numeric ID value, rather than
the looked-up text. This is one reason to dislike lookup fields (one
among many).

John W. Vinson[MVP]
Click to expand...
 
John

Thanks for the quick reply..

Datatype of the field group = Number
RowSource =
SELECT [Find duplicates for ClientRDAFile].[Group Field], [Find duplicates
for ClientRDAFile].[Distributor Name Field] FROM [Find duplicates for
ClientRDAFile] ORDER BY [Group Field];

BoundColumn = 2
Click to expand...

Ok... it's returning the [Distributor Name Field] since that's the
second column in the query. My guess is that it will work if you
change BoundColumn to 1, if the [Group Field] is in fact the field
that you want to use to search Group.
How do I tell what the listbox is returning? Where do I look to determine
that?
Click to expand...

Simplest is to put a "breakpoint" in the listbox's afterupdate code
(or some other appropriate form event) - click in the left margin of
the VBA editor next to an executable statement. When you run the code,
it will stop at that point. You can then type

?[listboxname]

in the Immediate window to see what value Access thinks the listbox is
returning.
How do I determine if the ClientRDAFile field Group is a Lookup field?
Click to expand...

Open the table in design view; select the field; and click the Lookup
tab in the properties window on the lower left. If it has "Combo Box"
selected, it's a lookup field.

But I suspect ou just have the wrong bound column and there is no
lookup field...

John W. Vinson[MVP]
 
John,

You were right it was the bound column is what needed to be corrected.

You have been very helpful and I appreciate your assistance. Is there a
couple MS courses that would have helped me?

Regards,
--
SailFL


John Vinson said:
John

Thanks for the quick reply..

Datatype of the field group = Number
RowSource =
SELECT [Find duplicates for ClientRDAFile].[Group Field], [Find duplicates
for ClientRDAFile].[Distributor Name Field] FROM [Find duplicates for
ClientRDAFile] ORDER BY [Group Field];

BoundColumn = 2
Click to expand...

Ok... it's returning the [Distributor Name Field] since that's the
second column in the query. My guess is that it will work if you
change BoundColumn to 1, if the [Group Field] is in fact the field
that you want to use to search Group.
How do I tell what the listbox is returning? Where do I look to determine
that?
Click to expand...

Simplest is to put a "breakpoint" in the listbox's afterupdate code
(or some other appropriate form event) - click in the left margin of
the VBA editor next to an executable statement. When you run the code,
it will stop at that point. You can then type

?[listboxname]

in the Immediate window to see what value Access thinks the listbox is
returning.
How do I determine if the ClientRDAFile field Group is a Lookup field?
Click to expand...

Open the table in design view; select the field; and click the Lookup
tab in the properties window on the lower left. If it has "Combo Box"
selected, it's a lookup field.

But I suspect ou just have the wrong bound column and there is no
lookup field...

John W. Vinson[MVP]
Click to expand...
 
Back
Top