Still having trouble running reports with multi select list boxes, and Happy New Year all!

[Micron]

Good points, but if I may:



<<<<<< You dont declare ctl as a control in a Dim statement
you don't SET it's value either. SET is for assigning objects to variables or properties

This will ensure that Option Explicit is added automatically.
Only for new procedures. It must be added to existing ones (but I knew that's what you meant)

[Gina Maylone]

THANK YOU EVERYONE! I KNOW it's a mess, I inherited it, then was presented with the need to do this comparison report. It's supposed to compare Sales ($) by Mfg between 2 time periods and include or not include the Bid Status. The user can choose 1+ or none, from either or both listboxes (BidStatus and Mfg). They can run a summary or detail report depending on the button they click. Yes, all the buttons are my various attempts to get this to work. I'll clean out the code and try to get it to compile. When I tried to upload the sample with tables it was too big. So I'll clear out a bunch of stuff to make it smaller. There are 2 reports per summary and detail (parent/child, not linked, I'd like to pass the same criteria line to the child report). The 2nd report (child) is based on another query (CompareMfg2) that queries the dates entered in the Compare To date fields on the form. The parent report is based on a query (CompareMfg) that queries the first set of dates entered. I know I could add the dates into the filter, but I just wanted to get it to work first.

Thank you again for your time!

[orange]

Try Compact and Repair of the database, then zip before posting.
Good luck.

[Gina Maylone]

Try Compact and Repair of the database, then zip before posting.
Good luck.

Here is the whole db, deleted a bunch of data and messy code. Compiled well for me. SampleDatabase.zip

Thanks again!

[moke123]

Here is the whole db, deleted a bunch of data and messy code. Compiled well for me. SampleDatabase.zip

Thanks again!

The only reason it compiled for you is that you do not have option explicit declared in every module.

https://bettersolutions.com/vba/vari...n-explicit.htm

You should go through your modules, add Option Explicit and then compile. It will point out all the errors.

You really need to do this before going any further.

[Gina Maylone]

The only reason it compiled for you is that you do not have option explicit declared in every module.

https://bettersolutions.com/vba/vari...n-explicit.htm

You should go through your modules, add Option Explicit and then compile. It will point out all the errors.

You really need to do this before going any further.

I'm confused, it is set under Options, and it is declared at the top of my modules on my filter form:

What am I missing? Thanks!

[orange]

I have looked at the database and the form. I selected dates in 2019 and 2020 (Jan 1 - Dec 31), a couple of BidStatus values and Greenheck as Mfg and selected/clicked Summary. I'm not sure what you expected, nor what date range etc was appropriate.



Here are the debug.print messages:

1st String [bidstatus] IN ("Design Build","Lost")
MFG [mfg] IN ("Greenheck")
Strcriteria [bidstatus] IN ("Design Build","Lost")and [mfg] IN ("Greenheck")

Suggestions:

- there seems to be no validation of controls on the form --that is you could run this without dates, or the wrong range of dates
- would be useful to you are others if you have some specific test data for a few specific mfgs (a value(s) you know to be correct)
- instructions for exactly how to complete the form to run the report
- a sample of each report(summary and detailed) so readers understand expected format
- more use of debug.print for validation of process and values
- info on how to interpret the report (no titles/info)
- remove the tested/ignored/overwritten procedures that are confusing/no longer needed
- the relationships view does not deal with all tables --this could be completed and tested with sample data (to clean up " I KNOW it's a mess")


Hope this is helpful.

Note: the 31-Dec-2020 date format is part of my regional settings, not part of your application.

Update: Just saw moke's comments re Option Explicit on every module. I agree totally. You have to manually update the existing modules with that code. Setting the parameter only works for new modules in your database.

[Gina Maylone]

I have looked at the database and the form. I selected dates in 2019 and 2020 (Jan 1 - Dec 31), a couple of BidStatus values and Greenheck as Mfg and selected/clicked Summary. I'm not sure what you expected, nor what date range etc was appropriate.



Here are the debug.print messages:

1st String [bidstatus] IN ("Design Build","Lost")
MFG [mfg] IN ("Greenheck")
Strcriteria [bidstatus] IN ("Design Build","Lost")and [mfg] IN ("Greenheck")

Suggestions:

- there seems to be no validation of controls on the form --that is you could run this without dates, or the wrong range of dates
- would be useful to you are others if you have some specific test data for a few specific mfgs (a value(s) you know to be correct)
- instructions for exactly how to complete the form to run the report
- a sample of each report(summary and detailed) so readers understand expected format
- more use of debug.print for validation of process and values
- info on how to interpret the report (no titles/info)
- remove the tested/ignored/overwritten procedures that are confusing/no longer needed
- the relationships view does not deal with all tables --this could be completed and tested with sample data (to clean up " I KNOW it's a mess")


Hope this is helpful.

Note: the 31-Dec-2020 date format is part of my regional settings, not part of your application.

Update: Just saw moke's comments re Option Explicit on every module. I agree totally. You have to manually update the existing modules with that code. Setting the parameter only works for new modules in your database.

Ok, I'll try to set it on every module, where I have I got an error (invalid inside procedure). I guess I am not sure where it should be inserted.

Use the following criteria:
Dates: 10/1/20 and 10/31/20 (I've deleted everything before 10/1)
Use: Greenheck as mfr and Openquote as bidstatus
Total should = $1,477,691.00

Using those dates and selecting all from both list boxes should give a total of $1,756,021.00

Use the same begin/end dates in both date fields.


SampleDatabase.zip

[Micron]

I guess I am not sure where it should be inserted.

At the top of every module, right under Option Compare Database (or whatever Compare Option you have there).

[orange]

1: Greenheck and OpenQuote


I processed the form as above and clicked Summary. I added a couple of debug.prints for testing.
I got 2 reports - first showed 0, the second is in the png above.

What is the purpose / meaning of the Begin Date and End date top right of the form??

Here is the Debug.Print output (including my testing lines)

Clicked Summary ...JED
Strcriteria and
Opening report: BidReportbyMfrSummary --------JED testing
Where BidStatus IN ('OpenQuote')
Where BidStatus Is Not Null
Where BidStatus IN ('OpenQuote')
Where mfg IN ('Greenheck')
Clicked Summary ...JED
1st String [bidstatus] IN ("OpenQuote")
MFG [mfg] IN ("Greenheck")
Strcriteria [bidstatus] IN ("OpenQuote")and [mfg] IN ("Greenheck")
Opening report: BidReportbyMfrSummary --------JED testing
Opening report: secondBidReportbyMfrSummary --------JED testing



Can you describe what the comparison is suppose to compare what to what?


2: All Mfg and all BidStatus


Here is the related Debug.print (including my test lines)

Where BidStatus
Where mfg
Where BidStatus IN ('BudgetEngineer','Design Build','Lost','Not Tracking','Omit','OpenQuote','Option','Plan Spec.','REBID','Won')
Where BidStatus IN ('AAB','Accurex','Advanced Thermal Solutions','Aerovent','Air Zone Industries','AJ Manufacturing','Alber','American Air Filter','Applied Air','Arundel Metal','Avocent','Avtron','Axivane','BARD','BFPE', 'BHV Inc','Bilco','Broan','Caterpillar','Cerus','Chroma lox','Cincinnati','Commercial Acoustics','Complete Mechanical Inc.','Computer Labor Svs','Computer Suport Misc','Converged Solutions','Curbs Plus','DCA','Dehumidifier Corporation of America','Delivery Charge','Delta Telephone','Denlar Hoods','Detroit Radiant','Dimplex','Druga','DSP Monoxivent','Ductsox','DVM','EHG Duct','Energy Labs','Enviro-Tec','FAMCO','Fantech','Federal Pump','Franklin Control Systems','Geist','Greenheck','Hamlin','Hartzell',' Haskris','Hitachi','Hunter Industrial','HVAC Misc','Indeeco','Industrial Plastic Fan','Johnson Air Rotation','Kinetics','Kwikool','Leading Edge','Lennox','LGS Contracts','Liebert','LMC','LMC Batt','LMC Caps','Manning System Parts','Marcraft Custom HVAC Systems','Mars','Maxitrol','Misc Support Prod
ucts','Mitsubishi','Modine','Monoxivent','Motivair ','Neptronic','Nortec','Nu-Tone','Panasonic','Pate Curb Co','Peerless','Phonetics','Plastec','Polaris','Po wer Cables','Powered Aire','Price','Progress Lighting','QMARK','Quick Draft','Raised Flooring','Reddi','Ruskin','SCADA','Sky Blade','Sourcetec','Spiral Duct','Starline','Start Up','Statewide Controls','Sterling','Subzero','Sunvent','Superior Radiant','Tandem Compressor','Tek Air','Titus','TPI Corp','Triatek','Turnkey','Tuttle & Bailey','UnderDuct','Upgrade','Viron','Vulcain/Honeywell','William Bros Corps of America','Wyant Htg & Air, Inc.')
Clicked Summary ...JED
1st String [bidstatus] IN ("BudgetEngineer","Design Build","Lost","Not Tracking","Omit","OpenQuote","Option","Plan Spec.","REBID","Won")
MFG [mfg] IN ("AAB","Accurex","Advanced Thermal Solutions","Aerovent","Air Zone Industries","AJ Manufacturing","Alber","American Air Filter","Applied Air","Arundel Metal","Avocent","Avtron","Axivane","BARD","BFPE", "BHV Inc","Bilco","Broan","Caterpillar","Cerus","Chroma lox","Cincinnati","Commercial Acoustics","Complete Mechanical Inc.","Computer Labor Svs","Computer Suport Misc","Converged Solutions","Curbs Plus","DCA","Dehumidifier Corporation of America","Delivery Charge","Delta Telephone","Denlar Hoods","Detroit Radiant","Dimplex","Druga","DSP Monoxivent","Ductsox","DVM","EHG Duct","Energy Labs","Enviro-Tec","FAMCO","Fantech","Federal Pump","Franklin Control Systems","Geist","Greenheck","Hamlin","Hartzell"," Haskris","Hitachi","Hunter Industrial","HVAC Misc","Indeeco","Industrial Plastic Fan","Johnson Air Rotation","Kinetics","Kwikool","Leading Edge","Lennox","LGS Contracts","Liebert","LMC","LMC Batt","LMC Caps","Manning System Parts","Marcraft Custom HVAC Systems","Mars","Maxitrol","Misc Support Products",
"Mitsubishi","Modine","Monoxivent","Motivair","Nep tronic","Nortec","Nu-Tone","Panasonic","Pate Curb Co","Peerless","Phonetics","Plastec","Polaris","Po wer Cables","Powered Aire","Price","Progress Lighting","QMARK","Quick Draft","Raised Flooring","Reddi","Ruskin","SCADA","Sky Blade","Sourcetec","Spiral Duct","Starline","Start Up","Statewide Controls","Sterling","Subzero","Sunvent","Superior Radiant","Tandem Compressor","Tek Air","Titus","TPI Corp","Triatek","Turnkey","Tuttle & Bailey","UnderDuct","Upgrade","Viron","Vulcain/Honeywell","William Bros Corps of America","Wyant Htg & Air, Inc.")
Strcriteria [bidstatus] IN ("BudgetEngineer","Design Build","Lost","Not Tracking","Omit","OpenQuote","Option","Plan Spec.","REBID","Won")and [mfg] IN ("AAB","Accurex","Advanced Thermal Solutions","Aerovent","Air Zone Industries","AJ Manufacturing","Alber","American Air Filter","Applied Air","Arundel Metal","Avocent","Avtron","Axivane","BARD","BFPE", "BHV Inc","Bilco","Broan","Caterpillar","Cerus","Chroma lox","Cincinnati","Commercial Acoustics","Complete Mechanical Inc.","Computer Labor Svs","Computer Suport Misc","Converged Solutions","Curbs Plus","DCA","Dehumidifier Corporation of America","Delivery Charge","Delta Telephone","Denlar Hoods","Detroit Radiant","Dimplex","Druga","DSP Monoxivent","Ductsox","DVM","EHG Duct","Energy Labs","Enviro-Tec","FAMCO","Fantech","Federal Pump","Franklin Control Systems","Geist","Greenheck","Hamlin","Hartzell"," Haskris","Hitachi","Hunter Industrial","HVAC Misc","Indeeco","Industrial Plastic Fan","Johnson Air Rotation","Kinetics","Kwikool","Leading Edge","Lennox","LGS Contrac
ts","Liebert","LMC","LMC Batt","LMC Caps","Manning System Parts","Marcraft Custom HVAC Systems","Mars","Maxitrol","Misc Support Products","Mitsubishi","Modine","Monoxivent","Moti vair","Neptronic","Nortec","Nu-Tone","Panasonic","Pate Curb Co","Peerless","Phonetics","Plastec","Polaris","Po wer Cables","Powered Aire","Price","Progress Lighting","QMARK","Quick Draft","Raised Flooring","Reddi","Ruskin","SCADA","Sky Blade","Sourcetec","Spiral Duct","Starline","Start Up","Statewide Controls","Sterling","Subzero","Sunvent","Superior Radiant","Tandem Compressor","Tek Air","Titus","TPI Corp","Triatek","Turnkey","Tuttle & Bailey","UnderDuct","Upgrade","Viron","Vulcain/Honeywell","William Bros Corps of America","Wyant Htg & Air, Inc.")
Opening report: BidReportbyMfrSummary --------JED testing
Opening report: secondBidReportbyMfrSummary --------JED testing


Note: I did not put a value for Begin Date or End Date top right???
And it wasn't checked for absence; and appears not to change anything???

[Gina Maylone]

The purpose of the report is to compare bids by manufacturer (also based on bid status) between one time period and another. The first set of dates (ie. 10/1/20-10/31/20) compared to the second set of dates (ie. 11/1/20 - 11/30/20) would show the amount bid for the mfr (Greenheck in this case) from the first time period to the second. I suggested using the same dates in both, to verify data easier.

The summary report will open 2 reports (BidReportbyMfr and SecondBidReportbyMfr), the first report will contain a subreport (SecondBidReportbyMfr) (which is where I am trying to figure out how to pass the criteria to), the SecondBidReportbyMfr report should match the top of the BidReportbyMfr. Sorry for the confusion.

I am currently getting correct totals in both summary and detail report. NOT in the subreports however. Also, I need to account for when the users don't choose a bid status or manufacturer. I know I can require them both, but sometimes they don't want any. Ugh.

My debug.print gives me:

Strcriteria [bidstatus] IN ("OpenQuote")and [mfg] IN ("Greenheck")1st String [bidstatus] IN ("OpenQuote")
MFG [mfg] IN ("Greenheck")
Strcriteria [bidstatus] IN ("OpenQuote")and [mfg] IN ("Greenheck")

I hope this helps! Thanks again for your time and effort!!

[orange]

Here are 2 pngs, showing the form and the reports produced.




Here is related Debug info
Clicked Summary ...JED
1st String [bidstatus] IN ("OpenQuote")
MFG [mfg] IN ("Greenheck")
Strcriteria [bidstatus] IN ("OpenQuote")and [mfg] IN ("Greenheck")
Opening report: BidReportbyMfrSummary --------JED testing
Opening report: secondBidReportbyMfrSummary --------JED testing



No report titles.
No indication of parameters Selected. Shouldn't there be some additional context info on the reports?

I don't know how anyone would use these without some additional info.

How do you interpret these reports? That is what do these mean to the user?

If this isn't the kind of output you expect or need, then please mock up something so we understand your report, sub report etc.

[Gicu]

Hi Gina,

Please have a look at the attached file, I have changed the approach used for the reports, I think you will find it much easier to follow.

Cheers,
Vlad

[Gina Maylone]

Hey Vlad! How are you doing?

I had to uncomment the top Dim statements and it ran perfectly! OMG! Subreports and everything!!! I'm speechless! How is the criteria being passed to the reports? I couldn't find it.?? THANK YOU! You're a rockstar!

Nevermind, I found it in the queries! Thanks again!

[Gicu]

Hi Gina,
Glad to hear it works. I simply added a boolean field (IsSelected) to the bid status and mfg tables and modified the queries feeding the reports to only show the records selected.
Cheers,
Vlad