Master/Detail Form

[Peter Lux]

Ok.. I have a form that uses master/detail to display tickets. On the main
form, the table is the sale table. On the subform, it's the sale_item. You
can have at more than one sale_item, but it's a rarity. I have the form
linked by ticket number and everything's ok for the most part.

One quirk I've noticed is on the subform, somehow I jump from one record to
the next (I might inadvertantly do this with the mousewheel) , then because
of the constraints on the table, I can't save and it won't let me delete. I
have to cancel the record, but it's already saved the sale record and then I
end up having master records but no detail.

Is there a way to keep access from posting both records until a user clicks
on a button?? I'm not sure how to postpone the save in the master? Is there
a way to prevent you from adding multiple children untill all the details
are provided in the datasheet?

Thanks!!

 

[Allen Browne]

There is no simple solution to this. Access must write the main form record
before you can create a related record in the subform; otherwise there would
be nothing for the related record to relate to.

It should be possible to track down the cause of the problem though. If the
tables are set up correctly, it should be possible to have multiple subform
records for the record in the main form, so it might be a matter of reading
the exact error message and understanding what triggered it.

 

[NetworkTrade]

well off the cuff - - you might consider dropping the Form/subForm approach
and instead redesign to a Form (probably based on a query that spans the two
tables) and is more controlling in stepping the user thru the order that you
tab and input data........

I can't think of a way to control the Form/subForm from going into a new
record as you describe though others may know a technique.......

 

[Peter Lux]

There is no simple solution to this. Access must write the main form
record before you can create a related record in the subform; otherwise
there would be nothing for the related record to relate to.

Allen,
Now that I think more about it, I'd like to save the main rec, but prevent a
2nd sub rec from saving before all fields in the 1st sub rec are filled in.
Is there a way to create a boolean on the subform that is tripped when a
user pushes the save button that then writes the sub record? So then the "On
Current" of the subform would just Cancel the save until that boolean was
true?

Or am I COMPLETELY off my rocker?

 

[Allen Browne]

Use the BeforeUpdate event of the form (i.e. the subform) to perform your
validation.

The event has a Cancel argument.
If you set that to True, the save will not proceed:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.SomeField) Then
Cancel = True
strMsg = strMsg & "You forgot SomeField" & vbCrLf
End If

If IsNull(Me.AnotherField) Then
Cancel = True
strMsg = strMsg & "You forgot AnotherField" & vbCrLf
End If

'etc.

If Cancel Then
strMsg = strMsg & "Fix the entry, or press <Esc> to undo."
MsgBox strMsg, vbExclamation, "Invalid entry"
End If
End Sub

For an even easier solution (no code needed), just open the subform's table
in design view, and set the Required property to Yes for the fields that
must have a value.