Updating fields in a junction table

Topic

New Reply

I have a form which allows users to enter details of a case. Each case can apply to multiple periods of offence and each period of offence could apply to multiple cases. As such, I have created three tables – one table called tblCase, another table called tblOffencePeriods and another table called tblCaseOffencePeriodLine. Each offence period can relate to different types of charges, so another table called tblOffence is linked into tblOffencePeriods to show what offence that particular period relates to.

In the form where the user can enter the case details, the record source of this form is based solely on tblCase. I then have a drop down box on the form which allows the user to enter the type of offence. This drop down box is based on table tblOffenceType. This table has a field which tells the database what offence it is (linked through another table called tblOffence, which in turn is linked through to tblOffencePeriods). So depending on what offence type the user chooses, I’m filling in a list box on the form with the different periods that relate to that type of offence.

What I’d like to do now is let the user choose the offence periods that relate to this case and then save the case ID and offence period ID to the tblCaseOffencePeriodLine. That way I can tell what periods of offence relate to that case. I’m unsure of how I achieve this. Should I have created a sub-form somewhere? I also want it so that when the user reopens the form, the periods they chose from the listbox are already highlighted.

Cheers,

Reply | Quote

Replies

I find it hard to get my head around all of this.

The usual way values in junction tables are entered (other than via the user choosing something) is via the Master and Child link fields in a subform. Often the Child field on the subform is hidden but its value in new records is automatically filled in.

Can you post a sample database to look at? or at least a screen capture of the Relationship Diagram?

Reply | Quote

Hi John,

I thought it might be a bit hard to explain in an email. I’ve attached a relationship diagram. I’m quite new to access, so maybe my relationships aren’t set up correctly? Hope this helps.

Cheers,
Jason

Reply | Quote

Ok, I’ve gotten a little further with this. I’ve added a sub-form based on the tblCaseOffencePeriodLine table. This allows users to choose the offence period and it automatically fills in the related case ID, which is what I want. However I want to restrict the periods they can select from based on the type of offence they chose on the form. Eg, Offence 1 might relate to periods 1999, 2000, 2001. Offence 2 might relate to periods ‘Quarter ending March 2010’, ‘Quarter ending June 2010’. So as they fill in the offence type on the form, I want to restrict the values they see on the sub-form. Does that make sense? Is this possible?

Cheers,
Jason

Reply | Quote

However I want to restrict the periods they can select from based on the type of offence they chose on the form. Eg, Offence 1 might relate to periods 1999, 2000, 2001. Offence 2 might relate to periods ‘Quarter ending March 2010’, ‘Quarter ending June 2010’. So as they fill in the offence type on the form, I want to restrict the values they see on the sub-form. Does that make sense? Is this possible?

The general answer to this is that you need to use the After Update event on the control to requery the control where you choose the Period so that it only shows the Periods applicable to that Offence. (Then repeat the code in the OnCurrent Event – probably)
How will it know what periods apply to what offences? Will that information be in a table somewhere?

Reply | Quote