Returning values based on Record Number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Does anyone know of a way to return values to a variable based on record
number?

I'm currently using an AutoNumber field to retreive a field value via the
DLookup function in a loop but would like to use the loop to say first of
all, return the CustomerName of record 1, then the CustomerName of record 2.

Any ideas?

Ta.

Neil
 
Open a record set and create a loop.

Function RunLoop()
Dim DB as Dao.DataBase
Dim Rec as Dao.Recordset

Set DB= CurrentDb
Set Rec = DB.OpenRecordSet("Select * From TableName Order By KeyField")
While Not Rec.Eof
Msgbox Rec!CustomerName
Rec.MoveNext
Wend

End Function
 
Records in tables don't have a position number.

So, there is basically no way to do what you have stated. Can you describe
more fully why you need to do this and where you are attempting to do this?
If in code, can you post the code or at least the relevant section(s) of the
code?
 
Hi,

I didn't realize records on a table didn't have actual record numbers.

I want to use VBA in Access. I am importing a text file to a table called
tblEMail. Then I want to loop round each record on that table picking out
the email address and name so that I can send out e-mails attaching data to
them.

I have already managed this, by adding an AutoNumber field and using that in
the loop. I've also set the database to Compact and Repair on close, so that
I can delete the data on the email table and when it imports a second time,
the auto number starts back at 1. I just wondererd if there was a more
robust way of doing it.

I think Ofer's post (cheers Ofer!) above answers the query unless with this
extra information you have anything to add?

Neil
 
While I'd have chosen a couple of other ways to solve the issues, your
solution covers the situation like a blanket. It works, don't change
it. :-)

HTH
 
No, I think Ofer grasped the problem and came up with a solution that should
work for you.
 
Back
Top