Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2011
    Posts
    28

    Unanswered: Next Record Where CheckBox = False

    I've created a database for multiple users to work from.

    Basically a list of work cases is entered into the database at the start of the day. The fields are;

    Reference (Text)
    Work List (Text)
    Received Date (Date)
    Reference worked (Checkbox [yes/no])
    Date Worked (Date)
    Employee (Text)

    Then multiple users work references on an AutoForm of the above table, completing each record. The problem is they have to manually find the next record where checkbox is false.

    Is there anyway where each individual can just GoTo Next record where the "Reference Worked checkbox = false" using some sort of code on a command button?

    Many Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a solution ([Selected] = [Reference worked] and Check_Selected is the name of the checkbox associated with the column [Selected]):
    Code:
    Private Sub Check_Selected_AfterUpdate()
    
        Dim rst As DAO.Recordset
        
        If Me.Check_Selected = True Then
            Set rst = Me.RecordsetClone
            rst.Bookmark = Me.Bookmark
            Do
                rst.MoveNext
                If rst.EOF Then Exit Do
            Loop Until rst!Selected = False
            If rst.EOF = False Then Me.Bookmark = rst.Bookmark
        End If
        rst.Close
        Set rst = Nothing
            
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Before using Sinndho's code make sure that the Microsoft DAO Object Library is set in References (From a form's Code Module, go to Tool - References)
    Don't know what version of Access you're using, but in 2003 it would be Microsoft DAO 3.6 Object Library.


    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Mar 2011
    Posts
    28
    Thanks - this worked perfectly. I hope to add some comments and repost the code check my understanding of how it works - as i'm not very good at VB or coding.

    The only problem I have is when the last field for the day is complete, is there anyway I can incorportate a message to confirm work is complete.

    The error I get is "runtime error 91. Objective Variable or with block variable not set." I presume this is as all records are true and not that i've used the code incorrectly?

    Tahnks for you help

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You'll also get the Error 91 if the user decides, for whatever reason, to ignore the record that's retrieved and this not tick the checkbox. A couple of slight modifications (note bold code) should handle both of these problems:

    Code:
    Dim rst As DAO.Recordset
        
        If Me.Check_Selected = True Then
            Set rst = Me.RecordsetClone
            rst.Bookmark = Me.Bookmark
            Do
                rst.MoveNext
                If rst.EOF Then MsgBox "No more records available!": Exit Do
            Loop Until rst!Selected = False
            If rst.EOF = False Then Me.Bookmark = rst.Bookmark
        Else
          MsgBox "Current Record Needs to be Completed!"
          Exit Sub
        End If
        rst.Close
        Set rst = Nothing
    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Mar 2011
    Posts
    28
    I've had some problems with trying this with multiple users at one time. For example if two ppl are in the database on record 1 and complete it, they both jump to record 2 and work it at the same time, is there any way to add an exception to exclude accounts that have already been jumped to?

    Thanks

Posting Permissions

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