Combo box causing problems?

[Lol Owen]

Hi, wonder if anyone has come across this one before. I have a form/subform based upon a query. The first field of the query, and the one displayed on the main form, is an autonumber. The next field down is also an aotunumber in it's own table, but here I have replaced it with a combo box such that users can select from a range of options. I have built the query to sort the records into ascending order based on the first field. When you run the query this is what happens. However, when the form is used to input new records and view existing ones they are sorted by the data in the combo box, which is not helpful. Is it something I am doing when actually creating the combo box? The combo wizard asks if you want to sort the data. is this having an overriding sort effect on the data when viewed through the form?

All help welcome, thanks, Lol

 

[scouser]

Hope this helps

Not sure if I understand you correctly but this might help:

SELECT DISTINCT tblName.FieldName, FROM tblName ORDER BY tblName.FieldName;

Hope I can be of some help, but apologise if I am barking up the wrong tree!!
My point above is:
Combo - whatever your column count is amend the above example to display the fields you wish to be viewed in the combo then order it by the field as you have in your query. Does that make sense?

I am sure I will be corrected by one of the resident experts!!

 

[Lol Owen]

It makes vague sense but I don't think that is the issue. In detail, the first field is Order Number, which in it's own table is an autonumber. the next field is Customer ID, which is a field from a table related to the Customer table. In the Customer table the Custoimer ID is also an autonumber. I use the combo box to select the Customer ID from the Customers table and put it into the Customer ID field in the related table. Whilst the query sorts all the records into Order ID ascending order, when the records are displayed on a form, built using the query, they are sorted into order based upon the field in the Customer ID combo box. That's why I was wondering about some sort of priority being in place. I think that's about as clear as I can explain it, and it's got me beat.

Cheers, Lol

 

[Kraj]

I know this is probably obvious enough that you've checked it, but what about the "Order By" properties for the query and form?

 

[Lol Owen]

In the query the records are sorted into Order ID ascending. i looked at the Order By for the form and there is nothing in it so: I tried the following:
[Order ID] - no change

SELECT[Orders].[Order ID]; - no change

I guess I could have the syntax wrong, but am I on the right track?

Lol

 

[Lol Owen]

Got it sussed. Open the form in form view, place cursor in field window you wish to sort by, click sort/ascend and bingo. Your'e away. So easy I'm surprised no-one else thought of it.

Cheers, Lol