Using variables within field names

[paulharvey]

Hi,

I am currently working on a database that has 100 fields of the name meterx, where x is a number from 1 to 100. Is there any way i can increment the field name using a loop in order to access the fields in turn ?

Thanks in advance.
Paul

 

[jebry]

Hi Paul!

Try this:

Dim intIndex As Integer
Dim strField As String
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("YourTable", dbOpenDynaset)

For intIndex = 1 To 100
strField = "meter" & Format(intIndex)
Debug.Print rst.Fields(strField)
Next intIndex

Set rst = Nothing

You can do what ever you need to with the field. This also works in forms using control names: Me.Controls(strField).

hth


Jeff Bridgham
bridgham@purdue.edu

 

[paulharvey]

Many thanks for the prompt response Jeff.

However, using the above code, I receive the error message "Compile error: User-defined type not defined" It appears to be highlighting the "rst As DAO.Recordset" portion of the code. Any ideas ?

Paul

 

[paulharvey]

Apologies, I jumped the gun with that post before checking the help file.

For anyone who has the same problem with the code, in the code viewer, click Tools, References and add the Microsoft DAO 3.51 Object Library. Works a treat.

Thanks again Jeff for your code sample.

Paul

 

[MichaelRed]

from a different perspective, Meter(N} for (N = 1 to 100 is an indication of a not well mormalized database, A more NORMAL[ized?] approach would probably include seperate records for each of hte items. In many designs using similar nomenclature, many of the fields so identified are not used for every record and thus take up 'realestate' un-necessaruly.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places