Hi all,

I am trying to create a survey with access, and I have a question in it
which states " Put these 5 options in order of importance" then I have 5
options, but how can I do this in access?

Any help would be greatly appreciated

Thanks

Jane

Unless you let us in on how YOU put the items in "order of importance",
we're not too likely to be able to guess...

Are you working in a form? Are you working with an option group control?
Give us a bit of context...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jane" <(E-Mail Removed)> wrote in message
news:77D96802-67DB-4553-A666-(E-Mail Removed)...
> Hi all,
>
> I am trying to create a survey with access, and I have a question in it
> which states " Put these 5 options in order of importance" then I have 5
> options, but how can I do this in access?
>
> Any help would be greatly appreciated
>
> Thanks

Jane,

Here's one way to do it. I'm sure there are other ways, some of which will
be easier and more efficient.

Start with a table to hold your option and its rank. Rank should be single
or decimal data type.

Put a list box on your form bound to the table. Your rowsource would be
something like "SELECT Option from tblOptions ORDER BY Rank".

Put two command buttons next to the list box, one to move an option up, the
other to move it down. When the user clicks the button, open a recordset,
move to the selected item in the listbox, and increase or decrease it's
rank. In the code below, I am decreasing the rank by 1.5. This would
effectively move the option upward in preference. You have to
increase/decrease the rank by enough to make the option lower/higher than
the next value.

Dim db as database
Dim rs as Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM MyTable", dbOpenDynaset)

rs.FindFirst "Option = '" & Me!MyListBox & "'" 'I'm assuming option
is text
rs.Edit
rs!Rank = rs!Rank - 1.5
rs.Update
rs.Close

The next step will be to resequence your options so they are again ranked 1
to 5. This will allow the user to change the rankings as many times as
he/she needs to.

Dim x as integer
Set rs = db.OpenRecordset("SELECT * FROM MyTable ORDER BY Rank",
dbOpenDynaset)
Do While Not rs.EOF
x = x + 1
rs.Edit
rs!Rank = x
rs.Update

rs.MoveNext
Loop
rs.Close

Set rs = Nothing
Set db = Nothing

Finally, requery the listbox to redisplay the options in the correct order.

There are probably better ways to do this. You could use an array rather
than a table to hold your data. You might be able to manipulate the
ListIndex property of the list box to move your options around. You can
even create a function that acts as the RowSourceType of the list box which
would allow you to manipulate the items.

Hope this helps.


"Jane" <(E-Mail Removed)> wrote in message
news:77D96802-67DB-4553-A666-(E-Mail Removed)...
> Hi all,
>
> I am trying to create a survey with access, and I have a question in it
> which states " Put these 5 options in order of importance" then I have 5
> options, but how can I do this in access?
>
> Any help would be greatly appreciated
>
> Thanks