If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
uuuuytu
James wrote in message ... 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.809 / Virus Database: 551 - Release Date: 9/12/2004 |
#2
|
|||
|
|||
Forms, SubForms and CheckBoxes......
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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.809 / Virus Database: 551 - Release Date: 9/12/2004 |
#3
|
|||
|
|||
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[s]). 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 _____________________________________ "James" wrote in message ... 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.809 / Virus Database: 551 - Release Date: 9/12/2004 |
#4
|
|||
|
|||
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 "James" wrote in message ... 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.809 / Virus Database: 551 - Release Date: 9/12/2004 |
#5
|
|||
|
|||
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 ________________________________ "Ken Snell [MVP]" wrote in message ... 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 "James" wrote in message ... 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.809 / Virus Database: 551 - Release Date: 9/12/2004 |
#6
|
|||
|
|||
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 "Tom Wickerath" wrote in message ... 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 ________________________________ "Ken Snell [MVP]" wrote in message ... 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 "James" wrote in message ... 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.809 / Virus Database: 551 - Release Date: 9/12/2004 |
#7
|
|||
|
|||
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........ --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.813 / Virus Database: 553 - Release Date: 13/12/2004 |
#8
|
|||
|
|||
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 "James" wrote in message ... 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........ --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.813 / Virus Database: 553 - Release Date: 13/12/2004 |
#9
|
|||
|
|||
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 _______________________________________ "Ken Snell [MVP]" wrote in message ... 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 _______________________________________ "James" wrote in message ... 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........ |
#10
|
|||
|
|||
"Tom Wickerath" wrote in message
... 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. -- Ken Snell MS ACCESS MVP |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
subform's field and corresponding recordset out of sync? | AB | General Discussion | 3 | November 8th, 2004 10:54 PM |