Multiselect in datasheet view?
in [Forms]
|
Prev: Dates with day below ten aren't found with docmd.applyfilter state
Next: Multiselect in datasheet view?
From: shmoussa on 12 Apr 2010 16:26 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.
From: John W. Vinson on 12 Apr 2010 18:00 On Mon, 12 Apr 2010 13:26:47 -0700 (PDT), shmoussa <shmoussa(a)gmail.com> wrote: >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). -- John W. Vinson [MVP]
From: shmoussa on 13 Apr 2010 01:15 On Apr 12, 6:00 pm, John W. Vinson <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote: > On Mon, 12 Apr 2010 13:26:47 -0700 (PDT), shmoussa <shmou...(a)gmail.com> wrote: > >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). > -- > > John W. Vinson [MVP] 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?
From: John W. Vinson on 13 Apr 2010 13:07 On Mon, 12 Apr 2010 22:15:02 -0700 (PDT), shmoussa <shmoussa(a)gmail.com> wrote: >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. -- John W. Vinson [MVP]
From: PieterLinden via AccessMonster.com on 13 Apr 2010 17:22
shmoussa wrote: >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 -- Message posted via http://www.accessmonster.com |