Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting value in subform (2000)

    Hi,
    I have a Main form (Archive) with a subform included (ArchiveSub).
    In the main form I have a list box (LongTermArchive), that has a Yes/No value, I would like to update the value of BoxNumber in ArchiveSub to 0 (zero) if the value in LongTermArchive is Yes. Being somewhat useless at VB, I am stuck (although I do know it needs to go in the After Update event in the ComboBox (cboLongTerm)).
    Could some kind soul lend a hand!
    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Setting value in subform (2000)

    That doesn't make sense to me.
    - I don't think the BoxNumber field should be set to 0, since that is not a valid box number if I remember correctly.
    - The subform can contain multiple records, should all be modified?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting value in subform (2000)

    No It doesn't make sense to me either!!
    In my eyes each file should be allocated a box number. They are insistant that the files that have an unallocated destruction date, i.e. if the subject of the file has been sectioned under the mental health act (which is indefinite), these should go in their own seperate boxes (with unallocated numbers). Although they still want the file details to be displayed on this database.
    I decided the best way for this would be to allocate the other filing system a box number of 0 (zero) as the field BoxNumber in TblBoxNumbers is numerical. I have entered 0 as a box number onto TblBoxNumbers.
    As the user will not be expected to record a box number when they input a new record, couldn't the box number be set too zero?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Setting value in subform (2000)

    There can only be one record for a given URN with BoxNumber = 0, so you won't be able to register that an URN is stored in more than one unallocated box.

    If a file has already been assigned more than one box number, and if the user then sets LongTermArchive to Yes, you have a problem: you can't set the box number for all those boxes to 0.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting value in subform (2000)

    In reality, box number 0 is an entire section of filing. The set up of the database is such that a single box can contain multiple URN's and also a URN can cover multiple boxes.
    With the current system a person doesn't allocate a Long Term archive file into a box, instead they place it into the Long Term Section of the archive (physically). The file is then recorded onto the old excel log, but with no box number, just a 'Yes' in the column Long Term.
    This section is quite a limited stock, and around 5 to 8 files are added annually so I presume that this is why its been done this way. With the current practice, the user will not allocate a box number, although the file always goes into the specific area.
    I've placed a box zero, to cover this area, effectively treating it like a box number that all Long Term Files go into. My presumption is that this will remain an infinite space until they decide to database it.
    I suppose that essentially it will not matter if the user doesnt enter a box number. I have reports that produce a list of files where the destruction date has expired. As these files (Long Term) have no destruction date, these are filtered by the value of LongTermArchive. I felt that it would be more beneficial to have a box number allocated than none, but if no box number will have any effect then this will work okay as well.
    I think thats probably answered my question!!!!!!!! Thanks for your continued patience throughout this ordeal! <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting value in subform (2000)

    Oops, it would appear that I'm going to need to allocate a file number to these Long Term Archive files.
    Been fiddling with the tables to see if I can create entries without box numbers and as TblURNBox is based on the two primary keys of BoxNumber in TblBoxNumbers and URN in TblArchive a number is needed for each file. The Italicised part that I'm unsure of how to do.

    Currently I've got this coding behind the After Update event of the combobox

    Private Sub cboLongTerm_AfterUpdate()
    If [cboLongTerm] = True Then
    'Set box number to zero in FrmArchiveSub
    MsgBox "Do not allocate a box!", vbOKOnly
    End If
    End Sub

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Setting value in subform (2000)

    See my question higher up in this thread. If the user tries to set long term to Yes for a file that already has multiple boxes assigned, you have a conflict. How do you plan to handle this?

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting value in subform (2000)

    The file once recorded on the log shouldn't be amended until the file is deleted. I've duplicated FrmArchive into a FrmDatabase. FrmArchive has alterable values in each field. FrmDatabase is purely so that the user can view the database.
    I have a start form that has several controls in it. First control opens FrmArchive, the user is asked by a MsgBox "Are you sure you wish to add/amend data?", Yes opens the form.
    Another control opens FrmDatabase, no vb message.

    I would hope that the user realises that any existing data in FrmArchive is not to be changed therefore the issue of changing a URN with multiple boxes wouldn't apply. Box 0 would essentially be an endless space. The user when registering a file will at that point be allocating a box number. Once registered the user will not need to amend the record, unless deleting.

    I can see the issue you are raising could be problematic. But apart from the above, I'm not too sure how best to approach it in any other way.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Setting value in subform (2000)

    It's a strange design, but OK. Try this:

    Private Sub cboLongTerm_AfterUpdate()
    If Me!cboLongTerm = True Then
    'Set box number to zero in FrmArchiveSub
    Me!FrmArchiveSub!BoxNumber = 0
    MsgBox "Do not allocate a box!", vbOKOnly
    End If
    End Sub

    This will set the BoxNumber control to 0 in whatever is the current record in the subform - if your description is correct, there should only be the single new record in the subform.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting value in subform (2000)

    Thanks Hans. The design has altered a bit since previous posts, but it does seem to work okay and the end result does the job well.
    Thankyou for all your help, really would have been stuck (about 3 weeks ago!) without it. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Posting Permissions

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