Looping through values in a table

  • Thread starter Thread starter Bob Chin
  • Start date Start date
B

Bob Chin

Hi,

I am trying to create a loop that goes through the values in a table
and returns some strings. My table looks like this:

Index String1 String2
XXX XXX XXX

I will then use the two strings in the table for calculations. So the
code will look something like this:

while I am not at the end of the table
mystring = string1
mystring2 = string2
dosomestuff with the strings
end while

Anyone know of an easy way to do this?

Thanks,
Bob
 
Something like ( untested aircode )

sub OperateOnStrings()
dim rs as DAO.recordset

set rs = currentdb.openrecordset("myTablename")

rs.movefirst 'go to start of recordset

while not(rs.eof) 'loop till end
me.mystring1 = rs!string1 'get value in form text string
me.mystring2 = rs!string2

'do whatever calculations
'really you dont need the strings in form text boxes
'you could just assign them to string variables, and operate on them there

rs.movenext 'get next record
wend

rs.close 'clean up
set rs = nothing

End Sub

This does what you ask, but there are usually better ways of operating on
data, eg by using select or update queries.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Thanks for the messages. I have a table that has e-mail addresses and
names. I am trying to loop through the table to send a custom e-mail
to each person in the table.

Thanks,
Bob
 
Bob,

I thought you originally said you wanted to do some calculations. Anyway...

Dim db As Database
Dim rs As DAO.Recordset
Dim strValue As String

Set db = CurrentDb
Set rs=db.OpenRecordset("SELECT * FROM tblMyTable")

Do While Not rs.EOF
'Get a field from the Recordset
strValue = rs!FieldName

'If you want to add values, use the AddNew and Update methods
'rs.AddNew
'rs!SomeField = "abc"
'rs.Update

'If you want to update some existing data, use the Edit and Update
methods
'rs.Edit
'rs!SomeField = "abc"
'rs.Update

'If you want to delete the current record, use the Delete method
'rs.Delete

rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Back
Top