Combo Boxes Containing "*"

  • Thread starter Thread starter Anthony Headley
  • Start date Start date
A

Anthony Headley

Morning/Afternoon Evening all,

I have an odd issue that maybe someone here could help me out with. Please
excuse if this is in the wrong group.

So, I have an ADP connected to a SQLExpress Server and performance is
quite good but I am having some issues selecting some Items from Combo
Boxes.

My issue is as follows:
I have a combo box that is part of a subform that has it?s rowsource =
"SELECT TOP 100 PERCENT EquipmentID, PieceNr FROM Table_Equipment ORDER BY
PieceNr"

This returnes about 1000 items that look about like this:
35 LY*101CA
35 LY*101DV
1 ML*101AM
2 ML*101DV
344 ML*101XX
....

The combo box is correctly configured to bind to the first column and show
only the second, and it box works great as long as I select the item from
the dropdown list (F4) or use the mouse. However, as soon as I paste, or
type, a value that I know is in the list the NotInList event fires; even
though I can select the item from the dropdown box.

I have even tried creating a progressive an OnKeyPress Query to narrow
down the choices in case 1000 items was too much, but even after the list
is narrowed down to 1 item it still fires the NotInList Event if I press
{Arrow Down}, {Arrow Left}, {Tab}, or {Enter}.

So then, as a work around I created this event:
~snip~
Private Sub Piece_Nr_NotInList(NewData As String, Response As Integer)
Me.Piece_Nr.RowSource = "SELECT EquipmentID, PieceNr FROM
Table_Equipment WHERE PieceNr = '" & NewData & "'"
If Me.Piece_Nr.ListCount > 0 Then
Me.Piece_Nr.ListIndex = 0 ?this seems to be the only way to have
the item selected
Response = acDataErrAdded ?this does not seem to requery the
control as stated in help.
Else
Response = acDataErrDisplay
End If
End Sub
~snip~
This works, kind of but leaves me with the dropdown extended and I have to
press enter twice before the value is taken.

Personally I think it because the values that I?m looking up contains a
?*?. It seems that as soon as I type the ?*? the Auto Expand function
stops expanding and the list is reset back to the beginning. I also also
tried looking other values like ?Test?, ?This?, ?DxxxxWork!? and they are
all ok type typing and pasting.

This is the first time I am experiencing this issue since this database
was at one time in its life a MDB. Also I have already tried LIKE %
queries but that did not help my workaround.

Any ideas would be super useful.

Thanks , And sorry again if this is the wrong group.


Anthony
 
If you suspect that Access is somehow choking on the * character (and that
seems as good a hypothesis as any) could you maybe try replacing it with
some other character? For instance, you could see what happens if you use
this as the rowsource:

"SELECT TOP 100 PERCENT EquipmentID, Replace(PieceNr,"*","#") AS
ChangePieceNr FROM Table_Equipment ORDER BY PieceNr"

A bit of a pain for your users (if it works at all) since I suppose they are
accustomed to the *, but they would soon get used to it.

Morning/Afternoon Evening all,

I have an odd issue that maybe someone here could help me out with. Please
excuse if this is in the wrong group.

So, I have an ADP connected to a SQLExpress Server and performance is
quite good but I am having some issues selecting some Items from Combo
Boxes.

My issue is as follows:
I have a combo box that is part of a subform that has it?s rowsource =
"SELECT TOP 100 PERCENT EquipmentID, PieceNr FROM Table_Equipment ORDER BY
PieceNr"

This returnes about 1000 items that look about like this:
35 LY*101CA
35 LY*101DV
1 ML*101AM
2 ML*101DV
344 ML*101XX
....

The combo box is correctly configured to bind to the first column and show
only the second, and it box works great as long as I select the item from
the dropdown list (F4) or use the mouse. However, as soon as I paste, or
type, a value that I know is in the list the NotInList event fires; even
though I can select the item from the dropdown box.

I have even tried creating a progressive an OnKeyPress Query to narrow
down the choices in case 1000 items was too much, but even after the list
is narrowed down to 1 item it still fires the NotInList Event if I press
{Arrow Down}, {Arrow Left}, {Tab}, or {Enter}.

So then, as a work around I created this event:
~snip~
Private Sub Piece_Nr_NotInList(NewData As String, Response As Integer)
Me.Piece_Nr.RowSource = "SELECT EquipmentID, PieceNr FROM
Table_Equipment WHERE PieceNr = '" & NewData & "'"
If Me.Piece_Nr.ListCount > 0 Then
Me.Piece_Nr.ListIndex = 0 ?this seems to be the only way to have
the item selected
Response = acDataErrAdded ?this does not seem to requery the
control as stated in help.
Else
Response = acDataErrDisplay
End If
End Sub
~snip~
This works, kind of but leaves me with the dropdown extended and I have to
press enter twice before the value is taken.

Personally I think it because the values that I?m looking up contains a
?*?. It seems that as soon as I type the ?*? the Auto Expand function
stops expanding and the list is reset back to the beginning. I also also
tried looking other values like ?Test?, ?This?, ?DxxxxWork!? and they are
all ok type typing and pasting.

This is the first time I am experiencing this issue since this database
was at one time in its life a MDB. Also I have already tried LIKE %
queries but that did not help my workaround.

Any ideas would be super useful.

Thanks , And sorry again if this is the wrong group.


Anthony
 
Baz, you're a star!
That works like a charm,

It did not even occur to me to fix the problem on the server side and not
the on the client. Unfortunately it is a quite an issue to leave it like
that for my users, however a little VBA and an option box can get me from
a "*" to an "-" in a jiffy.

I tried this both in Access 2007 and 2003 and it seems to be a bug with
the combo box control. Though it can't believe that no one has ever
reported such a thing, then again my Google searches were not too fruitful
since '"Combo box" asterix character problem bug issue' really does not
return anything useful.

Which way to Bug reporting ?:)

Anyway, Thanks again Baz You have saved me a lot of "head on table" time.

H
 
You're very welcome. I'm sure you are right, it's a bug, but I'm not
surprised that it hasn't come up before: data containing a * isn't that
common, and then someone would have to use it in a combo box as you did.

So congratulations: you seem to be the discoverer of a new bug! It's a pity
that bugs don't get names, 'cos this one could be named after you!

Baz, you're a star!
That works like a charm,

It did not even occur to me to fix the problem on the server side and not
the on the client. Unfortunately it is a quite an issue to leave it like
that for my users, however a little VBA and an option box can get me from
a "*" to an "-" in a jiffy.

I tried this both in Access 2007 and 2003 and it seems to be a bug with
the combo box control. Though it can't believe that no one has ever
reported such a thing, then again my Google searches were not too fruitful
since '"Combo box" asterix character problem bug issue' really does not
return anything useful.

Which way to Bug reporting ?:)

Anyway, Thanks again Baz You have saved me a lot of "head on table" time.

H
 
Exelent, I declare this that KB_Hossimo*Comobox bug and the
KB_Replace(BAZ,'*','-')Workaround.

I need to check this out but I don't think I had this problem with the
database as an MDB, on maybe I just ignored it.

The funny thing is that the client of the company that I'm building this
DB for is, and has been using SAP for years, and they dictated that Piece
Numbers, as we call them, Must all contain *s, it's a real pain, but this
is an execlent workaround.
 
Back
Top