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.
