Subform causing duplicate records?

  • Thread starter Thread starter Cindy
  • Start date Start date
C

Cindy

I am trying to do a Purchase Order database. I need a subform in the PO Data
Entry Form since there are usually multiple items in the PO. I have a
command button to Print PO but I end up with a report for each item in the
subform.

This is the SQL for the Print PO.

SELECT DISTINCT [Purchase Order Table].[PO Number], [Purchase Order
Table].[Purchase Order Date], [Purchase Order Table].[Requested By],
[Purchase Order Table].[Approved By], [Purchase Items List Query].Quantity,
[Purchase Items List Query].Description, [Purchase Items List Query].[Unit
Price], [Purchase Items List Query].Per, [Purchase Order Table].[Estimated
Cost], [Vendor Table].Company, [Vendor Table].[Street Address], [Vendor
Table].[Street Address 2], [Vendor Table].City, [Vendor Table].State, [Vendor
Table].[Zip Code], [Vendor Table].[Phone Number], [Vendor Table].[Fax Number]
FROM ([Vendor Table] INNER JOIN [Purchase Order Table] ON ([Vendor
Table].Company = [Purchase Order Table].Vendor) AND ([Vendor Table].Company =
[Purchase Order Table].Vendor)) INNER JOIN [Purchase Items List Query] ON
[Purchase Order Table].[PO Number] = [Purchase Items List Query].[PO Number];

Can anyone help me figure out what in the heck I have done wrong here? I'm
pulling my hair out!

Thanks!
Cindy
 
Your query has no WHERE clause, so you've probably tried to restrict the
report by including a WHERE clause in a lower level query such as [Purchase
Items List Query].

You may find it easier to remove the criteria from that query (perhaps even
remove the query from this SQL statement), and instead use the
WhereCondition of OpenReport to restrict it to the PO in the form. Here's an
example of how that's done:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Cindy said:
I am trying to do a Purchase Order database. I need a subform in the PO
Data
Entry Form since there are usually multiple items in the PO. I have a
command button to Print PO but I end up with a report for each item in the
subform.

This is the SQL for the Print PO.
Click to expand...
SELECT DISTINCT [Purchase Order Table].[PO Number],
[Purchase Order Table].[Purchase Order Date],
[Purchase Order Table].[Requested By],
[Purchase Order Table].[Approved By],
[Purchase Items List Query].Quantity,
[Purchase Items List Query].Description,
[Purchase Items List Query].[Unit Price],
[Purchase Items List Query].Per,
[Purchase Order Table].[Estimated Cost],
[Vendor Table].Company,
[Vendor Table].[Street Address],
[Vendor Table].[Street Address 2],
[Vendor Table].City,
[Vendor Table].State,
[Vendor Table].[Zip Code],
[Vendor Table].[Phone Number],
[Vendor Table].[Fax Number]
FROM ([Vendor Table] INNER JOIN [Purchase Order Table]
ON ([Vendor Table].Company = [Purchase Order Table].Vendor)
AND ([Vendor Table].Company = [Purchase Order Table].Vendor))
INNER JOIN [Purchase Items List Query]
ON [Purchase Order Table].[PO Number] = [Purchase Items List Query].[PO
Number];
 
Allen,

I've done some testing, changed to VBA code in your example (I use it in
another database with perfect results) and got the same result.

What I just noticed is that each item in the subform is showing as a
separate record in the form count that is on the bottom of a form. You know,
the 1 of 8, etc. I have 5 test PO's in there and it is showing 8 records.
It is not counting or showing PO #1 which has nothing in the subform but is
counting and showing PO#2 twice which has 2 items in the subform and PO#4
three times which has 3 items in the subform.

I think my problem is not the Print PO but something I've got set up wrong
in the form/subform. Since this is my first stab at subforms I'm not even
sure where to look. Any suggests?

Thanks!
Cindy

Allen Browne said:
Your query has no WHERE clause, so you've probably tried to restrict the
report by including a WHERE clause in a lower level query such as [Purchase
Items List Query].

You may find it easier to remove the criteria from that query (perhaps even
remove the query from this SQL statement), and instead use the
WhereCondition of OpenReport to restrict it to the PO in the form. Here's an
example of how that's done:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Cindy said:
I am trying to do a Purchase Order database. I need a subform in the PO
Data
Entry Form since there are usually multiple items in the PO. I have a
command button to Print PO but I end up with a report for each item in the
subform.

This is the SQL for the Print PO.
Click to expand...
SELECT DISTINCT [Purchase Order Table].[PO Number],
[Purchase Order Table].[Purchase Order Date],
[Purchase Order Table].[Requested By],
[Purchase Order Table].[Approved By],
[Purchase Items List Query].Quantity,
[Purchase Items List Query].Description,
[Purchase Items List Query].[Unit Price],
[Purchase Items List Query].Per,
[Purchase Order Table].[Estimated Cost],
[Vendor Table].Company,
[Vendor Table].[Street Address],
[Vendor Table].[Street Address 2],
[Vendor Table].City,
[Vendor Table].State,
[Vendor Table].[Zip Code],
[Vendor Table].[Phone Number],
[Vendor Table].[Fax Number]
FROM ([Vendor Table] INNER JOIN [Purchase Order Table]
ON ([Vendor Table].Company = [Purchase Order Table].Vendor)
AND ([Vendor Table].Company = [Purchase Order Table].Vendor))
INNER JOIN [Purchase Items List Query]
ON [Purchase Order Table].[PO Number] = [Purchase Items List Query].[PO
Number];
Can anyone help me figure out what in the heck I have done wrong here?
I'm
pulling my hair out!

Thanks!
Cindy
Click to expand...

.
Click to expand...
 
Does the subform use a query as its Record Source?

If so, open the query itself, and see if the records are duplicated there as
well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Cindy said:
Allen,

I've done some testing, changed to VBA code in your example (I use it in
another database with perfect results) and got the same result.

What I just noticed is that each item in the subform is showing as a
separate record in the form count that is on the bottom of a form. You
know,
the 1 of 8, etc. I have 5 test PO's in there and it is showing 8 records.
It is not counting or showing PO #1 which has nothing in the subform but
is
counting and showing PO#2 twice which has 2 items in the subform and PO#4
three times which has 3 items in the subform.

I think my problem is not the Print PO but something I've got set up wrong
in the form/subform. Since this is my first stab at subforms I'm not even
sure where to look. Any suggests?

Thanks!
Cindy

Allen Browne said:
Your query has no WHERE clause, so you've probably tried to restrict the
report by including a WHERE clause in a lower level query such as
[Purchase
Items List Query].

You may find it easier to remove the criteria from that query (perhaps
even
remove the query from this SQL statement), and instead use the
WhereCondition of OpenReport to restrict it to the PO in the form. Here's
an
example of how that's done:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Cindy said:
I am trying to do a Purchase Order database. I need a subform in the
PO
Data
Entry Form since there are usually multiple items in the PO. I have a
command button to Print PO but I end up with a report for each item in
the
subform.

This is the SQL for the Print PO.
Click to expand...
SELECT DISTINCT [Purchase Order Table].[PO Number],
[Purchase Order Table].[Purchase Order Date],
[Purchase Order Table].[Requested By],
[Purchase Order Table].[Approved By],
[Purchase Items List Query].Quantity,
[Purchase Items List Query].Description,
[Purchase Items List Query].[Unit Price],
[Purchase Items List Query].Per,
[Purchase Order Table].[Estimated Cost],
[Vendor Table].Company,
[Vendor Table].[Street Address],
[Vendor Table].[Street Address 2],
[Vendor Table].City,
[Vendor Table].State,
[Vendor Table].[Zip Code],
[Vendor Table].[Phone Number],
[Vendor Table].[Fax Number]
FROM ([Vendor Table] INNER JOIN [Purchase Order Table]
ON ([Vendor Table].Company = [Purchase Order Table].Vendor)
AND ([Vendor Table].Company = [Purchase Order Table].Vendor))
INNER JOIN [Purchase Items List Query]
ON [Purchase Order Table].[PO Number] = [Purchase Items List Query].[PO
Number];
Can anyone help me figure out what in the heck I have done wrong here?
I'm
pulling my hair out!

Thanks!
Cindy
Click to expand...

.
Click to expand...
Click to expand...
 
Allen,

Well, once again you saved the day. The problem was I had the subform
fields in the Print PO Query as well as the subform query. Removed them from
the Print PO Query and it worked perfect.

Thanks for your help.

Cindy

Allen Browne said:
Does the subform use a query as its Record Source?

If so, open the query itself, and see if the records are duplicated there as
well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Cindy said:
Allen,

I've done some testing, changed to VBA code in your example (I use it in
another database with perfect results) and got the same result.

What I just noticed is that each item in the subform is showing as a
separate record in the form count that is on the bottom of a form. You
know,
the 1 of 8, etc. I have 5 test PO's in there and it is showing 8 records.
It is not counting or showing PO #1 which has nothing in the subform but
is
counting and showing PO#2 twice which has 2 items in the subform and PO#4
three times which has 3 items in the subform.

I think my problem is not the Print PO but something I've got set up wrong
in the form/subform. Since this is my first stab at subforms I'm not even
sure where to look. Any suggests?

Thanks!
Cindy

Allen Browne said:
Your query has no WHERE clause, so you've probably tried to restrict the
report by including a WHERE clause in a lower level query such as
[Purchase
Items List Query].

You may find it easier to remove the criteria from that query (perhaps
even
remove the query from this SQL statement), and instead use the
WhereCondition of OpenReport to restrict it to the PO in the form. Here's
an
example of how that's done:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I am trying to do a Purchase Order database. I need a subform in the
PO
Data
Entry Form since there are usually multiple items in the PO. I have a
command button to Print PO but I end up with a report for each item in
the
subform.

This is the SQL for the Print PO.

SELECT DISTINCT [Purchase Order Table].[PO Number],
[Purchase Order Table].[Purchase Order Date],
[Purchase Order Table].[Requested By],
[Purchase Order Table].[Approved By],
[Purchase Items List Query].Quantity,
[Purchase Items List Query].Description,
[Purchase Items List Query].[Unit Price],
[Purchase Items List Query].Per,
[Purchase Order Table].[Estimated Cost],
[Vendor Table].Company,
[Vendor Table].[Street Address],
[Vendor Table].[Street Address 2],
[Vendor Table].City,
[Vendor Table].State,
[Vendor Table].[Zip Code],
[Vendor Table].[Phone Number],
[Vendor Table].[Fax Number]
FROM ([Vendor Table] INNER JOIN [Purchase Order Table]
ON ([Vendor Table].Company = [Purchase Order Table].Vendor)
AND ([Vendor Table].Company = [Purchase Order Table].Vendor))
INNER JOIN [Purchase Items List Query]
ON [Purchase Order Table].[PO Number] = [Purchase Items List Query].[PO
Number];

Can anyone help me figure out what in the heck I have done wrong here?
I'm
pulling my hair out!

Thanks!
Cindy

.
Click to expand...
Click to expand...
.
Click to expand...
 
Cindy,
I am trying to develop a PO database in Access 2007. I am new to VBA and SQL, although I have some experience in Access. Everything I have ever found on the net is for tracking purchases of a customer, but I am trying to track purchases I make from my vendors.

It seems you have written that type of database.

Can you send me the table layout and table fields of your database? What I am having the most trouble with is getting the subform to work within the main form and it seems you have solved that. I can never seem to get the results I expect to get.

Any help you can send in regards to creating and printing a multi-item order would be greatly appreciated.

Thanks in advance
Bry



Cindy wrote:

Allen,Well, once again you saved the day.
27-Jan-10

Allen

Well, once again you saved the day. The problem was I had the subfor
fields in the Print PO Query as well as the subform query. Removed them fro
the Print PO Query and it worked perfect

Thanks for your help

Cind

:

Previous Posts In This Thread:

Subform causing duplicate records?
I am trying to do a Purchase Order database. I need a subform in the PO Dat
Entry Form since there are usually multiple items in the PO. I have
command button to Print PO but I end up with a report for each item in th
subform

This is the SQL for the Print PO

SELECT DISTINCT [Purchase Order Table].[PO Number], [Purchase Orde
Table].[Purchase Order Date], [Purchase Order Table].[Requested By]
[Purchase Order Table].[Approved By], [Purchase Items List Query].Quantity
[Purchase Items List Query].Description, [Purchase Items List Query].[Uni
Price], [Purchase Items List Query].Per, [Purchase Order Table].[Estimate
Cost], [Vendor Table].Company, [Vendor Table].[Street Address], [Vendo
Table].[Street Address 2], [Vendor Table].City, [Vendor Table].State, [Vendo
Table].[Zip Code], [Vendor Table].[Phone Number], [Vendor Table].[Fax Number
FROM ([Vendor Table] INNER JOIN [Purchase Order Table] ON ([Vendo
Table].Company = [Purchase Order Table].Vendor) AND ([Vendor Table].Company
[Purchase Order Table].Vendor)) INNER JOIN [Purchase Items List Query] O
[Purchase Order Table].[PO Number] = [Purchase Items List Query].[PO Number]

Can anyone help me figure out what in the heck I have done wrong here? I a
pulling my hair out

Thanks
Cindy

Your query has no WHERE clause, so you have probably tried to restrict
Your query has no WHERE clause, so you have probably tried to restrict th
report by including a WHERE clause in a lower level query such as [Purchas
Items List Query]

You may find it easier to remove the criteria from that query (perhaps eve
remove the query from this SQL statement), and instead use th
WhereCondition of OpenReport to restrict it to the PO in the form. Here is a
example of how that is done
Print the record in the for
at
http://allenbrowne.com/casu-15.htm

-
Allen Browne - Microsoft MVP. Perth, Western Australi
Tips for Access users - http://allenbrowne.com/tips.htm
Reply to group, rather than allenbrowne at mvps dot org

SELECT DISTINCT [Purchase Order Table].[PO Number]
[Purchase Order Table].[Purchase Order Date]
[Purchase Order Table].[Requested By]
[Purchase Order Table].[Approved By]
[Purchase Items List Query].Quantity
[Purchase Items List Query].Description
[Purchase Items List Query].[Unit Price]
[Purchase Items List Query].Per
[Purchase Order Table].[Estimated Cost]
[Vendor Table].Company
[Vendor Table].[Street Address]
[Vendor Table].[Street Address 2]
[Vendor Table].City
[Vendor Table].State
[Vendor Table].[Zip Code]
[Vendor Table].[Phone Number]
[Vendor Table].[Fax Number
FROM ([Vendor Table] INNER JOIN [Purchase Order Table
ON ([Vendor Table].Company = [Purchase Order Table].Vendor
AND ([Vendor Table].Company = [Purchase Order Table].Vendor)
INNER JOIN [Purchase Items List Query
ON [Purchase Order Table].[PO Number] = [Purchase Items List Query].[P
Number];

Allen,I have done some testing, changed to VBA code in your example (I use it
Allen,

I have done some testing, changed to VBA code in your example (I use it in
another database with perfect results) and got the same result.

What I just noticed is that each item in the subform is showing as a
separate record in the form count that is on the bottom of a form. You know,
the 1 of 8, etc. I have 5 test PO's in there and it is showing 8 records.
It is not counting or showing PO #1 which has nothing in the subform but is
counting and showing PO#2 twice which has 2 items in the subform and PO#4
three times which has 3 items in the subform.

I think my problem is not the Print PO but something I have got set up wrong
in the form/subform. Since this is my first stab at subforms I am not even
sure where to look. Any suggests?

Thanks!
Cindy

:

Does the subform use a query as its Record Source?
Does the subform use a query as its Record Source?

If so, open the query itself, and see if the records are duplicated there as
well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,Well, once again you saved the day.
Allen,

Well, once again you saved the day. The problem was I had the subform
fields in the Print PO Query as well as the subform query. Removed them from
the Print PO Query and it worked perfect.

Thanks for your help.

Cindy

:


Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint Video Library Template Available For Download
http://www.eggheadcafe.com/tutorial...05-5995f2b0ab63/sharepoint-video-library.aspx
 
Cindy,
I am trying to develop a PO database in Access 2007. I am new to VBA and
SQL, although I have some experience in Access. Everything I have ever
found on the net is for tracking purchases of a customer, but I am trying
to track purchases I make from my vendors.

It seems you have written that type of database.

Can you send me the table layout and table fields of your database? What I
am having the most trouble with is getting the subform to work within the
main form and it seems you have solved that. I can never seem to get the
results I expect to get.

Any help you can send in regards to creating and printing a multi-item
order would be greatly appreciated.

Thanks in advance
Bry



Cindy wrote:

Allen,Well, once again you saved the day.
27-Jan-10

Allen,

Well, once again you saved the day. The problem was I had the subform
fields in the Print PO Query as well as the subform query. Removed them
from
the Print PO Query and it worked perfect.

Thanks for your help.

Cindy

:

Previous Posts In This Thread:

Subform causing duplicate records?
I am trying to do a Purchase Order database. I need a subform in the PO
Data
Entry Form since there are usually multiple items in the PO. I have a
command button to Print PO but I end up with a report for each item in the
subform.

This is the SQL for the Print PO.

SELECT DISTINCT [Purchase Order Table].[PO Number], [Purchase Order
Table].[Purchase Order Date], [Purchase Order Table].[Requested By],
[Purchase Order Table].[Approved By], [Purchase Items List
Query].Quantity,
[Purchase Items List Query].Description, [Purchase Items List Query].[Unit
Price], [Purchase Items List Query].Per, [Purchase Order Table].[Estimated
Cost], [Vendor Table].Company, [Vendor Table].[Street Address], [Vendor
Table].[Street Address 2], [Vendor Table].City, [Vendor Table].State,
[Vendor
Table].[Zip Code], [Vendor Table].[Phone Number], [Vendor Table].[Fax
Number]
FROM ([Vendor Table] INNER JOIN [Purchase Order Table] ON ([Vendor
Table].Company = [Purchase Order Table].Vendor) AND ([Vendor
Table].Company =
[Purchase Order Table].Vendor)) INNER JOIN [Purchase Items List Query] ON
[Purchase Order Table].[PO Number] = [Purchase Items List Query].[PO
Number];

Can anyone help me figure out what in the heck I have done wrong here? I
am
pulling my hair out!

Thanks!
Cindy

Your query has no WHERE clause, so you have probably tried to restrict
Your query has no WHERE clause, so you have probably tried to restrict the
report by including a WHERE clause in a lower level query such as
[Purchase
Items List Query].

You may find it easier to remove the criteria from that query (perhaps
even
remove the query from this SQL statement), and instead use the
WhereCondition of OpenReport to restrict it to the PO in the form. Here is
an
example of how that is done:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


SELECT DISTINCT [Purchase Order Table].[PO Number],
[Purchase Order Table].[Purchase Order Date],
[Purchase Order Table].[Requested By],
[Purchase Order Table].[Approved By],
[Purchase Items List Query].Quantity,
[Purchase Items List Query].Description,
[Purchase Items List Query].[Unit Price],
[Purchase Items List Query].Per,
[Purchase Order Table].[Estimated Cost],
[Vendor Table].Company,
[Vendor Table].[Street Address],
[Vendor Table].[Street Address 2],
[Vendor Table].City,
[Vendor Table].State,
[Vendor Table].[Zip Code],
[Vendor Table].[Phone Number],
[Vendor Table].[Fax Number]
FROM ([Vendor Table] INNER JOIN [Purchase Order Table]
ON ([Vendor Table].Company = [Purchase Order Table].Vendor)
AND ([Vendor Table].Company = [Purchase Order Table].Vendor))
INNER JOIN [Purchase Items List Query]
ON [Purchase Order Table].[PO Number] = [Purchase Items List Query].[PO
Number];

Allen,I have done some testing, changed to VBA code in your example (I use
it
Allen,

I have done some testing, changed to VBA code in your example (I use it in
another database with perfect results) and got the same result.

What I just noticed is that each item in the subform is showing as a
separate record in the form count that is on the bottom of a form. You
know,
the 1 of 8, etc. I have 5 test PO's in there and it is showing 8 records.
It is not counting or showing PO #1 which has nothing in the subform but
is
counting and showing PO#2 twice which has 2 items in the subform and PO#4
three times which has 3 items in the subform.

I think my problem is not the Print PO but something I have got set up
wrong
in the form/subform. Since this is my first stab at subforms I am not
even
sure where to look. Any suggests?

Thanks!
Cindy

:

Does the subform use a query as its Record Source?
Does the subform use a query as its Record Source?

If so, open the query itself, and see if the records are duplicated there
as
well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,Well, once again you saved the day.
Allen,

Well, once again you saved the day. The problem was I had the subform
fields in the Print PO Query as well as the subform query. Removed them
from
the Print PO Query and it worked perfect.

Thanks for your help.

Cindy

:


Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint Video Library Template Available For Download
http://www.eggheadcafe.com/tutorial...05-5995f2b0ab63/sharepoint-video-library.aspx
Click to expand...
 
rghjjjjjkkk

"Bry Anhuit" a écrit dans le message de groupe de discussion :
(e-mail address removed)...
Cindy,
I am trying to develop a PO database in Access 2007. I am new to VBA and
SQL, although I have some experience in Access. Everything I have ever
found on the net is for tracking purchases of a customer, but I am trying
to track purchases I make from my vendors.

It seems you have written that type of database.

Can you send me the table layout and table fields of your database? What I
am having the most trouble with is getting the subform to work within the
main form and it seems you have solved that. I can never seem to get the
results I expect to get.

Any help you can send in regards to creating and printing a multi-item
order would be greatly appreciated.

Thanks in advance
Bry



Cindy wrote:

Allen,Well, once again you saved the day.
27-Jan-10

Allen,

Well, once again you saved the day. The problem was I had the subform
fields in the Print PO Query as well as the subform query. Removed them
from
the Print PO Query and it worked perfect.

Thanks for your help.

Cindy

:

Previous Posts In This Thread:

Subform causing duplicate records?
I am trying to do a Purchase Order database. I need a subform in the PO
Data
Entry Form since there are usually multiple items in the PO. I have a
command button to Print PO but I end up with a report for each item in the
subform.

This is the SQL for the Print PO.

SELECT DISTINCT [Purchase Order Table].[PO Number], [Purchase Order
Table].[Purchase Order Date], [Purchase Order Table].[Requested By],
[Purchase Order Table].[Approved By], [Purchase Items List
Query].Quantity,
[Purchase Items List Query].Description, [Purchase Items List Query].[Unit
Price], [Purchase Items List Query].Per, [Purchase Order Table].[Estimated
Cost], [Vendor Table].Company, [Vendor Table].[Street Address], [Vendor
Table].[Street Address 2], [Vendor Table].City, [Vendor Table].State,
[Vendor
Table].[Zip Code], [Vendor Table].[Phone Number], [Vendor Table].[Fax
Number]
FROM ([Vendor Table] INNER JOIN [Purchase Order Table] ON ([Vendor
Table].Company = [Purchase Order Table].Vendor) AND ([Vendor
Table].Company =
[Purchase Order Table].Vendor)) INNER JOIN [Purchase Items List Query] ON
[Purchase Order Table].[PO Number] = [Purchase Items List Query].[PO
Number];

Can anyone help me figure out what in the heck I have done wrong here? I
am
pulling my hair out!

Thanks!
Cindy

Your query has no WHERE clause, so you have probably tried to restrict
Your query has no WHERE clause, so you have probably tried to restrict the
report by including a WHERE clause in a lower level query such as
[Purchase
Items List Query].

You may find it easier to remove the criteria from that query (perhaps
even
remove the query from this SQL statement), and instead use the
WhereCondition of OpenReport to restrict it to the PO in the form. Here is
an
example of how that is done:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


SELECT DISTINCT [Purchase Order Table].[PO Number],
[Purchase Order Table].[Purchase Order Date],
[Purchase Order Table].[Requested By],
[Purchase Order Table].[Approved By],
[Purchase Items List Query].Quantity,
[Purchase Items List Query].Description,
[Purchase Items List Query].[Unit Price],
[Purchase Items List Query].Per,
[Purchase Order Table].[Estimated Cost],
[Vendor Table].Company,
[Vendor Table].[Street Address],
[Vendor Table].[Street Address 2],
[Vendor Table].City,
[Vendor Table].State,
[Vendor Table].[Zip Code],
[Vendor Table].[Phone Number],
[Vendor Table].[Fax Number]
FROM ([Vendor Table] INNER JOIN [Purchase Order Table]
ON ([Vendor Table].Company = [Purchase Order Table].Vendor)
AND ([Vendor Table].Company = [Purchase Order Table].Vendor))
INNER JOIN [Purchase Items List Query]
ON [Purchase Order Table].[PO Number] = [Purchase Items List Query].[PO
Number];

Allen,I have done some testing, changed to VBA code in your example (I use
it
Allen,

I have done some testing, changed to VBA code in your example (I use it in
another database with perfect results) and got the same result.

What I just noticed is that each item in the subform is showing as a
separate record in the form count that is on the bottom of a form. You
know,
the 1 of 8, etc. I have 5 test PO's in there and it is showing 8 records.
It is not counting or showing PO #1 which has nothing in the subform but
is
counting and showing PO#2 twice which has 2 items in the subform and PO#4
three times which has 3 items in the subform.

I think my problem is not the Print PO but something I have got set up
wrong
in the form/subform. Since this is my first stab at subforms I am not
even
sure where to look. Any suggests?

Thanks!
Cindy

:

Does the subform use a query as its Record Source?
Does the subform use a query as its Record Source?

If so, open the query itself, and see if the records are duplicated there
as
well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,Well, once again you saved the day.
Allen,

Well, once again you saved the day. The problem was I had the subform
fields in the Print PO Query as well as the subform query. Removed them
from
the Print PO Query and it worked perfect.

Thanks for your help.

Cindy

:


Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint Video Library Template Available For Download
http://www.eggheadcafe.com/tutorial...05-5995f2b0ab63/sharepoint-video-library.aspx
Click to expand...
 
Back
Top