Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Close record (2003 SP2)

    I'm looking at a basic DB, where I'd like users to be able to click a button (on a continuous form by each record), that would populate a time & date field then close the record (in effect hide it from view).

    Trouble is, I'm not sure how best to do it.

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

    Re: Close record (2003 SP2)

    Should records be hidden when the date/time field is populated, and displayed if the field is blank?

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Close record (2003 SP2)

    That's exactly right, Hans.

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

    Re: Close record (2003 SP2)

    Create a query based on your table (or query) with <code>Is Not Null</code> in the Criteria line for the date/time field.
    Save this query and use it as record source for your continuous form.
    The On Click code for the command button could look like this (substituting the correct names, of course)

    Private Sub cmdSetDate_Click()
    Me,DateTimeField = Date ' or Now
    Me.Requery
    End Sub

    Use Date if you want to set the field to the current date (e.g. 06-Jul-2006), or Now if you want to set it to the current date and time (e.g. 06-Jul-2006 12:41).

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Close record (2003 SP2)

    Two things Hans; I'm guessing you meant the query criteria should be 'is null' & I'm getting a compile error (because I don't have a clue with VBA).

    The date/time field I wish to populate is [closed].

    Here's the code;
    Private Sub Command32_Click()
    On Error GoTo Err_Command32_Click
    Me,closed = Now()
    Me.Requery

    Exit_Command32_Click:
    Exit Sub

    Err_Command32_Click:
    MsgBox Err.Description
    Resume Exit_Command32_Click

    End Sub

    Never mind, I just spotted the comma instead of a full stop (period). Everthing works now.

    Many thanks.

  6. #6
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Close record (2003 SP2)

    Ah, just come up with a new issue, resulting from using a query as a record source; the data is stored in one main table, which is related to another (reference table). By using a query, I'm now unable to 'add' records, but if I use a sub-form I can't have continuous forms.

    Any ideas?

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

    Re: Close record (2003 SP2)

    Yes, I meant Is Null. (Sorry, it's all air code)

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

    Re: Close record (2003 SP2)

    Just adding the Is Null criteria to a query shouldn't make a difference as to whether you can add records. I'm afraid I don't understand your setup.

  9. #9
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Close record (2003 SP2)

    I don't think it's the null, but the second table, that's giving me the problem. However, here's a (very untidy) example, that I've been playing with.

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

    Re: Close record (2003 SP2)

    What you want is impossible. There can be multiple records in tblProg for a single record in tblMain, so if you want a continuous form, you shouldn't bring tblProg into it.

  11. #11
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Close record (2003 SP2)

    I did wonder about that.

    I would like to be able to display the settings from TblProg (duration & temp

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

    Re: Close record (2003 SP2)

    There could be a variable number of records in tblProg belonging to a single record in tblMain. How would you want to display them?
    If you really need this, switch to a single form based on tblMain only (or a query based on tblMain) with a continuous subform based on tblProg.

  13. #13
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Close record (2003 SP2)

    I think I'll go for a seperate 'data entry form', as I'd really like to keep the TblMain records on a continuous form.

    Thanks once more.

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

    Re: Close record (2003 SP2)

    That's a good workaround.

Posting Permissions

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