Re: Combo Boxes Containing "*"

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.

"Anthony Headley" <hoss@xxxxxxxxxxxxxxxxx> wrote in message
news:op.twfq8mix54t6ci@xxxxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Combo Boxes Containing "*"
    ... you seem to be the discoverer of a new bug! ... a value that I know is in the list the NotInList event fires; ... though I can select the item from the dropdown box. ... Response As Integer) ...
    (microsoft.public.access.adp.sqlserver)
  • Re: otherwise the figure in Azizs gp might offset some thorough advertisings
    ... The compact module rarely hosts Jimmy, ... it'll once prevent the character. ... Mel never calmly praised in response to the movement. ...
    (sci.crypt)
  • Re: Putting a scene into words.
    ... And here I'm asking for a ruler that I can use myself. ... internalisation, response. ... (character thinks what to do, tough this might not be needed ... from the square common room. ...
    (rec.arts.sf.composition)
  • Re: New Kid: Introduction and some Qs
    ... that's actually a promising response. ... wasn't going to put up with such nonsense, no matter what publishers ... If you're this much of a burst writer, you probably will need to do ... living with the guy (the character, ...
    (rec.arts.sf.composition)
  • Re: Training Week Ending February 19, 2006
    ... The character Pendejo was very ... When I accepted his direct & specific request, the character, ... Note this is entirely an invention of Charles Anderson, ... Obtaining a response from the ...
    (rec.running)