Using a checkbox form from one table to populate another table

[ajochung]

Hello,
Thank you for taking the time help me with this inquiry. I'm looking to find a means to do a few tasks:

1. I have a table which lists all of the equipment we use in our company and would like to use it as a template in a form so that users can select a checkmark if the equipment is being used that day. The checked items would then be used to populate another table which records all of the equipment the user has selected. I thought the checkmark method would be best so that the user can scroll through the list of equipment and select multiple pieces for the day. The reason I don't use a multi-value field is so that the user can also enter quantity and hours of equipment used in the same table when they scroll through the list.

2. Once all of the equipment pieces have been checked, I would like the user to be able to click a review button which would navigate to the next form with all of the selections for the day. This new form will have the option to go back to the first form if any corrections need to be made. And also have the option to submit the results.

3. Once the user is finished with their selections, the user can then select Submit which will finalize the recorded equipment table and clear the checkboxes and any inputs from the equipment template table (without modifying anything) so that it can be used for the next day and so forth.

If there are any suggestions it would be very appreciated, also, if you can think of a better method please let me know. Thank you!

*To give a little extra information, I am using access 2013 and am somewhat new to Access.

 

[Galaxiom]

A better technique is to use a fabricated ADO recordset. It eliminates the need for temporary table data.

Create an empty ADO recordset with no ActiveConnection and a Client cursor location.

Use the Fields.Append method to append any fields required, including the selection checkbox. Open the recordset.

Loop though another recordset based on the source table and copy the data into the ADO recordset. Set the display form's Recordset property to the ADO recordset. RecordSourceType must be Table/Query.

You will now have an updateable bound form showing the data along with the checkbox which will allow the user to select the records to be copied.

Filter the recordset based on the checkbox and transfer the selected records to the destination table using a loop through the ADO recordset. This can either be done using another recordset or dynamic Insert queries.

It all sounds like a lot of looping but it works very fast.

 

[ajochung]

I wish I knew what half of this means. I am familiar with a recordset but am not sure how to manipulate the recordset the way you suggested.

 

[Galaxiom]

Here is a start. It is an example of using a fabricated ADO recordset in a form to add a checkbox to each record.

 

[zcook123]

Here is a start. It is an example of using a fabricated ADO recordset in a form to add a checkbox to each record.

Hello. I am having a similar problem to this one but maybe reversed. I want to click checkboxes and append records to my table based on what boxes Ive checked. For example, if I am testing for lead and cadmium in a batch of samples, I want to click the checkboxes for lead and cadmium and have access duplicate my sample records with lead in one set and cadmium in the other. Does that make sense? I appreciate any help you can provide! I linked a screenshot with an example of what I mean