Problem with a recordset

[paddymx]

I have the following in a Command Button on a Sub from. When I run it I get no errors and the record set runs. But, even though it looks like the rst is being "moved next", per the msgbox before the move next(AbsolutePosition 0,1,2) of which there are 3 AND the msgbox just inside the "While", the MixItemID (number) stays the same telling me its never moving off the first rst of the strSQL. I've tried several variations with the same result. What am I doing wrong?


Public Sub ReCalcB_Click()
On Error GoTo ErrMsg
Dim dbs As Database
Dim rst As Recordset
Dim strSql As String
Set dbs = CurrentDb()
strSql = "SELECT * FROM [MixItemT]"
strSql = strSql & " WHERE [MixID]=" & Me![MixID] & " AND [MixitemID]> 0" & ";"

Set rst = dbs.OpenRecordset(strSql)


If (rst.EOF) Then
msgbox " There are no items for this mix"
Else
While (Not (rst.EOF))
msgbox "in while Mixid " & MixID & "MixitemID " & MixItemID
'Me.TFocus.SetFocus ' cause JuicePerCent var to be recalculated in each MixItem
msgbox rst.AbsolutePosition & "MixID " & MixID & "MixItemID " & MixItemID


rst.MoveNext
Wend
End If


rst.Close
dbs.Close


Me.Refresh 'refresh subform
Forms!mixingf!MixML = SumMl ' on main form
Exit_ErrMsg:
Exit Sub
ErrMsg:
Resume Exit_ErrMsg
PMMerrormsg (Err)
Resume Exit_ErrMsg


End Sub

[pbaldy]

The values MixID and MixItemID are likely coming from the form, not the recordset, which I assume is your intention. To get them from the recordset:

rst!MixItemID

[paddymx]

The values MixID and MixItemID are likely coming from the form, not the recordset, which I assume is your intention. To get them from the recordset:

rst!MixItemID

As always, thanks for the help!

MixID and MixItemID are both fields in the subforms recordsource table with MixID being the linked field of the main form. The "Tfocus" is a field in the subform I want to access for each MixItemID. I thought the strSQL was really a MIXItemID recordset. If not, how should I write the strSQL.

Edit : Yes MixID is on the subform "me!Mixid" as a way to isolate the records in the recordset.

EDIT2: I just changed strSQL to "strSql = strSql & " WHERE [MixItemT.MixID]=" & Me![MixID] & " AND [MixItemT.MixitemID]> 0" & ";" just incase their was a ambiguity but got the same results.

[pbaldy]

I'm not clear on what you're trying to accomplish. The SQL looks fine, it should get records for the current mixID. I meant that lines like this:

msgbox "in while Mixid " & MixID & "MixitemID " & MixItemID

are not getting values from the recordset, they're coming from the form. Your code doesn't really "do" anything, unless calling the SetFocus is supposed to, but it's not likely going to do what you expect. The SetFocus works on the form, not within the recordset, so it will always be on the same record.

[paddymx]

here is a picture I want to cycle through the records for a given mix(mixID) and then use the tfocus on the form to recalculate how can I write the sql to cycle through the records?:

[pbaldy]

I suspect it is cyclying. The 2 is changing because it's based on the recordset. Is the 6466 changing as well? My guess is not, since it isn't based on the recordset. In any case, I would perform whatever process is going on in the SetFocus event in this code, then requery the form. As I said, you sort of have apples and oranges here. You're cycling through a recordset but performing a process on a form. That process will only affect the current record on the form.

[paddymx]

No the 6466 is not changing . When I had the Tfocus active it only acted on the current record of the sub form. I will change the me.refresh to me.requery and see what happens.perhaps a better question is how do I get it to act on each record instead of the forms current record? BTW, requery didn't change anything.

[pbaldy]

I don't think it will matter. I think you have to perform whatever calculation is happening in the SetFocus event in your loop.

[paddymx]

Okay I'll try to figure it out. The calculations in Tfocus use other "T" fields on the form and require values from the parent , so I'm probably stuck with the current record!

[paddymx]

I don't think it will matter. I think you have to perform whatever calculation is happening in the SetFocus event in your loop.

I am working out the final changes during the loop, but, this resolved my issue. Thanks pbaldy

[pbaldy]

Happy to help!