Link to home
Create AccountLog in
Avatar of Michael Paravicini
Michael ParaviciniFlag for Chile

asked on

Is there an easy way to force that at least one record is saved in subform together with the main form?

Hi, I have a Form Quotes which uses a subform QUOTESCRM and I need to make sure that whenever the main form is saved at least one record or entry in QUOTES CRM is generated. However if the user only enters the fields in the mainform and does not enter the subform no record of the subform is saved... Thank you so much for any help. Best regards Michael
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Access always saves the Parent data before it even allows you to interact with the Child data, so the short answer is no, you really can't do this (nor should you do this, generally speaking, since you'll end up with a lot of dead wood in the database).

You could always write a bunch of code that would validate this. I'm not exactly sure what events would be most appropriate, since what you're doing is entirely counter to the way Access works. You could perhaps do something with global variables and such - but you'd have to give us more to go on.

A better path forward may be to let us know why you want to add a Child record to every Parent. We may be able to offer better methods or processes.
Avatar of Michael Paravicini

ASKER

Thank you for your answer. I have a quotes tables which  may have 1:N contacts associated with it. However, when I record the quote I expect to have at the same time a recording of this first contact and the status of this quote in the QUotes CRM table. This is the reason why I would like to ensure that with each Quote at least one QUOTE CRM is saved in the subform. Thank you so much for your help. Rg Michael
If you must, you can always create a record in the Quotes CRM table. You would do this in the either the BeforeUpdate or AfterUpdate event of the parent form:

Currentdb.Execute "INSERT INTO [Quotes CRM](Col1, Col2, Col3) VALUES(VAl1, VAl2, VAl3)"

Of course, you'd have to write the full statement, and be sure to properly assign the Parent's ID value to the relevant field in your Child table. After doing that, refresh the subform, and the blank record should be showing:

Me.YourSubformControl.Form.Recordsource = Me.YourSubformControl.Form.Recordsouce
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer