For Next Loops - How do I use it?? 1

[cariengon]

I'm not to advanced with the Loops in Access coding, so I'd appreciate it if someone would help me with this issue...

I have a recordset that has been retrieved from a query and all of the records are viewed in a Continuous Form so the user can check off (yes/no box) a record if needed. On my Exit button, I want to make it so if the user checked off the record(s), it will clear the checks (reset them to False) before closing the form. I tried the IfDirty - but that effects each individual record everytime a change is made and I don't want the changes to be permanent unless the process has been correctly completed.

I know I need to use a For...Next statement, but I don't know the terminology to assign the record as the element and the group as the recordset. This is what I've tried:

Dim myObject, myCollection

If MsgBox("This will reset any Offsets selected.", vbOKCancel, "Exit?&quot = vbCancel Then
Exit Sub
End If

For Each myObject In myCollection
If Me.Offset_CHECK = True Then
Me.Offset_CHECK = False
Me.Offset_DR = Null
Me.Offset_CR = Null
End If
Next


DoCmd.Close

When I try to run it, I get a type mismatch error.

Can anyone help me?

Thanks in advance,
Carie

 

[WayneRyan]

Carie,

You need something like the following:

' **********************************
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String

Set dbs = CurrentDB
strSQL = "Select * from tblWhatever"
Set rst = dbs.OpenRecordSet(strSQL)
While Not rst.EOF and Not rst.BOF
If (rst!fielda = "something" Then
rst.Edit
rst!fieldx = "something else"
rst.Update
End If
rst.MoveNext
Wend

' **********************************

That will step record-by-record through a table and
change fieldx to "something else" if fielda = "something"

hth,
Wayne

 

[cariengon]

Is there a way to narrow this down just to look at the records that have been pulled by the query? I have a huge table and the solution you've provided wouldn't be very efficient.

Like instead of creating a new SQL query, can you set the rst to be the Query that is feeding the form?

 

[cariengon]

OKAY - I've tried the rst.EOF (exactly as you've stated and I keep getting a "TOO FEW Parameters" error. Can you tell me why?

Private Sub cmd_Exit_Click()

Dim dbs As Database, rst As Recordset
Dim strSQL As String

Set dbs = CurrentDb
strSQL = "Select * From [1001 Consolidated Pooled Ins] Where LE_Account = Forms![200 Recon frm].LE_Account AND Offset = True"
Set rst = dbs.OpenRecordset(strSQL)

If MsgBox("This will reset any Offsets selected.", vbOKCancel, "Exit?&quot = vbCancel Then
Exit Sub
End If

While Not rst.EOF And Not rst.BOF
If rst!Offset_CHECK = True Then
rst.Edit
rst!Offset_CHECK = False
rst!Offset_DR = Null
rst!Offset_CR = Null
rst.Update
End If
rst.MoveNext
Wend



rst.Close
Set dbs = Nothing

End Sub

WHY WON't THIS WORK???

 

[WayneRyan]

cariengon,

You're getting real close!

I hate these short line widths ...

Your strSQL has to be put together like:

strSQL = "Select * " & _
"From tblYourTable " & _
"Where FieldName = '" & Me.txtControl & "';"

When VBA builds your current string, it doesn't know
that it should put the value for the control
Forms![200 Recon frm].LE_Account in the text.


The end result is that the strSQL string should look
like:

Select * From tblYourTable Where FieldName = 'abcde';


I hope this makes sense.
Wayne