reference field in recordset....by its index? (1 Viewer)

[pungentSapling]

I have a bit of code that creates a one field recordset... however, the record set does not always contain the same field. How can I reference the first field in the recordset without always knowing what the field is called? I sthere a way to reference it by its index?? for instance does the first field in a recordset always have an index of "0"?

here is a snippet of the code (it is a function that is supposed to return the value in the first field(of the first record) of the recordset:

Set dbs4 = CurrentDb
sql = "SELECT " & sEstRef & " FROM tblEstAssembly WHERE woID =" & iWoID
Set rst4 = dbs4.OpenRecordset(sql)
If rst4.EOF = False Then
rst4.MoveFirst
fGetEstimate = rst4!****HERE IS WHERE I AM STUCK***
End If
Set rst4 = Nothing
Set dbs4 = Nothing

End Function


clear as mud???
thanks

 

[RichMorrison]

<<
I sthere a way to reference it by its index?? for instance does the first field in a recordset always have an index of "0"?
>>

Yes.

RichM

 

[pungentSapling]

perhaps you could help me by giving me an example of the syntax... I have tried
fGetEstimate = rst4![0]

and it did not work
also
fGetEstimate = rst4!0
also
fGetEstimate = rst4!"0"

thanks for your help

 

[yippie_ky_yay]

fGetEstimate = rst4.Fields.Item(1)

Let me know if that helps (I have a couple more ideas).

-Sean

 

[dcx693]

I believe it's:
fGetEstimate = rst4.Fields(0)
for the first field.

 

[yippie_ky_yay]

Hey dcx693,

I thought so too, but 0 seems to be used for the index (or record) number.

But you're right that you don't need the keyword "item"!

Cheers!
-Sean

 

[dcx693]

I banged out this DAO code quickly to test out rst.Fields(0) and it seemed to work for all tables and queries I threw at it:
Sub RecordsetTest(tblName As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

 Set dbs = CurrentDb()
 Set rst = dbs.OpenRecordset(tblName)

 MsgBox rst.Fields(0).Name

 rst.Close
 Set rst = Nothing
 Set dbs = Nothing
End Sub

 

[RichMorrison]

The "Fields" collection is the default collection of the DAO "Recordset" object. Therefore;

myRS.Fields(0).Name

is the same as

myRS(0).Name

RichM

 

[dcx693]

True, I just like having the Fields in there anyway. Though if I know the field name, I sometimes using rst("Fieldname") along with rst!Fieldname.

 

[pungentSapling]

resolved

thank you all for the help...

I went with
fGetEstimate = rst4.Fields(0)

it works great... thankyou