Changes to form recordset not committing to disk

[Stoooo]

Any help with the following problem is gratefully received.

I have a combo box on a parent form that causes updates to a commission field in a subform. It is a continuous subform hence the need to scroll through each record in its recordset and apply the update as necessary.

The problem is that the changes to this field do no appear until the second time I update the combo box. .

E.g.
Commission field starts as 0.
Enter a value into the combobox (say 1) and check the commission field shows the corresponding new value (should be 40 say)
Still shows zero.
Change the combo box value to something else (say 2) check the commission field shows corresponding new value (should be 80 say)
Is now showing the value corresponing to 1 I.e. 40?!

I suspect I need to commit these changes somehow, I thought recordset.update would do this, but I guess not
Please help as I've been stuck on this for hours now.

Regards

Stooo.

comboBox_AfterUpdate()

'column number will be used to select the correct handset
'commission from a select query, dependent on upgrade status

Dim columnNumber As Integer

If Me!Upgrade Then

Select Case Me![Upgrade Banding].Value
Case "L"
columnNumber = 1
Case "M"
columnNumber = 2
Case "H"
columnNumber = 3
Case Else
columnNumber = 0
End Select

Else
columnNumber = 0
End If

'column number is adjusted for the length of the contract
columnNumber = columnNumber * Me!Term.Value

'define query and recordsets for handset commission and
'recordset for handset form where data will be entered

Dim wksThis As DAO.Workspace
Dim qryHSCommission As DAO.QueryDef
Dim rstHSCommission As DAO.Recordset
Dim rstHandset As DAO.Recordset
Dim idxRecord As Integer

Set wksThis = DBEngine(0)
Set qryHSCommission = CurrentDb.QueryDefs("qryHandsetcommissions")
Set rstHandset = Me!frmCustomerHandsets.Form.Recordset

'if recordset contains records then
If rstHandset.RecordCount <> 0 Then

'move to the first record on the handset form
rstHandset.MoveFirst

Do While Not rstHandset.EOF
qryHSCommission.Parameters("inputParam0Manufacturer") = rstHandset("Manufacturer")
qryHSCommission.Parameters("inputParam1Handset") = rstHandset("Handset")
qryHSCommission.Parameters("inputParam2Network") = Me!Network.Value
Set rstHSCommission = qryHSCommission.OpenRecordset
rstHSCommission.MoveFirst
rstHandset.Edit
rstHandset!Commission.Value = rstHSCommission(columnNumber).Value
rstHandset.Update
rstHSCommission.Close
rstHandset.MoveNext
Loop

End If

Set rstHSCommission = Nothing
Set qryHSCommission = Nothing
Set rstHandset = Nothing

 

[llkhoutx]

I'm surprised that your following line doesn't generate an error:

Set rstHandset = Me!frmCustomerHandsets.Form.Recordset

It does not use correct VBA syntax.

Correct syntax is

dim db as dao.database - Where's the database?
set db=currentdb - or whatever
Set rstHandset=db.openrecordset("tableName",dbopendynaset)

 

[Stoooo]

Hi llkhoutx,

Thanks for the reply, but I am trying to change the recordset of the subform rather than update the underlying table directly, as the form(s) could be based upon different tables in different situations. As I'm adjusting the form's recordset there is no need to reference the database object as the parent form (Me) is a child object of the database object (unless I have misunderstood). I am, I must admit, trying to take a shortcut rather than create a fairly lengthy SQL statement, but believe I'm in the right ballpark.

I feel the error may be that I'm failing to trigger an event somewhere, but am unsure of what exactly.

Any thoughts?

 

[RoyVidar]

Given that the subform control name is frmCustomerHandsets, the syntax for retrieving the subform recordset is correct (though, using version 2000+, I'd consider fetching the .recordsetclone in stead of the recordset).

It might be that the only thing you need is a .requery at the end of the sub.

Me!frmCustomerHandsets.Form.requery

Couple of other things,
You can perhaps increase performance a bit by not opening the rstHSCommission per each iteration, but instead have the recordset open all the time, and use .findfirst:

rstHSCommission.findfirst "Manufacturer ='" & rstHandset("Manufacturer") & _
"' and Handset ='" rstHandset("Handset") & "' and Network ='" & Me!Network.Value & "'"

- here I've assumed all fields are text, hence the single quotes, remove them for those fields having a numeric persuasion.

The DAO help states that setting a recordset to nothing (releasing it) effectively also closes it. I may be paranoid, but I always close them explicitly prior to releasing them.

And - I think also that if you'd go the query way (executing a query against the subform recordset in stead of recordset update) may improve on performance also (but again, remember to requry the form)

 

[Stoooo]

Thanks for the replies. I have solved the problem. I was updating the upgrade banding AFTER trying to adjust recordset instead of before, effectively using the old value. I've moved this to the start of the sub and the code now works fine.


Once again thanks for all your posts.


Stoooo