check subform for records

[johnlute]

Access 2003.

I'm trying to sketch together a code to check a subform for records. A
child record is required in the subform before the parent record can
be saved.

I put this together and it works fine except that after it fires I
can't navigate to the subform. Of course, this is because the parent
record hasn't been saved.

Any ideas how to get around this? I could use and Undo but then a user
might want to kill me every time their data gets undone because
they've forgotten to enter a child record.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rst As Recordset
Set rst = sfrmINGsAllergens.Form.RecordsetClone
If rst.RecordCount < 1 Then
Beep
MsgBox "Allergen info is required!", vbCritical
Cancel = True
End If
End Sub

Thanks in advance!

 

[Dirk Goldgar]

Access 2003.

I'm trying to sketch together a code to check a subform for records. A
child record is required in the subform before the parent record can
be saved.

From the sound of it, this is not ever going to work, uness the subform
record is created in advance, through some means other than this form. You
cannot create a related child record on a subform before the main form
record has been saved. Therefore, if you make saving a new main-form record
dependent on having a subform record exist, you have mutually contradictory
requirements.

Maybe I'm misunderstanding what you are trying to do. Could you explain in
more detail?

 

[johnlute]

Hi, Dirk!

Maybe I'm misunderstanding what you are trying to do.  Could you explain in
more detail?

Nope. You're understanding. i'm trying to do the impossible.

I don't know what I was thinking. Since I have to save the parent
record first - how then do I stop from navigating away from said
parent record if the subform has no records?

 

[Dirk Goldgar]

Nope. You're understanding. i'm trying to do the impossible.

I don't know what I was thinking. Since I have to save the parent record
first - how then do I stop from navigating away from said parent record
if the subform has no records?

Now *that* I can help you with.

Here's an example of code for a form's module that prompts the user to enter
a subform record if there isn't one, before moving to a new record or
closing the form:

'------ start of code ------
Option Compare Database
Option Explicit

Dim LastRecordID As Variant

Private Function RequireChildRecord(Optional Unloading As Boolean)

Dim GoBackID As Variant

GoBackID = Null

If Len(LastRecordID & vbNullString) > 0 Then
If (LastRecordID <> Nz(Me.ID, 0)) Or Unloading Then
If DCount("*", "tSub", "MainID=" & LastRecordID) = 0 Then
If MsgBox("No child record entered for record! Go Back?", _
vbExclamation + vbYesNo, _
"Fix Record?") _
= vbYes _
Then
GoBackID = LastRecordID
End If
End If
End If
End If

If Not IsNull(GoBackID) Then
If Unloading Then
DoCmd.CancelEvent
End If
Me.Recordset.FindFirst "ID=" & GoBackID
Else
LastRecordID = Me.ID
End If

End Function

Private Sub Form_Current()

RequireChildRecord

End Sub

Private Sub Form_Unload(Cancel As Integer)

RequireChildRecord True

End Sub
'------ end of code ------

In the above code,

"tSub" is the name of the subform's recordsource table
"ID" is the name of the primary key field of the main form's
recordsource table
"MainID" is the name of the foreign key field in tSub -- the Link Child
Field
The ID and MainID fields are numeric.

Note that what actually happens in the above code is that we keep track in a
module-level variable named "LastRecordID" of the *previous* record's ID
value. Then, each time we move to a new record, we check whether the
previously current record had any child records. If it didn't, we prompt
the user to enter the required info, and go back to the previous record.
This code does allow the user to say, "No, I really don't want to go back
now." You may or may not want to allow them that choice, but if you don't,
you may find them blowing away your application via the task manager, just
to get out of the loop.

Note also that this is proof-of-concept code. It doesn't handle the case
where the user deletes a main form record that doesn't have a child. I
believe that would be easy enough to do, but haven't done it.

 

[johnlute]

Dirk -

Holy cow! Is that ever cool! I actually have two subforms so I tweaked
yuor code to this:
Private Function RequireChildRecord(Optional Unloading As Boolean)
Dim GoBackID As Variant
GoBackID = Null
If Len(LastRecordID & vbNullString) > 0 Then
If (LastRecordID <> Nz(Me.IMNumber, 0)) Or Unloading Then
If DCount("*", "tblINGsAllergens", "IMNumber=" &
LastRecordID) = 0 Then
If MsgBox("Allergen information is required!", _
vbCritical + vbOKOnly) Then
GoBackID = LastRecordID

If DCount("*", "tblINGsSensitivities", "IMNumber="
& LastRecordID) = 0 Then
If MsgBox("Sensitivity information is
required!", _
vbCritical + vbOKOnly) Then
GoBackID = LastRecordID
End If
End If
End If
End If
End If
End If

If Not IsNull(GoBackID) Then
If Unloading Then
DoCmd.CancelEvent
End If
Me.Recordset.FindFirst "IMNumber=" & GoBackID
Else
LastRecordID = Me.IMNumber
End If

End Function

Does that ever work like a charm!

Note that what actually happens in the above code is that we keep track in a
module-level variable named "LastRecordID" of the *previous* record's ID
value.  Then, each time we move to a new record, we check whether the
previously current record had any child records.  If it didn't, we prompt
the user to enter the required info, and go back to the previous record.
This code does allow the user to say, "No, I really don't want to go back
now."  You may or may not want to allow them that choice, but if you don't,
you may find them blowing away your application via the task manager, just
to get out of the loop.

Note also that this is proof-of-concept code.  It doesn't handle the case
where the user deletes a main form record that doesn't have a child.  I
believe that would be easy enough to do, but haven't done it.

Thanks for the elegant and clear explanation. I decided to opt for the
blowing out the app simply because it's sensitive niformation that I
think they'd be afraid to blow out of.

Wowzers! Thanks a whole big bunch!!!

 

[johnlute]

Hi, Marsh.

Circular problem.  You can not create a subform record until
the main form record is saved, but you are preventing the
main form record from being saved until after the subform
record is created.

Yeah. I was having a brain cramp.

I suppose you could gather the allergen data in unbound main
form controls (positioned so they look like the subform).
When you are satisfied that sufficient allergen data has
been entered, save the main form record and then construct
an append query to add the allergen record.  After the first
allergen record has been added, you can make the subform
visible and use it to add more allergen records.

That's something I started to tinker with but you spelled it out
better than what I was trying to do. I'm thinking that this would
work, too.

Thanks for your input!

 

[Dirk Goldgar]

Holy cow! Is that ever cool!

I actually have two subforms so I tweaked yuor code to this:
[...]
If DCount("*", "tblINGsAllergens", "IMNumber=" & LastRecordID)
= 0 Then
If MsgBox("Allergen information is required!", _
vbCritical + vbOKOnly) Then
GoBackID = LastRecordID

If DCount("*", "tblINGsSensitivities", "IMNumber=" &
LastRecordID) = 0 Then
If MsgBox("Sensitivity information is required!",
_
vbCritical + vbOKOnly) Then
GoBackID = LastRecordID
End If
End If
End If
End If
[...]
I decided to opt for the blowing out the app simply because it's sensitive
niformation that I think they'd be afraid to blow out of.

I though you might decide that. But I note two things in the above-quoted
code.

1. Since you're not examining the results of the msgboxes, you don't need to
put them in If statements. The following code would be equivalent, but
simpler:

If DCount("*", "tblINGsAllergens", _
"IMNumber=" & LastRecordID) = 0 _
Then
MsgBox "Allergen information is required!", _
vbCritical + vbOKOnly
GoBackID = LastRecordID

If DCount("*", "tblINGsSensitivities", _
"IMNumber=" & LastRecordID) = 0 _
Then
MsgBox "Sensitivity information is required!", _
vbCritical + vbOKOnly
GoBackID = LastRecordID
End If

End If

2. It looks to me as if you are only checking tblINGsSensitivities if you've
already determined that there isn't a record in tblINGsAllergens. Is that
what you intended? If not, if you want to test each child table
independently, then you would revise the above to:

Dim strMessage As String

' ...

If DCount("*", "tblINGsAllergens", _
"IMNumber=" & LastRecordID) = 0 _
Then
strMessage = vbCr & "Allergen information is required!"
GoBackID = LastRecordID
End If

If DCount("*", "tblINGsSensitivities", _
"IMNumber=" & LastRecordID) = 0 _
Then
strMessage = strMessage & vbCr & _
"Sensitivity information is required!", _
GoBackID = LastRecordID
End If

If Len(strMessage) > 0 Then
MsgBox Mid(strMessage, 2), vbCritical + vbOKOnly
End If

' ...

 

[johnlute]

Awesome catch, Dirk!

I was so thrilled at the function that I jumped the gun a bit. As I
went back and continued testing it I realized exactly what you just
pointed out.

1. Since you're not examining the results of the msgboxes, you don't needto
put them in If statements.  The following code would be equivalent, but
simpler:

Thanks - I flew over that.

2. It looks to me as if you are only checking tblINGsSensitivities if you've
already determined that there isn't a record in tblINGsAllergens.  Is that
what you intended?  If not, if you want to test each child table
independently, then you would revise the above to:

    Dim strMessage As String

    ' ...

            If DCount("*", "tblINGsAllergens", _
                    "IMNumber=" & LastRecordID) =0 _
            Then
                strMessage = vbCr & "Allergen information is required!"
                GoBackID = LastRecordID
            End If

            If DCount("*", "tblINGsSensitivities", _
                    "IMNumber=" & LastRecordID) =0 _
            Then
                strMessage = strMessage & vbCr & _
                    "Sensitivity information is required!", _
                GoBackID = LastRecordID
            End If

            If Len(strMessage) > 0 Then
                MsgBox Mid(strMessage, 2), vbCritical + vbOKOnly
            End If

I want to check each table so your perception and revision is on
track! Thanks for the follow-up and correction. As always you are a
scholar and a gentleman. I hope the Easter Bunny left you a nice
chocolate morsel.