Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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?

    [attachment=90350:Offences.gif]
    Attached Images Attached Images
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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,
    Attached Files Attached Files

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by Jason View Post
    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.
    Regards
    John



  5. #5
    New Lounger
    Join Date
    Oct 2010
    Location
    Canberra ACT Australia
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •