Multiselect in datasheet view?

[shmoussa]

Hi,

I have a table that I am converting into a form. This table (table1)
has these fields: QuestionID, Question & Response (Combo Box). I have
another table (table2) with these fields: QuestionID, Response.

On my datasheet form, the Response combobox values are generated based
on the matches it finds in table2. So, each time I answer a question,
the drop down box requeries table2 to generate the drop down values of
the questionID that I am answering. This is working perfectly.

Some of my questions, however, require multiple answers in the
Response field. For example, if one of the questions says "What form
of communication have you used with the customer," then the user
should be able to select multiple forms of communication. How can I
enable this functionality?

Any suggestions are much appreciated. Thank you all.

 

[John W. Vinson]

Hi,

I have a table that I am converting into a form.

Well... jargon alert here. A table cannot be "converted" into a form. A Form
is a tool, a window which lets you view and edit the data in a Table. It's a
common mistake to think of a Form as containing your data; it doesn't, any
more than my office window "contains" the Owyhee Mountains.

This table (table1)
has these fields: QuestionID, Question & Response (Combo Box). I have
another table (table2) with these fields: QuestionID, Response.

That isn't making sense to me. Why should the Response be in the question
table? It should (I would think) only be in the table of responses!

On my datasheet form, the Response combobox values are generated based
on the matches it finds in table2. So, each time I answer a question,
the drop down box requeries table2 to generate the drop down values of
the questionID that I am answering. This is working perfectly.

Some of my questions, however, require multiple answers in the
Response field. For example, if one of the questions says "What form
of communication have you used with the customer," then the user
should be able to select multiple forms of communication. How can I
enable this functionality?

One answer would be to make the Response field in Table2 a "multivalue field"
- a much-maligned misfeature introduced in A2007. Access actually implements
this, under the covers, by creating a new, hidden table related one-to-many to
your Table2. You can do the same yourself; create a table named (say)
AllResponses, with fields QuestionID, ResponseID (linked to the primary key of
Table2) and Response (a text or number field).

 

[shmoussa]

Well... jargon alert here. A table cannot be "converted" into a form. A Form
is a tool, a window which lets you view and edit the data in a Table. It's a
common mistake to think of a Form as containing your data; it doesn't, any
more than my office window "contains" the Owyhee Mountains.


That isn't making sense to me. Why should the Response be in the question
table? It should (I would think) only be in the table of responses!



One answer would be to make the Response field in Table2 a "multivalue field"
- a much-maligned misfeature introduced in A2007. Access actually implements
this, under the covers, by creating a new, hidden table related one-to-many to
your Table2. You can do the same yourself; create a table named (say)
AllResponses, with fields QuestionID, ResponseID (linked to the primary key of
Table2) and Response (a text or number field).

Thanks for the response. I understand how tables and forms work- just
wasn't thinking correctly as I was typing. Appreciate the
clarification though!

I really appreciate the insight on the Access 2007 multi-value
feature. One problem- hope you can help:

My form is displayed to the user in datasheet format. I currently have
a drop down combo-box option in place, which will allow me to select
one response. However, how can I get a drop down box with the
checkboxes for specific questions on my datasheet form?

 

[John W. Vinson]

My form is displayed to the user in datasheet format. I currently have
a drop down combo-box option in place, which will allow me to select
one response. However, how can I get a drop down box with the
checkboxes for specific questions on my datasheet form?

I've never used multivalued fields (despise them!) and almost never use
Datasheets (preferring continuous forms instead, as they give me more
flexibility and more control), so I'm afraid I can't say. You could certainly
use a Continuous Form with a multiselect listbox or combo box, with some VBA
code to update your table, if the multiselect feature doesn't work as you
expect.

 

[PieterLinden via AccessMonster.com]

Hi,

I have a table that I am converting into a form. This table (table1)
has these fields: QuestionID, Question & Response (Combo Box). I have
another table (table2) with these fields: QuestionID, Response.

On my datasheet form, the Response combobox values are generated based
on the matches it finds in table2. So, each time I answer a question,
the drop down box requeries table2 to generate the drop down values of
the questionID that I am answering. This is working perfectly.

Some of my questions, however, require multiple answers in the
Response field. For example, if one of the questions says "What form
of communication have you used with the customer," then the user
should be able to select multiple forms of communication. How can I
enable this functionality?

Any suggestions are much appreciated. Thank you all.

Before you head down a fruitless path, you should probably download and
inspect Duane Hookum's "At Your Survey" database - It will give you an idea
of where to start if you want to build your own. Or you can use it..

I say this because I have been in the unfortunate position of trying to
summarize lots of non-normalized databases before, and it's truly hellish.
http://www.rogersaccesslibrary.com/forum/at-your-survey_topic3.html