Help needed with Form design for Data Entry

[homey]

Hello all
I am really struggling with the design of a data entry form for a journals database where I wish to add a new record/or update an existing one to 3 tables simultaneously.
Tables:
tDetails (describes journal details for every journal print or ejournal)
tPrint (describes print holdings at 3 different locations)
tEjournal (describes hyperlink and e-holdings for each journal)
journal_name is the Primary key in tDetails & foreign key in the other tables.

Relationships:
tDetails to tPrint is one to many (though not all journals have entries in tprint)
tDetail to tEjournal is one to many (though not all journals have entries in tEjournal)

So far I have tried to build a form using tDetails with the other tables represented as subforms with the child/master links. But I have discovered the well documented problem on this forum that the fields from the parent form tDetails are updated to the table as a new record as soon as you tab into a subform.
I do not if there is any code to cancel/undo the already saved record in the parent form/table once the subforms are filled if the user decides they have filled everything out in error.

My question is as a complete novice to Access & VB, is this form entry design the most appropriate for what I want to do? Is there a way round the form/subform anomoly?

I thought of writing a query instead to capture all the fields from all the tables to disply in just one form... but then I didn't know how to represent the one-to-many data entry part... ie adding more than one hyperlink to tEjournal for each new journal record.

I know this is all very elementary and I do feel extremely dim because i can't get my head round it.

I would be very grateful if someone could help me out.

ThankS !

 

[quest4]

I have a cmdButton, cmdDelete, and I run this code in the OnClick event:
If Me.NewRecord Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Else
DoCmd.Echo False
DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.Echo True
DoCmd.SetWarnings True
End If
I don't have much trouble with it. hth.

 

[homey]

Hello Pat & Quest4
Thanks for you input and I stand corrected on my knowledge of Access.
That makes sense about the parent/child sequence and all my tables are set up with Referential Integrity enforced so if I go into the parent table itself and delete a record all child records are removed as they should.
I just want to emulate that behaviour on my form

I have tried an UNDO button with the code as suggested

Me.Undo
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord

However, I get a runtime error 2046 'The Command or Delete Record action isn't available now.'

Is this anything to do with my setting the tab order so that the focus follows from the last field of the first subform to the first field of the second subform.
The code I used for this was :

Private Sub Print_Holdings_Exit(Cancel As Integer)
Forms!frmDataEntry!sfrmEjournalDat.SetFocus
DoCmd.GoToControl "E-holdings"
End Sub

In fact when I was trying to use a button for delete record I created from the wizard it failed with a different run time error which related to this setfocus code.

I hope you'll still have some patience to help me out a bit more.

Thanks again in advance

 

[quest4]

Actually that is from Access 2000, not Access 95. I uderstand what you are saying, but the funny part of this is that the lady who taught me how to do that, us to work for MS and helped desigh Access. Bad habits die hard and slow. FYI 4U.

 

[quest4]

Pat, thank you for that information, I will try and remember that. On my procedure, If it is a new record it does a undo and if it is not a new record then it does a delete. Just alittle FYI 4U. I know I used the wizard deleted record. hth.