Form design question

Topic

New Reply

Hi,

I have a form which allows users to enter details of a ‘case’. This form allows the user to select which offence this case relates to and then select the ‘periods’ of the offence (Eg, quarter ending March 2009, quarter ending June 2009). The offence period details are kept in their own table. The offence periods are presented as a listbox in a sub-form from which users can select or unselect 1 or more periods. The listbox is not ‘bound’ to anything, but rather the values are just populated by running a query. When the user saves the case, the periods the user selected, along with the case number, are written to a separate table.

The way I’ve got it set up at the moment, the user can only select and unselect the periods, they cannot edit the ‘name’ of the period. However each period also has a due date and an offence date. These dates do have a default date put in, however in the majority of cases this changes and so I would like the user to be able to enter it themselves. By presenting it as a listbox, the user is unable to do this. I don’t want to change the offence or due date in the offence period table, I just want them to be able to update it in the sub-form and write it to a separate table when the user saves the case.

I was thinking I could create another form and have it based on the offence period table, and then add the fields I need (Eg, offence period, offence due date, offence date), and also have a checkbox for each period which they could check on or off to select or unselect, but wouldn’t this mean they would be changing the values in the underlying table when they change the date?

Not sure how to go about this….

Cheers,
Jason

Reply | Quote

Replies

So you are creating records in tblCaseOffencePeriodLine, using a Listbox drawing values from tblOffencePeriod? Is that right?

This is the diagram you posted recently. Have you now added more fields to tblOffencePeriodLine?

Reply | Quote

Hi John,

I’ve changed the design slightly because each case is now able to have multiple offences. Please see the new attachment for the updated design. It still works basically the same way in that I would still create records in tblCaseOffencePeriodLine using a Listbox drawing values from tblOffencePeriod. I haven’t added more fields to the tblCaseOffencePeriodLine but would need to add two more fields to hold offence date and due date.

Cheers,

Reply | Quote

I just want them to be able to update it in the sub-form and write it to a separate table when the user saves the case.

I was thinking I could create another form and have it based on the offence period table, and then add the fields I need (Eg, offence period, offence due date, offence date), and also have a checkbox for each period which they could check on or off to select or unselect, but wouldn’t this mean they would be changing the values in the underlying table when they change the date?

Which is the ‘separate table’ you want this stuff written to? If it is tblOffencePeriodLine, then it will need fields for these dates you want the capacity to change.

I think it is difficult to do this with a listbox. the ‘standard solution’ is a subform bound to tblOffencePeriodLine, with a combo box to select an OffencePeriodID. When you make a selection values from tblOffencePeriods are copied to similarly named fields in tblOffencePeriodLine, where you can change them if needed.

Reply | Quote

Hi Jason

You might like to change your table design to a simple one to many Case —>> Offence structure, with Offence_Period calculated from one of the offence dates or a lookup field in the Offence table.

Regards

Reply | Quote