Advice on coding procedure for a form feeding three tables (1 Viewer)

[SurreyNick]

I’d like to get some advice on a good way to approach the coding for a form which will enable users to add a new exam to my db.

Adding a new exam involves adding data to three tables (ExamPapers, Questions and GradeBoundaries), all of which require inclusion of the PK from at least one other table. Two of the three tables (Questions and GradeBoundaries) also need to include the PK of the first table (ExamPapers). I have highlighted this on the attached pic. In light of this I assume the first task will be to populate data into the ExamPapers table and once this has been done populate the GradeBoundaries and Questions tables with the information pertaining to the new exam. When it comes to making choices for the FKs in the three tables I will have to provide the users with combo boxes.

I haven’t used tabbed controls anywhere in my db so I’d rather avoid doing so for this form too. With this in mind the only way I can conceive of approaching this task is to use text boxes on the parent form to populate the first table (tblExamPapers) and to have two subforms, both in continuous forms mode to populate the other two tables (tblGradeBoundaries and tblQuestions).

Coding-wise the only procedure I can think of is to force users to first populate the text boxes on the parent form in sequence and using an after update event on the last field execute a SQL statement to populate the ExamPapers table and at the same time create two temporary tables one for GradeBoundaries and one for Questions both linked to the subforms. Once these are populated use a command button to execute SQL statements to append the data to the main tables and delete the temporary tables.

Do you think this is the best approach?

Thanks.

Nick

 

[Uncle Gizmo]

I'm not getting your drift, when you place subforms on a main form they are automatically linked to the data in the main form, so there's no need to add information to the subforms with SQL statements? Otherwise there's something else you're not telling us? Please explain further..

 

[SurreyNick]

Yes that true but I wanted to avoid the users having to select/enter the ExamPaperID for each question and grade boundary. Some of these tests have as many as 100 questions and 10+ grade boundaries. I thought by using temp tables I could use the code routine to add the ExamPaperID when appending the data to the main tables.

 

[SurreyNick]

Oh yes, I should have added that I'm under the impression that for the new ExamPaperID to be accessible in the subforms I will need to requery the form after appending the first lot of data and in doing I would worry that the contents of the text boxes will return to null values. I want to keep these fields populated until all the data has been included for the three tables.

Am I making sense?

 

[Uncle Gizmo]

Sorry but I still don't understand a thing you are saying?

 

[Uncle Gizmo]

Do you have any experience with main form subform relationships? If not then you need to look up on that and you will understand why I am puzzled by your questions.

 

[Uncle Gizmo]

This is an article on many-to-many relationships:-

Many to Many Relationship - 3 Video's


The many-to-many relationship is demonstrated using sub-forms, it should offer some insight into the relationship between the main form and subform...

 

[Uncle Gizmo]

Heres a simple Example:-

 

[SurreyNick]

You know what, I think I've been going up a blind alley, creating a problem where there isn't one.

Of course I just need to link the the master and child fields of the subforms to the ID of the record on the main form!

It was immediately obvious when I had a look at your example.

I've been locked away for too damn long.

Thanks Uncle Gizmo.

 

[Uncle Gizmo]

It's not gelling with me, so I'll step away and let someone else pick up the baton...

 

[SurreyNick]

No need. You pointed out the obvious which I was being blind to. I'm sorry I wasted your time and troubled you unnecessarily.
As I said, I've been locked away for too long.
N.

 

[Uncle Gizmo]

You know what, I think I've been going up a blind alley,

Oh, yes, we've all been there, and if it's any consolation to you, getting sucked into a blind ally happens frequently, what ever level of experience you have.

 

[SurreyNick]

Thanks for the kind words. It's a comfort having demonstrated what a dunce I can be! LOL
Best I try to forget I ever started this thread

 

[Uncle Gizmo]

It's a comfort having demonstrated what a dunce I can be!

It just means you are now more experienced. There's a lot of people here with a vast amount of experience, in other words they've made a lot of cockups!

 

[MajP]

Access will not let you use a wizard to have multiple continuous subforms, but you can do it manually. I think if you look back to some of the examples I showed where you have the hidden txtLink to allow you to link two continuous subforms. I actually have a main form linked to a continuous subform and another continuous subform linked to the first subform through txtlink. This gives you all the benefit of subforms where a new record gets the parent key as a foreign key.

 

[SurreyNick]

Yeah, I'm really kicking myself MajP. I have no idea how I got it into my head that this was anything other than very straightforward. As you say, you've shared the solution with me. Once the obvious was pointed out I knew exactly how to tackle the task and it took less than 30 minutes to complete the form from scratch.

I am embarrassed I asked such a dumb question. All I had to do when I got myself mentally befuddled was to walk away and return to the task when I was thinking clearly again. Instead, I wasted people's time by posting this thread.

Nick.

 

[zeroaccess]

Access will not let you use a wizard to have multiple continuous subforms, but you can do it manually. I think if you look back to some of the examples I showed where you have the hidden txtLink to allow you to link two continuous subforms. I actually have a main form linked to a continuous subform and another continuous subform linked to the first subform through txtlink. This gives you all the benefit of subforms where a new record gets the parent key as a foreign key.

I just did this recently. Access pretends it is only going to allow you to link subform 2 to the main form, but if you type in manually a link to subform 1's ID, it will work.

In Subform2 properties, data tab:

Link Master Fields: [sbfSubform1].[Form].[RecordID]
^ Primary Key of Subform1

Link Child Fields: RecordID (Foreign Key - PK from Subform1)