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
"Allen Browne" wrote:
Previous Posts In This Thread:
On Monday, January 25, 2010 9:53 PM
Cindy wrote:
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
On Monday, January 25, 2010 11:59 PM
Allen Browne wrote:
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
Tips for Access users -
http://allenbrowne.com/tips.html
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];
On Tuesday, January 26, 2010 1:28 PM
Cindy wrote:
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
"Allen Browne" wrote:
On Tuesday, January 26, 2010 8:44 PM
Allen Browne wrote:
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
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
On Wednesday, January 27, 2010 8:59 PM
Cindy wrote:
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
"Allen Browne" wrote:
Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint Video Library Template Available For Download
http://www.eggheadcafe.com/tutorials/aspnet/223c6e57-f81f-44b3-ab05-59...2b0ab63 >> Stay informed about: Subform causing duplicate records? 
FAQ
Search
Profile
Private Messages
Log in
