Is it possible to make the main form create a record when the subform is updated?

[Seth Schrock]

I have a form with a subform. To save time, I have set several of the main form controls to have default values so that I don't have to edit them most of the time. The subform is often the first thing that I go to when adding records. My main problem is that I will be adding records to the subform and then I realize that the main form is still on a new record. As soon as I change a value in the main form, it creates a record and then all of the data that I had put into the subform is lost. I have thought of two ways to prevent this, but I can't figure out how to get either one to work properly.

Idea 1: Use the OnCurrent event to check if the record is a new record - if so, lock the subform, else unlock it. Problem: Even if I get the main form to create a record number (hence, no longer a new record), it doesn't retrigger the OnCurrent event to unlock the subform. I tried using the form's OnDirty event to fix this, but that didn't work either.

Idea 2: Make it so that when the subform gets updated (probably in some BeforeUpdate event), the main form creates a record. Problem: Not sure how to do this either.

Any another ideas?

[TheSmileyCoder]

Well there are probably alot of ways to do this. I believe the OnDirty ought to work to control whether the subform is enabled or not.
I don't think its the best suited event though.


In your subform, you could use the BeforeInsert event (This event occurs just as you start typing in the first field in a form.) to check whether the parent is a NewRecord.
Aircode below:

Private Sub BeforeInsert(Cancel as Integer)
 If me.Parent.NewRecord then
   Cancel=True
   MsgBox "Please save the record in the main form first"
 End If
End Sub

Another possiblity is to use the main forms Current event in conjunction with the main forms AfterUpdate Event.

Private Sub Form_Current()
  Me.SubFormControl.Enabled=not Me.NewRecord
End if

Private Sub Form_AfterUpdate()
  Call Form_Current()
End Sub

[Seth Schrock]

Thanks Smiley! I've decided to use your first option. However, would it be possible to make the main form become not a new record from within that If/Then statement? I tried

If Me.Parent.Dirty = True Then Me.Parent.Dirty = False

but that didn't work. Is there another way? If not, I can live with it.

[TheSmileyCoder]

Me.Parent.Dirty will ALWAYS be false. The subform cannot gain focus, without the main form saving (if its dirty).

I don't actually know if its possible to save the parent record in any subform event, since there might be a conflict of events. Worth a try of course. I also don't know if the Master/Child relation between subform and master form will properly, I suggest you make sure that the subforms foreign id is filled out properly.

If you were to try this, I feel fairly sure you should try using the BeforeInsert first, if it can work there it would be the most logical choice. Another option could be to use the subforms Enter event. This would however cause a main form save regardless of whether you actually add a child record.

I assume you have some field you can freely "dirty"? Lets assume that tb_Text is a control that does NOT have a defaultvalue assigned and you can dirty at will, then try:

Private Sub Form_BeforeInsert()
  Me.Parent.tb_Text="" 'Note that the forms dirty EVENT is not triggered when dirtyied by code
  Me.Parent.tb_Text=Null
  Me.Parent.Dirty=False 'Force a save
End Sub

Good luck.

[Seth Schrock]

Perfect! Here is what I did:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.Parent.NewRecord Then
    Me.Parent.SaleDate = Date
    Me.Parent.Dirty = False
    
End If

End Sub

SaleDate was one of the controls that had the default date, but all I did was reset it to the default date again. This was enough to trigger the main form to be dirty and then saved. I don't have to cancel the insert or anything.

[TheSmileyCoder]

Glad to hear it worked out for you :)