What's the recommended way to do this?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mscertified - 11 May 2007 15:29 GMT

I have a form which maintains my main table. On this form is a button that
opens a new form where related records in another table are entered (I know
it could be a subform but for various reasons that could not be done).
My problem is that when creating a new record on the main form and then
going to enter the related records, I have to have the autonumber key for the
main record. This means I have to save the new record before going to the
other screen (this in itself creates problems because I perform various
validations on save) . A problem also occurs when the user presses Escape to
undo his main table change (I would then have to go and delete the related
records I previously added).
Is there a recommended way to handle this (other than using a subform)?
Thanks.

Reply to this Message

Rick Brandt - 11 May 2007 15:37 GMT

> I have a form which maintains my main table. On this form is a button
> that opens a new form where related records in another table are
[quoted text clipped - 10 lines]
> Is there a recommended way to handle this (other than using a
> subform)? Thanks.

You MUST save the record in the main form before you enter records in the
related table. No way around that if you have proper table structures.

If you set up your relationships properly (enforced referential integrity) and
include a line to save the main record before opening the other form then you
should have no problems.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

mscertified - 11 May 2007 19:43 GMT

I tried that but one of my validations on 'save' is that a related record
must exist so it always fails that check. I also do other validations on
'save' that I want to postpone until I really move off the current record. I
also need to allow the user to hit Escape to undo all data entered. Can all
this be handled?
For now I have removed the table relationships which removes the need for a
save. Of course now I could get orphan records in the related table if the
user does an escape.

> > I have a form which maintains my main table. On this form is a button
> > that opens a new form where related records in another table are
[quoted text clipped - 17 lines]
> include a line to save the main record before opening the other form then you
> should have no problems.

Reply to this Message

Rick Brandt - 12 May 2007 01:22 GMT

>I tried that but one of my validations on 'save' is that a related record
> must exist so it always fails that check. [snip]

That is where you have gone wrong. You cannot insist that a child record exist
for a parent record. Only that a parent record exist for a child record.

You can make a report that *checks* for parents that have no children and alert
the user to that fact, but you cannot enforce that at data entry.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message