Please help I am at a loss!!

[dancat]

I am a complete novice at access and would appreciate any help i could get.

I have attached samples of the files i am working on, there are 2 tables and 1 form, what i am trying to do is auto fill fields based on the info from the risk types table into the risk assessment form, however after struggling for weeks i am at a loss.

what i would like is after selecting the options at the top of the form, the area below which asks for description and then proposed control, further action and residual risk to be filled in by when choosing what the risk description is the other boxes fill in automatically.

Please help

 

[JANR]

This db was hard to figure out, the sample you posted has several issues besides your question.
First the table Risk Assesment looks very DE-normalized it look's like a excel-flatfile. You should normalize
this table like taking out all the description1,ProposedControl1 etc into a seperate table.
If your current setup is ever going to work you would have to use A LOT of Dlookup's and it's a nightmare.
You will also store repeat data which is a big no-no. The db only provideded the subform to your main form so
I can't give you a correct syntax for all the lookup's but here is a general overview of your problem.

Private Sub cboRiskID_AfterUpdate()
Me.Description1 = DLookup("RiskType", "[Risk Types]", "[RiskTypeID]=" & Me.cboRiskID)
 Me.ProposedControl1 = DLookup("ProposedControl", "[Risk Types]", "[RiskTypeID]=" & Me.cboRiskID)
 Me.FurtherAction1 = DLookup("FurtherAction", "[Risk Types]", "[RiskTypeID]=" & Me.cboRiskID)
 Me.ResidualRisk1 = DLookup("ResidualRisk", "[Risk Types]", "[RiskTypeID]=" & Me.cboRiskID)
etc....
etc....
End Sub

As you see you have to add more Dlookup's for the remaining field's and it is not a proper way to do what
is best. The "& Me.cboRiskID" I put in is a combobox on the form which looks in the "Risk Types" table to get
RecordID of your lookup-table.
The best option for you is to re-evaluate you're table structure and dataflow before you design your forms.


JR

 

[dancat]

sorry i know i am a bit thick, as i stated earlier i dont know a great deal about access and i have never used statements like the ones you mentioned, i have tided up the example slightly, what i want is if risktype1 looks up an id, then description and other fields are filled in automatically, i have removed exising fields, as i do not know if they where the correct ones to add, the form this subform is taken from is called risk assesment. What i am looking for is about 6 risk types that can be selected an corresponding fields populated, i have added 1 to the form but maybe this to is the inorrect way to do it.


Again thanks in advance

 

[JANR]

I'll have a looksie and post back to you

JR

 

[JANR]

I have added a lookup subform on your form Risk Assessment Subform which is linked to the combobox RiskType1. When
You select a risktype the subform populates for viewing ONLY. The ID number for the diffrent types of risks is stored in you main table so you have a more normalized tablestructure. I'v also changed your relationship to 1 to many.
That's how far I could get with the sample you posted.

JR

 

[dancat]

thankyou very much for your help i will take a look today.