Solved Personnel Database work in Progress

[HillTJ]

Hi, I'm working on a Personnel Database that I intend to expand to keep records of employee training (later). I'd like some feedback about my table relationships to see if they seem sensible & whether any improvements can be recommended (or pitfalls avoided). Also, I have a problem. Upon opening 'frmPersonnel' & selecting the 'Contractor Details' tab, that subform is not linked to the 'Contractor Details' tab. That is upon entering a new record, access asks me to enter the 'ContractorID'. Is something wrong with my logic?. Also I wish to hide tab 'Contractor Details' if 'Personnel Category' on main form is not 'Contractor'. Thanking you in advance.

 

[Ranman256]

In a subForm, make sure the contractorID is is in both forms,
And the subForm is bound using the properties:
LinkMasterField
LinkChildField
Are both set to ContractorID.

 

[HillTJ]

Ranman256, That works but the subform does not automatically add a 'ContractorID' when entering a new record. Is my database design wrong? Appreciate it.

 

[zeroaccess]

Contractor Details subform needs to be linked to the Personel_ID.

You do not have a Personel_ID foreign key in tblContractor. Add a Personel_ID field to tblContractor of the same data type as in tblPersonnel and the link should work.

 

[HillTJ]

Zeroaccess, Thanks for the heads up. I added a field 'Personel_ID' to 'tblContractor' but could not link this field to 'Personel_ID' of 'tblPersonel_Contractor' I think this is because the key for this table is a composite key based upon both 'Personel_ID' &'Contractor_ID'. I thought this was a smart way of preventing duplicate records. That is, only 1 Personel/Contractor/Date combination. What do you think of the table relationships? Is there a smarter approach? Appreciate it.

 

[MajP]

Working with a single key is far easier. You do not have to make a composite key to have a unique combination. You can make a composite index instead. Example of an index on first and last name.

 

[HillTJ]

MajP, Got you, will give it a go. Thanks. Any comments on my table design/relationships?

 

[zeroaccess]

I'm not sure I have enough experience to comment on the current arrangement outside of, for this particular part: You'll need the relationship I described earlier for the subform to establish the many-to-one relationship with the main form/table. Once you create the new field and link them, it would be a good idea to enforce referential integrity, with cascade deletes so that if a main form record is deleted, the related subform records will also be.

 

[HillTJ]

zeroaccess, I just can't get this through my head!!. I've added the 'Personel_ID' field to 'tblContractor' & removed the composite key in 'tblPersonel_Contractor' so that the primary key in that table is 'Personel_ID'. I have created a 1:Many link between 'tblPersonel' Personel_ID (PK) & 'Personel_ID (Fk) in 'tblContractor' is this what you mean?

 

[zeroaccess]

Yes. You will then need to populate that field with values since you have existing records. Since you only have a few, it's easy to do by hand. Just type the ID (1 or 2 in your case) for the records in tblContractor and if you set the Master/Child links in your subform to that Personel_ID field, they should link up and start adding FK values going forward.

 

[HillTJ]

Got it!!. As explained in the above response, I need a way of automatically entering the ContractorID in the Sub-Sub form so that I can add a new record. I have this, Main form called frmPersonel, linked to tabbed subform called 'TabForm' by common 'personel_ID', 'TabForm' contains a subform called 'Contractor_Documents', linked to 'TabForm' via common 'Contractor_ID'. All works well until I go to enter a new record whereby I need to enter the "Contractor_ID' (which is the link). I don't see why I cannot have code copy 'Contractor_ID' from 'TabForm' into 'Contractor_ID' of 'Contractor_Documents' upon entry of a new record. It works if I enter 'Contractor_ID' manually. I would appreciate some assistance with this.

 

[zeroaccess]

Ok, sorry, I didn't realize you had a sub-subform. I believe that one needs to be linked to the parent subform.

You may find that arrangement works better if you move the frmContractor_Documents Subform into the Footer of frmPersonel_Contractor Subform.

 

[HillTJ]

Zeroaccess, never thought of that. Will give it a go.

 

[mike60smart]

See if this helps

 

[HillTJ]

mike60smart, thanks but your idea still exhibits my problem. See my post #11 for my best explanation, then try to add a new record to the contractor documents subform. You'll be able to select a unique record with the combo box but access will say something like it needs a value in tbleContractor (which makes sense as it's not automatically linked). I think I need some code to copy the Contractor_ID from the sub form to the sub-sub form, that way the link would be established. Cheers

 

[HillTJ]

All, as a trial i put =[Forms]![FrmPersonnel]![frmPersonel_Contractor Subform].[Form]![ContractorID] in the on entry event of the 'ContractorID' field on the sub-subform. Didn't work but I do think that I need to automatically copy the ContractorID from the sub form to the sub-sub form to create the link.

 

[mike60smart]

Hi See if the amended db solves the problem

 

[HillTJ]

mike60smart, Yes. A big thankyou. I see the additional fields, but I'd appreciate it if you could explain in simple terms what you did. The project continues with new vigor!

 

[mike60smart]

Hi

All i did was remove the composite key from tblContractorAttachments and added an Autonumber Primary Key instead.

 

[HillTJ]

Cool, i'm really grateful.