Table Problems

[clemsongal1026]

I have created a table with several fields. Each field has options such as 'very unlikely' 'unlikely' 'possible' 'likely' or 'very like' to choose from. Other fields have options such as 'high' 'moderate' or 'low'. In my table, the options are all listed in order from top to bottom high to low or very unlikely to very likely.

I have a form that pulls it's information from the table. My form has drop down menus that are supposed to pull the 'high to low' or 'very unlikely to very likely' options. The options all show up, but they don't show up in the order I have them placed in the table. They aren't alphabetical or reverse alphabetical. There really is no order as far as I can tell.

I went into the table and took off all of the 'sorts' off and it didn't do anything. I tried recreating the source type on the form and that still didn't work. I'm not really sure what's wrong or why it's not pulling the information in the manner I have it displayed on the table.

Any help would be greatly appreciated!
Thanks!

[Joe4]

Create a lookup table with two fields, something like:

MyName
MyOrder

Then, enter the hierarchy you want them to appear in your drop-down (i.e. High=1, Moderate=2, Low=3).

Then, go to the table that has the fields that you are setting. Click on the field in design view in the table, go to the Lookup tab, and enter the following:

Display Control: Combo Box
Row Source Type: Table/Query
Row Source: build a query based on your lookup table where you display "MyName" but sort by "MyOrder". The SQL of that code would look something like:

SELECT LookupTable.MyName
FROM LookupTable
ORDER BY LookupTable.MyOrder;

and enter the table name of your lookup table in the

[clemsongal1026]

I am still confused as to how to make this new table accessable by the old one. I went into the old table ('Drop Down Objects') and clicked over to design view. Then I went into Lookup for the field I wanted to change. I set the Row Souce/Type to Table/Query but then I get confused as to what to put for the Row Source. I thought I would click the ... and edit that way but apparently that's not right because nothing's changed.

Any clue what I'm doing wrong?
Thanks!

[Joe4]

You should two tables at work here:
1. Your main data table
2. Your lookup table (like I described in the previous post)

Once you have finished creating your lookup table, go into your main table and go to the field that you want your drop-down box to appear under.

Then follw the steps I laid out in the previous post. When you click the ..., you should get a query builder window. When you do, simply add your lookup table to the window and add your two fields to the query. Then under your Order feld, select to Sort Ascedning and uncheck the "Show" box. Then close out of the query and return back to your table design view.

If you then go back to the Datasheet View and look at this field, you should see the drop-down in the order you want.

[clemsongal1026]

Worked perfectly. Thanks!