Create a Report from a form with numorous checkboxes then printing only checked boxes

[FranCorona]

I have created a form based on numorous tables that allows someone to input new employees and show what equipment they are licensed on. The equipment are checkboxes so when the employee is trained it can be checked. I have tried to create a report from the form but get this message:The number of fields from your currently selected table or query exceeds the maximum width in microsoft access forms and reports. I need to know how to creat a report the will print specific employee information along with only the checkboxes that are checked. I created a report using fields from a query with all the info needed but it prints all the checkboxes, even the unmarked ones. I know there is

 Me.Checkbox.Visible = Me.Checkbox

but how do you add all of the check boxes into this code? There are 92 checkboxes by the way....I appreciate any help with this!

[FranCorona]

Ok I tried the following code:

 If Me.Van_15_PAX_ = -1 Then
Van_15_PAX_.Visible = True
Else
Van_15_PAX_.Visible = False
End If

If Me.Bus_48_PAX_and_Below_ = -1 Then
Bus_48_PAX_and_Below_ = True
Else
Bus_48_PAX_and_Below_ = False
End If 

and it worked with the Van but not the Bus; it comes back with this error: runtime error '-2147352567 (80020009)': You can't assign a value to this object. I have 92 check boxes with similar equipment to add to this code. Also, when it does work it leaves the blank space between the fields on the report; I need all of the checked data to line up properly on the report.

Thank you so much for any help with this! I am on a time crunch to get it done for my boss.....

[FranCorona]

Update: I got the code to run correctly for all of my check boxes. Now the problem I am having is that when I print it shows only the checked boxes but it has all the extra space in between where the unmarked data is invisible. Is there a formatting code that would bring only the checked data into the report for printing?

[pbaldy]

Ah, I see you did have a thread going. As mentioned in the PM, the table design is flawed (not normalized). What you have as checkbox fields should be records in a related table. This covers the concepts:

Fundamentals of Relational Database Design -- r937.com

With a normalized design, your report would be simple.

[FranCorona]

I have changed the design to the equipment table and created a single column with all of the equipment, but my problem now is there is no way to create a relationship with any of the other tables. In the original equipment table all of the equipment had its own column and yes they are check boxes so that when the employee is licensed the clerk can just check the box to show what he/she is licensed on. If I change this to text instead of a check box how can I easily update the form? I'm sorry, I don't really have a lot of experience with access and I have taught myself what I do know.....

[pbaldy]

Based on your description, I think you have a many-to-many relationship. That would typically be done with 3 tables, employees, equipment and a junction table that included the key fields from the first 2. If I was licensed on 5 types of equipment, I would have 5 records in that table, all with my employee ID and each with the appropriate equipment type ID. For the user, that would typically be maintained with a form bound to the employee table and a subform bound to the junction table. Similarly in a report, you'd use a subreport to display the items for which I was licensed.

[FranCorona]

I changed the format of my tables and created three tables as you advised: employee info table and equipment table and then I created a blank table and added the equipid field from the equipment table and the empnumb field from the employee table for a join table. I made sure that the relationships where there and then created a form from the employee table with the equipment table as a subform but it doesn't let me pick equipment. It is just a box where you can type new data in. I need to be able to choose multiple equipment as the employees are licensed. I feel so ignorrant but I just can not seem to figure it out....

[pbaldy]

For the equipment field I'd use a combo box that got its selections from the equipment table. The combo wizard should walk you through it.

[June7]

Options for form/subform arrangement:

1. main form bound to employees and subform bound to junction table with combobox to select equipment

2. main form bound to equipment and subform bound to junction table with combobox to select employee

[FranCorona]

Hello. Thank you so much for the input! I have it almost all figured out.....I have the input forms that are good. I created a query in order to create the report for printing and it works well except that when I print it prints all the records and I need to only print one employee at a time. Also, when I update an employee's record to add a piece of equipment after training, it puts that equipment on another page. How can I get it to keep all training equipment on one page? I'm sorry I hadn't gotten back on here to update but have been extrememly busy at work and was unable to devote 100% of my time to the project. Thank you again for any and all help! - Fran

[tysheena]

Sorry new to this forum.. trying to figure it out

[pbaldy]

If I understand correctly, this is one way to limit the report to a given employee:

BaldyWeb wherecondition

Probably have to see it to see how to keep equipment on one page.

[FranCorona]

How can I upload the database for you to take a look at it? If you have time to do so that is.....

[pbaldy]

Compact/Repair, then compress/zip, then attach in the Go Advanced area.

[FranCorona]

Licensing Database2.zip Did it work?