Solved Personnel Database work in Progress (1 Viewer)

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:24
Joined
Apr 1, 2019
Messages
712
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.
 

Attachments

  • Trainingv3.zip
    108.6 KB · Views: 146

Ranman256

Well-known member
Local time
Today, 06:24
Joined
Apr 9, 2015
Messages
4,337
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

To train a dog, first know more than the dog..
Local time
Yesterday, 23:24
Joined
Apr 1, 2019
Messages
712
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

Active member
Local time
Today, 05:24
Joined
Jan 30, 2020
Messages
671
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

To train a dog, first know more than the dog..
Local time
Yesterday, 23:24
Joined
Apr 1, 2019
Messages
712
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

You've got your good things, and you've got mine.
Local time
Today, 06:24
Joined
May 21, 2018
Messages
8,463
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.

Index.jpg
 

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:24
Joined
Apr 1, 2019
Messages
712
MajP, Got you, will give it a go. Thanks. Any comments on my table design/relationships?
 

zeroaccess

Active member
Local time
Today, 05:24
Joined
Jan 30, 2020
Messages
671
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

To train a dog, first know more than the dog..
Local time
Yesterday, 23:24
Joined
Apr 1, 2019
Messages
712
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

Active member
Local time
Today, 05:24
Joined
Jan 30, 2020
Messages
671
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

To train a dog, first know more than the dog..
Local time
Yesterday, 23:24
Joined
Apr 1, 2019
Messages
712
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

Active member
Local time
Today, 05:24
Joined
Jan 30, 2020
Messages
671
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

To train a dog, first know more than the dog..
Local time
Yesterday, 23:24
Joined
Apr 1, 2019
Messages
712
Zeroaccess, never thought of that. Will give it a go.
 

mike60smart

Registered User.
Local time
Today, 10:24
Joined
Aug 6, 2017
Messages
1,899
See if this helps
 

Attachments

  • Trainingv3.zip
    145.2 KB · Views: 84

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:24
Joined
Apr 1, 2019
Messages
712
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

To train a dog, first know more than the dog..
Local time
Yesterday, 23:24
Joined
Apr 1, 2019
Messages
712
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

Registered User.
Local time
Today, 10:24
Joined
Aug 6, 2017
Messages
1,899
Hi See if the amended db solves the problem
 

Attachments

  • Trainingv3.zip
    153.4 KB · Views: 145

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:24
Joined
Apr 1, 2019
Messages
712
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

Registered User.
Local time
Today, 10:24
Joined
Aug 6, 2017
Messages
1,899
Hi

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

HillTJ

To train a dog, first know more than the dog..
Local time
Yesterday, 23:24
Joined
Apr 1, 2019
Messages
712
Cool, i'm really grateful.
 

Users who are viewing this thread

Top Bottom