Newbie data validation question

[Bob]

I have form and linked subform, frmMain and subform1. I want to intercept
the event that is triggered when I try to move to the next record on
frmMain. Before I am allowed to move to the next record shown on frmMAin. I
need to verify that the sum of all amounts in column 2 for the records in
Subform1 is 0.

I don't think I can use the BeforeUpdate event of frmMain, since the record
dat of frmMain is already saved when my user goes from frmMain to subform1,
so moving to the next frmMAin record would not trigger the BeforeUpdate evnt
of frmMain. Similarly the BeforeUpdate event of subform1 will trigger every
time we change rows and that validation only needs to be done before we
close the form or when we try to move off the current record shown on
frmMain.

I'm logically thinking of what event is available to trap before I complete
the move to another record (next or previous) on frmMain, I can't seem to
find any directly in the list of available events for the form.

Can anyone give me an idea or a bit of sample code that I can use to do
this?

Thanks for any help.

Bob

 

[Dirk Goldgar]

I have form and linked subform, frmMain and subform1. I want to
intercept the event that is triggered when I try to move to the next
record on frmMain. Before I am allowed to move to the next record
shown on frmMAin. I need to verify that the sum of all amounts in
column 2 for the records in Subform1 is 0.

I don't think I can use the BeforeUpdate event of frmMain, since the
record dat of frmMain is already saved when my user goes from frmMain
to subform1, so moving to the next frmMAin record would not trigger
the BeforeUpdate evnt of frmMain. Similarly the BeforeUpdate event of
subform1 will trigger every time we change rows and that validation
only needs to be done before we close the form or when we try to move
off the current record shown on frmMain.

I'm logically thinking of what event is available to trap before I
complete the move to another record (next or previous) on frmMain, I
can't seem to find any directly in the list of available events for
the form.

Can anyone give me an idea or a bit of sample code that I can use to
do this?

Although there's a KnowledgeBase article on how to implement a
"RecordExit" event, which might almost fill the bill for you (but not
when the form is being closed), I think your best bet is to have a
function that you call in the form's Current event and the form's Unload
event. That procedure would return the primary key of the first record
(if any) that didn't meet your criteria, so you could then use that
information to position the form to that record.

Such a function might look something like this (air code):

'----- start of function code -----
Function OutOfBalanceRecord() As Variant

Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = _
"SELECT MainTable.MainID " & _
"FROM MainTable INNER JOIN SubTable " & _
"ON MainTable.MainID = SubTable.MainID " & _
"GROUP BY MainTable.MainID " & _
"HAVING Sum(SubTable.Amount) <> 0"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
With rs
If .EOF Then
OutOfBalanceRecord = Null
Else
OutOfBalanceRecord = .Fields(0)
End If
.Close
End With
Set rs = Nothing

End Function
'----- end of function code -----

Then, with this function in place, you'd have code like this in the
form's module:

'----- start of code for form module -----
Private Sub Form_Current()

Dim varBadID As Variant

varBadID = OutOfBalanceRecord()

If Not IsNull(varBadID) Then
If Me.MainID <> varBadID Then
MsgBox "Record " & varBadID & " is out of balance. " & _
"Please correct it before proceeding."
Me.Recordset.FindFirst "MainID = " & varBadID
End If
End If

End Sub

Private Sub Form_Unload(Cancel As Integer)

Dim varBadID As Variant

varBadID = OutOfBalanceRecord()

If Not IsNull(varBadID) Then
If MsgBox( _
"Record " & varBadID & " is out of balance. " & _
"Do you want to correct it now?", _
vbYesNo,
"Out of Balance") _
= vbYes _
Then
Cancel = True
If Me.MainID <> varBadID Then
Me.Recordset.FindFirst "MainID = " & varBadID
End If
End If
End If

End Sub
'----- end of code for form module -----

Again, that's all air code, but something along those lines ought to do
the job.