CODEWITHSUNDEEP
ms-accessms-access-2007
Matt Young
Matt Young

Reputation: 11

How to add a checkbox in a form next to a query to append to another table

I am building a training database.The tables I am using are: Employees, Training Courses, In Training, and Completed Training. I have a query that filters off of a form to show employees that have not received the training.

I want to add a checkbox next to the results so I can select some or all of them to be added to the In Training table. I have been stuck on this for days searching the internet and reading books looking for the answer.

Upvotes: 1

Views: 1803

Answers (1)

moongster
moongster

Reputation: 189

Just saw this question. Here is an alternative solution to this problem. There is no checkbox, but you get a subform with combo box to update the course and progress for a particular employee.

  1. Create 4 tables with the fields:

    • tblEmployees
      • EmployeeID (Primary Key)
      • EmployeeName (and any additional fields you might need)
    • tblProgress -> this stores "IN TRAINING", "COMPLETED" as 2 separate records
      • ID (AutoNumber) (Primary Key)
      • ProgressDesc
    • tblTrainingCourses
      • CourseID (Primary Key)
      • CourseName
    • tblEmployeeTraining -> here we are using composite key
      • EmployeeID (Primary Key)
      • CourseID (Primary Key)
      • Progress

  2. Create a one-to-many relationship between tblEmployees and tblEmployeeTraining, linking the EmployeeID.

  3. Configure the following fields in tblEmployeeTraining (in Design View), by selecting the [Lookup Wizard...] from Data Type column.

    • CourseID
      • Choose the tblTrainingCourses as the table for the Lookup Values
    • Progress
      • Choose the tblProgressas as the table for the Lookup Values

  4. Create a Form based on the tblEmployees. You should get a subform included that will display the Course Name and Progress. Basically, the 2 columns in the subform will show combo box for you to select the course and the progress.

If you do not want the relationship in step 2, maybe you want to store the employee data after it is deleted, then you need to create a query and use it as the RecordSource for the subform. Instead of deleting the record from tblEmployees, you may want to add a field in tblEmployees to determine if the employee is active or not. Then, you can use the above steps and just do filtering on the form to display those active employees.

Upvotes: 2

Related Questions