General reference clarifications

[Scotth]

I have some basic questions about how I reference different parts of a recordset. Lets say that in my query, qryRecordSource, I have records 1,2,3 and 4 which each contain vaules for fields A,B,C,D. Graphically, it looks like this:


Record..... A.............B.............C.............D
1.......<valueA1>..<valueB1>..<valueC1>..<valueD1>
2.......<valueA2>..<valueB2>..<valueC2>..<valueD2>
3.......<valueA3>..<valueB3>..<valueC3>..<valueD3>
4.......<valueA4>..<valueB4>..<valueC4>..<valueD4>

I would then use the code:

Dim rst as DAO.Recordset
Dim dbs as DAO.Database
Dim qd as DAO.QueryDef
Dim fld as Field
Dim frm as Form
Dim ctl as Control

Set dbs = CurrentDb
Set qd = dbs.QueryDefs!qryRecordSource
qd.Parameters![MyParam]= "MyValue"
Set rst = qd.OpenRecordset


This much I think I understand. What is the syntax that I need to use to reference the following items:

• the name of field C?
• the 2nd record?
• the value contained in field B of record 4?

Additionally,

• what does frm.Name reference?
• what does fld.Name reference?
• what does ctl.Name reference?

Any help understanding this would be greatly appreciated.

Scott

[IROY55]

1.
the name of field C?
Fields(C).Name
the 2nd record?
"Select * From Table1 where A=value2"
the value contained in field B of record 4?
This is a bit complicated: you may get all records and then loop through your recordet (and resetting counter at the same time). Wnen counter = 4 then myvalue = Fields(B).Value

Additionally,


what does frm.Name reference?
what does fld.Name reference?
what does ctl.Name reference?

Name is a property which represents OBJECT's name. In this case it's a Form, Field and Control respectfully.

Roy

[James Stanich]

Item 1 :

Sub FieldExample()
   Dim rs As ADODB.Recordset
   Dim fld As ADODB.Field
 
   Set rs = New ADODB.Recordset
   ' Open the recordset, specifying an SQL statement
   ' and a connection string.
   rs.Open "Select * from authors", "DSN=pubs;UID=sa"
   
   Debug.Print "Fields in Authors Table:" & vbCr
 
   ' Loop through each Field object in the
   ' Fields collection of the table and display properties.
   For Each fld In rs.Fields
      Debug.Print "Name:  " & fld.Name & vbCr & _
            "Type:  " & fld.Type & vbCr & _
            "Value: " & fld.Value
   Next fld
 
   ' Close the recordset.
   rs.Close
 
End Sub

Item 2 :

IF rst.Eof=False then
 
Rst.MoveNext
 
End If

Item 3 :

' By looping you can get all values : 
 
Dim IntNext as Integer, IntMax as Integer, MyData as String
 
IntMax=rst.Recordcount
 
If IntMax>0 Then
 
For IntNext = 1 to IntMax
 
MyData = _
rst.FIELDNAMEHERE1 & vbtab & _
rst.FIELDNAMEHERE2 & vbtab & _
rst.FIELDNAMEHERE3 & vbtab & _
rst.FIELDNAMEHERE4
 
Debug.Print MYDATA
 
rst.MoveNext
 
Next
 
' This should print the contents of your recordset to the Immedaite Window