Newbie to Combo Box and Reports

This is a great example to obtain information between two dates. What I want
to do is to retrieve records containing the same Purchase Order number. The
same purchase order number can cover several records as each record describes
item(s) received from a given manufacturer. The reason for wanting a report
of this kind is to review the items a given purchase order number covered.

Another report I'd like to generate is to review all items ordered from a
given manufacturer. This would be useful in planning for a future order,
i.e. you would have some idea of what you were able to order from a given
manufacturer previously.

Another report that would be useful would be to generate a report showing
all the records for a given item. This would also be useful in planning for
a future order, i.e. you would have some idea if more than one manufacturer
can supply a given item.

I had no trouble creating a combo box to show all the purchase order numbers
from the table. Where I'm stuck is knowing how to take the selected purchase
order number and using this value as a key to generate a report containing
all records containing this key.

I'm searching both the Access 2007 help site as well as other tutorials
located using a Google search, but so far haven't been able to find a way to
do what I would like to do, as described in the foregoing.

Thanks for your reply. If you have any other suggestions these will be
appreciated and gratefully received!
"Tom Wickerath" wrote:

> Hi JA,
>
> You can try this tutorial:
>
> http://www.accessmvp.com/TWickerath/downloads/customdialogbox.zip
>
>
> Tom Wickerath
> Microsoft Access MVP
> http://www.accessmvp.com/TWickerath/
> http://www.access.qbuilt.com/html/expert_contributors.html
> __________________________________________
>
> "JAJansenJr" wrote:
>
> > I have a combo box set to display all purchase order numbers in a table. The
> > combo box gets the purchase order numbers from the table and successfully
> > displays these in the drop down box. What I would like to have is a report
> > which will display all the records containing the purchase order number
> > selected by the combo box, with the option to print the report. I'm totally
> > new to Access 2007 and have not found a way to do this. This is surprising
> > because I would imagine displaying a report based on a particular purchase
> > order number (or other criteria) should be a pretty basic kind of report.
> > Any and all help in finding a way to do this sincerely appreciated.

Restricting a report's results on the basis of a single criterion can be done
either by using a parameter in the report's RecordSource query, e.g. by
putting the following in the 'criteria' row of the purchase order number
column in query design view:

Forms!YourForm!cboPurchaseOrderNumber

where YourForm is the name of the form and cboPurchaseOrderNumber the name
of the combo box. Its then just a case of opening the report with a button
on the form.

Alternatively the report can be designed to return all records by default
and restricted by means of the WhereCondition argument of the OpenReport
method, in which case the code in the Click event procedure of the button on
the form to open the report in print preview would go like this:

Dim strCriteria As String

strCriteria = "[PurchaseOrderNumber] = " & Me.cboPurchaseOrderNumber

DoCmd.OpenReport "YourReportNameGoesHere", _
View:=acViewPreview, _
WhereCondition:=strCriteria

This assumes that the PurchaseOrderNumber column is a number data type. If
its text data type you'd use:

strCriteria = "[PurchaseOrderNumber] = """ & Me.cboPurchaseOrderNumber & """"

One thing which does puzzle me slightly is that you say "each record
describes item(s)", suggesting that a row could contain more than one type of
item. A row should only contain one type of item, so if a manufacture
supplies more than one type of item these would appear in separate rows.
This can be illustrated with a simple query on the sample Northwind database
which comes with Access. If you run this you'll see that there are multiple
rows returned per customer, one for each product supplied to them:

SELECT Customers.CustomerID, Customers.CompanyName,
Products.ProductName, COUNT(*) AS NumberSupplied
FROM Products INNER JOIN ((Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID)
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID)
ON Products.ProductID = [Order Details].ProductID
GROUP BY Customers.CustomerID, Customers.CompanyName,
Products.ProductName;

Ken Sheridan
Stafford, England

"JAJansenJr" wrote:

> This is a great example to obtain information between two dates. What I want
> to do is to retrieve records containing the same Purchase Order number. The
> same purchase order number can cover several records as each record describes
> item(s) received from a given manufacturer. The reason for wanting a report
> of this kind is to review the items a given purchase order number covered.
>
> Another report I'd like to generate is to review all items ordered from a
> given manufacturer. This would be useful in planning for a future order,
> i.e. you would have some idea of what you were able to order from a given
> manufacturer previously.
>
> Another report that would be useful would be to generate a report showing
> all the records for a given item. This would also be useful in planning for
> a future order, i.e. you would have some idea if more than one manufacturer
> can supply a given item.
>
> I had no trouble creating a combo box to show all the purchase order numbers
> from the table. Where I'm stuck is knowing how to take the selected purchase
> order number and using this value as a key to generate a report containing
> all records containing this key.
>
> I'm searching both the Access 2007 help site as well as other tutorials
> located using a Google search, but so far haven't been able to find a way to
> do what I would like to do, as described in the foregoing.
>
> Thanks for your reply. If you have any other suggestions these will be
> appreciated and gratefully received!
> "Tom Wickerath" wrote:
>
> > Hi JA,
> >
> > You can try this tutorial:
> >
> > http://www.accessmvp.com/TWickerath/downloads/customdialogbox.zip
> >
> >
> > Tom Wickerath
> > Microsoft Access MVP
> > http://www.accessmvp.com/TWickerath/
> > http://www.access.qbuilt.com/html/expert_contributors.html
> > __________________________________________
> >
> > "JAJansenJr" wrote:
> >
> > > I have a combo box set to display all purchase order numbers in a table. The
> > > combo box gets the purchase order numbers from the table and successfully
> > > displays these in the drop down box. What I would like to have is a report
> > > which will display all the records containing the purchase order number
> > > selected by the combo box, with the option to print the report. I'm totally
> > > new to Access 2007 and have not found a way to do this. This is surprising
> > > because I would imagine displaying a report based on a particular purchase
> > > order number (or other criteria) should be a pretty basic kind of report.
> > > Any and all help in finding a way to do this sincerely appreciated.

This is very helpful. I am so new to Access 2007 I am not sure of the
specific steps I need to take. I would like to try the first suggestion
(putting the following in the "criteria" row etc). Could you elaborate on
the exact steps I need to take to do this. Thanks very much!

"Ken Sheridan" wrote:

> Restricting a report's results on the basis of a single criterion can be done
> either by using a parameter in the report's RecordSource query, e.g. by
> putting the following in the 'criteria' row of the purchase order number
> column in query design view:
>
> Forms!YourForm!cboPurchaseOrderNumber
>
> where YourForm is the name of the form and cboPurchaseOrderNumber the name
> of the combo box. Its then just a case of opening the report with a button
> on the form.
>
> Alternatively the report can be designed to return all records by default
> and restricted by means of the WhereCondition argument of the OpenReport
> method, in which case the code in the Click event procedure of the button on
> the form to open the report in print preview would go like this:
>
> Dim strCriteria As String
>
> strCriteria = "[PurchaseOrderNumber] = " & Me.cboPurchaseOrderNumber
>
> DoCmd.OpenReport "YourReportNameGoesHere", _
> View:=acViewPreview, _
> WhereCondition:=strCriteria
>
> This assumes that the PurchaseOrderNumber column is a number data type. If
> its text data type you'd use:
>
> strCriteria = "[PurchaseOrderNumber] = """ & Me.cboPurchaseOrderNumber & """"
>
> One thing which does puzzle me slightly is that you say "each record
> describes item(s)", suggesting that a row could contain more than one type of
> item. A row should only contain one type of item, so if a manufacture
> supplies more than one type of item these would appear in separate rows.
> This can be illustrated with a simple query on the sample Northwind database
> which comes with Access. If you run this you'll see that there are multiple
> rows returned per customer, one for each product supplied to them:
>
> SELECT Customers.CustomerID, Customers.CompanyName,
> Products.ProductName, COUNT(*) AS NumberSupplied
> FROM Products INNER JOIN ((Customers INNER JOIN Orders
> ON Customers.CustomerID = Orders.CustomerID)
> INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID)
> ON Products.ProductID = [Order Details].ProductID
> GROUP BY Customers.CustomerID, Customers.CompanyName,
> Products.ProductName;
>
> Ken Sheridan
> Stafford, England
>
> "JAJansenJr" wrote:
>
> > This is a great example to obtain information between two dates. What I want
> > to do is to retrieve records containing the same Purchase Order number. The
> > same purchase order number can cover several records as each record describes
> > item(s) received from a given manufacturer. The reason for wanting a report
> > of this kind is to review the items a given purchase order number covered.
> >
> > Another report I'd like to generate is to review all items ordered from a
> > given manufacturer. This would be useful in planning for a future order,
> > i.e. you would have some idea of what you were able to order from a given
> > manufacturer previously.
> >
> > Another report that would be useful would be to generate a report showing
> > all the records for a given item. This would also be useful in planning for
> > a future order, i.e. you would have some idea if more than one manufacturer
> > can supply a given item.
> >
> > I had no trouble creating a combo box to show all the purchase order numbers
> > from the table. Where I'm stuck is knowing how to take the selected purchase
> > order number and using this value as a key to generate a report containing
> > all records containing this key.
> >
> > I'm searching both the Access 2007 help site as well as other tutorials
> > located using a Google search, but so far haven't been able to find a way to
> > do what I would like to do, as described in the foregoing.
> >
> > Thanks for your reply. If you have any other suggestions these will be
> > appreciated and gratefully received!
> > "Tom Wickerath" wrote:
> >
> > > Hi JA,
> > >
> > > You can try this tutorial:
> > >
> > > http://www.accessmvp.com/TWickerath/downloads/customdialogbox.zip
> > >
> > >
> > > Tom Wickerath
> > > Microsoft Access MVP
> > > http://www.accessmvp.com/TWickerath/
> > > http://www.access.qbuilt.com/html/expert_contributors.html
> > > __________________________________________
> > >
> > > "JAJansenJr" wrote:
> > >
> > > > I have a combo box set to display all purchase order numbers in a table. The
> > > > combo box gets the purchase order numbers from the table and successfully
> > > > displays these in the drop down box. What I would like to have is a report
> > > > which will display all the records containing the purchase order number
> > > > selected by the combo box, with the option to print the report. I'm totally
> > > > new to Access 2007 and have not found a way to do this. This is surprising
> > > > because I would imagine displaying a report based on a particular purchase
> > > > order number (or other criteria) should be a pretty basic kind of report.
> > > > Any and all help in finding a way to do this sincerely appreciated.
>

1. Firstly create the unbound form with the combo box to select the purchase
order number. Save it under a suitable name such as frmPurchaseOrderDlg.

2. Next you need to create the query on which the report will be based.
Open the query designer and add the necessary table or tables, joining the
tables on the relevant columns if there is more than one table.

3. Next add the relevant columns (fields) from the tables by dragging them
into the columns of the design grid.

4. In the 'criteria' row of the purchase order number column enter the
reference to the combo box on the form you created in step 1 like so:

Forms!frmPurchaseOrderDlg!cboPurchaseOrderNumber

using whatever names you've decided to call the form and combo box if
different from my example. The initial 'Forms' stays the same, though,
whatever the name of the form or control; it’s a reference to the Forms
collection. Save the query.

5. You can now test the query by opening the form you created in step 1 and
selecting a purchase order number in the combo box. Then, with the form
still open, open the query. All being well it should return the rows for the
selected purchase order number only.

6. If the query is working properly create a report based on it, which you
can do using the report wizard. Save the report.

7. Go back to the form you created in step 1 and, in form design view, add
two command buttons, using the button wizard, one to print the report the
other to preview it. Save the form.

To open the report you simply open the form, select a purchase order number
and click one of the buttons.

You can if you wish have multiple controls on the form and reference them in
different columns of the query, so you can restrict the report's results on
the basis of as few or as many of the controls on the form in combination as
you wish. Lets take it one step at a time, however, and get it working with
just one control first.

Ken Sheridan
Stafford, England

"JAJansenJr" wrote:

> This is very helpful. I am so new to Access 2007 I am not sure of the
> specific steps I need to take. I would like to try the first suggestion
> (putting the following in the "criteria" row etc). Could you elaborate on
> the exact steps I need to take to do this. Thanks very much!
>
> "Ken Sheridan" wrote:
>
> > Restricting a report's results on the basis of a single criterion can be done
> > either by using a parameter in the report's RecordSource query, e.g. by
> > putting the following in the 'criteria' row of the purchase order number
> > column in query design view:
> >
> > Forms!YourForm!cboPurchaseOrderNumber
> >
> > where YourForm is the name of the form and cboPurchaseOrderNumber the name
> > of the combo box. Its then just a case of opening the report with a button
> > on the form.
> >
> > Alternatively the report can be designed to return all records by default
> > and restricted by means of the WhereCondition argument of the OpenReport
> > method, in which case the code in the Click event procedure of the button on
> > the form to open the report in print preview would go like this:
> >
> > Dim strCriteria As String
> >
> > strCriteria = "[PurchaseOrderNumber] = " & Me.cboPurchaseOrderNumber
> >
> > DoCmd.OpenReport "YourReportNameGoesHere", _
> > View:=acViewPreview, _
> > WhereCondition:=strCriteria
> >
> > This assumes that the PurchaseOrderNumber column is a number data type. If
> > its text data type you'd use:
> >
> > strCriteria = "[PurchaseOrderNumber] = """ & Me.cboPurchaseOrderNumber & """"
> >
> > One thing which does puzzle me slightly is that you say "each record
> > describes item(s)", suggesting that a row could contain more than one type of
> > item. A row should only contain one type of item, so if a manufacture
> > supplies more than one type of item these would appear in separate rows.
> > This can be illustrated with a simple query on the sample Northwind database
> > which comes with Access. If you run this you'll see that there are multiple
> > rows returned per customer, one for each product supplied to them:
> >
> > SELECT Customers.CustomerID, Customers.CompanyName,
> > Products.ProductName, COUNT(*) AS NumberSupplied
> > FROM Products INNER JOIN ((Customers INNER JOIN Orders
> > ON Customers.CustomerID = Orders.CustomerID)
> > INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID)
> > ON Products.ProductID = [Order Details].ProductID
> > GROUP BY Customers.CustomerID, Customers.CompanyName,
> > Products.ProductName;
> >
> > Ken Sheridan
> > Stafford, England
> >
> > "JAJansenJr" wrote:
> >
> > > This is a great example to obtain information between two dates. What I want
> > > to do is to retrieve records containing the same Purchase Order number. The
> > > same purchase order number can cover several records as each record describes
> > > item(s) received from a given manufacturer. The reason for wanting a report
> > > of this kind is to review the items a given purchase order number covered.
> > >
> > > Another report I'd like to generate is to review all items ordered from a
> > > given manufacturer. This would be useful in planning for a future order,
> > > i.e. you would have some idea of what you were able to order from a given
> > > manufacturer previously.
> > >
> > > Another report that would be useful would be to generate a report showing
> > > all the records for a given item. This would also be useful in planning for
> > > a future order, i.e. you would have some idea if more than one manufacturer
> > > can supply a given item.
> > >
> > > I had no trouble creating a combo box to show all the purchase order numbers
> > > from the table. Where I'm stuck is knowing how to take the selected purchase
> > > order number and using this value as a key to generate a report containing
> > > all records containing this key.
> > >
> > > I'm searching both the Access 2007 help site as well as other tutorials
> > > located using a Google search, but so far haven't been able to find a way to
> > > do what I would like to do, as described in the foregoing.
> > >
> > > Thanks for your reply. If you have any other suggestions these will be
> > > appreciated and gratefully received!
> > > "Tom Wickerath" wrote:
> > >
> > > > Hi JA,
> > > >
> > > > You can try this tutorial:
> > > >
> > > > http://www.accessmvp.com/TWickerath/downloads/customdialogbox.zip
> > > >
> > > >
> > > > Tom Wickerath
> > > > Microsoft Access MVP
> > > > http://www.accessmvp.com/TWickerath/
> > > > http://www.access.qbuilt.com/html/expert_contributors.html
> > > > __________________________________________
> > > >
> > > > "JAJansenJr" wrote:
> > > >
> > > > > I have a combo box set to display all purchase order numbers in a table. The
> > > > > combo box gets the purchase order numbers from the table and successfully
> > > > > displays these in the drop down box. What I would like to have is a report
> > > > > which will display all the records containing the purchase order number
> > > > > selected by the combo box, with the option to print the report. I'm totally
> > > > > new to Access 2007 and have not found a way to do this. This is surprising
> > > > > because I would imagine displaying a report based on a particular purchase
> > > > > order number (or other criteria) should be a pretty basic kind of report.
> > > > > Any and all help in finding a way to do this sincerely appreciated.
> >

Hi JA,

> This is a great example to obtain information between two dates. What I want
> to do is to retrieve records containing the same Purchase Order number.

You will use the same logic shown in the article, except that your situation
will be easier, since you are not querying between a range of values. In your
case, the combo box to select a City (ie. select one parameter) would be more
analogous to selecting (in an unbound combo box) or entering directly (in an
unbound text box) the purchase order number.

> Another report I'd like to generate is to review all items ordered from a
> given manufacturer.

Use the same idea. For a list of manufacturers, I'd probably use a combo box
with a RowSource based on a lookup table. That way, people could select the
manufacturer from a list, instead of having to spell the manufacturer's name
correctly in a text box (or fragment of a manufacturer's name, if you
concatenated the wildcard search character (*).

> Another report that would be useful would be to generate a report showing
> all the records for a given item.

This is very do-able, using the material I have supplied as a guide. It's
not intended to be an exact fit for any one purpose. Try creating the sample
from scratch, so that you can understand how it works. Then try applying the
same logic to your particular situation.

Here are links to my QBF samples. if you are okay with using some Visual
Basic for Applications (VBA) code:

http://www.accessmvp.com/TWickerath/downloads/elements.zip

http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip


The first one shown above, Elements.zip, is about as easy as they get. Here,
I have shown just a single multi-select list box control, to allow one to
select one or more elements from the periodic table in the list. Hey, I'm a
chemist, so this example was natural for me! Anyway, you can make a
continuous selection by holding down the Shift key, or a discontinuous
selection by using the Control key.

The Chap08QBF sample is the basis for all of my QBF forms. This sample came
from Scott Barker's book titled "Access 2000 Power Programming". It includes
the original form/subform, as well as my modifications to these two forms.

If you'd like to learn how these work, I have a Word document and sample
Access database available here:

http://www.seattleaccess.org/downloads.htm

Look for:
Query By Form - Multi Select
Tom Wickerath, February 12, 2008

If you are new to VBA code, then your first stop should be to check out
these two documents:

http://www.seattleaccess.org/downloads.htm
DAO - Back To Basics Compilation/Demo
by Tom Wickerath, Jan/Feb 2007

and

Access Basics by Crystal
http://www.accessmvp.com/Strive4Peace/Index.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

"JAJansenJr" wrote:

> This is a great example to obtain information between two dates. What I want
> to do is to retrieve records containing the same Purchase Order number. The
> same purchase order number can cover several records as each record describes
> item(s) received from a given manufacturer. The reason for wanting a report
> of this kind is to review the items a given purchase order number covered.
>
> Another report I'd like to generate is to review all items ordered from a
> given manufacturer. This would be useful in planning for a future order,
> i.e. you would have some idea of what you were able to order from a given
> manufacturer previously.
>
> Another report that would be useful would be to generate a report showing
> all the records for a given item. This would also be useful in planning for
> a future order, i.e. you would have some idea if more than one manufacturer
> can supply a given item.
>
> I had no trouble creating a combo box to show all the purchase order numbers
> from the table. Where I'm stuck is knowing how to take the selected purchase
> order number and using this value as a key to generate a report containing
> all records containing this key.
>
> I'm searching both the Access 2007 help site as well as other tutorials
> located using a Google search, but so far haven't been able to find a way to
> do what I would like to do, as described in the foregoing.
>
> Thanks for your reply. If you have any other suggestions these will be
> appreciated and gratefully received!

"Tom Wickerath" wrote:
Thanks to both Tom and Ken. I'll be trying your ideas out.

I worked with a Microsoft support engineer who did not provide a complete
solution but from what he suggested I then decided to use the Access help
system and searched for "where condition" and
then "Open Report Macro Action". This latter help page provided an example
that matched my need and described the exact "Where Condition" clause I
needed for the Open Report Macro. Everything now works perfectly.

> Hi JA,
>
> > This is a great example to obtain information between two dates. What I want
> > to do is to retrieve records containing the same Purchase Order number.
>
> You will use the same logic shown in the article, except that your situation
> will be easier, since you are not querying between a range of values. In your
> case, the combo box to select a City (ie. select one parameter) would be more
> analogous to selecting (in an unbound combo box) or entering directly (in an
> unbound text box) the purchase order number.
>
> > Another report I'd like to generate is to review all items ordered from a
> > given manufacturer.
>
> Use the same idea. For a list of manufacturers, I'd probably use a combo box
> with a RowSource based on a lookup table. That way, people could select the
> manufacturer from a list, instead of having to spell the manufacturer's name
> correctly in a text box (or fragment of a manufacturer's name, if you
> concatenated the wildcard search character (*).
>
> > Another report that would be useful would be to generate a report showing
> > all the records for a given item.
>
> This is very do-able, using the material I have supplied as a guide. It's
> not intended to be an exact fit for any one purpose. Try creating the sample
> from scratch, so that you can understand how it works. Then try applying the
> same logic to your particular situation.
>
> Here are links to my QBF samples. if you are okay with using some Visual
> Basic for Applications (VBA) code:
>
> http://www.accessmvp.com/TWickerath/downloads/elements.zip
>
> http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip
>
>
> The first one shown above, Elements.zip, is about as easy as they get. Here,
> I have shown just a single multi-select list box control, to allow one to
> select one or more elements from the periodic table in the list. Hey, I'm a
> chemist, so this example was natural for me! Anyway, you can make a
> continuous selection by holding down the Shift key, or a discontinuous
> selection by using the Control key.
>
> The Chap08QBF sample is the basis for all of my QBF forms. This sample came
> from Scott Barker's book titled "Access 2000 Power Programming". It includes
> the original form/subform, as well as my modifications to these two forms.
>
> If you'd like to learn how these work, I have a Word document and sample
> Access database available here:
>
> http://www.seattleaccess.org/downloads.htm
>
> Look for:
> Query By Form - Multi Select
> Tom Wickerath, February 12, 2008
>
> If you are new to VBA code, then your first stop should be to check out
> these two documents:
>
> http://www.seattleaccess.org/downloads.htm
> DAO - Back To Basics Compilation/Demo
> by Tom Wickerath, Jan/Feb 2007
>
> and
>
> Access Basics by Crystal
> http://www.accessmvp.com/Strive4Peace/Index.htm
>
>
> Tom Wickerath
> Microsoft Access MVP
> http://www.accessmvp.com/TWickerath/
> http://www.access.qbuilt.com/html/expert_contributors.html
> __________________________________________
>
> "JAJansenJr" wrote:
>
> > This is a great example to obtain information between two dates. What I want
> > to do is to retrieve records containing the same Purchase Order number. The
> > same purchase order number can cover several records as each record describes
> > item(s) received from a given manufacturer. The reason for wanting a report
> > of this kind is to review the items a given purchase order number covered.
> >
> > Another report I'd like to generate is to review all items ordered from a
> > given manufacturer. This would be useful in planning for a future order,
> > i.e. you would have some idea of what you were able to order from a given
> > manufacturer previously.
> >
> > Another report that would be useful would be to generate a report showing
> > all the records for a given item. This would also be useful in planning for
> > a future order, i.e. you would have some idea if more than one manufacturer
> > can supply a given item.
> >
> > I had no trouble creating a combo box to show all the purchase order numbers
> > from the table. Where I'm stuck is knowing how to take the selected purchase
> > order number and using this value as a key to generate a report containing
> > all records containing this key.
> >
> > I'm searching both the Access 2007 help site as well as other tutorials
> > located using a Google search, but so far haven't been able to find a way to
> > do what I would like to do, as described in the foregoing.
> >
> > Thanks for your reply. If you have any other suggestions these will be
> > appreciated and gratefully received!