Continous Form SQL/Loop ?

[mcadle]

I have a continous form, in the OnClose property I have SQL code which runs an update where certain fields match. It works perfectly, except it only runs for the top/current record. How do you make it run for all records displayed?

I am thinking that I need to create a recordset and work through each record. Any ideas on a loop for the form? Or anything just down and dirty to get it done?

Thanks for all of the help in advance.

 

[Treason]

Here's untested code I don't have access handy but you'll get the idea...

Dim rst as Recordset
Set rst = Screen.ActiveForm.RecordsetClone

Do Until rst.EOF = True
DoCmd.RunSQL "Put your SQL Code HERE"

rst.MoveNext
Loop

 

[mcadle]

I figured I was going to have to do a recordset. Here is the code I am using:

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Select * from [fleet assignment]")
DoCmd.GoToRecord , , acFirst
Do Until rst.EOF

If [damage] = True Then
DoCmd.RunSQL "sql"
End If
If [accident] = True Then
DoCmd.RunSQL "sql"
End If

rst.MoveNext

If Not rst.EOF And [damage] = False And [accident] = False Then
DoCmd.GoToRecord , , acNext
End If
Loop

rst.Close
Set dbs = Nothing
DoCmd.GoToRecord , , acNewRec

I also tried this:

Dim rst As Recordset
Set rst = Screen.ActiveForm.RecordsetClone
Do Until rst.EOF = True

If [damage] = True Then
DoCmd.RunSQL "sql code"
End If

rst.MoveNext
Loop

No matter what I get a Type Mismatch. I put msgbox's every other line to determine where and it is in the "Set" statement. Any ideas?

 

[mcadle]

Pat,

I have a continous form where the control source is set to one table (through query.) It is completely different than the table that I am updating. There is only one common field between the two tables. Unfortunately, I have no way to relate the two tables by any key. On the form, if the user checks the check box I have to have it update the other table (same field.)

If I am overthinking this whole issue, than fine. But I started this a while ago, left it, and now I'm back to it again. I can't remember why I am headed down this path but it seems to make sense. I understandwhat you said about looping the query, but this isn't a subform and its on the On Close event of the form. I did this because if it happens when the user checks the checkbox, it takes a long time because it has to update a table that resides in a different database on a different server. I thought the recordset would essentially create a snapshot of the data where I could progress through each record, find the true statement, and run an SQL update. Is this incorrect?