Recordset Navigation and variable column name 1

[elvenmaiden]

Receiving the following error message:
"Item cannot be found in the collection corresponding to the requested name or ordinal"

Scenerio: The rsSalesHours is a table in Access generated by a crosstab query which has variable column names (i.e. column names Jan, Feb, Mar... or June, July, Aug depending on the crosstab parameters).

Need to create another table that contains the difference between two of these variable columns based on a users selection from a combo box being populated by the rsSalesHours column names. i.e. could be Dec - Jan or Dec - Jun.

The combo box selections are held in the mstrComparison and mstrCurrent.

Want to navigate through the recordset and collect the value from the variable column name the user selected. Do a difference calculation and use the calculation and uniqueID from rsSalesHours to populate another recordset.


The error occurs when trying to use a variable as a column to reference rsSalesHours data (see red text).

Can anyone help I am absolutly stumped! Thanks!



'dimensions connection and recordset
Dim CurConn As New ADODB.Connection

'holding tblCrosstab info for navigation
Dim rsSalesHours As New ADODB.Recordset
'holds difference of each record found in tblDifference
Dim rsDifference As New ADODB.Recordset

Dim intX As Integer
Dim intColumnCount As Integer

'Open Recordsets for use
With rsSalesHours
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenStatic
.Open "SELECT * FROM tblCrosstab"
End With

With rsDifference
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenDynamic
.LockType = adOpenDynamic
.Open "tblDifference"
End With

'counts columns in recordset
intColumnCount = rsSalesHours.Fields.Count

Dim intDifference As Integer
Dim intCurrent As Integer
Dim intComparison As Integer

'ensures both dates have been selected for difference calculation
If mblnComparison = True And mblnCurrent = True Then
rsSalesHours.MoveFirst
Do Until rsSalesHours.EOF
For intX = 3 To intColumnCount - 1
If rsSalesHours(intX).Name = mstrCurrent Then
intCurrent = rsSalesHours!mstrCurrent
End If
If rsSalesHours(intX).Name = mstrComparison Then
intComparison = rsSalesHours!mstrComparison
End If
Next intX

intDifference = intCurrent - intComparison

rsDifference.AddNew
rsDifference!Cust# = rsSalesHours!Cust#
rsDifference!Difference = intDifference

rsSalesHours.MoveNext
Loop

 

[PraveenMenon]

you cant do this. Because it is looking for a field named mstrCurrent in rsSalesHours

try this
dim str

str = "select (" & fldName1 & "-" & fldName2 & &quot from " & tableName

Set rec=cn.execute(str)

All the best Praveen Menon
pcmin@rediffmail.com

 

[mattKnight]

I think

intCurrent = rsSales.Fields(mstrCurrent)

will do it as the ! operator if 'shorthand' for the field, but you can access the fields collection of the recordset "conventionally"

iow

intCurrent = rsSales!mstrCurrent

is equivalent to

intCurrent = rsSales.Fields("mstrCurrent")

is equivalent to asking for the value of the field called mstrCurrent

so if you remove the the " you will get the desired response.

alternatively, if you know which 'column' the field is in the table you can refer to it by integer (zero based collection I believe) Thus the 3rd column could be accessed by

intCurrent = rsSales.Fields(4)

I hope this helps you


Matt

 

[elvenmaiden]

Thanks mattKnight,

This did the trick:
intCurrent = rsSales.Fields(mstrCurrent)

You are the bomb!!!!!