Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2011
    Posts
    6

    Smile Unanswered: Check Box on Form

    hi
    I knew it would not be long before I'm asking another question.

    I have a Form it's tabular/continuous default view. It shows the user their workload schedule when they login in. However, I have created a check box on the form for the user to click on when they have completed a report and I want it to store the date & time in the "Timeout" field. I used the suggested code below from another post to get the date & time into the field but it does not work for me. Also, when I click on the check box it does not allow me to tick the box.

    What I am trying to achieve is to allow the user to click on the check box when they complete a report. Then I want to run an append query at the end of each day from this table for reports ticked, dated & time stamped into another table called Performance and at the end of each month this will be measured against the agreed day & time each report has to be sent out to the Business owners which also has these fields in the same table. After the append takes place I then want the check box cleared for the following week.

    I hope this make sense.

    Private Sub Timeout_AfterUpdate()
    If ckTimeOut = -1 Then
    TimeOut = Time()
    OutDate = VBA.Date
    ElseIf ckTimeOut = 0 Then
    TimeOut = ""
    OutDate = ""
    End If
    End Sub

    can anyone help?

    always appreciate the help.

    tx
    fiona

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you place an unbound control (here a CheckBox) on a continuous form, every instance of it will react synchronously (ie. In the case of a CheckBox, when you check or uncheck one they'll be all checked or unchecked).

    If you want to have one "true" instance of the control for each record on the form (ie. in the case of a CheckBox, only the one you check/uncheck changes its state while the others remain unaffected) you need to bound the control to a field of the form Recordset. You could add a "dummy" column: [ckTimeOut] to the table that is used as the form RecordSource, then use this code:
    Code:
    Private Sub ckTimeOut_AfterUpdate()
    
        If Me.ckTimeOut = True Then
            Me!TimeOut = Time()
            Me!OutDate = Date
        ElseIf Me.ckTimeOut = False Then
            Me!TimeOut = Null
            Me!OutDate = Null
        End If
        Me.Refresh
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Apr 2011
    Posts
    6

    Check Box on Form

    Hi
    thanks for reply I tried this did not work.
    I am working on the front end of the database in access 2003 is this a problem as its a linked table?

    appreciate the help

    tx
    fiona

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by db_fiona View Post
    ... I tried this did not work.
    I am working on the front end of the database in access 2003 is this a problem as its a linked table?
    No, the code works (I tested it with Access 2003 and Access 2007) with a local or with a linked table (external Access database or SQL Server).

    It would be easier if you could precise the problem(s) you encountered (error messages, error codes, etc.), "this did not work" does not mean anything helpful.
    Have a nice day!

  5. #5
    Join Date
    Apr 2011
    Posts
    6
    Hi

    thanks for the reply your right I did not explain this very well, apologies. I have a front end database which has tables linked and queries/forms set up in it. The back end just has the tables. I created a check box on a form named "Workload" in the front end and bound this to a field named "ckTimeout". On the property sheet under AfterUpdate I added an event procedure and put in the code you gave me. When I open the form and try to click on the check box it depresses but it does not tick.

    I then tried the same process in the back end database creating a query from the table and then a form from the query. I then added the check box in the same way as above and it allowed me to tick the box however, it updated the field in the table with the following date 29/12/1899.

    1) I don't understand why this check box does not work on the front end form.
    2) Why the field ckTimeout does not update with the correct date & time.

    Any help is much appreciated.

    tx fiona

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by db_fiona View Post
    I created a check box on a form named "Workload" in the front end and bound this to a field named "ckTimeout". On the property sheet under AfterUpdate I added an event procedure and put in the code you gave me. When I open the form and try to click on the check box it depresses but it does not tick.
    What's the data type of the field ckTimeout? It should be boolean if you bind a checkbox to it. A checkbox can represent 2 values (True or False), 3 at best (True/False/Null) if you allow it to be tri-state.
    Quote Originally Posted by db_fiona View Post
    I then tried the same process in the back end database creating a query from the table and then a form from the query. I then added the check box in the same way as above and it allowed me to tick the box however, it updated the field in the table with the following date 29/12/1899.
    I do not see any method to set the value of a boolean field to a date and/or a time (see above).
    Quote Originally Posted by db_fiona View Post
    1) I don't understand why this check box does not work on the front end form.
    I can imagine several reasons why it does not work but don't have enough information to determine the actual one.
    Quote Originally Posted by db_fiona View Post
    2) Why the field ckTimeout does not update with the correct date & time.
    Please read again your first post. You wrote that you wanted to set the value of a filed named TimeOut to the current time and the value of a field named OutDate to the current date:
    Private Sub Timeout_AfterUpdate()
    If ckTimeOut = -1 Then
    TimeOut = Time()
    OutDate = VBA.Date
    ElseIf ckTimeOut = 0 Then
    TimeOut = ""
    OutDate = ""
    End If
    End Sub
    Have a nice day!

Posting Permissions

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