Access form, dynamically change order of form, the way fields are displayed

In Microsoft Access (latest version), I have created a form, it has a large number of questions listed, user ticks the relevant questions which I want to form the basis of another form.
However, the only way I know is to put them all on another form and make them visible in VBA if it has been ticked on the previous form. However, this method looks very messy as there are large portions of the form blank. Is there a way to order them? So they appear one after the other without spaces(dynamically)?

Not sure if I  am making sense but unsure how else to explain it.

Thanks in advance for any help offered.
melinhomesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You can adjust the Top and Left properties of the controls.
mbizupCommented:
If your database is designed so that your questions are stored as data in a table versus simply being displayed as labels on a form (hard-coded), you could use a continuous form to display the questions, avoiding spaces between them.
melinhomesAuthor Commented:
Hi mbizup
Yes the first form is based on a table. So the fields are for instance:
Question1
Question2
Question3
Question4
So on the next form, if only Question 1,2 & 4 are set to visible I want them to display as:
Question1
Question2
Question4
Only way I knew was to display as
Question1
Question2
BLANK SPACE HERE
Question4

Will continuous form work for this method? I thought it was to display multiple records one after the other.
Thanks again
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

mbizupCommented:
There are plenty of examples out there, but check out this sample of a *flexible* questionnaire database by Duane Hookom:

Questionnaire Database
melinhomesAuthor Commented:
Hi Gustav
Thanks for the feedback, I will look into these controls also and report back :)
mbizupCommented:
Structure wise, I was thinking more along the lines of a single field for questions.  You can add other fields such as 'order' to get the layout you want...
melinhomesAuthor Commented:
Hi Gustav

I think the top and left properties of the controls may be the way forward based on further reading. Based on the below example:
First form is based on a table. So the fields are for instance:
Question1     visible - true
Question2     visible - true
Question3     visible - false
Question4     visible - true

Only way I knew was to display as
Question1
Question2
BLANK SPACE HERE
Question4

How do i get it to display like this:
Question1
Question2
Question4

Do you have an example of how to code? I'm unsure of the positioning as i've been reading about twips?

Thank you
mbizupCommented:
Just one more comment about storing your questions in a single column... in addition to a column to define 'sort order' you can also use a 'category' field to define groups of questions, which can then be used to visually display groups of questions separately (similar to the pages you are describing).  

It might be more work up front, but will pay off in the long run if the survey ever goes from 4 questions to 40.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
No, but I would loop through the controls similar to:

TopPos = TopOfFirstControl

For i = 1 To 40
    With Me("Question" & CStr(i))
        If Me("Tick" & CStr(i)) = True Then
            .Visible = True
            .Top = TopPos
            TopPos = TopPos + VerticalSpacing
        Else
            .Visible = False
        End If
    End With
Next

Open in new window

PatHartmanCommented:
I hate to even offer this suggestion because I think you are taking the wrong approach.  The design suggested by mbizip is unquestionably the most flexible and can be implemented with NO CODE.  If you'd like to pursue it, we can elaborate.

However, you can do what you want by creating a form with a bunch of unbound controls named as "question?" with "?" being a sequence number.  In the form's open event, you can use code to populate the ControlSource with the name of the data field using a loop similar to what Gus Suggested and at the same time make the control visible.  You will need some way of "knowing" how the fields should be set.  Leaving the field with the ticks open seems akward.  It would probably be better to save the ticks to a table and use that table to control the display form.  You will probably also need code to reset the form just in case it gets saved with some controls visible and bound.  I'm not sure what event you would need to run this in but it needs to run before you set the ControlSources.

All in all, this is a very bad idea.  Making a survey style schema is oh so much more flexible and it requires NO CODE.
melinhomesAuthor Commented:
Thank you very much!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.