How can I loop through fields in a known record

[Steve]

Hi
my question is this

how can I loop through a Known record ( same record every time) and store
each field into a variable
Somthing like this - (dont use this as it dont work)
Dim Deft(i) as variant
For i = 1 To 20
Deft(i) = DLookup(Fields(i), "tblSetupLocal", "[SID]=1")
Next i

So I can manipulate the variables and then send each variable value back to
the correct field in the rocord the same way. it would be very helpfull if I
could do this.

Steve - From a land down under

 

[John W. Vinson]

Hi
my question is this

how can I loop through a Known record ( same record every time) and store
each field into a variable
Somthing like this - (dont use this as it dont work)
Dim Deft(i) as variant
For i = 1 To 20
Deft(i) = DLookup(Fields(i), "tblSetupLocal", "[SID]=1")
Next i

So I can manipulate the variables and then send each variable value back to
the correct field in the rocord the same way. it would be very helpfull if I
could do this.

Steve - From a land down under

Use a Recordset:

Dim i As Integer
Dim Deft(1 to 20) As Variant
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb ' get the current database
Set rs = db.OpenRecordset("SELECT * FROM tblSetupLocal WHERE [SID]=1", _
dbOpenDynaset)
For i = 1 to 20
Deft(i) = rs.Fields(i)
Next I

or use rs!fieldname instead of rs.Fields(i), or set rs!fieldname to a value,
or set rs.Fields(i) to a value as appropriate.

Note that although you can manipulate data in tables using recordsets in this
way - and it's often the only, or best way - it is frequently better to use
Update queries or other types of action queries.

John W. Vinson [MVP]

 

[Allen Browne]

See how this goes, Steve:

Function LoopFields()
Dim rs As DAO.Recordset
Dim strFields() As String
Dim i As Integer

Set rs = DBEngine(0)(0).OpenRecordset("MyTable", dbOpenDynaset)
ReDim strFields(rs.Fields.Count - 1)
For i = 0 To rs.Fields.Count - 1
strFields(i) = rs.Fields(i).Name
Next
rs.Close
Set rs = Nothing

For i = LBound(strFields) To UBound(strFields)
Debug.Print strFields(i)
Next
End Function

 

[Steve]

Thanks John
Im feeling very dumb right now as I spent hours going through Microsoft help
files ( there are so many and make no sence to me whatsoever and Im no fool
just on a learning curve)
It sems to me that when I type into the help box on access a who list of
stuff comes up but sorting through it to find what you want is a nightmare -
any suggestions on narrowing it all down.

Hi
my question is this

how can I loop through a Known record ( same record every time) and store
each field into a variable
Somthing like this - (dont use this as it dont work)
Dim Deft(i) as variant
For i = 1 To 20
Deft(i) = DLookup(Fields(i), "tblSetupLocal", "[SID]=1")
Next i

So I can manipulate the variables and then send each variable value back to
the correct field in the rocord the same way. it would be very helpfull if I
could do this.

Steve - From a land down under

Use a Recordset:

Dim i As Integer
Dim Deft(1 to 20) As Variant
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb ' get the current database
Set rs = db.OpenRecordset("SELECT * FROM tblSetupLocal WHERE [SID]=1", _
dbOpenDynaset)
For i = 1 to 20
Deft(i) = rs.Fields(i)
Next I

or use rs!fieldname instead of rs.Fields(i), or set rs!fieldname to a value,
or set rs.Fields(i) to a value as appropriate.

Note that although you can manipulate data in tables using recordsets in this
way - and it's often the only, or best way - it is frequently better to use
Update queries or other types of action queries.

John W. Vinson [MVP]

 

[John W. Vinson]

Thanks John
Im feeling very dumb right now as I spent hours going through Microsoft help
files ( there are so many and make no sence to me whatsoever and Im no fool
just on a learning curve)
It sems to me that when I type into the help box on access a who list of
stuff comes up but sorting through it to find what you want is a nightmare -
any suggestions on narrowing it all down.

Don't beat up on yourself! You are in very good (and very large) company in
considering Microsoft's Help system to be seriously lacking. I'm pretty good
at Access, and have a good memory... but I *STILL* have Access 97 installed,
because it was the last version to have a reasonably usable Help file.

Microsoft developers have been hearing (loudly, sometimes not all that
politely) from MVP's and other concerned folks about this, and there is *some*
improvement; what I've seen of 2007 help is... well, maybe up to 60% of the
quality of 97, disregarding the fact that most of it requires a fast web
connection (which of course not everyone has).

I wish I had some better suggestions, but the indexing and searching of Access
Help simply stinks, and I don't know of any good alternative (other than these
newsgroups and their assembled centuries of experience).

John W. Vinson [MVP]