Reports print Twice

[brharrii]

I'm working with a report that is requests a Record ID and then generates a report on a single record. When I try to print certain records, two copies of the same record are generated for some reason. The problem I believe is related to two fields on my table, one for Customer and the other for brand. My form is setup with a combobox that provides a list based on a tblCustomer table. Once you select the customer, the brand combobox updates to only display the brands that correspond with the selected customer. I found that on reports for customers that have more than one brand, even if I've selected a specific brand on the form, the report prints a copy for each brand associated with the customer. Once I clear the customer information on the record, the report goes back to printing only one copy.




Row Source cbo2 - Brand:

SELECT tblBrand.BrandID, tblBrand.BrandDesc, tblBrand.CustomerID
FROM tblCustomers LEFT JOIN tblBrand ON tblCustomers.CustomerID = tblBrand.CustomerID
WHERE (((tblBrand.CustomerID)=[Forms]![frmProductInfoEdit]![cb1]));

After Update code for Customer cboBox:

Private Sub cb1_AfterUpdate()
  Me.cb2 = Null
  Me.cb2.Requery
  Me.cb2 = Me.cb2.ItemData(0)
End Sub

Report code:

SELECT tblProductInfo.ProductID, tblProductInfo.ProductNumber, tblProductInfo.ProductDesc1, tblProductInfo.ProductDesc2, tblPhase.PhaseDesc, tblPhase.IntendedUse, tblProductInfo.Effective, tblProductInfo.DateUsed, tblFish.FishDesc, tblFish.ScientificName, tblPack.PackDesc, tblCustomers.CustomerDesc, tblBrand.BrandDesc, tblProductInfo.ShelfLife, tblCut.CutDesc, tblSauce.SauceID, tblSauce.SauceNumber, tblSauce.SauceDesc1, tblSauce.Amount, tblSauce.Ingredients, tblSauce.InjectorSettings, tblFilm.FilmNumber, tblFilm.FilmDesc, tblFilm.Film2Number, tblFilm.Film2Desc, tblFilm.FilmTemperature1, tblFilm.FilmTemperature2, tblFilm.FilmTare, tblRetailCase.RetailCaseNumber, tblRetailCase.RetailCaseDesc, tblRetailCaseType.RetailCaseTypeTare, tblRetailCaseType.RetailCaseTypeWeight, tblRetailCaseType.RetailCaseTypeUnits, tblMasterCase.MasterCaseNumber, tblMasterCase.MasterCaseDesc, tblMasterCase.MasterCasePalletConfiguration, tblMasterCaseType.MasterCaseTypeWeight, tblMasterCaseType.MasterCaseTypeUnits, tblMasterCaseBottom.MasterCaseBottomNumber, tblMasterCaseBottom.MasterCaseBottomDesc, tblDivider.DividerNumber, tblDivider.DividerDesc1, tblComponent.ComponentNumber, tblComponent.ComponentDesc1, tblLabel.LabelNumber, tblLabel.LabelDesc, tblFilm.FilmDesc2, tblProductInfo.FilmPocket, tblProductInfo.FilmCoding, tblProductInfo.RetailCoding, tblProductInfo.PortionMinWeight, tblProductInfo.PortionMaxWeight, tblProductInfo.PortionTargetWeight, tblProductInfo.SpecialInstructions, tblProductInfo.SpecialInst, tblProductInfo.ImagePath
FROM tblSauce RIGHT JOIN (tblRetailCaseType RIGHT JOIN (tblRetailCase RIGHT JOIN (tblPhase RIGHT JOIN (tblPack RIGHT JOIN (tblMasterCaseType RIGHT JOIN (tblMasterCase RIGHT JOIN (tblMasterCaseBottom RIGHT JOIN (tblLabel RIGHT JOIN (tblFilm RIGHT JOIN (tblDivider RIGHT JOIN (tblCut RIGHT JOIN ((tblCustomers LEFT JOIN tblBrand ON tblCustomers.CustomerID = tblBrand.CustomerID) RIGHT JOIN (tblComponent RIGHT JOIN (tblFish RIGHT JOIN tblProductInfo ON tblFish.FishID = tblProductInfo.Fish) ON tblComponent.ComponentID = tblProductInfo.Component) ON tblCustomers.CustomerID = tblProductInfo.Customer) ON tblCut.CutID = tblProductInfo.Cut) ON tblDivider.DividerID = tblProductInfo.Divider) ON tblFilm.FilmID = tblProductInfo.Film) ON tblLabel.LabelID = tblProductInfo.Label) ON tblMasterCaseBottom.MasterCaseBottomID = tblProductInfo.MasterCaseBottom) ON tblMasterCase.MasterCaseID = tblProductInfo.MasterCase) ON tblMasterCaseType.MasterCaseTypeID = tblMasterCase.MasterCaseType) ON tblPack.PackID = tblProductInfo.Pack) ON tblPhase.PhaseID = tblProductInfo.Phase) ON tblRetailCase.RetailCaseID = tblProductInfo.RetailCase) ON tblRetailCaseType.RetailCaseTypeID = tblRetailCase.RetailCaseType) ON tblSauce.SauceID = tblProductInfo.Sauce
WHERE (((tblProductInfo.ProductNumber)=[Item Number]));

[June7]

I would have to analyse the db directly. If you want to provide, follow instructions at bottom of my post.

[brharrii]

Here it is, thank you.


If you look at tblProductInfo you will see that there is only 1 record. It's a product for customer "Bob's Burgers" under Brand name Tropical Storm. If you open the form frmProductInfoEdit you can see how the Customer / Brand combo boxes work. and finally if you open repSpecificationReport it will ask you for a product Number. Type in 1111111, that will pull up the 1 record in the databse. When the print preview comes up you should see 4 pages. the first 2 are for bob's Burgers but under the brand name Blue Horse - Not the correct one. The last 2 pages have the correct customer / brand combo "bob's burgers - Tropical Storm".

Thanks!

ProductDatabase - Copy.zip

[June7]

Why do you have code to clear bound combobox (cb2)?

Reason for the additional record is because of tblBrand joined to tblCustomers in the report's RecordSource. Join tblBrand to tblProductInfo instead.

[brharrii]

If I remember correctly, cb2 has to be cleared in case the cb1 has a value and then is changed to a different value. I'm not sure if we are talking about the same bit of code though. IS that what you're referring to?

Thank you! I made a change to the report record source and fixed the problem for me

[June7]

Okay, get it about cb2 now, and should have realized that.