Create a question database to load in a form using pull-down menus

[B747driver]

Thank you for any responses. New to forum, new to Access. I'll not pretend to be knowledgeable.



I'll start with "can it be done?" before asking how to do it. If I understand Access correctly Access is probably the best way to do this.

I have a form existing as a pdf that has blocks for 10 questions. These questions are intended to be asked of pilots testing for an additional rating. The examiner is presently required to hand write his questions in these 10 blocks. No answers are necessary on the form. Only the questions.

Here are my requirements. I'd like to know if this is possible in Access?

1. Can my currently existing form be imported into or created in Access? It is a simple one-page form.
2. I'd like the 10 questions to cover 10 separate aircraft systems. Once a system is chosen for a question I'd like for that system to be excluded from subsequent question blocks. For example, if question 1 covers the electrical system I do not want the electrical system questions presented again as an option in the remaining blocks.
3. Can the questions be presented as a pull-down list inside each question block and loaded on the form? The examiner would have the ability to choose which question would be asked. The question list for each system would be short, maybe a maximum of 5 questions per system.
4. Could an option be created that would allow an examiner to add his own questions to each system list?
5. Upon loading the questions the examiner could print the form.

I can provide a copy of the existing form if it would help.

I am lower than a novice, but I'd like to get this to work. The only way to learn is to find a project and do it.

I would be grateful for any assistance, guidance, suggestions or comments.

Thank you in advance for considering my questions.

[Welshgasman]

Yes to all points.

[Micron]

Do yourself a favour and read all of these,

https://www.accessforums.net/showthr...773#post521773

starting with db normalization and don't start designing forms or reports until you understand it. Then create your tables and relationships then post a pic of the relationships. Note that you cannot simply paste images in a post here. See "how to attach files" at the top of the page.

When you get to table design, I think you will need at least one junction table with a compound index (please don't ask me, rather look those terms up). Your index might be comprised of QuestionID from tblQuestions and CategoryID from tblCategory. Then you will need cascading combos to select a category first (?). That will build a list of questions for the second combo that belong to that category. Once the question is selected, the category combo would need to have its list rebuilt (requeried) to exclude questions that are on the form. There are probably a few ways to do that but you're not there yet.

All in all, doable and a great exercise for learning a lot of Access stuff but not a simple project to start learning on.

[B747driver]

Hi Micron,

Thank you for this information. Welshgasman replied saying all my requirements were doable. My next question was how. You have gotten me started with where to look. Thank you! I searched the internet, YouTube videos, etc., and I just couldn't find anything that showed me how to do what I am wanting to do. Thus the reason for posting on this forum.

I'm not looking for someone to do this for me. I won't learn anything that way. I know this is an ambitious project for a beginner, but that's ok. I have time and desire, and it's going to save several people a lot of trouble if I am successful. I'm fairly proficient in just about all Office products, but I've just never had occasion to learn/use Access. I think I just found a way to fix that.

With your permission, I'll contact you if I get stuck somewhere.

Thank you again for your help and suggestions! Really appreciate it!

Sincerely,

B747driver

[B747driver]

Thank you Welshgasman. Now just a matter of learning how to do it.

Take care and stay safe.

B747driver

[madpiet]

In a nutshell, what you're describing is a Survey. There should be a copy of Duane Hookum's "At Your Survey", a database for creating surveys, adding questions, possible responses, and then respondents and their responses. then you can tally all that up inside the database.

At Your Survey - Roger's Access Library

It's a crazy old database (originally from 1997/2000), but the design won't change. So I'd just use it as is.

Oh, the bit about "removing questions about <topic> after one has been added"... If you add it using some form event, you can add code to that to remove it from the list of options... Say you had a listbox of (questionID, question, category) then you move the selected question from the lbxAvailableQuestions to lbxSelectedQuestions, you could do something like get a list of categories from lbxSelectedQuestions and then remove those from the lbxAvailableQuestions list. (you'd have to use a recordset and .AddItem to add items to the listbox, because if you don't, you can't use .RemoveItem to remove one.

But, yes, it's doable.

[Micron]

Lots of ways to do just about anything. Here's a pic that I think illustrates the listbox approach. When a value from the left is moved to the right, it is taken off of the left list. Thus it's not possible to duplicate on the right.