...related record required...

[degras]

If I go to add a new invoice in the form, but do not enter any data (so the record is not yet saved) and then I go to enter an invoice item (in the subform) I get the following message:
"You cannot add or change a record because a related record is required in the table 'Invoice'"

Is there a way in VBA to save the invoice before entering the invoice items subform so this problem does not happen?

[RuralGuy]

The act of moving the focus from a MainForm to a SubForm will attempt to save the MainForm record. The same is true for the reverse: moving from the SubForm to the MainForm will attempt to save the SubForm record.

[degras]

The act of moving the focus from a MainForm to a SubForm will attempt to save the MainForm record. The same is true for the reverse: moving from the SubForm to the MainForm will attempt to save the SubForm record.

I put a "Me.Dirty = False" in the on enter event of the subform now I get the error message when running: "Me.Dirty = False" in the subform. So it's still not working correctly.

[RuralGuy]

What you added is redundant. The save *will* take place when you move the focus to the SubForm. What do you have as the PrimaryKey field of the MainForm RecordSource, and what are you using as the LinkChild/MasterFields?

[degras]

What you added is redundant. The save *will* take place when you move the focus to the SubForm. What do you have as the PrimaryKey field of the MainForm RecordSource, and what are you using as the LinkChild/MasterFields?

The following code is needed on the subform to allow focus to progress (otherwise it sticks on one of the fields on the subform) :
Me.Dirty = false

...in its full context in the subforms VBA it looks like:
Private Sub DoCalc()
Me.Totals = Me.UnitPrice * Me.Qty
Me.Dirty = False
End Sub

Private Sub Qty_Exit(Cancel As Integer)
DoCalc
End Sub

Private Sub UnitPrice_Exit(Cancel As Integer)
DoCalc
End Sub

The main MainForm record source is set to the Invoice table. I don't see where the PrimaryKey field is set, is this a property? If I click the record source Access asks me if I want to invoke the query builder. In the subform control the link master field is ID and the link child field is Invoice.

[RuralGuy]

The PrimaryKey field is set and viewed in the table in design view. The LinkChildField is usually the Primarykey field of the RecordSource of the MainForm and a ForeignKey field of the RecordSource of the SubForm and the two fields *must* be the same FieldType. Is this true in your case?

[RuralGuy]

It is also a good idea to name identical fields in different table the same name as a form of documentation.

[RuralGuy]

I guess you have resolved this issue, right?

[degras]

I guess you have resolved this issue, right?

Not really.

I tried setting the query to include all the fields on the main table and I did the same for the subtable setting the query to include all fields on the subtable e.g. instead of just having the record source as "invoice". It did not seem to make any difference to the tab problem I was having before.

Note: The senario is adding a new record on the main form.

I'm in a dilema, without the "Me.Dirty = false" command the tabbing gets stuck on one of the fields in the subtable. with "Me.Dirty = false" I get the following error if I try to enter the subform first:

"You cannot add or change a record because a related record is required in the table 'Invoice'"

Just to recap the code is:
Private Sub DoCalc()
Me.Totals = Me.UnitPrice * Me.Qty
Me.Dirty = False
End Sub

Private Sub Qty_Exit(Cancel As Integer)
DoCalc
End Sub

Private Sub UnitPrice_Exit(Cancel As Integer)
DoCalc
End Sub

This error does not occur if I first edit any fields in the main form before entering the subform, which is a work around for the time being.

[RuralGuy]

I generally do those sort of calculations in the RecordSource query. Have you tried that?

[degras]

I generally do those sort of calculations in the RecordSource query. Have you tried that?

How do you put in the calculation?

[RuralGuy]

In the query design grid in an empty column put:
Total: =[UnitPrice] * [Qty]