Searching or NotInList

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I asked a question a little while ago about the NotInList property and got
some help which was great but I have another question relating to this.

If something is typed into the combo box that doesn't appear on the list I
want to be able to search for the record that matches some of the words as
there are apparently lots of potential cases where notInList will come into
play.
EG. If the Item on the table is called ItemA and someone types in Blue ItemA
I want to pick up the details for ItemA from the table.
Can this be done by using a search of some kind or does it have to be coded
as below for each keyword like ItemA?
 
You can search on a part of a string, so if you entered ItemA but you have
"My ItemA Data", it will return this record
But to return a record that match only a part of the string the use entered
will be to difficult, and almost impossible, how can you tell on which part
of the string to search on.

For my first advice, you can use the Dlookup with like

Dim MyStr
MyStr = DLookup("ID", "qryMyTable", "EquipDesc Like '*" &
Me!cboMachine.column(1) & "*'")
If not Isnull(MyStr) then
Me!cboMachine = MyStr
Me!txtNewEquipment = NewData
Else
MsgBox "Sorry. This item " & vbCrLf & _
"isn't on the list.", _
vbInformation + vbOKOnly, "No Matching Record"
End If
 
I'm not quite sure I understand the question. If what you are saying in you
want to find an occurance of what was entered in the combo box in a field in
the table. You used the example finding ItemA if a user types in Blue ItemA.
The problem you are going to have is there is a high probability you will
get more that one match.
Possibility range is: None, One, Many. So, you will have to plan for that.
A DLookup will handle two of the cases, but will not handle the many
possibility. I think the solution would be to construct a parameter query
that will return a recordset for all matches. Then test for the recordcount.
The first two possibilities would be easy to handle. For the many
possibility, I would suggest poping up a dialog with either a combo or a list
box to allow the user to select one and return the selection the user made.
 
Back
Top