Saving Mulitple Records to Multiple Tables

[gwoman]

It has been a while since I have worked with Access ...
I am creating a Survey Form that has 1 Main form and 2 subforms: Main Form = Part 1 Peronsal Info > Survey ID, Name, Work Location, Title > Source Table = Survey
SubForm1 = Part 2 Rating Form > Questions 1 thru 8 are Text Boxes, a combo box for rating the questions (i.e. Agree, Strongly Agree, No Opinion, etc) and a Comments Text Box ... (this is a many to one relationship to the Main Form via Survey ID) > Source Table = Survey Response
SubForm2 = Part 3 Additional Comments Not covered in SubFrom 1 > Comment Sequence ID, Comments Text Box ... (this is a many to one relationship to the Main Form via SurveyID) > Source Table = Additional Survey Response

I am getting can't save error messages when I try to save my records from SubForm1 and Subform2 ....
Any ideas or suggestions would be much appreciated ...
Thanks
g-woman

 

[fneily]

First, is your SurveyID in the main form a FOREIGN key in the subforms? SurveyID can not be the primary key in the subforms.
What are the primary keys in the other two tables?
Look at your tables in design view and bring up the property sheet of the table. Check the Link Child Fields and Link Master Fields to see if they're the same data type.
Do you have any referential integrity set? Remember, you can't have a child without a parent.
Break it down. Do one form, one subform and see if that works. Then add the second. Where it fails, you might see the reason.

 

[TheAceMan1]

gwoman . . .

I've tried o put your [blue]table relationships[/blue] together to make sense of this, but always come up with a gray area. As far as the error message is concerned:

[blue] . . . you can't save a subform record, without first saving an parent record related to the subform! . . .[/blue]

Your thoughts? . . .

See Ya! . . . . . .

 

[gwoman]

Thanks for your quick responses ... I apologize ... I was detained yesterday ...
OK ... my relationship diagram is as such ..
Table Survey contains SurveyID, DeptID, FieldInd, Position, BusEntyID, DataSubAreaID (this is all the information being collected on the Main Form ..
SubForm1 is collecting SurveyID, SurvRespQstnNum, QstnRespTypID,CommentText (SurveyID in SubForm1 is child to SurveyID parent in Main Form)into Table Survey Question Response which has a many to one relationship with Table Survey ...
SubForm2 is collecting SurveyID, AddCommentSeqNum, AddCommentText (SurveyID in SubForm2 is child to SurveyID parent in MainForm)into Table Survey Response Additional Comment which as a many to one relationship with Table Survey ...
I would like the SurveyID created in the MainForm to be associated with the SurveyIDs in both subforms as the user in entering data in all 3 forms at the same time ...
I am thinking that I need to save the record in the MainForm first in order to assign the SurveID to the data in the SubForms ... is that right?

I hope this makes sense .... thanks again!!
g-woman

 

[FastLearnerIThink]

should be fine if you follow fneily's suggestions. link master (SurveyID from main form) to child (SurveryID from subform) in form properties and ensure that they are both the same datatype. If SurveyID in main form is an autonumber (I suspect it would be), then SurveyID in the subform should be a number, not an autonumber.

BUT this only works if data in mainform and subforms are written to the same table. If they are written to different tables (each subform writes to a seperate table than mainform) then you need an autonumber field for each table and as fneily points out, SurveyID in subforms CANNOT be the primary keys on the tables written to by the subforms.


-- Fast Learner, I Think

Here's something to think about. How come you never see a headline like 'Psychic Wins Lottery!'? - Jay Leno