Recordset and Field Referencing Questions (1 Viewer)

[kermit5]

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

 

[afloyd]

This looks like your working with a two-dimensional array. You might want to put you recordset into an array and then you will have an easier time referencing specific row,col values.

for example

array(1,2) would be 2nd row, 3rd value. Arrays are zero based when referencing rows and columns.

you will have to do a loop to fill the array, but then life should be much easier if you have to pick a value from in the middle of grid.

HTH

-Al

 

[Pat Hartman]

You've got your spreadsheet hat on. Processing data in tables is different. Unfortunately, you have probably also designed your table so that it stores data as you would in a spreadsheet.

To reference a particular column of a particular row, you must first position the recordset to the row you want to work with. You can use one of the Find methods to do that. Then you just reference the column name qualified by the recordset name -- rst!A or rst!B, etc.

Before you invest a lot of time in coding, you might want to do a little reading on database design and normalization to be sure you have properly defined your table.