By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,153 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

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

Seth Schrock
Expert 2.5K+
P: 2,932
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?
Oct 14 '12 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeInsert()
  2.   Me.Parent.tb_Text="" 'Note that the forms dirty EVENT is not triggered when dirtyied by code
  3.   Me.Parent.tb_Text=Null
  4.   Me.Parent.Dirty=False 'Force a save
  5. End Sub
Good luck.

Share this Question
Share on Google+
5 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub BeforeInsert(Cancel as Integer)
  2.  If me.Parent.NewRecord then
  3.    Cancel=True
  4.    MsgBox "Please save the record in the main form first"
  5.  End If
  6. End Sub
Another possiblity is to use the main forms Current event in conjunction with the main forms AfterUpdate Event.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.   Me.SubFormControl.Enabled=not Me.NewRecord
  3. End if
  4.  
  5. Private Sub Form_AfterUpdate()
  6.   Call Form_Current()
  7. End Sub
Oct 14 '12 #2

Seth Schrock
Expert 2.5K+
P: 2,932
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
Expand|Select|Wrap|Line Numbers
  1. 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.
Oct 14 '12 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeInsert()
  2.   Me.Parent.tb_Text="" 'Note that the forms dirty EVENT is not triggered when dirtyied by code
  3.   Me.Parent.tb_Text=Null
  4.   Me.Parent.Dirty=False 'Force a save
  5. End Sub
Good luck.
Oct 14 '12 #4

Seth Schrock
Expert 2.5K+
P: 2,932
Perfect! Here is what I did:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeInsert(Cancel As Integer)
  2. If Me.Parent.NewRecord Then
  3.     Me.Parent.SaleDate = Date
  4.     Me.Parent.Dirty = False
  5.  
  6. End If
  7.  
  8. 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.
Oct 14 '12 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
Glad to hear it worked out for you :)
Oct 14 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.