Access to a field of a table indirectly!

[alinagoo]

Hello all.
I have the following code but it does not work!
Please help me how can i access to a field with a variable keeping Name of that field?

PRIVATE SUB ANALYZE(FieldName as string)
dim rst As DAO.Recordset
dim Minval as integer
Dim MyDB As DAO.Database

Set MyDB = CurrentDb
MyDB.OpenRecordset("MyTable",dbOpenDynaset)

   rst.MoveFirst
   Minval=rst!FieldName <----- Problem is here

   'some code here ......
  
   rst.Close
   Set rst = Nothing

end sub

[ahmedtharwat19]

what th problem number.

read this help may help :

Subscript out of range (Error 9)

Elements of arrays and members of collections can only be accessed within their defined ranges. This error has the following causes and solutions:


You referenced a nonexistent array element.
The subscript may be larger or smaller than the range of possible subscripts, or the array may not have dimensions assigned at this point in the application. Check the declaration of the array to verify its upper and lower bounds. Use the UBound and LBound functions to condition array accesses if you're working with arrays that are redimensioned. If the index is specified as a variable, check the spelling of the variable name.

You declared an array but didn't specify the number of elements. For example, the following code causes this error:
Dim MyArray() As Integer
MyArray(8) = 234 ' Causes Error 9.

Visual Basic doesn't implicitly dimension unspecified array ranges as 0 – 10. Instead, you must use Dim or ReDim to specify explicitly the number of elements in an array.

You referenced a nonexistent collection member.
Try using the For Each...Next construct instead of specifying index elements.

You used a shorthand form of subscript that implicitly specified an invalid element.
For example, when you use the ! operator with a collection, the ! implicitly specifies a key. For example, object!keyname. value is equivalent to object.item(key name).value. In this case, an error is generated if keyname represents an invalid key in the collection. To fix the error, use a valid key name or index for the collection.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh


medo

[alinagoo]

what th problem number.

read this help may help :

Subscript out of range (Error 9)

Elements of arrays and members of collections can only be accessed within their defined ranges. This error has the following causes and solutions:


You referenced a nonexistent array element.
The subscript may be larger or smaller than the range of possible subscripts, or the array may not have dimensions assigned at this point in the application. Check the declaration of the array to verify its upper and lower bounds. Use the UBound and LBound functions to condition array accesses if you're working with arrays that are redimensioned. If the index is specified as a variable, check the spelling of the variable name.

You declared an array but didn't specify the number of elements. For example, the following code causes this error:
Dim MyArray() As Integer
MyArray(8) = 234 ' Causes Error 9.

Visual Basic doesn't implicitly dimension unspecified array ranges as 0 – 10. Instead, you must use Dim or ReDim to specify explicitly the number of elements in an array.

You referenced a nonexistent collection member.
Try using the For Each...Next construct instead of specifying index elements.

You used a shorthand form of subscript that implicitly specified an invalid element.
For example, when you use the ! operator with a collection, the ! implicitly specifies a key. For example, object!keyname. value is equivalent to object.item(key name).value. In this case, an error is generated if keyname represents an invalid key in the collection. To fix the error, use a valid key name or index for the collection.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh


medo

Thanks for your attention but the problem is not out of range!
Generated error is:
"Run-Time Error '3265'
Item Not Found In This Collection"

I think,
Interpreter could not recognize "FieldName" is a variable containing name of a field!

[ahmedtharwat19]

your code miss some thing like this

MyDB.OpenRecordset("MyTable",dbOpenDynaset)

it should be

Set rst = MyDB.OpenRecordset("MyTable",dbOpenDynaset)

and i think the type of minval not a type of tabel field,
rebuild the code , and you can visut the link below

runtime error 3265

(medo)

[alinagoo]

No,I found solution!
Answer is:

Minval = rst(FieldName)

Bye

[ahmedtharwat19]

Minval = rst(FieldName)

it`s the same

Minval = rst!FieldName

[alinagoo]

Minval = rst(FieldName)

it`s the same

Minval = rst!FieldName

They are the same?
I don't know VB's syntax very well but they tested and just rst(FieldName) worked properly.
All the best,Alinagoo

[NeoPa]

alinagoo: Thanks for your attention but the problem is not out of range!
Generated error is:
"Run-Time Error '3265'
Item Not Found In This Collection"

There appears to be some misunderstandin g here. Medo was not suggesting the error was the Out of Range error. He was suggestiong you provide information about whatever error you had and providing an example for you to follow. It's hard to help someone when they don't provide the information necessary to know what's wrong.

Later (Post #4), Medo suggested an alternative solution. This is an alternative usage, but it will not work (as you found out) for the scenario you described. You would need the actual name of the field for this, and you explained clearly in your first post you were dealing with a variable which contains the name of the field. The version you found is the most appropriate for your needs.

I hope this has cleared up any confusion.

[alinagoo]

There appears to be some misunderstandin g here. Medo was not suggesting the error was the Out of Range error. He was suggestiong you provide information about whatever error you had and providing an example for you to follow. It's hard to help someone when they don't provide the information necessary to know what's wrong.

Later (Post #4), Medo suggested an alternative solution. This is an alternative usage, but it will not work (as you found out) for the scenario you described. You would need the actual name of the field for this, and you explained clearly in your first post you were dealing with a variable which contains the name of the field. The version you found is the most appropriate for your needs.

I hope this has cleared up any confusion.

Yes,Sure and thanks you and other dear Bies.