How can I prevent entry into a subform before parent record is created?

[MatMac]

Hi. I can't work out how to stop the user entering data into a subform, if the parent record has not been started.

The situation...

In my database, a COURSE has one or more SESSIONS, these are separate linked tables, with each SESSION record having an associated COURSE ID number, and referential integrity enforced.

I have a COURSE form, with a SESSIONS subform within it.
I have the navigation buttons in the COURSE form enabled, so the user can move through all COURSE records, and also start a new COURSE record. As the user moves through each COURSE record, the name and date of each associated SESSION record are displayed in the SESSION subform. The subform is enabled for editting and also for creating new SESSION records.

Clearly, if the user has used the COURSE form navigation buttons to start a new COURSE record, but has not yet entered anything into the COURSE form, there is no COURSE ID number generated.

If the user then tries to enter new SESSION details into the SESSION subform, referential integrity constraints will not permit this, and an error message is generated.

What I want to do...

Best option - have the subform disabled until an associated COURSE ID number is generated. I have tried various events, and understand how to disable a control, but can't seem to find the right event triggered when using form navigation buttons to start a new record.

Second best option - hide the error message and display my own, which is less cryptic for the user. (I can display my own, but can't seem to suppress the system message, even with "setwarnings (false)" in my VB on_error event.

What I DON'T want to do - is have to disable adding records from the COURSE form, and instead create a separate ADD COURSE form.

Many thanks in advance.

Mat Mackenzie.

 

[macca the hacke]

What about adding an event procedure to the on current of the subform that makes it locked unless the parent form course id <>""?

 

[boblarson]

You could do what I'm doing on a project of mine, which is to keep the subform invisible until the main form is filled out. When the appropriate fields have been filled out you can use DoCmd.RunCommand acCmdSaveRecord to force the save of the main form before the user can fill in anything in the subform and that will make it so the record exists.

 

[MatMac]

Thanks Bob, but how do I make the subform invisible when the user goes to a new record?

Recall its got to be visible at all other times.

Thanks - Mat

 

[boblarson]

In the On Current event of the Main Form put

If IsNull(Forms!YourMainFormNameHere.YourSubFormContainerNameHere.Form.YourSubFormIDTextBoxHere) Then
   Me.YourSubFormContainerNameHere.Visible = False
Else
   Me.YourSubFormContainerNameHere.Visible = True
End If

The "YourSubFormContainerNameHere" is the name of the control on your main form which houses the subform (it can be the same name as your subform, but it isn't necessarily so).

 

[MatMac]

Many Thanks

Bob, Macca - many many thanks for sorting this out for me. In the end I opted for Macca's suggestion, to keep the subform visible, and lock it when the course ID was null.

Private Sub Form_Current()
If IsNull([Forms]![courses]!tcID) Then
With [Forms]![courses]!Sessionsubform
.Enabled = False
End With
Else
With [Forms]![courses]!Sessionsubform
.Enabled = True
End With
End If
End Sub


It was the OnCurrent event that I was missing. I don't think I really understand when this fires.

Anyway, all working now.

Got another awkward problem listed under Forms forum, which maybeyou guys will find easy as well?!!

All the best - Mat.

 

[boblarson]

As an FYI - the On Current event fires when changing from record to record.

 

[MatMac]

Brilliant - that's just what I was after.

Cheers - Mat.

 

[MStef]

Look at "DemoMainFA2000.mdb",
open frmMAIN and try.