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 )
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.
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.
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).
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.
I could only attach a screen shot...the db is over 2 mb