Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    6

    Question Unanswered: Checkbox control on another table

    I have two tables. tblMain which has a list of students (name, date of birth, social security number, eye color, height, etc...)

    Then I have tblVehicle which has a list of vehicle types (make, model, size, special qualifications, color, etc...)

    ---

    I have a form (MainForm) that displays the records from tblMain and what I'm stuck with is creating a section on the MainForm that allows you to check on and off what vehicles that the students are qualified on.

    I want to use checkboxes to easily show that one student is qualified on some vehicles, while another student is not qualified.

    Additionally, the reason I have the vehicles on another table is that with each vehicle is a lot of other information specific to that vehicle (training manual number, color, size, etc...)

    The end state I want is being able to check on and off the various vehicle types on the MainForm for each student, and being able to create a Report with not just the vehicle name, but a Report with records from both the tblMain and tblVehicle.

    Any help would be fantastic.

    *of note, my database is on secure government system that I can't upload to the forums.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As I see it, the problem is not how you can display the information from the table tblVehicle but how you'll store the "vehicle" information associated with each student that matters.

    You need to create a many-to-many relationship between tblMain and tblVehicle: One student can qualify on more than one vehicle (you wrote: "... show that one student is qualified on some vehicles..."), and I suppose that several students can be qualified on the same vehicle (which seems obvious). You then need a junction table (i.e. a table that will associate sets of unique Ids from each primary table) to store this information. If you're not familiar with the concepts of many-to-many relationships and junction tables, see:
    Database Design - Many-to-many
    Defining relationships between tables in a Microsoft Access database
    Working with Many-to-Many Data Relationships
    Microsoft Access Tips: The Many to Many Relationship
    Free SQL Tutorial - Lesson 2

    When this is in place, you can use various methods to bring the information on a form: Form/SubForm interface, Listbox, Dynamic Query, VBA loop based on a RecordSet, ...
    Have a nice day!

  3. #3
    Join Date
    Aug 2011
    Posts
    6

    re:

    Sinndho:

    I followed one of the tutorials on the links you provided. They were very helpful! I was able to create a junction table with both UID's from tblMAIN and tblVEHICLE that I named tbljuntVEHIC.

    On my junction table it now has the vehicID and studentID and also has fields for each vehicle type.

    The problem I'm running into now is displaying this data on the MainForm (control data is set to tblMAIN).

    If I'm understanding you correctly, I need to make a subform to show this data on the MainForm? How can I go about that. I tried using the subform/subreport wizard and I can't seem to get it to display properly.

    Essentially what I want is the ability to check on and off (yes/no) for each vehicle type they are qualified on, on the MainForm. Do I have to have all the data that I want on the MainForm in this junction table? Or does having a subform allow me to display the checkboxes on the MainForm.

    Thanks for you help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •