Stepping through controls on a subform

[Rafi]

I have a form (frmPmt) subform (subPmtDetail) into which I enter payment
details (invoice number and amount) relating to a particular payment. Once I
hvae entered all the Invoice numbers and their corresponding amounts on the
subform I would like to set Paid=True on my invoice table. How do I step
throuh the Invoice IDs that were entered on the subform?

Thanks

 

[Ken Snell \(MVP\)]

Something like this, perhaps, if you're running the code in the subform:

Dim dbs As DAO.Database
Dim strSQL As String
Set dbs = CurrentDb
With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
strSQL = "UPDATE Invoices SET Paid=True " & _
"WHERE InvoiceID=" & .InvoiceID.Value
dbs.Execute strSQL, dbFailOnError
.MoveNext
Loop
End With
dbs.Close
Set dbs = Nothing



If you want to run the code in the main form, then this is how the code must
be changed:

Dim dbs As DAO.Database
Dim strSQL As String
Set dbs = CurrentDb
With Me.NameOfSubformControl.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
strSQL = "UPDATE Invoices SET Paid=True " & _
"WHERE InvoiceID=" & .InvoiceID.Value
dbs.Execute strSQL, dbFailOnError
.MoveNext
Loop
End With
dbs.Close
Set dbs = Nothing

 

[Rafi]

Works like a chram! Thanks

Something like this, perhaps, if you're running the code in the subform:

Dim dbs As DAO.Database
Dim strSQL As String
Set dbs = CurrentDb
With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
strSQL = "UPDATE Invoices SET Paid=True " & _
"WHERE InvoiceID=" & .InvoiceID.Value
dbs.Execute strSQL, dbFailOnError
.MoveNext
Loop
End With
dbs.Close
Set dbs = Nothing



If you want to run the code in the main form, then this is how the code must
be changed:

Dim dbs As DAO.Database
Dim strSQL As String
Set dbs = CurrentDb
With Me.NameOfSubformControl.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
strSQL = "UPDATE Invoices SET Paid=True " & _
"WHERE InvoiceID=" & .InvoiceID.Value
dbs.Execute strSQL, dbFailOnError
.MoveNext
Loop
End With
dbs.Close
Set dbs = Nothing