Adding Combo Boxes to Reports

[KristenD]

Can I add combo boxes to reports in order to customize them for the info I am looking for? If so, do I do this in the event portion of the property sheet?

I have combo boxes in my tables for certain fields that need to be manipulated for reports and such. I am unsure of how to proceed and I don't want to add a ton of queries to my database in order to pull reports that are needed. I would attach my db but it is too large.

The query I have written that I would like to supply several reports from has the following fields:
EmpID
EmpName
EmploymentStatus (combobox)
CraftCode
SuptName
StatusChange (combobox)
EvalDate
Rating (combobox)
SkillCode (combobox)
SkillDescription

I would like to have a combo box for CraftCode as well as when the reports are pulled they may be looking for the code for plumbers or laborers or sheet metal or what have you. Then when they pull up that report I would like the users to be able to pick from the combo boxes what they are looking for with the exception of Rating and Skill Code which I would like to add parameters to. Which I guess I could do in the query but I would rather have it all done when the report is clicked on.

Any advice??

 

[Beetle]

This should be done using a form. The form would have the combo boxes for the user to select the necessary criteria, and the query would reference those controls (combo boxes) in the criteria row for the necessary columns. The report would then be based on the query.

Although the report designer will allow you to place a combo box on a report, this is a completely pointless exercise, as reports are not interactive so a user would never be able to select anything from a combo box on a report.

BTW - having lookup fields (combo boxes) in your tables can be problematic. More info on that subject here.

 

[KristenD]

If I create this on a form will I then be able to get it to a report?

Will I be able to put parameters on certain fields? And if I am understanding this correctly, I should be able to create this form from the query I wrote, right?

Thanks again for your quick response!

 

[Beetle]

Well, you don't create the form from the query, you just create the form and put the appropriate combo and/or text boxes on it so the user can select (from a combo box) or enter (in a text box) the values they want to search for. These would all be unbound controls on a form. The query would then reference these form controls in its criteria, so the results of the query would be limited by what is chosen in the form. Suppose you have a form, let's call it frmSearch, with a combo box for EmploymentStatus (let's call the combo box cboEmpStatus). In the criteria row for the EmploymentStatus column in your query you would put;

Forms!frmSearch!cboEmpStatus

You then base your report on this query so the report is always subject to the same criteria limitations as the query.

 

[KristenD]

I am so sorry as I am trying to understand this (I am self taught at this so it does take me awhile to wrap my head around the logic being that I come from an HR background) and need to talk it out to make sure I truly understand it.

In the form, I create the combo boxes and/or text boxes I need. Then with the query I go back to the design view and in the criteria I would put the expression that I am looking for. The parameters I am looking for would be in the query as well. Then I can base the report off the query and this can be changed as needed.

So that being said, I would need to grant access to my users to this form to be able to change the data they are looking for so it would be a 2 step process to get the information they are requesting.

Is this correct?

BTW -- I will be out in Denver in August...love it out there!

 

[KristenD]

I should not use the form wizard either since it keeps adding subforms to the main form right?

 

[KristenD]

One more...sorry. When I do either the combo box or the text box it does not allow me to used the wild card when doing the report. Now having been working on this all day, I now realize it is the craft code that will be driving this particular report set that the management team is requesting. On a previous version of the database I ran several different queries as the common denominator in the craft code is the last digit. The first 2 digits represent other delineations. If an employee is a 103 the are a skilled employee, 203 would be certified, 303 is a foreman 413-483 are apprentices but they are all plumbers. I need that last digit to be able to drive all 4 reports as the other factors have constants that are able to be worked out with parameters or criteria.

I was just hoping for an easier way to do this based on that last digit as the queries and reports work off the wild card in the criteria so staying with plumbers it has **3 to be able to pull all classes of plumbers.

I am thinking that I am going to have to do what I really didn't want to do which is have the 4 queries and 4 reports just change the criteria in the craft code for each one.

 

[Beetle]

Well, you've sort of lost me with the Craft Code thing, but I'll try to better explain what I mean about using a form to enter the criteria for the search.

First you set up a form (without the wizard - just create a new form in Design View). On this form you would place a few unbound controls (unbound means they are not bound to a field in a table) like, for example, a couple of combo boxes and a couple of text boxes (whatever you think you'll need). The text boxes are easy, you just place them on the form and give them a sensible name (like txtCraftCode). The combo boxes are (slightly) more complicated because they need a Row Source to return values, but you can use the wizard for these if you want (when you add the combo box the wizard should come up). Just make sure that when you get to the step where it asks you if you want to store the value in a field you select No (I never use the wizards so I don't know exatly what it says but it's something like that). Then save the form and name it something like frmSearch. You then reference these form controls (the combo and text boxes) in the criteria of your query.

So let's say our hypothetical search form has one combo box (named cboJobType), where the user selects a JobType, and one text box (named txtCraftCode) where they enter a CraftCode. In the query, which should have a JobType column and a CraftCode column (along with whatever other columns you might need to query to return), you would put;

Forms!frmSearch!cboJobType

in the criteria row of the JobType column, and;

Forms!frmSearch!txtCraftCode

in the criteria row of the CraftCode column.

The query will only return records that meet the criteria entered in the form.

Now, you have a report that uses this query as its Record Source. On your search form you add a command button that opens the report. All the user needs to do is open the search form, enter the desired criteria and click the button.

I am thinking that I am going to have to do what I really didn't want to do which is have the 4 queries and 4 reports just change the criteria in the craft code for each one.

Without knowing your table structure I couldn't say, but I wouldn't think so. You should (probably) be able to use one query/report, and just change the criteria. If you want to post back with more details about exactly what you need to search for and how, plus maybe some info about your basic table structure, I can try to be more specific.

BTW -- I will be out in Denver in August...love it out there!

Can't argue with that. Not too many other places I'd want to live (San Diego or Austin would probably be on the short list). Haven't been to NY but I'll get out there one of these days.

 

[KristenD]

For our Craft (we are a construction company whose labor force is made up of plumbers, pipe fitters, welders, sheet metal mechanics, etc.) we have codes that correlate to their occupation 103, 203, and 303 would all be plumbers but different levels. So if I want to see which plumbers have a rating of 3 or better in SkillCode 310-313, then I need to see all 3 codes. I had this working in the query criteria using the wild card **3 to pull all of them. But I am unsure of how to recreate that using a combo box or a text box on a form.

I have attached my relationship structure of the tables.

I have created the database for our resources department since a lot of our field employees jump back and forth between job sites and lay offs and such, we want to be able to see how they are meeting and/or exceeding quality and production standards of our company as well as following our hiring policy as an EEO requirement.

I hope I have explained it well enough for you!

I believe I am going to a Lake when I am out there in August for some camping...always a new adventure every time I head out. Last year was Oktoberfest on Larimer (sp?) street in Denver.