Tab Control Subform - Foreign Keys Not recorded in repsective tables

[Giles64]

Hi
I am an access novice and would appreciate some help please. Bear with me if I have not shown correct protocol and/or terminology.
I am trying to set up a database where each record in tblSDSRegister can have multiple Risk Assessments conducted throughout the life of the product. The physical Risk Assessment form these tables are based on has lots of controls so I have made 5 tables with a foreign key relating back to tblRiskAssessment.
I have placed the 5 subforms on a tab control – so these individual pages will form one record once brought back together. The 5 tables are named tblSDSRequest, tblSDSDetails, tblSDSHealthEffect, tblSDSControlMeasures, tblSDSEvaluation
Each Risk Assessment can only have a maximum of one entry in each of the 5 related tables. I do not seem to be able to get the relationship right. I thought I needed a junction table to synchronise the tabbed subforms but I have confused myself so much I not sure anymore. The foreign keys are not being recorded in the respective tables? Please see attached.
I have tried using one table and a single form and one table with multiple subforms, each has their own problems unfortunately.
I have no experience in coding at all, so please be patient and explain how and why, that would be much appreciated.
I am hoping someone can offer some guidance as to how best to approach this, thanks in advance.

[mike60smart]

Hi

If you add a field named "AssessmentDate" to the table "tblSDSRiskAssessment"

This field needs to be populated before you can add data to all the tables in the Tab Control.

[Gicu]

Please review this updated file. I have changed the relationships, changed the Details table (removed one of each pair of mutually exclusive fields - left the last one for you - ShedulePoison) and update the subform to use option groups instead of independent check-boxes, etc.
You will see that I moved the Reviewed By to the Risk Assessment table to give you a field to enter to force the creation of the new PK (the tab is disabled in code until you have a valid SDSRAID_PK). I had to change the combo for the ReviewedBy into a textbox as you haven't included its row source table(s). Note that in that combo you are correctly storing the PersonId but in the bound table you set up the field as text, should be Number (Long).

Cheers,

[ssanfu]

I would suggest you stop and evaluate the table designs. It looks like at least 4 of the tables are converted from or designed like spreadsheets.

Examples:

tblSDSControlMeasures (currently 22 fields /should be 4 fields)
-----------------------
SDSCMID_PK
SDSRAID_FK
ControlMeasureID_FK ..Link to tblControlMeasureTypes (Ventilation, Spillage, Air Monitor, Waste, Enviro, PPE, Transport, ...) <- this is data; not attributes
Remark (or Comment)


tblSDSDetails (currently 9 fields / should be 4 fields)
-------------
SDSDID_PK
SDSRAID_FK
ClassID_FK ................Link to tblClassTypes (Design For Purpose, Classified Hazardous, Classified Dangerous Goods) <- this is data; not attributes
ScheduleTypeID_FK . ...Link to tblScheduleTypes (Schedule Poison, NonSchedule Poison)


tblSDSHealthEffects
-------------------
SDSHEID_PK
SDSRAID_FK
ExposureTypeID_FK ....Link to tblExposureTypes (Skin, Eyes, Inhalation,.....) <- this is data; not attributes
Remark

tblSDSEvaluation
----------------
SDSEID_PK
SDSRAID_FK
ApprovedByID_FK ......Link to tblApprovers
AssessorID_FK ..........Link to tblAssessors
RiskEvaluationID_FK ...Link to tblRiskEvaluationTypes (Significant Remain So, Significant Controlled, Significant Improve Controls)
RiskComment
AssessorDate
ApprovalDate


(7 new tables - in magenta)


The redesign of table "tblSDSControlMeasures" (and the other tables) is because if you want to add a new control measure, you would have to change the design of the table, change all of the involved queries, change the design of the forms involved, change the design of reports and any code that uses that table. If the table is designed correctly, all that is involved it adding a new control measure to a table. Done!


I am not sure about the purpose of table "tblSDSRiskAssessment". I would replace it with table "tblSDSRequest".



*********************

Granted, I know nothing of the business you are in, but you should not have tables designed like spreadsheets.....
These are just my observations....


Good luck with your project.....

[Giles64]

Thanks for the quick response, I will adjust the independent check boxes.
FYI – combo box recordsource is below – so it should be alright to reinstate that?
SELECT [Personnel Record].ID, [Personnel Record].Surname & "," & [Personnel Record].[First Name] AS Expr1, [Personnel Record].[Internal Workforce] FROM [Personnel Record] WHERE ((([Personnel Record].[Internal Workforce])=Yes)) ORDER BY [Personnel Record].Surname & "," & [Personnel Record].[First Name];
As each record can have multiple risk assessments equally each risk assessment may be reviewed several times, so I have previously pulled current risk assessment details by creating a query which shows the most current “ReviewDate” and “NextReview” then I have created a new query and incorporated other fields from those 5 tables, however that is not working and I’m sure it is due to the change of relationships, would you be able to show me what I have wrong please?
qrySDSRegisterCurrent should show
SDSMRID_PK should equal 2 and SDSRAID_PK = 4, but the records show 1 and 3 respectively
As the setup has changed I must be missing something else?? I have attached sample with queries.

I also take it that the Risk Assessment table does not require the foreign keys of the 5 tables?

Your help is much appreciated, thanks for the quick response

[Giles64]

Hi Vlad thanks for your help however I am having trouble still - I thought I responded to your original post but not sure, can you look at post 5, that is me again.
Also I am finding an issue when you create a new record in FrmSDSRegister the relationship seems to disappear??

[mike60smart]

Hi Giles

I have colour coded the links between Pk and FK

Creating a new record appears to work just fine

[mike60smart]

Sorry here with the updated Db

[Giles64]

Thank you, your are quite right, not sure what I was doing. I have however come across another problem where the qrySDSRegister does not display all records. I'm not sure whether I have missed some "refreshing" as I added a "NextReview"date on tblSDSMonitorReview for record 4, I have tried by adding it through the table and also the form but the record does not come through on the query, help would be much appreciated.

[Giles64]

Thank you, your are quite right, not sure what I was doing. I have however come across another problem where the qrySDSRegister does not display all records. I'm not sure whether I have missed some "refreshing" as I added a "NextReview"date on tblSDSMonitorReview for record 4, I have tried by adding it through the table and also the form but the record does not come through on the query, help would be much appreciated.

[mike60smart]

Hi Greg

Look at the revised query in the attached.

[Giles64]

Hi
Thanks, but it now shows all records, I was wanting only the most recent record for each SDSNoID_PK which is "currently used", so I would like to see from the query the details of SDSNoID_PK 1 = SDSMRID_PK 2 and SDSRAID_PK 4, SDSNoID_PK 2 = SDSMRID_PK 4 and SDSRAID_PK 6, SDSNoID_PK 3 = SDSMRID_PK 5 and SDSRAID_PK 8. I thought the way to achieve this was to combine the first query qryCurrentReviewDate which gives me those records but i run into trouble when I try and get details from all the other tables - surely it can only be the relationships within the query but I am failing to see it, any help would be appreciated. Thanks again

[Gicu]

You need to join the query returning the latest review date (max) to the query on both the ID and the date fields. Maybe have a look at the query, I hope that is what you're after.
As for the combo you can't reinstate it in the sample because you haven't included the actual table (Personnel Record), it should work OK in your actual db.

Cheers,

[Giles64]

Excellent! Thank you so much to all of you for your help.