Forms, SubForms and CheckBoxes......

[James]

Hello.

Using Access 2000.

I have a Form ("frmMain") with a subForm which displays in Datasheet view
only.

There is a checkbox on frmMain.

On each line in the subForm there is also a checkbox.

What I am trying to do is this:

When the user clicks the checkbox on the main form I want each of the
checkboxes in the subForm Datasheet to also be "True" (checked in other
words)

Remember... there is an unlimited number of records in the subForm Datasheet
for each record in the main form.

I can get the first line of the subForm Datasheet to check using the
following code:

Private Sub Set_Received()
[Forms]![frm_Orders]![sfm_OrderDetails].[Form]![Received] = True

End Sub

(This code is called from the Click Event for the checkbox on the main
form.)

I don't know how to get Access to check the remaining checkboxes on the
subForm.


T.I.A.

 

[Tom Wickerath]

Hi James,

Try running an update query in VBA code. Base the update query on the recordsource for your
subform, and include the appropriate WHERE clause that filters the recordset to only those
records displayed in the subform (see the Link Master Field / Link Child Field field). This is
likely the primary key field that is available to the main form.

Start by creating a new SELECT query. Add a parameter to specify the primary key. Then convert it
to an Update query. For the present time, hardcode the value to update in the field that the
checkbox for the subform is based upon. For example, update all records to either true or false.
Once you have an update query that is working properly, click on View > SQL View in query design.
Copy the resulting SQL statement. You will use this as the basis for a query that is run in VBA
code. You'll need to make the appropriate substitutions for the primary key parameter and the
hard-coded update value.

Here is an example that you can follow, using the sample Northwind database:

1). Open the Categories form in design view. Add a checkbox with label "Discontinued". Name the
checkbox: chkDiscontinued.
2). In this case, the link child & master fields is named CategoryID, and the controlsource for
the checkbox in the subform is the Discontinued field in the Products table. Create the
following SELECT query (you can copy the SQL statement below and paste it into the SQL view for a
new query):

SELECT Categories.CategoryID, Products.ProductID, Products.ProductName,
Products.SupplierID, Products.CategoryID, Products.QuantityPerUnit,
Products.UnitPrice, Products.UnitsInStock, Products.UnitsOnOrder,
Products.ReorderLevel, Products.Discontinued
FROM Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Categories.CategoryID)=[Enter CategoryID]));

3.) In query design view, convert the query to an update query (Query > Update Query). Add the
word True (or -1) to the Update To row for the Discontinued field. The resulting SQL statement
should now look like this:

UPDATE Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
SET Products.Discontinued = True
WHERE (((Categories.CategoryID)=[Enter CategoryID]));

This is the SQL statement that you will use as the basis for a query run in VBA code. You'll need
to make the appropriate substitutions for the True value on the third line, and for the [Enter
CategoryID] prompt on the last line.

4.) Go back into form design view. You can either add the following code to the click event
procedure for a new command button, or as the After_Update event procedure for the checkbox on
the main form. My example uses the latter method. Note below how I have used the quotes ("), the
line continuation character (an underscore) and ampersands to break the SQL statement into
several lines.


Private Sub chkDiscontinued_AfterUpdate()
' This procedure uses DAO, so you must have a reference set
' to the DAO object library for it to work.

Dim db As DAO.Database
Dim strSQL As String

On Error GoTo ProcError

strSQL = "UPDATE Categories " _
& "INNER JOIN Products ON " _
& "Categories.CategoryID = Products.CategoryID " _
& "Set Products.Discontinued = " & Me.chkDiscontinued & " " _
& "WHERE Categories.CategoryID= " & Me!CategoryID & ";"

CurrentDb.Execute strSQL, dbFailOnError

' Requery the subform
Me.Product_List.Requery

ExitProc:
On Error Resume Next
db.Close
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in chkDiscontinued_AfterUpdate event procedure..."
Resume ExitProc
End Sub


5). You might want to add an On_Current event procedure to the form to set the checkbox on the
main form to unchecked when you switch to the next main record:

Private Sub Form_Current()
On Error GoTo ProcError

Me.chkDiscontinued = 0

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Form_Current event procedure..."
Resume ExitProc
End Sub


Tom
_____________________________________


Hello.

Using Access 2000.

I have a Form ("frmMain") with a subForm which displays in Datasheet view
only.

There is a checkbox on frmMain.

On each line in the subForm there is also a checkbox.

What I am trying to do is this:

When the user clicks the checkbox on the main form I want each of the
checkboxes in the subForm Datasheet to also be "True" (checked in other
words)

Remember... there is an unlimited number of records in the subForm Datasheet
for each record in the main form.

I can get the first line of the subForm Datasheet to check using the
following code:

Private Sub Set_Received()
[Forms]![frm_Orders]![sfm_OrderDetails].[Form]![Received] = True

End Sub

(This code is called from the Click Event for the checkbox on the main
form.)

I don't know how to get Access to check the remaining checkboxes on the
subForm.


T.I.A.

 

[Ken Snell [MVP]]

You need to cycle through the subform's recordsetclone to do this:


Private Sub Set_Received()
With [Forms]![frm_Orders]![sfm_OrderDetails].[Form].RecordsetClone
.MoveFirst
Do While .EOF = False
![Received] = True
.MoveNext
Loop
End With
End Sub

 

[Tom Wickerath]

Ken,

I think you left out a .Edit and a .Update statement.....


Private Sub Set_Received()
With [Forms]![frm_Orders]![sfm_OrderDetails].[Form].RecordsetClone
.MoveFirst
Do While .EOF = False
.Edit '<------
![Received] = True
.Update '<------
.MoveNext
Loop
End With
End Sub


Tom
________________________________


You need to cycle through the subform's recordsetclone to do this:


Private Sub Set_Received()
With [Forms]![frm_Orders]![sfm_OrderDetails].[Form].RecordsetClone
.MoveFirst
Do While .EOF = False
![Received] = True
.MoveNext
Loop
End With
End Sub

 

[Ken Snell [MVP]]

You are correct..... it was late at night (it was, so I'll use that excuse!)
< g >

Thanks for the catch , Tom.
--

Ken Snell
<MS ACCESS MVP>

Ken,

I think you left out a .Edit and a .Update statement.....


Private Sub Set_Received()
With [Forms]![frm_Orders]![sfm_OrderDetails].[Form].RecordsetClone
.MoveFirst
Do While .EOF = False
.Edit '<------
![Received] = True
.Update '<------
.MoveNext
Loop
End With
End Sub


Tom
________________________________


You need to cycle through the subform's recordsetclone to do this:


Private Sub Set_Received()
With [Forms]![frm_Orders]![sfm_OrderDetails].[Form].RecordsetClone
.MoveFirst
Do While .EOF = False
![Received] = True
.MoveNext
Loop
End With
End Sub

--

Ken Snell
<MS ACCESS MVP>

Hello.

Using Access 2000.

I have a Form ("frmMain") with a subForm which displays in Datasheet view
only.

There is a checkbox on frmMain.

On each line in the subForm there is also a checkbox.

What I am trying to do is this:

When the user clicks the checkbox on the main form I want each of the
checkboxes in the subForm Datasheet to also be "True" (checked in other
words)

Remember... there is an unlimited number of records in the subForm Datasheet
for each record in the main form.

I can get the first line of the subForm Datasheet to check using the
following code:

Private Sub Set_Received()
[Forms]![frm_Orders]![sfm_OrderDetails].[Form]![Received] = True

End Sub

(This code is called from the Click Event for the checkbox on the main
form.)

I don't know how to get Access to check the remaining checkboxes on the
subForm.


T.I.A.

 

[James]

I would just like to say thanks very much to Ken and Tom for their input on
this subject.

Both methods worked very well.

Tom's technique meant a little more work but had the added advantage of
unchecking all the boxes on the subForm if the box on the main form was
subsequently unchecked.

Ken's was economical from a coding point of view and worked fine. If the
user checked the box on the main form by accident it was necessary to
manually uncheck the boxes on the subForm. A small price to pay for such an
elegant solution.

Once again I thank you both for your response.

Much respect........

 

[Ken Snell [MVP]]

Actually, James, my suggestion is easily modified to do what you seek:

Private Sub Set_Received()
With [Forms]![frm_Orders]![sfm_OrderDetails].[Form].RecordsetClone
.MoveFirst
Do While .EOF = False
.Edit
![Received] = Me.Set_Received.Value
.Update
.MoveNext
Loop
End With
End Sub

 

[Tom Wickerath]

Tom's technique meant a little more work....
If you remove the error handling and comments from my procedure, you are left with only 7 lines
of code. My method may have seemed like a little more work, but I was trying to teach you the
logic, so you'd know how to approach a similar problem in the future.

Both methods work. This just goes to show that often times there is more than one way to tackle a
problem. I'm not sure, but I think that if one had hundreds (or thousands) of related records,
then the method I outlined might execute a bit faster versus the Do While loop.

Tom
_______________________________________


Actually, James, my suggestion is easily modified to do what you seek:

Private Sub Set_Received()
With [Forms]![frm_Orders]![sfm_OrderDetails].[Form].RecordsetClone
.MoveFirst
Do While .EOF = False
.Edit
![Received] = Me.Set_Received.Value
.Update
.MoveNext
Loop
End With
End Sub


--

Ken Snell
<MS ACCESS MVP>
_______________________________________


I would just like to say thanks very much to Ken and Tom for their input on
this subject.

Both methods worked very well.

Tom's technique meant a little more work but had the added advantage of
unchecking all the boxes on the subForm if the box on the main form was
subsequently unchecked.

Ken's was economical from a coding point of view and worked fine. If the
user checked the box on the main form by accident it was necessary to
manually uncheck the boxes on the subForm. A small price to pay for such an
elegant solution.

Once again I thank you both for your response.

Much respect........

 

[Ken Snell [MVP]]

Both methods work. This just goes to show that often times there is more than one way to tackle a
problem. I'm not sure, but I think that if one had hundreds (or thousands) of related records,
then the method I outlined might execute a bit faster versus the Do While

loop.


I believe that you are correct in the above speed comparison, Tom.