Here one Problem I've encountered, I've been told this was a bug.
Using check boxes and a table "Pick" field - Yes/no.
Using a cmdButton with ADO select all and Deselect choice.
When click select all, about 3 gets selected, then I hit the
select button again (mabey 2 times more)and all of them becomes selected. Here is the select all code:
Private Sub cmdSelAll_Click()
If Me.Filter = "" Then Me.Filter = "([Drawing Number] <> '')"
    Me.FilterOn = True
    
     Dim Cnx As ADODB.Connection 'Declares the Object Varibles
     Dim Rst As ADODB.Recordset
        
    Set Cnx = New ADODB.Connection
    Set Rst = New ADODB.Recordset

    Cnx.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Access.CurrentDb.Name & ";Persist Security Info=False"
    If Cnx.State = 1 Then Cnx.Close
    Cnx.Open

    Rst.Open "select * from DrawingIndex_tbl where " & Me.Filter, Cnx, adOpenKeyset, adLockOptimistic

    Do Until Rst.EOF
        Rst.Fields("Pick") = True
        Rst.Update
        Rst.MoveNext
    Loop

    Rst.Close
    Cnx.Close
    Set Rst = Nothing
    Set Cnx = Nothing
    Me.Filter = Me.Filter
    Me.Requery
    Me.Refresh
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End Sub

The select all code in DAO works fine, but the ADO code seems to do this.

Here is the DAO code for Select all:


Private Sub SelAll_frame_AfterUpdate()
    Dim db As Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim int1 As Integer
    Dim stat As Integer
    Dim boPick As Boolean
    'Set rst = Me.Recordset

    If Len(Me.Filter) = 0 Then
        MsgBox "Form must be filtered !!!"
        Exit Sub
    End If
    strSQL = "SELECT DrawingIndex_tbl.* " _
        & "FROM DrawingIndex_tbl " _
        & "WHERE " & Me.Filter & ";"

    Set db = DBEngine.Workspaces(0).Databases(0)
    Set rst = db.OpenRecordset(strSQL, DB_OPEN_DYNASET)
    With rst
        If .RecordCount = 0 Then
            Exit Sub
        End If
        If Me!SelAll_frame = 1 Then
            boPick = True
        Else
            boPick = False
        End If
        .MoveFirst
        Do Until .EOF
            .Edit
            !Pick = boPick
            .Update
            .MoveNext
        Loop
        .Close
    End With

    Set rst = Nothing
    Set db = Nothing
    Me.Requery
    'rst.Close
    'Set rst = Nothing
End Sub

So here is the question, What the cause and is there a fix for ADO?
Thanks


John Paul

__________________
John Paul

Hi,

   That is a puzzler. They should both work. I think the problem is with the Me.Filter and in the SQL statement. I would turn Me.Filter into a variable, and enter the variable into the SQL statement and see if that works. Then I would use a variable for the SQL statement, like this:

vFilter = Me.Filter
sSQL = "SELECT * from DrawingIndex_tbl WHERE " & vFilter

Then the Rst.Open would be:

Rst.Open sSQL, Cnx, adOpenKeyset, adLockOptimistic

I couldn't figure out what type of variable Me.Filter would be, and that would effect the syntax.

Anyway, I have found that with ADO, NOT using variables in these places can cause problems somehow.

HTH


mmcdonal

P.S. - Why don't you use DAO?


mmcdonal

Thanks,
I have been looking for a fix on this problem for almost 8 months.
The orig. programmer who did the code passed it off as a MS bug.
Your solution worked great!!!! :)
The orig. prog. convinced me to use ADO because it was technically better and was faster if applied to an Internet Application.
At the time I didn't know the difference between ADO or DAO.
The second database I built I used DAO because the users we're on Access 2000. This first database was done in ADO and when opened
in Access 2000 it bugs out (or crashes). But, when used on access 2003 it works fine. (I'm guessing Access 2003 accommodates VBA better.) I don't know which is better to use? and is it possible to use ADO and DAO within the same database?
I'm working on a new transmittal database to merge into the first database also. This one writes to and excel form. I have some questions on this app. also. I'll post it next on the forum.
I'm having problems getting the second record set to write.
Thanks again.

John Paul

I spoke to soon, I'm still having problems with the select all and deselect
code. It worked the first time, but that was it. If I do a compact and repair, then it will work for a while. I've been told that this was a Access bug and that Access ballons the data.
So once again, Here a challenge, Please try the code and hopefully
we can find a solution.
Here is a additive that may lend a clue, If I did a select all and deselect
in DAO, it works fine: here is the code for the select all (bopick is varible):

Private Sub SelAll_frame_AfterUpdate()
    Dim db As Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim int1 As Integer
    Dim stat As Integer
    Dim boPick As Boolean
    'Set rst = Me.Recordset

    If Len(Me.Filter) = 0 Then
        MsgBox "Form must be filtered !!!"
        Exit Sub
    End If
    strSQL = "SELECT DrawingIndex_tbl.* " _
        & "FROM DrawingIndex_tbl " _
        & "WHERE " & Me.Filter & ";"

    Set db = DBEngine.Workspaces(0).Databases(0)
    Set rst = db.OpenRecordset(strSQL, DB_OPEN_DYNASET)
    With rst
        If .RecordCount = 0 Then
            Exit Sub
        End If
        If Me!SelAll_frame = 1 Then
            boPick = True
        Else
            boPick = False
        End If
        .MoveFirst
        Do Until .EOF
            .Edit
            !Pick = boPick
            .Update
            .MoveNext
        Loop
        .Close
    End With

    Set rst = Nothing
    Set db = Nothing
    Me.Requery
    'rst.Close
    'Set rst = Nothing


End Sub

Thanks

John Paul

Try putting a breakpoint in your code and stepping thru. Also open your immediate window on your tool bar and select view / immediate window - something like this:

Private Sub SelAll_frame_AfterUpdate()
    Dim db As Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim int1 As Integer
    Dim stat As Integer
    Dim boPick As Boolean
    'Set rst = Me.Recordset

    If Len(Me.Filter) = 0 Then
        MsgBox "Form must be filtered !!!"
        Exit Sub
    End If
    strSQL = "SELECT DrawingIndex_tbl.* " _
        & "FROM DrawingIndex_tbl " _
        & "WHERE " & Me.Filter & ";"

    Set db = DBEngine.Workspaces(0).Databases(0)
    Set rst = db.OpenRecordset(strSQL, DB_OPEN_DYNASET)


    With rst

'------------------
Stop
'make sure you pointer is at the beginning, but force it to the last
'record first and then back to the first record

.movelast
.movefirst
debug.print .AbsolutePosition
'------------------


        If .RecordCount = 0 Then
            Exit Sub
        End If
        If Me!SelAll_frame = 1 Then
            boPick = True
        Else
            boPick = False
        End If
        .MoveFirst
        Do Until .EOF
            .Edit
            !Pick = boPick
            .Update
            .MoveNext
        Loop
        .Close
    End With

    Set rst = Nothing
    Set db = Nothing
    Me.Requery
    'rst.Close
    'Set rst = Nothing


End Sub

Thanks, The DAO code is okay, It's the ADO code that is not working correctly. It takes clicking the button about three times to get it to select all.
I'm wondering, If this isn't the result in a lot of records it has to go through? Perhaps I should place a double loop in the code?
Or a do until pick = true?
What does everyone think?

Thanks

John Paul

I have found a answer to this. The ADO code actually performs
correctly and the pick object is selected. The problem is in the
refreshing of the form to reflect the check boxes as checked.
I was advised to Place "Docmd...." above the Refresh and Requery.

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.Requery
Me.Refresh

This would refresh the form.
This may not be a perfect solution, Because when performed,
the view on the form sometimes may look like It didn't check all of
the records, but when I scroll the records, All of the checks show up.

Hope this helps anyone who is trying to develope a select/deselect
All code.
Thanks to programmers for taking time to help!!!!!!!!!!!

Thanks

John Paul