Resetting recordsets...

[Hatrick]

I have an application with a loop that loads data into a recordset via SQL, then performs some calculations and reloads a new set of data into the same recordset(in place of the old data).
How do I clear the BOF and EOF settings of the recordset when I load new data into the recordset?
It is currently giving my incorrect EOF/BOF values when I load the second set of data into the recordset.
Thanks,
Dan

[jfg]

Hatrick

before you load new data into the recordset (the second time round) you should set it tonothing

set rsQuery=nothing

then load second set of data
what cursortype of recordset areyou using

John

[Lee M.]

So what you mean is you want to replace the existing value in the record?
If I understand you correctly you would use the Recordset object's navagation methods to move to the record you want to edit.
Then you would execute the recordset's Edit method.
Then assign the values to the fields in the record using the Fields collection of the Recordset object.

i.e. rs.Fields("Lastname") = "Smith"

or, because Fields is the default collection of the recordset object, you can omit the explicit refrence to the fields collection, when you assign a value to it:

rs!Lastname = "Smith"
Save the record to the database using the recordsets update method.

I hope this helps!
Lee

[Hatrick]

The code for what i am trying to do is below: Parts that are unimportant to the question have been cut out for space/clarity reasons. I don't want to replace any part of a record, but the whole recordset. i want to load data into the set, calculate some values, clear the WHOLE set then load the next set of data into the recordset.

I tried the "set rsRawMatUnits = Nothing" but I still got erroneous values for the BOF/EOF the second time through.
Thanks for any help with this.


Do Until rsProdNum.EOF

ProdNum = rsProdNum(0)
Dim rsRawMatUnits As ADODB.Recordset
Set rsRawMatUnits = New ADODB.Recordset

strSQL = "SELECT RawMatPrice.RawMatDesc...Blah Blah Blah... And ProdSpecs.ProdNum = '" & ProdNum & "'"


Set rsRawMatUnits = dbCon.Execute(strSQL)

If Not (rsRawMatUnits.BOF) And Not (rsRawMatUnits.EOF) Then
rsRawMatUnits.MoveFirst
End If

i = 0
Do Until rsRawMatUnits.EOF

***perform calculations code goes here...

rsRawMatUnits.MoveNext

Else
rsRawMatUnits.MoveNext
End If
i = i + 1
Loop
rsProdNum.MoveNext
Set rsRawMatUnits = Nothing
Loop
End If

[Hatrick]

Okay, I feel like a moron...
It was reading true to both BOF and EOF on the second set of data pulled into the recordset because it was empty. The SQL statement wasn't finding any matching records the second time through.
And I can't even use the "it's monday or Friday" excuse.
Thanks for the help. I'll just crawl back into my little hole now...

[Lee M.]

We've all been there!!!