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!

"johnlute" <jlute@marzetti.com> wrote in message 
news:433f18a8-914f-4d6a-97f3-d42fb4c38892@h27g2000yqm.googlegroups.com...
> 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?

-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

Hi, Dirk!

> Maybe I'm misunderstanding what you are trying to do. =A0Could you explai=
n 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?

johnlute wrote:

>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
>

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.

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.

-- 
Marsh
MVP [MS Access]

"johnlute" <jlute@marzetti.com> wrote in message 
news:d1dee189-3e76-4271-84fa-24c00a771f21@n34g2000yqb.googlegroups.com...
>
>> 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?

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.

-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

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 =3D Null
    If Len(LastRecordID & vbNullString) > 0 Then
        If (LastRecordID <> Nz(Me.IMNumber, 0)) Or Unloading Then
            If DCount("*", "tblINGsAllergens", "IMNumber=3D" &
LastRecordID) =3D 0 Then
                If MsgBox("Allergen information is required!", _
                            vbCritical + vbOKOnly) Then
                    GoBackID =3D LastRecordID

                    If DCount("*", "tblINGsSensitivities", "IMNumber=3D"
& LastRecordID) =3D 0 Then
                        If MsgBox("Sensitivity information is
required!", _
                            vbCritical + vbOKOnly) Then
                        GoBackID =3D 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=3D" & GoBackID
    Else
        LastRecordID =3D 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 i=
n a
> module-level variable named "LastRecordID" of the *previous* record's ID
> value. =A0Then, each time we move to a new record, we check whether the
> previously current record had any child records. =A0If it didn't, we prom=
pt
> 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." =A0You may or may not want to allow them that choice, but if you do=
n't,
> you may find them blowing away your application via the task manager, jus=
t
> to get out of the loop.
>
> Note also that this is proof-of-concept code. =A0It doesn't handle the ca=
se
> where the user deletes a main form record that doesn't have a child. =A0I
> 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!!!

Hi, Marsh.

> Circular problem. =A0You 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. =A0After 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!

"johnlute" <jlute@marzetti.com> wrote in message 
news:2ca530f3-1e89-437b-9a7f-5a5d6710e29c@r27g2000yqn.googlegroups.com...
>
>Holy cow! Is that ever cool!

<g>

> 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

    ' ...


-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

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 need=
 to
> put them in If statements. =A0The 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. =A0Is t=
hat
> what you intended? =A0If not, if you want to test each child table
> independently, then you would revise the above to:
>
> =A0 =A0 Dim strMessage As String
>
> =A0 =A0 ' ...
>
> =A0 =A0 =A0 =A0 =A0 =A0 If DCount("*", "tblINGsAllergens", _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 "IMNumber=3D" & LastRecordID) =3D=
 0 _
> =A0 =A0 =A0 =A0 =A0 =A0 Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 strMessage =3D vbCr & "Allergen informati=
on is required!"
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 GoBackID =3D LastRecordID
> =A0 =A0 =A0 =A0 =A0 =A0 End If
>
> =A0 =A0 =A0 =A0 =A0 =A0 If DCount("*", "tblINGsSensitivities", _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 "IMNumber=3D" & LastRecordID) =3D=
 0 _
> =A0 =A0 =A0 =A0 =A0 =A0 Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 strMessage =3D strMessage & vbCr & _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 "Sensitivity information is requi=
red!", _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 GoBackID =3D LastRecordID
> =A0 =A0 =A0 =A0 =A0 =A0 End If
>
> =A0 =A0 =A0 =A0 =A0 =A0 If Len(strMessage) > 0 Then
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 MsgBox Mid(strMessage, 2), vbCritical + v=
bOKOnly
> =A0 =A0 =A0 =A0 =A0 =A0 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. :)