Step through records 1

[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

 

[mdthornton]

Depending on the calculation, you can do this either directly in query or in VB. Give us some more details.... Best Regards,
Mike

 

[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

 

[mdthornton]

Sorry if I'm being dim but what are you trying to achieve with:

DoCmd.OpenQuery "qryAppendEmployeesQuery"

Are you trying to add the names to another table? Best Regards,
Mike

 

[samn265]

I was trying to test an append query. Please ignore this line in my function: DoCmd.OpenQuery "qryAppendEmployeesQuery"

 

[mdthornton]

Still not sure what you want to do... as it stands the function will return the last lastname in the recordset. If you are trying to build a temporary table of last names you could do this in your loop:

CurrentDb.execute "insert tmpTable values('" + rstError![LastName] + "')"

tmpTable must exist and be empty (presumably) so do this before you get into the loop:

CurrentDb.execute "delete * from tmpTable" Best Regards,
Mike

 

[samn265]

Mike,

That is exactly what I am looking for. A temp table to insert the obtained record. I tried your code but it did not work. Is there anything I am doing wrong?


Sorry for the confusion.

Every time I move to the next record, I am basically getting the name of the next employee in which I would to like to perform a calculation to see whether or not he/she is eligible for reward days.

I am able to successfully move from one employee to another but I can't seem to insert the name of that employee in a table, which is used along another query to get info only for that employee out of many others.

"activeEmployeeName" is the function name that carries the name of that employee.

I hope this helps.

 

[mdthornton]

OK, I've written the function out for you. To start with just create a table called tmpTable with a single text column. The function should insert a list of names into this table. Once you get this working you can tailor it to what you really need to do.

Function ActiveEmployeeName() As String
Dim rstError As DAO.Recordset

CurrentDb.execute "delete * from tmpTable"
Set rstError = CurrentDb.OpenRecordset("qrySelectCountofActiveEmployees&quot
Do Until rstError.EOF
ActiveEmployeeName = rstError!LastName
CurrentDb.execute "insert into tmpTable values('" + ActiveEmployeeName + "')"
MsgBox "Last Name is: " & ActiveEmployeeName
rstError.MoveNext
Loop
rstError.Close
Set rstError = Nothing
End Function Best Regards,
Mike