Check box to select output fields

[Pat Hartman]

@DampFloor, If this is a class where you are supposed to be learning about database design, you absolutely have to fix the design before submitting your project. If you are simply supposed to be learning how to work with Access in order to create forms and reports and the GUI elements are more important than the underlying schema, then you can probably keep going. Just know that your interface will be clunky compared to what it otherwise would have been.

A simple method to produce lists of fields for picking is to use a multi-select listbox. The technique is advanced for a beginning class but it could get you out of this hole.

There are three options for the RowSource of a combo/listbox.
1. Table/Query
2. Value List
3. Field list from a table or query

Using the third option, you provide a table or query and the listbox will show all the fields in that table or query. You can then allow the user to select multiple fields. Your code would loop through the listbox and construct an SQL String. You can then use the SQL String as a query. This will work fine for any case where you just want to export the query to a .csv or Excel. However, if you want to use the new query as the RecordSource for a form or report we're back in the "it's really hard to get there from here" territory.

If you are trying to select just the set of fields related to a specific vendor, just hard code the selection and make three of everything. Three forms, three reports, three queries and leave it at that. The user selects vendor 1, 2, or 3, and you use the objects for that vendor.

 

[DampFloor]

Mark,

The deliverable of the check boxes is due in two days. I have almost completed getting all the selected quotes into a single table and from there I believe i will be able to truly utilize the aspects of a relational database. Unfortunately this project has gone past the point of simply writing a report on why this is an improper method.

Pat,

This course is not about learning about access. It is more about creating a project of any sort for a client. I do understand your dismay with my approach here but it has turned into something i just need to complete. If you view arnel's example which he posted, that is exactly what i want to accomplish and it seems entirely possible given the right knowledge.

 

[Mark_]

If its due in two days, go back to what you had working and worry about getting it right AFTER you get the grade. How long until you get graded on the end result?

 

[DampFloor]

I have about a month till the final project is due. However i still do not have the check box going...

 

[Mark_]

Can you create a new DB, copy over the form and the table driving it, and post it here with notes of what you've tried?

 

[DampFloor]

First i will thank you for your patience.

Here it is. What ive done is added the two tables which i am attempting to use and created a query which brings them together. Then i created a form with a sub form which is a datasheet of the query. It is after this i get lost...let me know if you require anymore information.

 

[Mark_]

OK, the reason you've been having errors is you never added checkbox controls to your form.

Step 1) Add check box controls to your FORM (Not subform) Just like Arnel did in his sample. This is done by opening the form in design view, clicking on "Design" at the top, selecting the check box control then clicking on your form where you wish it to be.

Step 2) Open the properties for the control box. Give it a name that makes sense for what you are doing.

step 3) Look at the sample Arnel posted. See which even he is using for each control then use the same type of code. You will have to replace the control name he's using with the ones you provided in Step 2).

Were I in your position, I'd start by adding ONE checkbox control, make sure you do it properly, rename it, then get the code for that check box to work. Once you've figured that part out, you should be able to do the same with the rest.

Just so you understand, the way you have your query set up you will not be able to edit data on the subform.

You also do not have the same definition for your two files. In one, Code is a number. For the other Code is a short text. If you are trying to simply view both together this would prevent you from doing a UNION query.

 

[Mark_]

OK, a few other issues you would have with how you are setting up your query.

As it is adding everything from both files, you will get redundant data. I'm uploading a modified version to show the difference between what you were trying and what you would get with a normal UNION of queries. I'm hard coding in the Sysco/GFS and bakedgoods as expressions in the source queries for the union. Still not editable, but will only show a given record once. I also put them into a form set to datasheet.

The sample data shows the issue you were having.

If you want to hide/unhide fields, change sort, do filtering and the such, the built in Access abilities may meet your needs. Just open F_All and right click on the header for any field. That will tell you what you can/can't do in that field. This does include hiding/unhiding columns, much like what you are trying to do.

After you get past this part of the project we can talk about how you get your data into the same structure the Union query uses. This is one of the reasons you put it all into one table rather than having separate tables the way you have it.

 

[DampFloor]

Thanks for the info Mark. I quick hiccup I have encountered when trying to follow your steps is that when i open the VBA screen (ALT F11) there is nowhere to type my code. Do you know what my issue here could be? attached is a screenshot.

 

[DampFloor]

Sorry, once again with the screenshot

 

[Mark_]

Should be the same place you put it before? You already have a form that was giving you errors with Me. so I figured you already had the spot you were going to put the code. You would use the same events as Arnel. You have been looking at how he did it, correct? Have you been looking through the events also?

 

[Mark_]

Thinking it through, did you open the sample I posted? Did you look at the form I added based off of the union query? Just want to see if that will do what you need.

 

[DampFloor]

Hi Mark,

So I went with the Hide/Unhide fields functionality that access provides. Not exactly what i want but it will work just fine in this case. I am currently getting all of the selected quotes into one table and then I will be making a form which will be used to create menu items. Im sure i will run into some difficulties there. Thank you so much for all the help and patience!