ComboBox Numeric Descend 2

[kennedymr2]

I have a very large Table ... the main lookup item is the OrderNumber which is numeric

For the sake of this question i an looking up orderno 434 , but i also have an order 4342

I have a query which uses the OrderNo as the main sort key and it is set up as descend...
I need to have the display display the latest order at the top.
If i have the Query sorted by OrderNo (ascending) all works fine.. i can key in 434 and it shows this invoice in the dropdown, i can also key in 4342 and all works fine.

BUT... If i set the query up in Orderno (Descending), the dropdown displays fine (in decending order), but when i key in 434 it jumps to 4342...

Am i missing something...

Would appreciate some help...

Regards Kennedymr2

 

[MajP]

I am not sure an easy solution to work around, but this is normal behavior. It is going to find the first record that completely contains your text. Since it is in desc "434" will be first found in "4342". You may want to consider a find as your type combo box. This will ensure you find what you are looking for.
faq702-6304
I am not sure how this will work on a very larger set of records because there are a lot of requeries and this may be slow.

 

[MajP]

As I look at the code, it is designed for a text field. You can either modify it to work with a numeric or create a text field in your query. txtOrderno:cstr([orderNo])

 

[kennedymr2]

MajP

Thanks for your quick reply....

I will give the FIND a try... but i feel it will be too slow, as my table has over 20,000 records.

Funny how the combo looks at the query this way, even though it is a numeric field.

I did try setting the main key as Format(orderno,"000000")
which zero filled the lookup, but i then have to type in 000434 in the combo lookup, which is not very practical...
If i could get the combo box to zero fill the box as i typed, this may be practical !!???

Appreciate any ideas..

Thanks

 

[kennedymr2]

Majp... Thanks for your idea...

I have tried your idea... txtOrderno:cstr([orderNo])

When in Descend order i still get the problem..
when typing in 434 i get the 4342 coming up..

Appreciate any ideas... its a nuisance of a problem..@!!@

Many thanks Kennedymr2

 

[MajP]

20k should not be a lot if you are only pulling the ID. I was suggesting if you use the find as you type, then add the extra field to your query txtOrderno:cstr([orderNo]) since it works on a text field only using the like operator. I expected if you use this on a standard combo you would give the same behavior.

 

[TheAceMan1]

How are ya kennedymr2 . . .

The problem with the combobox and the method provided by [blue]MajP[/blue] (which is a filter), is that [blue]were still pinging against a numeric decending list![/blue]

Whats needed here is the equivalent of [blue]FindLast![/blue] , versus FindFirst.

At present I see a textbox for data entry and a subform to simulate the dropdown listing (subform appears when textbox receives focus). Basic code (no frills) in the [blue]On Change[/blue] event of the textbox would look like:

[blue]   [subFormName].Form.Recordset.[purple][b]FindLast[/b][/purple] "[OrderNumber] = " & Me!TextboxName.Text[/blue]

[blue]Your Thoughts? . . .[/blue]

BTW: Ascending sort order is [purple]prime[/purple] for search as you type because you enter higher & higher values. There's no way to enter lower & lower . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[kennedymr2]

TheAceMan1

Thanks for the advice... i can see i am not going to get the result i need just with a standard ComboBox.
Will see if i can get a presentable result using the FIND.
Was HOPING !! that here may be a trick using just a ComboBox... o well...
Appreciate you helping me on quite a few occassions...


Mapj... Will persue all the ideas today... and see what end up with ... Really appreciate your advice...

Its good to have this lifeline, when a problem like this props up... thanks..

Regards Kennedymr2

 

[kennedymr2]

I have had a good look at using Find...
It does work, but is a bit slow and cumborsome..

I have spoken to the user, and they said they really only need the latest orders to the top, as these are the ones they will probably alter the most.
As a compromise, i have altered the ComboBox to sort Ascending, so everything works normally.

BUT, i have put a small list box on the form , displaying the last 100 orders, with the latest to the top. They can then click the listbox for these orders to display.

Really appreciate the help and time offered. I have also learned a few alternatives re using Find etc.

Regards Kennedymr2