records on a subform

[paulevans]

HI I have a form that contains a subform. The subform collects its data from a query. based on an id code from the main form. The sub form is also a continuous form.

what I am trying to do is beable to select any record on the form and change a percentage value. This will give me a new total for a given record.

on the main form I sum all the totals of the sub form using a simple query.

the problem I have is that each time I add a new percentage to a record all my values add up correctly but the focus of my sub form returns to the first record of the sub form. The code I use on the percent exit field is:

Private Sub thisclaim_Exit(Cancel As Integer)
Dim val1 As Double
Dim val2 As Double
Dim val3, val4, val6 As Double
Dim val5 As Integer
Dim oldper As Double
Dim mess As String


val1 = Me![Valuation]
val2 = Me![thisclaim]
oldper = Me![per]

'check for values of percent must be 0 to 100 not below not more

If val2 < 0 Or val2 > 100 Or val2 < oldper Then
mess = MsgBox("The Value you have entered is not within the permitted range." & _
vbCr & _
"Please enter a value between " & Me![per] & " and 100", vbOKOnly, "Invalid Data Range")
Me![thisclaim] = oldper
Me![percom] = val1 * (oldper / 100)
Exit Sub

End If

val3 = val1 * (val2 / 100)

Me![percom] = val3


Dim dstring As String
If IsNull(Me![Description]) Then
dstring = ""
Else
dstring = Me![Description]
End If

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Forms![newapplication]![csa2].Requery
Forms![newapplication]![soa3].Requery

If IsNull(Forms![newapplication]![soa3].ItemData(0)) Or Forms![newapplication]![soa3].ItemData(0) = 0 Then
Forms![newapplication]![variationoffset] = 0
Else
Forms![newapplication]![variationoffset] = Forms![newapplication]![soa3].ItemData(0)
End If


DoCmd.FindRecord dstring

End Sub

Can any one tell me why and what I need to do to change this.
Is this because the subforms data is based on a query and is updated after the rest of the record?
thanks for any help

 

[paulevans]

Hi I have replace the data source back to the original table and my code works as I wanted.

This therefore looks like the query is is doing a requery after my code and hence reseting the record back to the first.

Therefore where would I need to put my code inorder after the query

 

[paulevans]

For all of you that looked at this post and have a similar problem.

The solution to my problem was to move the combo box queries to the sub form
and then when I requery the combo box it is only the sub form that is requried and not the main form. It seems that if you issue a requerry to a combo box on a main form the requery takes place after all events on the subform and hence reloads the sub form last.

If anyone disagrees with this please let me know as would hate to be barking up the wrong tree.