...Snapshot record set.

[JoanieB]

I have a query fueling a form. The properties show that the query is Dynamic. In the code behind the form, I set a recordset and do some work. After scrolling thru the recordset, I then update fields on the form. When I try to set me!txtfield.value = variable determined in code, I get a runtime error 3226 for a recordset that cannot be updated. I've tried opening the recordset as Dynamic {didnt think it would help} and it still gives me the error. I know what the problem is, but I don't see where there's an error. Any ideas?
Here's the code:

Dim RS As Recordset
Dim Outs As Double
Dim Pricer As Double
Dim Outb As Double

Outs = 0
Outb = 0
Pricer = 0
DoCmd.SetWarnings False
DoCmd.Close acTable, "TblFgnBreakdown"
DoCmd.OpenQuery "QmakFgnBreakDown"
Set RS = CurrentDb.OpenRecordset("TblFgnBreakdown", dbOpenDynaset)

If RS.RecordCount > 0 Then
RS.MoveFirst
Do Until RS.EOF
If RS!Status <> &quot;Verified&quot; Then
Outs = Outs + RS!ShareAmt
End If
RS.MoveNext
Loop
Pricer = Me!TxtPrice.Value
Outb = (Outs * Pricer)
Me!TxtOutsBal.Value = (Outs * Pricer)
Me!TxtOutsShs.Value = Outs
Else
Me!TxtOutsBal.Value = 0
Me!TxtOutsShs.Value = 0
End If
Set RS = Nothing
DoCmd.Close acTable, (&quot;TblFgnBreakDown&quot

Does it have to do with the variables being &quot;Double&quot;? I need them as Double b/c we have to calculate to 5 decimal places, but the field on the form is &quot;Standard&quot; to show 5 decimals.

Thanks for your time!

 

[JoanieB]

PS: It only happens on SOME of the records being updated...

 

[vb6novice]

JoanieB,

It doesn't look like your code has anything to do with the problem since you code is not trying to update records (for that matter, I don't know why you are opening your RS recordset as a dbOpenDynaset). The problem is more likely to be in the query that is fueling your form. Does that query contain totals? If so, you will not be able to update it because Access does not know which records are to be changed.

Please give further info about the query feuling the form.

 

[JoanieB]

I only opened it as dynaset when trying to figure this out before. As to the problem: I know now that it's the main query (call it Qry1) fueling the form. Originally I had a 'grouped' query in Qry1, but only as a qualifier - no data was being used in the query's output other than 1 table - I'm just checking against a maximum date. I tried to create another query from the 'grouped' query and using it instead; I also tried creating a table from the grouped query and using that. I still can't update the output of Qry1. How can I specify that I need records that match the maximum date determined in a query and still update the output?

 

[vb6novice]

It is unclear why you need a 'grouped' query just to have records that match a maximum date. You usually only need grouping when you want to know the sum or avaerage or count of some group of records. If you know what the maximum date is for all records, that date can be the criteria for the field which you are comparing. If the maximum date is different for each record, your query can include a field with a calculation in the critera.

Your code seems to indiciate that you are updating a total value of all the Outs multiplied by Pricer. That is not normally a value that would be kept in the same table with the individual records you probably have in tblFgnBreakdown.

Is Qry based on tblFgnBreakdown. What are the fields in tblFgnBreakdown and what is the SQL for the query.