dealing with check boxes on a continuous form 1

[robojeff]

I have a continuous form on which I display a check box, an assembly number, and a quantity with the row source from my table (Atbl)

I have a query which pulls the data from these three table fields in which I would like to sum up the total quantity
of all items which have their check box checked and have put together the following query which displays the records which are selected by their check boxes but I want a total of all quantity fields that are selected but am not sure how to do this with a query

SELECT DISTINCTROW Atbl.cbox, Atbl.Assm, Atbl.Qty AS [Sum Of Qty]
FROM Atbl
WHERE (((Atbl.cbox)=True));

I would also like to have a clear button on my form which when clicked clears all of these check boxes in the table. I know that this needs to be an OnClick update but I am not sure how to clear these... Would I use another query for this?

Any suggestions would be appreciated...

 

[dhookom]

Your query for the qty of all checked would be

SELECT Sum(Abs(cbox *Qty)) AS [Sum Of Qty]
FROM Atbl;

To clear all of the check boxes, you can run an update query to set all cbox fields to 0.

DoCmd.RunSQL "Update Atbl SET cbox = 0 WHERE cbox <> 0"

Duane
Hook'D on Access
MS Access MVP

 

[robojeff]

Thanks Duane-

The quantity sum works great but the clear check boxes fails with a run-time error 3008 The table 'Atbl" is already open exclusively by another user...

This is because the Atbl table is the control source data for my continuous form which is being displayed while I want to clear the entries.

I tried to do this with a DOA update also with no luck.. Not sure if there is a graceful way other than closing the form, running the query and then openning back up the form... Does this sounds reasonable or is ther another way?

Thanks for your help!

 

[dhookom]

You can use some dao code to create a recordset clone. Then loop through the clone setting the field to 0.

Duane
Hook'D on Access
MS Access MVP

 

[robojeff]

Thanks Duane-

I tried the following but I get a run time error 3027: can not update - database or object read only with the following code:

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb


strSQL = "SELECT Atbl.cbox FROM Atbl; "
Set rs = dbs.OpenRecordset(strSQL, dbOpenDynaset)
       If rs!cbox.Value <> "0" Then
           
            With rs
              .Edit
              !cbox = "0"
              .Update
            End With
        End If
            rs.MoveNext    ' get next record
 '     End If
        
    Loop

 

[dhookom]

As I stated, I would use a recordsetclone:

Private Sub cmdResetcbox_Click()
    Dim rs As DAO.Recordset
    Me.Dirty = False
    Set rs = Me.RecordsetClone
    With rs
        rs.MoveFirst
        Do Until .EOF
            If .Fields("cbox") = -1 Then
                .Edit
                    .Fields("cbox") = 0
                .Update
            End If
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[robojeff]

Thank you Duane-

That works great!