dropdown list box - control order

[Charles]

I am trying to add the ability for a user to change the order in which
the elements are listed in a dropdown list box. Before I added the
ListID field the dropdown list box order was controlled using the
MotorhomeID. The problem I am having is that I don't know the best
method for allowing people to make the ListID changes easily.

Database: MS Access 2000

Table: Default-Motorhome
Fields:
MotorhomeID (autonumber)
MotorhomeName (text)
ListID (number)

I want the user to be able to change the ListID from a form.

Example: Motorhome A
ListID=1

Motorhome C
ListID=2

Motorhome B
ListID=3
The ListID controls what number the record is displayed as in a list
dropdown box. Notice that Motorhome C is listed above Motorhome B.

[Larry Linson]

What would be a natural order for them? That is what you ought to use. If
"Motorhome A" actually refers to Manufacturer and Model, then that would be
reasonable; if it refers to Mr. Smith's Motorhome, it _might_ still be
preferrable.

Larry Linson
Microsoft Access MVP

"Charles" <ch*****@charlesgreen.org> wrote in message
news:3f**************************@posting.google.c om...

I am trying to add the ability for a user to change the order in which
the elements are listed in a dropdown list box. Before I added the
ListID field the dropdown list box order was controlled using the
MotorhomeID. The problem I am having is that I don't know the best
method for allowing people to make the ListID changes easily.

Database: MS Access 2000

Table: Default-Motorhome
Fields:
MotorhomeID (autonumber)
MotorhomeName (text)
ListID (number)

I want the user to be able to change the ListID from a form.

Example: Motorhome A
ListID=1

Motorhome C
ListID=2

Motorhome B
ListID=3
The ListID controls what number the record is displayed as in a list
dropdown box. Notice that Motorhome C is listed above Motorhome B.

[Charles]

Larry,
Thank you for the responce. Here is more detail.

My customer uses access to create a RV Rental Contract. To simplify
the scenario I have 2 tables, 2 forms, and 1 report.

Tables: Customer (store customer data and selected RV)
RV (stores RV data)

Forms: Contact (used to enter customer and select RV from combo box)
RV (used to enter available RVs)

Report: Contact (Rental Agreement)

Table: Customer
----------------------
CustID (autonumber) – customer ID – primary key
Name (text) – customer name
RVID (number) – RV ID - foreign key

Table: RV
----------------------
RVID (autonumber) – RV ID – Primary Key
RVName (text) – RV Name
ListID (number) – I added this to keep track of the desired order in
the in the combo box.
Service (Yes/No) – Used to signify what RVs are in service and those
out of service.

My customer currently has the ability to manage what RVs are listed in
the combo box by having the "Service" field checked or unchecked.
Currently, the user selects the desired RV from a combo box.

What I am trying to do is to give them the ability to control the
order that they are listed in. The source data for the combo box is
stored in the RV table. For instance:

Table: RV
RVID | RVName | ListID |Service
---------------------------------------------
1 | Motorhome A | 1 |Yes
2 | Motorhome B | 2 |Yes
3 | Motorhome C | 3 |Yes

Combo Box would list:
Motorhome A
Motorhome B
Motorhome C

However if I change the ListID of Motorhome B & C

RVID | RVName | ListID |Service
---------------------------------------------
1 | Motorhome A | 1 |Yes
2 | Motorhome B | 3 |Yes
3 | Motorhome C | 2 |Yes

I want the combo box to list them like this.
Combo Box would list:
Motorhome A
Motorhome C
Motorhome B

The problem: How do I create a form that will allow the user the
ability to change the ListID easily without giving it the same value
as one of the records.

----------------------------------------------

What would be a natural order for them? That is what you ought to use. If"Motorhome A" actually refers to Manufacturer and Model, then that would bereasonable; if it refers to Mr. Smith's Motorhome, it _might_ still be preferable.
Larry Linson
Microsoft Access MVP "Charles" <ch*****@charlesgreen.org> wrote in message
news:3f**************************@posting.google. com...

I am trying to add the ability for a user to change the order in which the elements are listed in a dropdown list box. Before I added the
ListID field the dropdown list box order was controlled using the
MotorhomeID. The problem I am having is that I don't know the best
method for allowing people to make the ListID changes easily.

.. Database: MS Access 2000

Table: Default-Motorhome
Fields:
MotorhomeID (autonumber)
MotorhomeName (text)
ListID (number)

I want the user to be able to change the ListID from a form.

Example: Motorhome A
ListID=1

Motorhome C
ListID=2

Motorhome B
ListID=3
The ListID controls what number the record is displayed as in a list dropdown box. Notice that Motorhome C is listed above Motorhome B.

[Richard Bernstein]

Put an option group (frame control) on the form, with one option button for
each of the sort choices, and make the default value equal to the value of
the option which is in the combo's RowSource.

In the AfterUpdate event of the frame control, code sql for the combo's
RowSource, with a different order by clause for each option, and then
requery the combo (requery may or may not be necessary).

EG, aircode,

Private Sub fraSort_Afterupdate
Dim str as string
str= "Select field1, field2, field3 from table order by "
select case fraSort
case 1
str = str & "field1"
case 2
str = str & "field2"
case 3
str = str & "field3"
end select

combo1.rowsource = str
combo1.requery
End Sub

Richard Bernstein

ch*****@charlesgreen.org (Charles) wrote in
news:3f**************************@posting.google.c om:

The problem: How do I create a form that will allow the user the
ability to change the ListID easily without giving it the same value
as one of the records.