Adding record to subform on exit

Question

I have a form with several subforms on a tab control. The form is used to edit records, normally, but it's also used to add records by just opening it to new via a button. I want to have it insert a record on one of the subforms on exit, but ONLY IF it's a new record. I'm not sure if this is even possible or how I would do it. Any ideas would be greatly appreciated.

Form: frmMainRevision (from table tblMain)

Subform: frmViewStatus (from table tblStatus)

There's a common field called RevID (generated in tblMain)

Answer 1

Scott,

The data in the main table is saved on exit. But no corresponding info is inserted into tblStatus, which is what we need.

Answer 2

Rich,

Yes, when the record is added to tblMain, I would like to add something to tblStatus using the RevID field, which is present in both tables.

The relationship is a one-to-many relationship. The tblMain table has the main record and tblStatus has many records relating to it.

The frmMainRevision opens from a selection table to whichever record is chosen there. But, there is also a button they can click for New Revision. This button will open the same form but to a new record instead of the selected one.

When the new record is added, it saves when the form is closed. Each of the fields has a required option, as needed.

Answer 3

If I understand you correctly, you want to add a record to tblStatus when a new record is added to tblMain. Yes, this can be done, but some questions first.

Is the relationship between tblMain and tblStatus a one to one relationship?

You stated new records can be added "via a button". Are you referring to a command button, the "New (blank) record" button in the navigation buttons, or both?

When a new record is added through frmMainRevision does the user have to explicitly "Save" the new record, or will the new record save just by navigating to a different record?

When a new record is saved through frmMainRevision is any validation being done to ensure required fields are populated?

Answer 4

Hi April, I'm an independent adviser and will try to help.

I'm not clear as to why you want to do this. If you have Bound forms, then as soon as you leave the record, the record is committed. So there is really no need of this.

If you have unbound forms (I'd have to ask why) then you would have to have code already that writes the record to the table. So to help you modify that code, we would need to see it.

Please provide more info so we can help.

Answer 5

You can put code in the AfterInsert event procedure of the parent form, which will insert a row into the table on which the subform in question is based.  The code will execute immediately after inserting a new record in the parent form.  The question then is what data would you want to insert into the new row in the referencing table on which the subform is based?