Recordset Question

[karmahum]

I am using a recordset to retrieve data from a table. Because I need to retrieve data from a number of fields within in the table (depending on the contents of a number of textboxes), I have created a function that I call when these textboxes are exited. Into the function I feed the value of the textbox as well as the name of the field that the recordset should filter on. Below is the code:

Function basHealthRiskScore(strField As String, strValue As Variant) As Double
Dim rsSnap As Recordset
Dim Counter As Integer
Dim Response As String
Dim tblName As String
Dim strField1 As String
Dim strField2 As String


tblName = "tblHealthRiskFactors"

Set rsSnap = CurrentDb.OpenRecordset(tblName, dbOpenDynaset)
rsSnap.Filter = "[" & strField & "]=" & strValue
Set rsSnap = rsSnap.OpenRecordset
Counter = rsSnap.RecordCount
If Counter = 1 Then 'At least on record found
Me.txtRiskValue = rsSnap!strField1 * rsSnap!strField2
End If
End Function

This works in that it opens the records set and filters properly (the filter will only result in 1 record each time). The problem is that I now want to grab the values from two other fields in the recordset, which will vary. So, is it possible to field the field column number into the function and somehow grab the values?

Or, and this is a big or, is there a better way to accomplish this?

Thanks.

 

[karmahum]

Any thoughts...

Well, no replies to my questions...So, either it can not be done or it is beyond repair.

I am still looking for a solution...Any thoughts would be greatly appreciated.

Thanks.

 

[WayneRyan]

karmahum,

You can reference the fields in your recordset by either:

var = rst.Fields(1)
or
var = rst.Fields("chrg_uid")

I would not suggest referencing them by number because
if you ever change your table's design, it could have very
negative (and hard to debug) effects on your code.

I'd pass the field names in as strings.

hth,
Wayne

 

[karmahum]

Thanks...

Thanks WayneRyan...

I did not know that I could reference the field using the field number. So thanks for that information.

In the end, I used a Select Case to determine which field was calling the function and then reference the apporpriate fields.

Thanks much.