Step through records

[samn265]

I have a table of x number or records. Each record has a field name called LastName. I would like to go from 1st record to last record one record at a time to perform a calculation (query) on that record and then move on to the next record. How would you write a code to this task?
Thanks,
Sam

 

[cmmrfrds]

Will probably need to use VBA code if you are comfortable with it, but give an example of your requirements, the formula, and other helpful information needed to solve the problem. It might be possible to do in a query.

 

[LittleSmudge]

Dim rst as ADODB.Recordset
Set rst = NEW ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open "SELECT fieldname FROM tablename;"
while NOT rst.EOF

do the caln here based on rst!fieldname

rst.MoveNext
wend
rst.Close
set rst = Nothing


That will do for Access 2000, 2002
For older versions of Access the first couple of lines will need to change to use DAO rather than ADO refs.

 

[samn265]

Below is a function to step through a query, one record at a time until the end of the file. I am having a trouble inserting the value of each record “ActiveEmployeeName” to a table. I do not want to append to the table. I just want to have one record in that table which is the value that is passed from the function. I tried to use Make_Table query but it did not work. Any idea of how to do this code?

Function ActiveEmployeeName() As String
Dim rstError As DAO.Recordset
Set rstError = CurrentDb.OpenRecordset("qrySelectCountofActiveEmployees&quot
Do Until rstError.EOF
'MsgBox "Last Name is: " & rstError!LastName
DoCmd.OpenQuery "qryAppendEmployeesQuery"
ActiveEmployeeName = rstError!LastName
MsgBox "Last Name is: " & ActiveEmployeeName
rstError.MoveNext
Loop
rstError.Close
Set rstError = Nothing
End Function


I appreciate your help. Thanks,
Sam

 

[samn265]

Help!

 

[bustell]

Depending on your calculation / formula, you may be able to do it through on UPDATE query using the IIF function.

This would be a much faster way of performing the update. If you would like, post the details of your formula.
Pat B

 

[riny]

Sorry if I'm being over simple, but you could also append it, do whatever, then delete it.