By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,471 Members | 722 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,471 IT Pros & Developers. It's quick & easy.

Cycle through records

P: 2
Hey all,

I'm trying to get access (through VBA) to cycle through all the records in a form and perform a task for each one that has something in a text box. I think I know how to get it to do what I want, I just don't know how to reference a record in my For each... statement. Here is the code I have:

Dim Record As Recordset

For Each Record In Forms!frmRFIattach

If Not Forms!frmRFIattach!Attachment = "" Then
'Do some stuff
End If

Next Record

Any help is appreciated!!
Oct 20 '06 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 1,221
Here is a portion of some code I posted in another message:

Notice that "rs" is a handle to a record set which is opened with OpenRecordSet.
That handle is how you can reference anything having to do with that recordset, whether it is a field found in the records of the recordset, or the end-of-file condition or anything else. In this sample, [Item], [Warehouse] and [OnHand] are all fields in my records of the recordset.

This code again is only a portion of a program, it's not supposed to look complete.


Expand|Select|Wrap|Line Numbers
  1. Set db = CurrentDb
  3.     Set rs = db.OpenRecordset("ItemWarehouse")
  4.     Onhandqty = 0
  5.     On Error GoTo Loopend
  6.     rs.MoveFirst
  8.     strFind = "Item='" & CStr(ItemNbr) & "'"
  9.     rs.FindFirst (strFind) ' first occurrence of this item in itemwh file
  11. LoopItems:
  12.     While (Not (rs.EOF))
  13.     If rs.NoMatch Then GoTo Loopend
  14.     If rs![Item] <> ItemNbr Then GoTo NextItem
  15.     ' If rs![Warehouse] <> "9301" And rs![Warehouse] <> "3405" Then GoTo NextItem
  16.     If rs![Warehouse] = "PCR" Then GoTo NextItem
  17.     Counter = Counter + 1
  18.     Onhandqty = Onhandqty + rs![OnHand]
  19.     If Counter > 1 Then GoTo Loopend  'don't bother looking after we found both warehouses
  20. NextItem:
  21.     rs.FindNext (strFind)
Oct 20 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.