Getting data from records (1 Viewer)

[Lanason]

Is it possible to use a variable instead of an actual filed name when getting data from a set of records

GBL_Process = myrec1![Subject] where [Subject] is the actual Field name

can I do something like FieldName = "1234"
then use GBL_Process = myrec1![FieldName] where FieldName is a variable? is there a syntax for this?

 

[theDBguy]

Hi Adrian. Sure. Try:

rs.Fields(varName)

 

[The_Doc_Man]

Lanason, if myrec1 is an open recordset, that works. The alternative depends on how much data you want at a given time - a single value or perhaps you are doing this in a loop and want the corresponding field from every record.

The method suggested by theDBguy is exactly what you would use for the "loop" context or for the context of reading several fields.

If your goal was a one-off type of lookup, consider DLookup unless the recordset is already open to the right record anyway for other purposes. If you have to do this several times (more than one field OR more than one record), use the .Fields(fieldname) syntax.

 

[Lanason]

Thanks guys so what is the syntax exactly? is it ?

GBL_Process = myrec1.Fields(fieldname)

 

[theDBguy]

Thanks guys so what is the syntax exactly? is it ?

GBL_Process = myrec1.Fields(fieldname)

Hi Adrian. If you show us your complete code, we can tell you exactly which one to use. Based on what you have posted earlier, that syntax, as suggested by Doc, should work.