Impossible Subform Question??

  • Thread starter Thread starter tyng
  • Start date Start date
T

tyng

I am currently working on a
access program which has a main form with 4 subforms. The fou
subforms are sign-off forms for various authorities to approv
changes made on the main form. Once the main form is filled in, a
auto e-mail request is sent to all of the approprite approvers t
view and sign off in their respectful subform. The problem is,
want to be able to send an approval e-mail out once all subforms hav
been signed off (approved). The subforms basically have a numbe
field (DeviationNumber)corresponding/linked to the main form number
and initial (Signed), and a date field (Date). After update of th
last subform date field, I would like it to send a distributio
e-mail. Note that there is no order in which the subforms will b
initialed/signed. I have tried running append queries to check boxe
after each subform is signed off with no success (linking probs).
Probably need to run an if then statement to check each entry and i
all are not null then send e-mail but I am unsure how to do this i
VB
 
This is a simple (if somewhat inefficient) way to look up each of the tables
that the subforms are based on, to see if any one of them does not yet have
the DeviationNumber. You can then call the function in the AfterInsert event
of each subform, like this:

Private Sub Form_AfterInsert()
If AllApproved(Me.DeviationNumber) Then
DoCmd.SendObject, ...
End If
End Sub


Function AllApproved(lngDeviationNumber As Long) As Boolean
Dim strWhere As String
Dim bUnapproved As Boolean
Dim varApproval As Variant

strWhere = "[DeviationNumber] = " & lngDeviationNumber

bUnapproved = isnull( DLookup("Id", "Table1", strWhere))
If Not bUnapproved then
bUnapproved = isnull( DLookup("Id", "Table2", strWhere))
End If
If Not bUnapproved then
bUnapproved = isnull( DLookup("Id", "Table3", strWhere))
End If
If Not bUnapproved then
bUnapproved = isnull( DLookup("Id", "Table4", strWhere))
End If

AllApproved = Not bUnapproved
End Function
 
Back
Top