Form_BeforeUpdate Cancel blocks use of subform

[Birrel88]

Hello there,

I encountered a problem after I tried to fix an annoying bug in my program.
I hope you guys can help me out with this.

Setup:
Mainform INCOME is setup with a couple of controls where I can select a customer and put in stuff like cash money and date.

Next there are a couple (3) subforms in the mainform.
One of them is for adding RENT, one for checking the history of already created records, and one for other sold stuff.

Now, when the company the program is for, selected a customer in the mainform, and did or did not put in other values in the controls on the main form, then switched over to the subform to add rent, right at that moment access created a record in the table bound to the mainform.

That was a problem, since most of the time, users selected a customer on the main form, and added rent on the subform, and not putting any other values in the controls on the mainform.

Now the incomes table consisted of 2 rows added:
1. A row with the selected customer, and nothing else
2. A row with the selected customer, nothing else, and a relation to the RENTS table, since they added rents.

No problems thusfar you think.
Well, the problem is, the first row added was a blank row.
It has no relations with other tables, and no other info next to the customers name.

Why adding the relation to the RENTS table to a new row?
That is because of the setup my program uses. Has something to do with adding the rent first to a temporary table, then, after save, adding it to the normal table. Really dont want to change that, a lot of the rest of my program needs this style.

Solution?
To prevent access from saving the mainform (since that is happening when the user switched over to the subform), I added some code:

Private Sub Form_Load()
[INDENT]Dim iCanSave As Boolean[/INDENT]
End Sub

Above code is from the MAINFORM and makes a variable used in the next code

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not (iCanSave) Then
        Cancel = True
    End If
End Sub

Above code is from the MAINFORM and prevents saving when the user activates the subform

Private Sub cmdSave_Click()
iCanSave = True
DoCmd.RunCommand acCmdSaveRecord
End Sub

This is part of the save button.


Problem:
I want the Mainform only to save when the user presses the cmdSave button, and not when the user will click on the subform.

But, when the cancel event is fired, nothing in the subform will work anymore. It doesnt save the mainform, that worked, but no button, controls or something in the subform will work.

So is there some command that cancels the saving of the mainform, but then continue working with the form and subforms like no (trying to)save has ever happened?


I hope someone understands the story above. English is not my native language so if there is any more explanation needed I am willing to provide that

Thanks in advance

 

[Mark_]

For myself, on a new record I would hide the subforms UNTIL they have saved the parent record. This would force them to add the parent record before they try to put in child records.

 

[Cronk]

Another approach, rather than hiding/unhiding subforms, is to use the onEnter event of the subform control, test that a record is saved and if not, display a message and set the focus back to the required control on the parent form.

 

[Pat Hartman]

Birril,
When you have a relationship between two tables, the record in the parent table MUST be inserted before you can add a record in the child table. Otherwise, the child would be an orphan. Inserting the parent record in the main form generates the data needed for the foreign key used in the child table to link to the parent table.

NONE of this requires any code, nor should you be attempting to stop it. Probaly what you want to do is to stop theparent record from being saved if all the required data is not present and valid and you would do that by putting your validation code in the main form's BeforeUpdate event and then canceling the save if any error is found --

Cancel = True
Exit Sub