Combo box problem with SKU #'s 209 vs 2091

[KB]

Access 2000 build 9.0.2720

I have an app, with the normal datasheet view for entering products
into an order. Entry of product can be either of 2 ways, enter SKU #
(text field - some sku's have letters) or via description. If you
enter a sku, description fills in.

The problem is when you want to enter sku # 209 for example, but it
pops up 2091 (with the 1 highlighted). Hitting enter, or tab selects
2091, and you have to backtrack, enter 209 in sku (which brings up
2091) and delete key the highlighted 1 then enter or tab. A great way
to accidentially enter the wrong product!

Is there any way to change this behavior, or as I suspect I'm stuck
with it?

Thanks!

 

[Ken Snell [MVP]]

You're using a combo box for the SKU # entry, right? If you don't want the
AutoExpand feature to work for that combo box, open the form in design view,
click on the combo box, click on Properties icon on toolbar, click on Data
tab, and set AutoExpand property to No.

 

[Arvin Meyer]

Try padding out the field with zeros in the query used as the rowsource for
the combo. That will push the numbers to the right and order 291 before
2190. Here's some code to help you pad it out. strIn is your field, and
intChars is the number of characters you want to pad to:

Public Function LeftZeros(strIn As String, intChars As Integer) As String
On Error Resume Next

If intChars < 1 Then
LeftZeros = ""
Else
LeftZeros = right(String(intChars, left(0, 1)) & strIn, intChars)
End If

End Function


So:

Expr1: LeftZeros([SKU#], 4)

would yield:

0209
2091
0ABC
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[KB]

Works great, thanks! Learn something new each day.