Page 1 of 4 123 ... LastLast
Results 1 to 15 of 51
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    User form for specifying months (2000)

    Hi,
    Wonder if anyone would be kind enough to help. I'm working on a database where the user needs to record the length that a record needs to be kept for in the physical sense.
    For arguments sake I have 3 fields:
    DateOfFinalisation
    LengthOfArchive
    DestructionDate
    I'd like the user to simply select the LengthOfArchive in months. But I also want this to be displayed in years as a non-editable sub text that is updated if the user changes the value. Ideally I'd like this to be in a pop-up box activated on the On-Click function in the text box of LengthOfArchive, similar to a calendar type thing. The reason behind this is that the majority of Archived cases are recorded in months, with around 20% being recorded in years. For something like a period of 30 years archive, the user isn't going to want to work out the value in months. This is mainly so that the format is specific to what I want as opposed to what the user enters. I know I could create a table with 1 column in months and another in years and so on, but for values up to 30, this is going to be around 720 records.
    This then needs to be added to DateOfFinalisation and displayed in DestructionDate...................
    Think that makes sense.

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

    Re: User form for specifying months (2000)

    What do you want the user to see for an archive period of one and a half years?
    (a) 18 months
    ([img]/forums/images/smilies/cool.gif[/img] 1 year 6 months

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

    Re: User form for specifying months (2000)

    I would ideally like them to select 18 months, but something similar to a label to be displayed with the period in years, i.e. 1yr 6 months. More as confirmation that they have entered the correct period of time, i.e. 25yrs 6 months will be cumbersome to work out in months.
    As an afterthought, it wouldn't really matter if they had two combo boxes, one with years, one with months, the only thing that I am set in stone about is that the format has to be specific so that users cannot enter variants of the same thing.
    I'm unsure also of how to convert, say 18mnths to adding 18 months to the FinalisationDate.......................

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

    Re: User form for specifying months (2000)

    Will the user ever be allowed to edit the DestructionDate directly? If not, you don't need it in the table, it can be a calculated field in a query or on the form.

    I would use a combo box to enter the number of years (0 ... 30) and another combo box to enter the number of months (0 ... 11). You can then calculate the destruction date from the values of the combo boxes and the finalisation date:

    =DateAdd("m",12*Nz([cboYears],0)+Nz([cboMonths],0),[txtFinalisationDate])

    See attached demo.
    Attached Files Attached Files

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

    Re: User form for specifying months (2000)

    Hi Hans,
    No that would be fine, the premise is that the user cannot alter the destruction date or alter the format of the archive length.
    With regards to whether the use needs the destruction date I think this would be yes. Every month a report will be printed out detailing what files are to be binned dependant upon whether the Destruction date is less than Now()..........
    Thanks again for your help.

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

    Re: User form for specifying months (2000)

    If your table tblArchive contains fields FinalisationDate, ArchiveYears and ArchiveMonths, you can create a calculated column DestructionDate in a query based on tblArchive. It will then be available for displaying on forms and reports, and for filtering, for example on DestructionDate < Date()

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

    Re: User form for specifying months (2000)

    I have a field called LengthSentence, is it possible to update and concatenate the fields from cbxYear and cbxMonths into one field. So it field LengthSentence will equal [cbxYear] & " Years " & [cbxMonths] & " Months", and this field to be carried across to the main table.
    I presume that on a more general note that if I wish to populate a field with a figure calculated within a form that I will need to use an after update code to do this.
    My form currently has a field LengthArchive that incorporates Years & Months hence the above query. Essentially this is solely aesthetic and of is no use to the user, but needs to be recorded for paper purposes.
    The only thing important is the Destruction date which is calculated through the =dateadd("m",12*Nz([cbxYear],0)+Nz([cbxMonths]),0)[txtFinalisationDate]).

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

    Re: User form for specifying months (2000)

    There is no need to have a field LengthSentence in your table; you should use fields SentenceYears and SentenceMonths (or similar names) in the table, and set the control source of cbxYear and cbxMonths to these fields. Just as with the destruction date, you can create a calculated column

    LengthSentence: [cbxYear] & " Years " & [cbxMonths] & " Months"

    in a query, or a text box with control source

    =[cbxYear] & " Years " & [cbxMonths] & " Months"

    on a form / report. The idea is to store only essential information in the table itself, and calculate all information derived from it in a query or in an expression.

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

    Re: User form for specifying months (2000)

    Again Hans, I bow to your superior knowledge and have seen the error of my ways........... <img src=/S/grin.gif border=0 alt=grin width=15 height=15> . Of course this way works a whole lot better.
    I have another thing I'm unsure of how to approach.
    I've attached the database that I'm working on for reference purposes.
    I have a form called frmEmptyBoxes based on a query that filters all the empty boxes for filing purposes (I've previously covered this in another post). This is different to FrmArchiveMain as this has no limitations on it.
    The idea is that due to the fact that files cannot be allocated a filing box number due to the variation of sizes of the files, that the user will enter details in the actual space as to the size available. Therefore FrmEmptyBoxes will effectively be a search facility for the user to scan through to find an appropriate filing space for the file.
    Now the bit I'm stuck on..............I'm not too sure how to add a record specifically to this box from this form. I know I could add a subform to this form from TblArchive but I didn't quite want to do it this way. I'd rather the user click on a Add File control and then be taken to FrmArchiveMain, box number..............it must sound desperately simple, but I'm unsure of the coding, presumably on the On_Click event of the Add File control.

    Another thing that I can see myself having problems with is when a user deletes a record from FrmArchiveMain with a delete record control (not added yet!) I would like them to enter details as to the space being made available in the box through a confirmation process, similar to:

    Are you sure you want to delete this record? and then
    Please enter details of space vacated.......all being compulsory.

    Again this would hold its own issues. If the box already had a space, the user would edit the current entry (i.e. in notes, tblboxnumbers).
    But then the BoxFull value in TblBoxNumbers will need to be changed to false or left how it is dependant on the value in Notes.
    Also when the user marks a box as full in ArchiveMain then this would need to make the value of Boxfull true (which isn't a problem) but also clear the value in notes as the space isn't available............

    Something about a steep learining curve comes to mind.......................
    Attached Files Attached Files

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

    Re: User form for specifying months (2000)

    I think you are making things unnecessarily complicated. I would use one form, with options to view all boxes or just boxes with available space.

    I don't understand how the subform on frmArchiveMain (confusingly also named frmArchiveMain) works - the user cannot move from record to record, so (s)he has no way of knowing how many files (records in the subform) there are.

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

    Re: User form for specifying months (2000)

    I hadn't noticed that I had named the sub form the same as the main form, so this has been changed.
    Essentially what I was after was for the user to have a seperate form just detailing the empty boxes with no file details in as it will be the space available through the field Notes, that they'd be interested in.
    For instance, Box 42 reads Half full, Box 43 reads Empty, the file will take up an entire box, so they use box 43 and enter the file into this one.
    The FrmArchiveMain with FrmArchiveMainSub I would like to display all records so that if the user is searching for a specific file, combo boxes can be used to find the record. I haven't yet entered this or placed navigation controls on the sub form.
    Hence my previous post.............
    My guideline for this database are a bit fuzzy generally, and I'm trying to organise it as best I can.
    1) The user can search easily for empty boxes and view their contents. Then entering file data into this empty box. Marking down whether the box is full after the entry has been entered. A problem may arise similar to point 2a, but from the reverse side.
    2) The user can search for archived files, find the specific box number and then destroy the file or temporarily remove it for queries. With the last point, I've been asked whether I can change the colour of the box or text to signify this..........probably a check box or similar I thought in FrmArchiveSub.
    a) Destroying files will be based on a delete record control. My concern with this is that the user will not alter the value of BoxFull to False and therefore alot of boxes will be marked as full, whereas in reality their not.

    I think I'm appoaching it from the correct angle..................or not.

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

    Re: User form for specifying months (2000)

    I myself wouldn't use two separate forms, but it is up to you to decide that. You could put a command button cmdAdd with caption "Add File" to frmEmptyBoxes. The following event procedures make sure that the button is only enabled if a box is not full:

    Private Sub Form_Current()
    Me.cmdAdd.Enabled = Not Me.Boxfull
    End Sub

    Private Sub Text7_AfterUpdate() 'Text7 is bound to the Yes/No field BoxFull
    Me.cmdAdd.Enabled = Not Me.Boxfull
    End Sub

    The On Click code for the command button looks like this:

    Private Sub cmdAdd_Click()
    DoCmd.OpenForm "frmArchiveMain", , , "BoxNumber = " & Me.Boxnumber, , acDialog
    Me.Notes.SetFocus
    Me.cmdAdd.Enabled = Not Me.BoxFull
    End Sub

    I don't think you can automate much when adding a new file to a box, since Access cannot decide whether the box will be full. But when the user sets Boxfull to True, you can set the Notes field to Null if you like.

    When removing a file from a box, you can set Boxfull to False in code, since you know for sure that removing a file will leave some empty space.

    You can set the validation rule for the TblBoxNumbers table to
    [Notes] Is Not Null Or [Boxfull]
    This will force the user to enter notes if the box is not full.

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

    Re: User form for specifying months (2000)

    I think that covers everything.
    With regards to:

    I don't think you can automate much when adding a new file to a box, since Access cannot decide whether the box will be full. But when the user sets Boxfull to True, you can set the Notes field to Null if you like.

    Could a vb pop-up be executed to question the user as to whether this will make the box full?

    Private Sub cmdAdd_Click()
    Dim strSQL As String

    On Error GoTo ErrHandler

    If MsgBox("Will this box be made full?", _
    vbQuestion + vbYesNo) = vbNo Then
    Exit Sub
    End If

    strSQL = "UPDATE tblBoxnumbers SET Boxfull = True "
    DoCmd.RunSQL strSQL ' or CurrentDb.Execute strSQL

    Exit Sub

    ErrHandler:
    If Err <> 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    DoCmd.OpenForm "frmArchiveMain", , , "BoxNumber = " & Me.Boxnumber, , acDialog
    Me.Notes.SetFocus
    Me.cmdAdd.Enabled = Not Me.BoxFull
    End Sub

    or similar............................


    [i]When removing a file from a box, you can set Boxfull to False in code, since you know for sure that removing a file will leave some empty space.[i/]

    Presumably this would be within the delete record control in FrmArchiveMain. My coding might be a problem here..........sorry.
    I presume that this would also not apply if a space already existed within this box.

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

    Re: User form for specifying months (2000)

    Don't use strSQL = "UPDATE tblBoxnumbers SET Boxfull = True". That would set Boxfull to True for ALL 144 boxes <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>
    In fact, you don't need an SQL update statement here, since you only want to set it for the current record. Just include an instruction

    Me.Boxfull = True

    I wouldn't do this from frmEmptyBoxes, but from the form where the user actually enters the information about the file being added. But it is possible to do it from frmEmptyBoxes.

    Don't put the DoCmd.OpenForm after the error handling section; it must be before Exit Sub.

    <img src=/w3timages/blueline.gif width=33% height=2>

    In the code for the "Remove file" button, just set BoxFull on the main form to False; if it was False already, no harm is done.

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

    Re: User form for specifying months (2000)

    Sorry another question.
    I've used the code:
    Private Sub cmdAdd_Click()

    DoCmd.OpenForm "frmArchiveMain", , , "BoxNumber=" & Me.Boxnumber, , acDialog

    If MsgBox("Will this box be made full?", _
    vbQuestion + vbYesNo) = vbNo Then
    Exit Sub
    End If
    Me.Boxfull = True
    On Error GoTo ErrHandler

    Exit Sub

    ErrHandler:
    If Err <> 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    DoCmd.OpenForm "frmArchiveMain", , , "BoxNumber = " & Me.Boxnumber, , acDialog
    Me.Notes.SetFocus
    Me.cmdAdd.Enabled = Not Me.Boxfull

    Me.Notes.SetFocus
    Me.cmdAdd.Enabled = Not Me.Boxfull

    End Sub

    Which seems to work fine on the exit of FrmArchiveMain. I think that this would work ok as there is no facility for the user to add a new record here. I'm going to see how this works in practice being dealt with via FrmEmptyBoxes.

    The only problem being that this doesn't display a blank record in FrmArchiveMain for the user to enter data onto. Just the previous record.

Page 1 of 4 123 ... LastLast

Posting Permissions

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