Book recommendation on automated form generation?

[Batox]

Yes, I just spent 1h searching these forums. I think I now have an idea how to set up my tables. I currently have two questionnaires, but I want to set up a flexible structure which can accommodate more. I also need quite a lot of flexibility concerning the answers. My current database model looks like this:

tCustomers
- customerID (pk)
- Name
- Address

tControlTypes
- controlTypeID (pk)
- controlType (e.g. "RadioButton")
- controlText (e.g. "never")

tAnswerTypes
- answerTypeID (pk)
- answerType (e.g. "5RadioStyle1" or "Free Text")

tAnswerTypeControls
- answerTypeControlID (pk)
- answerTypeID (fk)
- controlTypeID (fk)

tQuestions
- questionID (pk)
- answerTypeID (fk)
- surveyType
- questionText
- explanationText

tResponses
- responseID (pk)
- customerID (fk)
- questionID (fk)
- date
- responseValue

I'm aware that this is still not fully normalized, surveyType could get its own table, for now I'd like to continue with the above structure. With the two questionnaires I currently have, the following answerTypes exist:
- RadioButtonsImportance (meaning a row of 5 buttons ranging from "unimportant" to "extremely important"
- freeText
- freeTextWithImportanceButtons (a combination of both above)

An example question for the last type would be:
- Which other features would you like to see included:
_____________________ o unimportant ... o extremely important


Now I'm wondering whether it's possible to automatically generate the forms? From an entry in the tQuestions table, it should be possible to automatically generate a form with question text, expanation text, and the proper answer controls? But how can I automatically bind these controls to the proper fields in the tResponses table? I suspect that the answer to this question is "the design of tResponses is not sophisticated enough"... but there are also some technical aspects to the VBA code I'd have to write which are unclear.

I'm aware that a full answer to this question is impossible, or at least way too long for a forum. I currently have the Viescas "Inside out" book, which was quite helpful for the first few steps, but is not useful when it comes to advanced database programming. Maybe the best answer I can get is a good book recommendation . Btw, I'm working with Access 2003. I hear good things about the Access 2002 Developers Handbook set by Litwin, Getz and Gunderloy, but there's many other highly recommended books... which one has a heavy focus on VBA in Access?

Otherwise, feel free to discuss my design, and/or anything else you may find helpful.

 

[ajetrumpet]

Now I'm wondering whether it's possible to automatically generate the forms? From an entry in the tQuestions table, it should be possible to automatically generate a form with question text, expanation text, and the proper answer controls? But how can I automatically bind these controls to the proper fields in the tResponses table?

forms are the best part of the database, and they are usually not automatically generated. they are user created, with some SPICE to them! I wouldn't recommend even researching this. It is a waste of time. Make your own forms, and guided the user through as you see fit. That's part of the fun, and it also results in a great accomplishment in the end on your part.

and btw, YES, you can automate forms in VBA, but I don't do it for the very reasons I mentioned above.

 

[Uncle Gizmo]

If you highlight a table in the list on the left of your screen, and click the "create" icon on the ribbon, then click on "form" a wizard will automatically generate a form For you and include all of the fields from the table you selected. I believe there is a similar wizard in all previous versions of MS access.

 

[Uncle Gizmo]

On reading Adams reply, I realise I have missed the point of your question.

You can programaticaly change the record source of a form, and you can also direct your controls on the form to look at different aspects of the record source, however I would agree with Adam it's not the way to go.

Before you make your decision, I suggest you research "table driven databases" as an alternative, for example most versions of access have a "switchboard" add-in which will automatically produce the switchboard for you. Access 2007's version of this is macro driven, I personally have always avoided macros, so I would suggest you grab an earlier access version of the switchboard and have a play with it.

It is a very good example of a table driven structure. (well I say a good example, it's not too bad for a Microsoft example.)

 

[Batox]

forms are the best part of the database, and they are usually not automatically generated. they are user created, with some SPICE to them! I wouldn't recommend even researching this. It is a waste of time. Make your own forms, and guided the user through as you see fit. That's part of the fun, and it also results in a great accomplishment in the end on your part.

and btw, YES, you can automate forms in VBA, but I don't do it for the very reasons I mentioned above.

Thanks Adam, this is what I already suspected, and it's nice to see a confirmation. Nonetheless, the task ahead somehow feels clumsy. I'll now design a form with ~30 questions, explanation texts and radio button groups. Sure, cut&paste make this process rather fast, but somehow I fear what happens if things should change. What if the customer doesn't like my layout, or wants to change it at a later time? Back to entering the same 30 * 3 items again? At the same time I think the proper place for the information is the database (as outlined in my initial question). Things could be so smooth - just change some info in the database, run some VBA/macro to update (or create) the form, and all is well...

 

[Uncle Gizmo]

>>>What if the customer doesn't like my layout,<<<

If possible construct a non working example of the form layout you intend to use and get the customer to approve this before you spend a lot of time and trouble on making it work properly.

 

[ajetrumpet]

What if the customer doesn't like my layout, or wants to change it at a later time?

YOU'RE THE PROFESSIONAL. THEY SHOULD TRUST YOU, SHOULDN'T THEY!? if they don't like it, ask them "WHAT DO YOU WANT EXACTLY?" that should get their attention!

 

[Batox]

Thanks for your help, I'll do as you proposed .

 

[ajetrumpet]

np. good luck with your project.