Hi there, I've really appreciated

[roddy17]

Hi there,
I've really appreciated the help that i've gotten from reading and submitting posts to Tek tips.
I have a report that i want to produce. A simple looking report. It just displays a list of product where the criteria is true.
On one of my forms, i have a list of 8 hard coded criteria such as color and each color has a check box. (These colors are hard coded on the display because that's how the boss wants it, so then i need to store them in the PRODUCT TABLE as separate fields.)
I then also created a table listing these 8 colors so that i could list them in a drop down box, and then select one, and then create a report listing all product that are related to that color.

I sort of know the logic of what i want to do, but i do not know the code. (I am assuming that i need to use some sort of recordset, but i have no idea yet how to work with that.) I think that i also need to use a loop, and i don't know how to use that in VBA yet either. Anyway, the following is my logic:

If (Forms![Report form]![cboColor] = Blue) then
Search loop - of all records in PRODUCT TABLE
Find all product where check box for Blue = True
open report "List Product by Color"
End If

If (Forms![Report form]![cboColor] = Red) then ... etc

i guess the simplest way would be to repeat the code for each color, or maybe some sort of Case statement?

I am hoping that this makes sense.
I thank you already for your efforts in looking at this one.
........roddy

 

[bpurser]

Well, I have three thoughts. First, off your boss....second, use a select case to build a where condition for the report (this assumes your fields for colors are ColorXXX where the XXX is the color name, and that they are Yes/No fields):

dim StrWhere as string
dim strReport as string

Select case me.cmbColor

Case is = to "red" 'not entirely sure about the syntax there
strWhere="[ColorRed]=-1"

Case is ="blue"
strWhere="[ColorBlue]=-1"

etc for all color choices
end select

strReport="NameOfReportHere"

docmd.openreport strReport,acpreview,,strWhere

This will open the report filtered for only those entries where the colortype field that matches the combo is checked.

Option three is to fool your less than knowledgeable boss, have ONE field denoting color, and use an option group to select it, with however many color choices you have, denoting 1 as red, 2 as blue, etc...this will make life much simpler. It does hinge on the assumption that your color choices are mutually exclusive, though.

HTH
Ben

 

[roddy17]

Thanks Ben
I love your idea of going with the option group, unfortunately there is a possibility of more than one option being checked at the same time.
But that's ok, we've got a start in the right direction. I think that your syntax is correct, I'm not getting any errors with it. It's just not being selective enough yet. Right now, the way it is designed, the report is coming up with every single record being displayed.
But it is tweeking my creative juices (what i have of creativity anyway ).
Question for you though on this line of code:
strWhere="[ColorBlue] = -1"
[ColorBlue] -> I recognize that -1 is checking to find a check box that is 'true' or checked. But is [ColorBlue]the name of the check box on the form or should it be the name of the field in the table?
I assumed that it is the name of the field in the table but so far it isn't narrowing down what is displayed on the report.
Not giving up yet though.
thanks...roddy