Forcing a record in sub before saving record on main form

[ImStuk]

I have a main form that is the Date, Employee initials, dept., etc...

A sub form that has the daily activities for them.

How can I make sure there is at least one record in the sub form before saving the data in the main form?

 

[Eupher]

When do you want this validation performed?

Ken S.

 

[ImStuk]

I don't want them to start the main form, then close it and not have data on the sub form. When they try and close the main form I would like a dialog box to tell them they are cancelling the main record unless there is data entered in the sub.

 

[RobSindle]

Are you asking:
1. How do I force a record into the subform so that a mainform record will always have at least one related subform record?
OR
2. How do I check for the existence of a subform record before I leave the mainform, so I can "NOT SAVE" the main form record if they didn't supply a subform record?

Reminder.
If referential integrity is enforced between the 2, the mainform record will have to "exist" before you can "add" the subform record.

 

[ImStuk]

2. How do I check for the existence of a subform record before I leave the mainform, so I can "NOT SAVE" the main form record if they didn't supply a subform record?

 

[Eupher]

ImStuk,

Here's a link with good info about referencing forms and subforms:

http://www.mvps.org/access/forms/frm0031.htm

Also take a look at the RecordCount property and the Undo method. Basically, I would test for a recordcount of 0 in the subform's recordset, and use the undo method to cancel any changes to the main form.

Ken S.

 

[TheAceMan1]

How are ya ImStuk . . . . .

In the MainForms [blue]BeforeUpdate[/blue] event, copy/paste the following:

[blue]   Dim sfrm As Form, Msg As String
   Dim Style As Integer, Title As String, DL As String
   
   DL = vbNewLine & vbNewLine
   sfrm = Me!subFormName.Form
   
   If sfrm.RecordsetClone.RecordCount = 0 Then
      Msg = "No Daily Activities for this Record!" & _
            "Record can't be saved!" & _
            "Enter at least one Activity and try again . . ."
      Style = vbCritical + vbOKOnly
      Title = "Missing Data Error! . . ."
      MsgBox Msg, Style, Title
      [purple]Cancel = True[/purple]
   End If

   Set sFrm = Nothing[/blue]

See Ya! . . . . . .

 

[PHV]

Hi TheAceMan1 !
I'd replace this:
sfrm = Me!subFormName.Form
By this:
Set sfrm = Me!subFormName.Form

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ImStuk]

I'm getting a Compile Error...invalid use of property on the "sfrm =" part of this...

sfrm = Me!frmActivitiesSub.Form

 

[PHV]

Replace frmActivitiesSub by the name of the control hosting the subform.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ImStuk]

Thank you. I don't get a compile error now, but when I tab from the main form I am getting the custom error about "no data" before I get a chance to enter data in the sub form.

I guess the main form is attempting to update before tabbing to the sub form.

I am close though...

 

[TheAceMan1]

ImStuk . . . . .

[blue]PHV[/blue] is right. Should be:

[blue]   Set sfrm = [purple][b]Me[/b][/purple]!frmActivitiesSub.Form[/blue]

[blue]Replace frmActivitiesSub by the name of the control hosting the subform.[/blue]

And [purple]Me[/purple] is?

See Ya! . . . . . .