return a field value

[ghloid]

Basically, we are trying to figure out a way to return a query field value to a variable. We use the code shown below to set variables and try to return the value of a premade query's "QRY_NAME" field. With this value, we can then populate another build on the fly SELECT statement to populate another control's row source property.

I think this code should work, however, we get an error 3265 stating "item not found in this collection."

The error occurs with "FldValue = Qry!FldName.Value" (I denoted it with stars in the code.)
****************************************

Private Sub cbo_Letter_AfterUpdate()
Dim ProctorList As String
Dim DB As Database
Dim Qry As QueryDef
Dim FLD As Field
Dim FldName As String
Dim FldValue As String

Set DB = CurrentDb()
Set Qry = DB.QueryDefs!qry_Query_Name
FldName = Qry.Fields(2).Name

****FldValue = Qry!FldName.Value****

ProctorList = "SELECT " & FldValue & ".Proctor_ID, [" & FldValue & "]![Last_Name] & ', ' & [" & FldValue & "]![First_Name] AS Name FROM " & FldValue & ";"

cbo_Proctor.RowSource = ProctorList
cbo_Proctor.Requery
DB = Nothing
Qry = Nothing
End Sub

Is there another way to get the QRY_NAME value out of this query? Maybe through some simple SELECT statement tied to a variable name (we've tried this countless times, with no avail). Any help is greatly appreciated.

THANKS!!!!

 

[scking]

You have dimensioned FLDName as a string and then tried to use it as an object. Doesn't work that way.

'This would probably work
Dim FldName As DAO.Field
Set FldName = Qry.Fields(2)

'As would something like this which expands the
'string variable FldName inside the braces.
FldValue = Qry![FldName].Value

Steve King Growth follows a healthy professional curiosity

 

[ghloid]

We attempted both suggestions you offered. We tried changing the FldName variable to a DAO.Field with no good results. Then we tried replacing the FldValue definition with:
FldValue=qry![FldName].value

Same results. We get the error:

3265 Item not found in this collection.

Any other ideas? Strange that this is such a hard thing to do. You'd figure it would be pretty simple.

Thanks for any further help you can offer.

 

[scking]

Try placing a breakpoint on the error line and using the immediate window to determine what will work. That usually is the easiest method of overcoming one of these problems.

When you tried 'Set FldName = Qry.Fields(2)' did you get an error? If not then you have an object reference to the field of the query. Then, using intellisense of the object browser figure out what properties are available with the object. Then enter,

?FldName.

in the immediate window and you should get a list of the properties and methods available for the object. Enter the property to determine which will return the string name for the field. Then determine the correct means of using the string to get the value of the field. I use a number of different ways such as the following.

rstTable.Fields(intTableCnt).Name
rstTable.Fields(intTableCnt).Type

Although this is a table recordset you could get exactly the same by opening the query as a recordset.

Let me know how it goes.

Steve King
Growth follows a healthy professional curiosity

 

[ghloid]

We tried typing "?FldName." in the immediate window, and got the error "Expected Identifier or Bracketed expression".

In the code, we currently have the FldName defined as a DAO.Field object, and we have: Set FldName = Qry.Fields(2)

Is there something more we would need to use the immediate window, or are we just still seeing errors in the code. I've never really used the immediate window much before, so maybe I'm thinking wrong here.

If you have any other suggestions, please let us know.

 

[scking]

Ok, the immediate window is probably the most important tool we have as VBA developers. It tells you everything you ever wanted to know about your code, even if you don't want to know. It represents a static status of your code. In order to do that you HAVE to tell it where you want to get the information. That is done by setting a breakpoint on the offending line of code. Place your cursor on the line that gives you the error and press {F9}. This will stop the processing prior to your error and allow you to see exactly the values of the variables that will be used on that line of code. Then is when the immediate window will show you values rather than give you an error. If FldName is defined as a string it will give you a string name, if it is defined as an object with properties it will allow intellisense to display all the methods and procedures for the object. You just need to select a property that returns a value. For instance, if FldName was instantiated as a field object, you would then be able to type ?FldName.Name and get the name property which returns a string showing you the name of the field.

Steve King Growth follows a healthy professional curiosity