Displaying a specific record in a form

[dkwong]

I have an admin form to update fields in a Course table. The form has a search combo box containing a list of the course names. Once a course is selected, the course attributes of a course are displayed according to the selected CourseID. The Course table has the following fields: CourseID (autonumber), CourseName. Currently, I have the search combo box listing the CourseNames in the Course table in alphabetical order; therefore, the CourseID's are not necessarily in incremental order. When I open the form, I want to display the first Course in the list i.e. the first in alphabetical order, not the first in the Course table. However, I can only seem to get the latter. I'm using the GoToRecord action in a macro specifying "First" in the Record argument so it's displaying the first record in the Course table. How can I display the first record at the top of the alphabetical order instead of the record with the lowest autonumber? Hope this is clear. Thanks for your help!

 

[GingerR]

in your form design, look at the property ORDER BY.

set it to tablename.fieldname
in your case
Course.CourseName

see if that does it.

g

 

[dkwong]

Thanks, but it doesn't make a difference. It's still listing the first record in the table regardless of the alphabetical order. Any other ideas? Thanks!

 

[gusbrunston]

Hi DK:

If I understand your problem, I would not use a macro to go to the first record, but instead:

Create a query (or an SQL statement) that lists the courses alphabetically, and lists the CourseIDs in the second column.

Then use that query as the source of your combo box list. If the CourseID is the field to use for finding the course attributes, then bind column 2 in the property sheet.

In the property sheet (Data) for the combo box:

   Row source type: Table/Query
        Row source: qryNameOfMyQuery

In designing the query, I would put the alphabetical field (Course name) in the first column, and select to sort that field in ascending order. Then add the CourseID field in the next column.

Instead, you could use the wizards for the query and the combo box to help you do what you want to do

Good luck. Gus Brunston An old PICKer, using Access2000
padregus@attbi.com