Combo values/NotInList - coding problem

[Guest]

I am trying to allow users to enter item names using a combo box - no problem.
If they type a name and it doesn't exist on the combo box list, I want to
check whether what they have typed in has the word 'machine' in it.
If it does, I want to default the combo box selection to a specific record
that does exist in the list (the Main Machine record) and output what they
typed in to another field.
If their entry is missing from the list and doesn't contain the word
'machine' I just want to put out a message.
The final part I have working using the NotInList property but can't get the
first bit to work.
The combo box I am using hides the first 4 fields that it selects, including
the key field, and what is type in is a description. It then populates a
number of fields on the form based on the record selected.
Can anyone advise? Perhaps I need to completely rethink the way I am going
about this.

 

[Guest]

Try:

Private Sub cboMachine_NotInList(NewData As String, Response As Integer)

On Error GoTo ErrHandler

If (InStr(1, LCase$(NewData), "machine", vbDatabaseCompare)) Then
Me!cboMachine = DLookup("ID", "qryMyTable", "EquipDesc = 'Main
Machine'")
Me!txtNewEquipment = NewData
Else
MsgBox "Sorry. This item " & vbCrLf & _
"isn't on the list.", _
vbInformation + vbOKOnly, "No Matching Record"
End If

Response = acDataErrContinue

Exit Sub

ErrHandler:

MsgBox "Error in cboMachine_NotInList( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & "Error #" & _
Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

.. . . where cboMachine is the name of the combo box, ID is the combo box's
bound column, qryMyTable is the name in the combo box's Row Source Property,
EquipDesc is the name of the field that shows the description that the user
types in (and is also a field in qryMyTable), and txtNewEquipment is the name
of the text box where the user's typed in value will be assigned.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Thanks this has helped greatly.

Try:

Private Sub cboMachine_NotInList(NewData As String, Response As Integer)

On Error GoTo ErrHandler

If (InStr(1, LCase$(NewData), "machine", vbDatabaseCompare)) Then
Me!cboMachine = DLookup("ID", "qryMyTable", "EquipDesc = 'Main
Machine'")
Me!txtNewEquipment = NewData
Else
MsgBox "Sorry. This item " & vbCrLf & _
"isn't on the list.", _
vbInformation + vbOKOnly, "No Matching Record"
End If

Response = acDataErrContinue

Exit Sub

ErrHandler:

MsgBox "Error in cboMachine_NotInList( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & "Error #" & _
Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

. . . where cboMachine is the name of the combo box, ID is the combo box's
bound column, qryMyTable is the name in the combo box's Row Source Property,
EquipDesc is the name of the field that shows the description that the user
types in (and is also a field in qryMyTable), and txtNewEquipment is the name
of the text box where the user's typed in value will be assigned.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

['69 Camaro]

You're welcome!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

[Guest]

The solution you provided worked 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 or does it have to be coded as below for each keyword like
ItemA?