Custom Ordering of Subforms on Main Form

rossco · Post by **rossco** » 06 Jul 2010, 04:42

On a separate Setup screen I've created a facility for users to create questions and related subquestions. This part works perfectly. I've also inserted Custom Sort fields for the questions and also the subquestions so users can control and change the order of the questions very easily later on a main working form. This setup area works perfectly.

I need help with the main working form. I want to insert up to five questions and related subquestions. I thought I could use five separate subforms and use the Custom Sort field to determine which question appears in each specific subform but so far I can't make it work.

On the main working form, I put five separate text boxes each with the control source =1, =2, =3 etc and each named Position1, Positon2, Position3, etc. For Question 1 I set the Master link = MasterQuestionID;Position1. Then the Child link = MasterQuestionID,CustomOrder. For Question 2 I set the Master link = MasterQuestionID;Position2. Then the Child link = MasterQuestionID,CustomOrder, and so on for the other questions.

Sounds good in theory but the actual relevant questions don't appear in the subforms. I mean, no questions appear anywhere at all.

Many thanks for any help.

Post by **HansV** » 06 Jul 2010, 05:29

That sounds reasonable, but I'm afraid it's impossible to determine why it doesn't work without seeing the database. Could you create a stripped down copy (we don't need to see the setup part, just the tables and the forms you're asking about, compact it, zip it and attach it to a reply? Thanks.

rossco · Post by **rossco** » 07 Jul 2010, 03:06

Thanks Hans. Your help is much appreciated.

Post by **HansV** » 07 Jul 2010, 05:56

The problem was in the relationship between the All Observations and Master Observations tables - it was set up the wrong way.
See the attached version. The two subforms show different approaches.

Observe_2010-07-07A.zip

rossco · Post by **rossco** » 07 Jul 2010, 06:39

Many thanks!

rossco · Post by **rossco** » 07 Jul 2010, 11:58

I don't think the relationships are quite right yet. I am setting up a database to be sent out to many different sites so that each site they can simply do spot checks on random dates by answering questions. This means users should be able to start a new observation record with a new date and all five master questions need to appear ready to have answers entered.

Sorry I realise this wasn't the initial topic but it has indeed evolved.

Thanks

Post by **HansV** » 07 Jul 2010, 13:14

I'll have a look at this later today.

Post by **HansV** » 07 Jul 2010, 15:32

Should the subquestions also appear immediately below each master question when a new observation record is started?

rossco · Post by **rossco** » 09 Jul 2010, 06:33

Yes they should.

Post by **HansV** » 09 Jul 2010, 09:28

OK, we'll need to split the tables. On the one hand you need tables describing the master questions and sub questions; these tables will be fixed. On the other hand, we'll need tables to record the answers given by the end users (sites). New records will be added to these tables all the time. Which field or fields should the end users fill in?

rossco · Post by **rossco** » 09 Jul 2010, 09:46

I took those out the answer fields when I stripped down the db for posting. Each sub question has a three point rating scale for yes, no, undecided so I used three tick boxes.

Many thanks indeed.

Post by **HansV** » 09 Jul 2010, 10:16

Sorry about all these questions, but we need to get the design straight. Do the end users fill in answers to both the main and sub questions, or only to the sub questions?

rossco · Post by **rossco** » 09 Jul 2010, 10:26

No worries about the questions. I'm totally grateful for your help. For some reason this project has been a mental blank - same as writer's block I guess. Answers are needed only for the sub questions. The term Main Question might be a misnomer. The term Category or Group Heading is probably better. Sorry for the misunderstanding.

Post by **HansV** » 09 Jul 2010, 13:04

Take a look at the attached version of the database. It is by no means intended as a final product, but hopefully it'll give you some ideas.

I have added a separate table for the responses.
To create a new set of responses, open the form frmNewObservation, fill in the text boxes and click the button. The code behind the button has comments to explain what is going on.
To keep frmObservations compact, I have moved the subforms to tab pages. But you're not obliged to keep this setup, of course.

Observe_2010-07-07B.zip

rossco · Post by **rossco** » 17 Jul 2010, 13:09

Brilliant! Totally perfect. Thank you so much for your help - it was very much appreciated.

Eileen's Lounge

Custom Ordering of Subforms on Main Form

Custom Ordering of Subforms on Main Form

Re: Custom Ordering of Subforms on Main Form

Re: Custom Ordering of Subforms on Main Form

Re: Custom Ordering of Subforms on Main Form

Re: Custom Ordering of Subforms on Main Form

Re: Custom Ordering of Subforms on Main Form

Re: Custom Ordering of Subforms on Main Form

Re: Custom Ordering of Subforms on Main Form

Re: Custom Ordering of Subforms on Main Form

Re: Custom Ordering of Subforms on Main Form

Re: Custom Ordering of Subforms on Main Form

Re: Custom Ordering of Subforms on Main Form

Re: Custom Ordering of Subforms on Main Form

Re: Custom Ordering of Subforms on Main Form

Re: Custom Ordering of Subforms on Main Form