How to do Access Combo Box Lookups

  • Thread starter Thread starter gromit12
  • Start date Start date
G

gromit12

Hi,

I have a scale of the following format that I'm using as possible
responses to 20 questions.

1: Very Good
2: Good
3: Average
4: Poor
5: Very Poor

For the 20 questions on a form, I want a user to be able to use a combo
box to enter their data. The combo box option should show both the
number and the text e.g. "3: Average", but I only want it to store the
number (in this case 3) in the table. Also, when I later run through
the datasets of many users, I want to see the combo boxes change to the
selection of that user.

How should I set up may data so this works?

Thanks for any help,
 
In the row source property of the combo bo you will need to adjust some
of the settings. Click the ellipse symbol to the right of the row
source field to bring up the Query Grid. In the first column place the
number value for the ratings, in the second column combine the values
as example: Rating: [FieldName for Number] & " " & [FieldName for
description] this will concat these values into a string for display.
Go back to your combo properties and make sure the column count is set
to 2, and the width od the first column is set to 0". But make sure
you have the bound coulmn set to 1 to store only the numeric
values.This should now display the concat value and hide the stored
value of the number.
 
Thanks Keith,

A further question, if you don't mind - some of the questions I have
have 7 options rather than 5, and others may be altered in future to
have any number of options. I'd thought of having a QuestionScales
lookup table where each field is a question: some fields would then
have 5 entries and some would have 7, or whatever. I could have a field
for Number running from 1 to whatever, but then I wouldn't want blank
options 6 and 7 to appear for questions with 5 options.

What's the best way of dealing with this?

Thanks very much,

Graham
 
Ah, I found a solution (in case anyone has a similar issue)

On the combobox rowsource property

SELECT [tblScales].[SelNo], [tblScales].[Q1] FROM tblScales WHERE
((Len([Q1])>"0"));

Seems to work OK
 
Back
Top