Coding and trapping event for subform add new record

[Noise]

I have a main form and a subform. The main form has all the data for
the parent record. The subform has all the data for the child
record(s). There can be many child records for a single parent record.

I want to always be able to add new parent records. Under given
circumstances I want to allow or restrict when child records can be
created. All that works OK.

My problem is when I am on a parent record where the child record is
not allowed to add new records. If I go to add a new Parent record,
then the child record's subform shows up as a big blank spot. I think
it's because at that time of creating the new parent record, the child
record had AllowAdditions = FALSE, so it won't allow it.

It works fine when I am at a parent record with a child record that
allows additions of new child records.

What I want to do is this: I want to be able to always create a new
parent/child record no matter what is the status of the current child
record.

I could set the subform's property to AllowAdditions = TRUE, but I
don't know where to put the code. I tried putting it on the subform,
but apparently got it wrong. I also tried to put it on the main form
but under which event?

What event will always capture the creation of a new record on the
parent form?

I thought about the CommandBeforeExecute event, but I cannot figure
out how to program it. I was thinking of the following, but don't know
what to do with it.




Private Sub Form_CommandBeforeExecute(ByVal Command As Variant, ByVal
Cancel As Object)

Forms![EnterData_MAIN]![EnterData_SUB].Form.AllowAdditions = True

End Sub




The MSDN technical information talks about it but doesn't tell me how
to actually DO it or where to PUT it or how to implement it.

Anyone have any ideas?

 

[George Nicholson]

Private Sub Form_Current()
' (Parent Form)

If Me.NewRecord = True
' Parent form is on a new record
Me.SubformControlName.Form.AllowAdditions = True
Else
'?? Me.SubformControlName.Form.AllowAdditions = False
End if

End Sub

HTH,

 

[Jon A]

This doesn't seem to work.

I did some research on MSDN and there is an entry that says a
subform's open events will be triggered before a parent form's events.
So I think this doesn't work because the subform events have already
triggered before this even gets executed, and the subform had (at the
time of the new record) the setting AllowAdditions = FALSE.

So I still would like to know how I can capture the "New Record" event
before it occurs. MSDN mentions a "CommandBeforeExecute" method that
will do this, but even though I coded it like the example given by
MSDN, I get "syntax" errors (it seems most of the code on MSDN gives
syntax errors - don't they test that code before they post it?)...

My original questions still is:

 

[Dirk Goldgar]

This doesn't seem to work.

I did some research on MSDN and there is an entry that says a
subform's open events will be triggered before a parent form's events.
So I think this doesn't work because the subform events have already
triggered before this even gets executed, and the subform had (at the
time of the new record) the setting AllowAdditions = FALSE.

So I still would like to know how I can capture the "New Record" event
before it occurs. MSDN mentions a "CommandBeforeExecute" method that
will do this, but even though I coded it like the example given by
MSDN, I get "syntax" errors (it seems most of the code on MSDN gives
syntax errors - don't they test that code before they post it?)...

As far as I can tell, CommandBeforeExecute applies to forms PivotTable
and PivotChart views only, and I think you'd have to have Office Web
Components installed and referenced to use it. I don't see the event's
relevance to what you're trying to do.

Sometimes the code that is posted on MSDN has bugs, but by far the
majority of the time any apparent errors are due to misunderstandings or
misuse by those trying to use it.

My original questions still is:

The form's BeforeInsert event will fire when a user first starts to
modify a new record. However, there is no single event that fires only
when the user navigates to the "new record". Normally one would use
the form's Current event, and check the form's .NewRecord property in
that event, as George Nicholson recommended.

George Nicholson's code,

Private Sub Form_Current()
' (Parent Form)

If Me.NewRecord = True
' Parent form is on a new record
Me.SubformControlName.Form.AllowAdditions = True
Else
'?? Me.SubformControlName.Form.AllowAdditions = False
End if

End Sub

.... looks good to me, though of course you have to modify it both to use
the correct name for the subform control -- replacing his
"SubformControlName" -- and to decide what do do when it's *not* a new
record. Right now, he has commented out that line in the Else clause,
so the subform's AllowAdditions property will remain unchanged from
however it was last set. You have to change that.

When you say, "This doesn't seem to work," you don't give us much to go
on. More information about exactly how it doesn't work would be
helpful, and you ought to post the code you're actually using.

Be aware, by the way, that there is *no* event that will fire in the
subform when it's parent is a new record, for the simple reason that the
subform cannot have focus while the parent record is unsaved. Access
will not permit that to happen; it will always save the main form
record when shifting the focus to the subform, and vice versa.