Loop through a field in subform datasheet

[Dugantrain]

Hi all, I'm trying to accomplish a pretty simple task that I don't yet know the code for. In the On-Click event of an option button on a Parent Form, I need it to loop through all the records of the Subform datasheet and put the Number 0 into a certain field of every record. I'm using A2K, so if I must make a call to the database, then I'll be using ADO.

 

[WayneRyan]

dugantrain,

Use an update query to update the table and
use Forms![YourMainForm]![ChildKey] as the
criteria.

Wayne

 

[Dugantrain]

OK, got it. For future reference, I would like the code to loop through a recordset on a form, just so that I know how to do it.

 

[Elana]

Here is some sample code I use to loop through every record displayed on a form and then calculate an amount for each record and place the calculated amount in another table.

'calculate payment requests by underwriter

Dim dbs As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset

Set dbs = CurrentDb()
'look at open, but hidden form to find policy uwrs
Set rst1 = Forms!frmTabUwrs.frmPolicyUwrsSubform.Form.RecordsetClone
Set rst2 = dbs.OpenRecordset("tblRequestsByUwr", dbOpenDynaset)


rst1.MoveFirst

Do Until rst1.EOF
rst2.AddNew
rst2!MRequestID = Me.MRequestID
rst2!SUwrID = rst1!SUwrID
rst2!AmtReq = Me.ReqAmt * rst1!UwrPct
rst2!DateReq = Me.DateRequested

rst2.Update

rst1.MoveNext

Loop

Me.Form.Refresh
rst1.Close
rst2.Close


HTH

 

[Dugantrain]

What's up with declaring an object as Database in A2k? I try:
Dim db as ADODB.database
and there is no ADODB.database. Is this declaration implied or am I missing a reference?

 

[Elana]

I believe it's implied... I have this code (partial code showin here) and it works fine without declaring the current database.

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

rst.Open "tblRequestsMaster", CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic

HTH
E