Return First Forename From A Sorted Table

[Boltie]

Hi All, I'm currently struggling with what I thought would be a very simple query. :banghead:

I have a form that contains a combobox. I want the default value for this combobox to be the Staff_ID of the first forename to appear in a table when sorted alphabetically.

The table I'm querying is called 'Staff'. Fields within are 'Staff_ID', 'Forename', 'Surname'. The table is sorted by 'Forename' in acending order.

From the example tables below I would expect to return a value of '3' for example 1 and '9' for example 2.

Example 1
Staff_ID Forename Surname
3 Andrew Banks
7 John Jacobs
2 Mark Jones
8 David Smith


Example 2
Staff_ID Forename Surname
9 Alice Jones
3 Andrew Banks
7 John Jacobs
2 Mark Jones
8 David Smith

Any help would be much appreciated. Also, apologies if this has been posted elsewhere but I was unable to find anything anywhere.

P.S. I'm using Access 2007

 

[pr2-eugin]

Sorry come again? How would the Staff ID change when you sort? I am lost with your requirement.

 

[Boltie]

The Staff_ID won't change for any of the reocrds. I was just using it as an example that if I add staff and the order changes, I want the Staff_ID of the first record. I should have made the examples contain different names. Sorry for the confusion, I've amended my post.

 

[pr2-eugin]

Ah okay makes sense now. Well you can just use the ComboBox name, if the ComboBox property dictates its bound column is 1, and it is hidden based on the Column width, then you can use the name of the combo box it will get you the StaffId.

 

[Boltie]

Sorry, I'm not sure I follow. I'm pretty new to databases.

The ComboBox I have on the form is titled 'Staff_ID'.
Row Source: = SELECT Staff.Staff_ID FROM Staff;
Row Source Type: Table/Query
Bound Column 1

This is all fine. It's the Default Value expression I'm unsure of. My first attemp was along the lines of;
=DLookUp("[Staff_ID]","Staff","[Forename]=Min([Staff].[Forename]")

Obviously the Min function only works for numeric values.

There must be a simple expression that says pull the value of this field for the first record of this sorted table.

 

[pr2-eugin]

Okay scratch that, try this,

Row Source: SELECT Staff.Staff_ID, Staff.Forename FROM Staff ORDER BY Staff.Forename;
Row Source Type: Table/Query
Bound Column: 1
Column Widths: 0Cm;2.501cm

Now Save and see the list, it will populate the list of all staff arranged alphabetically, when you use the ComboBox you will get the Staff_ID which is hidden. Makes sense?

 

[Boltie]

I think you might be confused with what I want. If not, I'm incredibily sorry for being slow.

I just want the defualt value of this combo box to be set upon opening the form. The dropdown list is fine.

 

[Boltie]

Does it matter that I'm creating a form that isn't based on viewing individual records from a table?

It seems strange that even if I enter '1' into the default value property of the combo box and then save and switch to form view, the combo box is still blank. It allows me to open the dropdown list and then select from the staff list. Why wouldn't it populate with '1' for Staff_ID if I have explicitly stated to have that value for default?

Before switching back to design view I remove any value from the combo box so that when I switch back to form view the box is empty ready for the default value to be input. This will later become an event to clear the box when the form is closed.

P.S I suppose what I'm saying is there is no control source for this combobox. I just wanted it to feed from the staff list and then later use this value for other boxes to feed off. Surely I don't need a control source?

 

[Boltie]

Ah okay makes sense now. Well you can just use the ComboBox name, if the ComboBox property dictates its bound column is 1, and it is hidden based on the Column width, then you can use the name of the combo box it will get you the StaffId.

I didn't understand what you meant here at first and even when I did and tried it it didn't work. It is right though. Where I've been going wrong is not closing the form and instead switching between design and form view. I closed the form with "=[Staff_ID].[ItemData](0)" in the default value property. I came back to the DB after leaving it for a short while, opened the form and bam, there it is.

That's another lesson learnt. Access doesn't reload the form upon switching between views. I thought that was the case, obviously not.

Cheers for your help Paul! Thanks added.

 

[Mile-O]

I just want the defualt value of this combo box to be set upon opening the form.

In your Form_Load event:

Me.YourComboBox = Me.YourComboBox.ItemData(0)