Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Posts
    13

    Unanswered: Selecting certain data from subform...$$$

    I'am at it again... What I am tring to do is when adding or editing an employee in the main form you select his/her department. After all other required fields are filled in I want my subform to only select (in a drop down box) certifications for the department that was selected in the main form. Each employee has many certifications...Once again I think it has something to do with my linking the tables. PLEASE, I hate to beg, but I am so stumped.

    I attached the database as a .txt file, just change it to mdb, but you great excellent people probably already now that...(Notice how bad I'am sucking up...l )

    Thanks,
    Eric
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    Eric, what you need for this is a linking table. You have an Employee Table and a Certs Table (All Certs). This requires a Many-to-many relationship, many employees can have 0 or many certs. Your linking table might be called [EmployeeCert] with at least 3 fields. The unique primary Key would be a combination of the EmpID and CertID.

    EmpID|CertID|DateAcquired

    Now make a query using the [EmployeeCert] linking table and the [Certs] table with a join on the CertID to show the cert name. Base your subform on this query with the EmpID as the linking field.


    \
    ~

    Bill

  3. #3
    Join Date
    Dec 2004
    Posts
    13
    Thanks for the reply...I tried all day to figure out what you meant, but I can't . This is confusing because what I intended to happen is when you go to edit a current employee in the form mode, select a different department for him/her, and the certifications that he had for the past department would disappear, and you would be able to select certifications in the new department. But if he/she were to switch back to the old department, those certifications would still be there? I will send $10 to through paypal if someone can give me something to work with.....And now I am extreamly desperate..lol...

    I think Iam getting ahead of myself, what I really need is to select certifications(in subform, because there are many for each employee), for the particular department.

    I am not understanding the whole thing about the linking table...I put those three fields that you listed in a table and linked empID to empID and certID to certID (main tables to linking table). I then created queries and subform, but the subform was showing certID and date acquired(can't even enter date).

    Thanks,
    Eric
    Last edited by gixer311; 12-30-04 at 20:04.

  4. #4
    Join Date
    Dec 2004
    Posts
    13
    This is my updated database. I figured out that I can't have the certifications list linked to my employees because it would add records to it(i want that to be set so that you can't modify the table AllCerts). Please any help on how to set it so that when I select the department, the certifications will appear in the subform.

    Thank you,
    Eric

    I could only attach a screen shot...the db is over 2 mb
    Attached Files Attached Files
    Last edited by gixer311; 12-31-04 at 17:15.

  5. #5
    Join Date
    Feb 2004
    Posts
    533
    try compacting the database. this usually reduces the size considerably. Also add to a .zip file then attach the zip.

    have been in a crunch with another project so not watch the forum as much.


    /
    ~

    Bill

Posting Permissions

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