NotInList Peculiarity

[Klingon Coder]

Hi,
I am hoping one of you gurus may be able to help me out.
I have a combo box on a Task form that brings back the ContactID from the
Contact table, but displays a concatenation of the last name a comma and the
first name (i.e. Doe, John). The user is able to enter the Contact name as
either “John Doe†or “Doe, Johnâ€. There is code in place that will take the
user’s entered data and parse the last name and the first name regardless of
how they entered it. A comparison of the last name and first name is then
made against those in the Contact table and if the user’s information matches
the proper name is selected. The only difference in the behaviour is if they
entered Doe, John, the list jumps to the first name that matches the text
being typed, i.e. typing the D will bring the user to the first Name
beginning with D etc. If they enter John Doe when they attempt to leave the
combo box the name Doe, John will be highlighted with the list part of the
combo box being open. As near as I can figure out, this would be the normal
way for it to behave and I don’t have any issue with the user having to tab
twice to get out of the combo box. The situation I am having trouble with is
when the contact is not in the list.

I have the combo box Limit To List property set to yes and if a user enters
a value that isn’t in the list a message box opens asking if they want to add
the contact to the list. On an affirmative reply, the Contact form opens and
inserts the names that they had typed on the Task form into their respective
text boxes. Everything works flawlessly as far as triggering the NotInList
event, the Contact form opens and the proper fields are filled based on the
information that was passed from the Task form. The user can save the record
and close the form, but here is where things get hairy. If the user entered
the Contact name as “Doe, John†on the Task form, then the new Contact
information is brought back and fills the combo box as it should and they can
proceed with the rest of their data. However, if the user entered “John Doeâ€
into the combo box, the NotInList event fires and as I mentioned the
information is inserted into the appropriate text boxes, but when the user
saves the data and closes the form they get the following message box:

“The text you entered isn’t an item in the list.
Select an item from the list, or enter text that matches one of the listed
items.â€

When the user clicks the corresponding OK button the combo box list opens
and the new data is shown in the list (not necessarily highlighted, but it is
in the list). I am not sure why the combo box isn’t having the new Contact
information displayed in this circumstance. It would appear that it is
comparing the First Name to the Last Name and getting a mismatch, but what
doesn’t make sense is why it can find the Contact when the name is already in
the list, but not when it has just been added.

I have enclosed the NotInList code in hopes someone might be able to point
out what I am missing. Thanks.


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

Dim db As Database
Dim rs As Recordset
Dim lngContactID As Long
Dim strLast As String
Dim strFirst As String
Dim varContact As Variant

If InStr(1, NewData, ",") > 0 Then
'Comma Last, First Name
strLast = ParseLast(NewData)
strFirst = ParseFirst(NewData)
ElseIf InStr(1, NewData, " ") > 0 Then
strLast = RParse(NewData, , " ")
strFirst = Parse(NewData, , " ")
Else
' no comma First Last Name
strLast = NewData
End If

strLast = SetUpper(strLast)
strFirst = SetUpper(strFirst)

'dlookup against table and set name values
strsearch = "[lastname] = " & "'" & strLast & "' and " & "[firstname]= "
& "'" & strFirst & "'"
varContact = DLookup("[ContactID]", "tblContact", strsearch)

If IsNull(varContact) Then ' not in list either way

If vbYes = MsgBox("'" & NewData & "' is not a current Contact." &
vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, "Add Contact") Then
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("SELECT * FROM [tblContact] WHERE 1=2;")

With rs
.AddNew
![LastName] = strLast
![FirstName] = strFirst
lngContactID = ![ContactID]
![ContactType] = [ContactType]
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.OpenForm "frmContact", , , "[ContactID]=" & lngContactID,
acFormEdit, acDialog, 1
Response = acDataErrAdded
Me.cboContactID = varContact
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
Me.cboContactID = varContact
Exit Sub
End If
Me.cboContactID.Value = lngContactID
NewData = Me.cboContactID.Text
End Sub

--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

 

[Joshua A. Booker]

Coder,

This line is causing the notinlist event to fire again.

Me.cboContactID = varContact

What happens if you leave it out? If the value entered the first time is
the same as in the table then you shouldn't need to set the combo again. I
guess your double formatting thing could make this necessary. In that case,
you need to stop the recursion by setting a variable to true and check for
it before running notinlist like this:

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

if not blnRecurse then
blnRecurse = true
'Your Code Here.....
blnRecurse = false
End if

End Sub

HTH,
Josh

Hi,
I am hoping one of you gurus may be able to help me out.
I have a combo box on a Task form that brings back the ContactID from the
Contact table, but displays a concatenation of the last name a comma and the
first name (i.e. Doe, John). The user is able to enter the Contact name as
either "John Doe" or "Doe, John". There is code in place that will take the
user's entered data and parse the last name and the first name regardless of
how they entered it. A comparison of the last name and first name is then
made against those in the Contact table and if the user's information matches
the proper name is selected. The only difference in the behaviour is if they
entered Doe, John, the list jumps to the first name that matches the text
being typed, i.e. typing the D will bring the user to the first Name
beginning with D etc. If they enter John Doe when they attempt to leave the
combo box the name Doe, John will be highlighted with the list part of the
combo box being open. As near as I can figure out, this would be the normal
way for it to behave and I don't have any issue with the user having to tab
twice to get out of the combo box. The situation I am having trouble with is
when the contact is not in the list.

I have the combo box Limit To List property set to yes and if a user enters
a value that isn't in the list a message box opens asking if they want to add
the contact to the list. On an affirmative reply, the Contact form opens and
inserts the names that they had typed on the Task form into their respective
text boxes. Everything works flawlessly as far as triggering the NotInList
event, the Contact form opens and the proper fields are filled based on the
information that was passed from the Task form. The user can save the record
and close the form, but here is where things get hairy. If the user entered
the Contact name as "Doe, John" on the Task form, then the new Contact
information is brought back and fills the combo box as it should and they can
proceed with the rest of their data. However, if the user entered "John Doe"
into the combo box, the NotInList event fires and as I mentioned the
information is inserted into the appropriate text boxes, but when the user
saves the data and closes the form they get the following message box:

"The text you entered isn't an item in the list.
Select an item from the list, or enter text that matches one of the listed
items."

When the user clicks the corresponding OK button the combo box list opens
and the new data is shown in the list (not necessarily highlighted, but it is
in the list). I am not sure why the combo box isn't having the new Contact
information displayed in this circumstance. It would appear that it is
comparing the First Name to the Last Name and getting a mismatch, but what
doesn't make sense is why it can find the Contact when the name is already in
the list, but not when it has just been added.

I have enclosed the NotInList code in hopes someone might be able to point
out what I am missing. Thanks.


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

Dim db As Database
Dim rs As Recordset
Dim lngContactID As Long
Dim strLast As String
Dim strFirst As String
Dim varContact As Variant

If InStr(1, NewData, ",") > 0 Then
'Comma Last, First Name
strLast = ParseLast(NewData)
strFirst = ParseFirst(NewData)
ElseIf InStr(1, NewData, " ") > 0 Then
strLast = RParse(NewData, , " ")
strFirst = Parse(NewData, , " ")
Else
' no comma First Last Name
strLast = NewData
End If

strLast = SetUpper(strLast)
strFirst = SetUpper(strFirst)

'dlookup against table and set name values
strsearch = "[lastname] = " & "'" & strLast & "' and " & "[firstname]= "
& "'" & strFirst & "'"
varContact = DLookup("[ContactID]", "tblContact", strsearch)

If IsNull(varContact) Then ' not in list either way

If vbYes = MsgBox("'" & NewData & "' is not a current Contact." &
vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, "Add Contact") Then
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("SELECT * FROM [tblContact] WHERE 1=2;")

With rs
.AddNew
![LastName] = strLast
![FirstName] = strFirst
lngContactID = ![ContactID]
![ContactType] = [ContactType]
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.OpenForm "frmContact", , , "[ContactID]=" & lngContactID,
acFormEdit, acDialog, 1
Response = acDataErrAdded
Me.cboContactID = varContact
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
Me.cboContactID = varContact
Exit Sub
End If
Me.cboContactID.Value = lngContactID
NewData = Me.cboContactID.Text
End Sub

--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

 

[Klingon Coder]

Hi,

Thanks for your reply. I tried both of your suggestions, and neither ended
up with any change in my previous results. Any other ideas?

--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

Coder,

This line is causing the notinlist event to fire again.

Me.cboContactID = varContact

What happens if you leave it out? If the value entered the first time is
the same as in the table then you shouldn't need to set the combo again. I
guess your double formatting thing could make this necessary. In that case,
you need to stop the recursion by setting a variable to true and check for
it before running notinlist like this:

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

if not blnRecurse then
blnRecurse = true
'Your Code Here.....
blnRecurse = false
End if

End Sub

HTH,
Josh

Hi,
I am hoping one of you gurus may be able to help me out.
I have a combo box on a Task form that brings back the ContactID from the
Contact table, but displays a concatenation of the last name a comma and the
first name (i.e. Doe, John). The user is able to enter the Contact name as
either "John Doe" or "Doe, John". There is code in place that will take the
user's entered data and parse the last name and the first name regardless of
how they entered it. A comparison of the last name and first name is then
made against those in the Contact table and if the user's information matches
the proper name is selected. The only difference in the behaviour is if they
entered Doe, John, the list jumps to the first name that matches the text
being typed, i.e. typing the D will bring the user to the first Name
beginning with D etc. If they enter John Doe when they attempt to leave the
combo box the name Doe, John will be highlighted with the list part of the
combo box being open. As near as I can figure out, this would be the normal
way for it to behave and I don't have any issue with the user having to tab
twice to get out of the combo box. The situation I am having trouble with is
when the contact is not in the list.

I have the combo box Limit To List property set to yes and if a user enters
a value that isn't in the list a message box opens asking if they want to add
the contact to the list. On an affirmative reply, the Contact form opens and
inserts the names that they had typed on the Task form into their respective
text boxes. Everything works flawlessly as far as triggering the NotInList
event, the Contact form opens and the proper fields are filled based on the
information that was passed from the Task form. The user can save the record
and close the form, but here is where things get hairy. If the user entered
the Contact name as "Doe, John" on the Task form, then the new Contact
information is brought back and fills the combo box as it should and they can
proceed with the rest of their data. However, if the user entered "John Doe"
into the combo box, the NotInList event fires and as I mentioned the
information is inserted into the appropriate text boxes, but when the user
saves the data and closes the form they get the following message box:

"The text you entered isn't an item in the list.
Select an item from the list, or enter text that matches one of the listed
items."

When the user clicks the corresponding OK button the combo box list opens
and the new data is shown in the list (not necessarily highlighted, but it is
in the list). I am not sure why the combo box isn't having the new Contact
information displayed in this circumstance. It would appear that it is
comparing the First Name to the Last Name and getting a mismatch, but what
doesn't make sense is why it can find the Contact when the name is already in
the list, but not when it has just been added.

I have enclosed the NotInList code in hopes someone might be able to point
out what I am missing. Thanks.


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

Dim db As Database
Dim rs As Recordset
Dim lngContactID As Long
Dim strLast As String
Dim strFirst As String
Dim varContact As Variant

If InStr(1, NewData, ",") > 0 Then
'Comma Last, First Name
strLast = ParseLast(NewData)
strFirst = ParseFirst(NewData)
ElseIf InStr(1, NewData, " ") > 0 Then
strLast = RParse(NewData, , " ")
strFirst = Parse(NewData, , " ")
Else
' no comma First Last Name
strLast = NewData
End If

strLast = SetUpper(strLast)
strFirst = SetUpper(strFirst)

'dlookup against table and set name values
strsearch = "[lastname] = " & "'" & strLast & "' and " & "[firstname]= "
& "'" & strFirst & "'"
varContact = DLookup("[ContactID]", "tblContact", strsearch)

If IsNull(varContact) Then ' not in list either way

If vbYes = MsgBox("'" & NewData & "' is not a current Contact." &
vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, "Add Contact") Then
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("SELECT * FROM [tblContact] WHERE 1=2;")

With rs
.AddNew
![LastName] = strLast
![FirstName] = strFirst
lngContactID = ![ContactID]
![ContactType] = [ContactType]
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.OpenForm "frmContact", , , "[ContactID]=" & lngContactID,
acFormEdit, acDialog, 1
Response = acDataErrAdded
Me.cboContactID = varContact
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
Me.cboContactID = varContact
Exit Sub
End If
Me.cboContactID.Value = lngContactID
NewData = Me.cboContactID.Text
End Sub

--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

 

[Joshua A. Booker]

Coder,

Please post your new code so we can see how you inserted the recursion
check.

Also, make sure blnRecurse is a module level variable not a procedure level.

Don't use this in the sub:

dim blnrecurse as boolean

Instead, use this a the top of the module (after Option Explicit)

public blnRecurse as boolean

HTH,
Josh

Hi,

Thanks for your reply. I tried both of your suggestions, and neither ended
up with any change in my previous results. Any other ideas?

--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

Coder,

This line is causing the notinlist event to fire again.

Me.cboContactID = varContact

What happens if you leave it out? If the value entered the first time is
the same as in the table then you shouldn't need to set the combo again. I
guess your double formatting thing could make this necessary. In that case,
you need to stop the recursion by setting a variable to true and check for
it before running notinlist like this:

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

if not blnRecurse then
blnRecurse = true
'Your Code Here.....
blnRecurse = false
End if

End Sub

HTH,
Josh

Hi,
I am hoping one of you gurus may be able to help me out.
I have a combo box on a Task form that brings back the ContactID from the
Contact table, but displays a concatenation of the last name a comma

and
the

first name (i.e. Doe, John). The user is able to enter the Contact

name
as

either "John Doe" or "Doe, John". There is code in place that will

take
the

user's entered data and parse the last name and the first name

regardless
of

how they entered it. A comparison of the last name and first name is then
made against those in the Contact table and if the user's information matches
the proper name is selected. The only difference in the behaviour is

if
they

entered Doe, John, the list jumps to the first name that matches the text
being typed, i.e. typing the D will bring the user to the first Name
beginning with D etc. If they enter John Doe when they attempt to

leave
the

combo box the name Doe, John will be highlighted with the list part of the
combo box being open. As near as I can figure out, this would be the normal
way for it to behave and I don't have any issue with the user having

to
tab

twice to get out of the combo box. The situation I am having trouble

with
is

when the contact is not in the list.

I have the combo box Limit To List property set to yes and if a user enters
a value that isn't in the list a message box opens asking if they want

to
add

the contact to the list. On an affirmative reply, the Contact form

opens
and

inserts the names that they had typed on the Task form into their respective
text boxes. Everything works flawlessly as far as triggering the NotInList
event, the Contact form opens and the proper fields are filled based

on
the

information that was passed from the Task form. The user can save the record
and close the form, but here is where things get hairy. If the user entered
the Contact name as "Doe, John" on the Task form, then the new Contact
information is brought back and fills the combo box as it should and

they
can

proceed with the rest of their data. However, if the user entered

"John
Doe"

into the combo box, the NotInList event fires and as I mentioned the
information is inserted into the appropriate text boxes, but when the user
saves the data and closes the form they get the following message box:

"The text you entered isn't an item in the list.
Select an item from the list, or enter text that matches one of the listed
items."

When the user clicks the corresponding OK button the combo box list opens
and the new data is shown in the list (not necessarily highlighted,

but it
is

in the list). I am not sure why the combo box isn't having the new Contact
information displayed in this circumstance. It would appear that it is
comparing the First Name to the Last Name and getting a mismatch, but what
doesn't make sense is why it can find the Contact when the name is

already
in

the list, but not when it has just been added.

I have enclosed the NotInList code in hopes someone might be able to point
out what I am missing. Thanks.


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

Dim db As Database
Dim rs As Recordset
Dim lngContactID As Long
Dim strLast As String
Dim strFirst As String
Dim varContact As Variant

If InStr(1, NewData, ",") > 0 Then
'Comma Last, First Name
strLast = ParseLast(NewData)
strFirst = ParseFirst(NewData)
ElseIf InStr(1, NewData, " ") > 0 Then
strLast = RParse(NewData, , " ")
strFirst = Parse(NewData, , " ")
Else
' no comma First Last Name
strLast = NewData
End If

strLast = SetUpper(strLast)
strFirst = SetUpper(strFirst)

'dlookup against table and set name values
strsearch = "[lastname] = " & "'" & strLast & "' and " &

"[firstname]=
"

& "'" & strFirst & "'"
varContact = DLookup("[ContactID]", "tblContact", strsearch)

If IsNull(varContact) Then ' not in list either way

If vbYes = MsgBox("'" & NewData & "' is not a current Contact." &
vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, "Add

Contact")
Then

Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("SELECT * FROM [tblContact]

WHERE
1=2;")

With rs
.AddNew
![LastName] = strLast
![FirstName] = strFirst
lngContactID = ![ContactID]
![ContactType] = [ContactType]
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.OpenForm "frmContact", , , "[ContactID]=" & lngContactID,
acFormEdit, acDialog, 1
Response = acDataErrAdded
Me.cboContactID = varContact
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
Me.cboContactID = varContact
Exit Sub
End If
Me.cboContactID.Value = lngContactID
NewData = Me.cboContactID.Text
End Sub

--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

 

[Klingon Coder]

Hello again,

As per your suggestion I placed the following declaration in the modGlobals
module

Public blnRecurse As Boolean

And on the form open event for the Task form I inserted the following line
of code:

blnRecurse = True

The rest of my code is as follows:

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

Dim db As Database
Dim rs As Recordset
Dim lngContactID As Long
Dim strLast As String
Dim strFirst As String
Dim varContact As Variant

If Not blnRecurse Then
blnRecurse = True

If InStr(1, NewData, ",") > 0 Then
'Comma Last, First Name
strLast = ParseLast(NewData)
strFirst = ParseFirst(NewData)
ElseIf InStr(1, NewData, " ") > 0 Then
strLast = RParse(NewData, , " ")
strFirst = Parse(NewData, , " ")
Else
' no comma First Last Name
strLast = NewData
End If
strLast = SetUpper(strLast)
strFirst = SetUpper(strFirst)

'dlookup against table and set name values
fred = "[lastname] = " & "'" & strLast & "' and " & "[firstname]= "
& "'" & strFirst & "'"

strsearch = "[lastname] = " & "'" & strLast & "' and " &
"[firstname]= " & "'" & strFirst & "'"
varContact = DLookup("[ContactID]", "tblContact", strsearch)
If IsNull(varContact) Then ' not in list either way

If vbYes = MsgBox("'" & NewData & "' is not a current Contact." & vbCrLf &
"Do you wish to add it?", vbQuestion + vbYesNo, "Add Contact.") Then
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("SELECT * FROM [tblContact] WHERE
1=2;")

With rs
.AddNew
![LastName] = strLast
![FirstName] = strFirst
lngContactID = ![ContactID]
![ContactType] = [ContactType]
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.OpenForm "frmContact", , , "[ContactID]=" &
lngContactID, acFormEdit, acDialog, 1
Response = acDataErrAdded
Me.cboContactID = varContact
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
'Response = acDataErrAdded
'lngContactID = varContact
'Me.cboContactID = varContact
Exit Sub

End If
End If
blnRecurse = False

End Sub

Now when I attempt to add a contact, the NotInList event doesn't fire at
all. Instead I get the message "The text you entered isn't in the list...."
If

When I put a breakpoint at the start of the code and step through it, it
will trip the NotInList event, but the results are the same..."Not in list"
message box. Actually I just checked on something and the NotInList will
kick in, but not on the first attempt. When it does work, it allows me to
add the Contact on the Contact form, but when I close the form, I get the
same message that "The text you entered..."

Any ideas?

Thanks in advance

--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

Coder,

Please post your new code so we can see how you inserted the recursion
check.

Also, make sure blnRecurse is a module level variable not a procedure level.

Don't use this in the sub:

dim blnrecurse as boolean

Instead, use this a the top of the module (after Option Explicit)

public blnRecurse as boolean

HTH,
Josh

Hi,

Thanks for your reply. I tried both of your suggestions, and neither ended
up with any change in my previous results. Any other ideas?

--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

Coder,

This line is causing the notinlist event to fire again.

Me.cboContactID = varContact

What happens if you leave it out? If the value entered the first time is
the same as in the table then you shouldn't need to set the combo again. I
guess your double formatting thing could make this necessary. In that case,
you need to stop the recursion by setting a variable to true and check for
it before running notinlist like this:

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

if not blnRecurse then
blnRecurse = true
'Your Code Here.....
blnRecurse = false
End if

End Sub

HTH,
Josh


Hi,
I am hoping one of you gurus may be able to help me out.
I have a combo box on a Task form that brings back the ContactID from the
Contact table, but displays a concatenation of the last name a comma and
the
first name (i.e. Doe, John). The user is able to enter the Contact name
as
either "John Doe" or "Doe, John". There is code in place that will take
the
user's entered data and parse the last name and the first name regardless
of
how they entered it. A comparison of the last name and first name is then
made against those in the Contact table and if the user's information
matches
the proper name is selected. The only difference in the behaviour is if
they
entered Doe, John, the list jumps to the first name that matches the text
being typed, i.e. typing the D will bring the user to the first Name
beginning with D etc. If they enter John Doe when they attempt to leave
the
combo box the name Doe, John will be highlighted with the list part of the
combo box being open. As near as I can figure out, this would be the
normal
way for it to behave and I don't have any issue with the user having to
tab
twice to get out of the combo box. The situation I am having trouble with
is
when the contact is not in the list.

I have the combo box Limit To List property set to yes and if a user
enters
a value that isn't in the list a message box opens asking if they want to
add
the contact to the list. On an affirmative reply, the Contact form opens
and
inserts the names that they had typed on the Task form into their
respective
text boxes. Everything works flawlessly as far as triggering the
NotInList
event, the Contact form opens and the proper fields are filled based on
the
information that was passed from the Task form. The user can save the
record
and close the form, but here is where things get hairy. If the user
entered
the Contact name as "Doe, John" on the Task form, then the new Contact
information is brought back and fills the combo box as it should and they
can
proceed with the rest of their data. However, if the user entered "John
Doe"
into the combo box, the NotInList event fires and as I mentioned the
information is inserted into the appropriate text boxes, but when the user
saves the data and closes the form they get the following message box:

"The text you entered isn't an item in the list.
Select an item from the list, or enter text that matches one of the listed
items."

When the user clicks the corresponding OK button the combo box list opens
and the new data is shown in the list (not necessarily highlighted, but it
is
in the list). I am not sure why the combo box isn't having the new
Contact
information displayed in this circumstance. It would appear that it is
comparing the First Name to the Last Name and getting a mismatch, but what
doesn't make sense is why it can find the Contact when the name is already
in
the list, but not when it has just been added.

I have enclosed the NotInList code in hopes someone might be able to point
out what I am missing. Thanks.


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

Dim db As Database
Dim rs As Recordset
Dim lngContactID As Long
Dim strLast As String
Dim strFirst As String
Dim varContact As Variant

If InStr(1, NewData, ",") > 0 Then
'Comma Last, First Name
strLast = ParseLast(NewData)
strFirst = ParseFirst(NewData)
ElseIf InStr(1, NewData, " ") > 0 Then
strLast = RParse(NewData, , " ")
strFirst = Parse(NewData, , " ")
Else
' no comma First Last Name
strLast = NewData
End If

strLast = SetUpper(strLast)
strFirst = SetUpper(strFirst)

'dlookup against table and set name values
strsearch = "[lastname] = " & "'" & strLast & "' and " & "[firstname]=
"
& "'" & strFirst & "'"
varContact = DLookup("[ContactID]", "tblContact", strsearch)

If IsNull(varContact) Then ' not in list either way

If vbYes = MsgBox("'" & NewData & "' is not a current Contact." &
vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, "Add Contact")
Then
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("SELECT * FROM [tblContact] WHERE
1=2;")

With rs
.AddNew
![LastName] = strLast
![FirstName] = strFirst
lngContactID = ![ContactID]
![ContactType] = [ContactType]
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.OpenForm "frmContact", , , "[ContactID]=" &
lngContactID,
acFormEdit, acDialog, 1
Response = acDataErrAdded
Me.cboContactID = varContact
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
Me.cboContactID = varContact
Exit Sub
End If
Me.cboContactID.Value = lngContactID
NewData = Me.cboContactID.Text
End Sub

--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

 

[Joshua A. Booker]

Coder,

First remove the bnlRecurse = true in the form_open event then try this:

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

Dim db As Database
Dim rs As Recordset
Dim lngContactID As Long
Dim strLast As String
Dim strFirst As String
Dim varContact As Variant

If Not blnRecurse Then
blnRecurse = True

If InStr(1, NewData, ",") > 0 Then
'Comma Last, First Name
strLast = ParseLast(NewData)
strFirst = ParseFirst(NewData)
ElseIf InStr(1, NewData, " ") > 0 Then
strLast = RParse(NewData, , " ")
strFirst = Parse(NewData, , " ")
Else
' no comma First Last Name
strLast = NewData
End If
strLast = SetUpper(strLast)
strFirst = SetUpper(strFirst)

'dlookup against table and set name values
fred = "[lastname] = " & "'" & strLast & "' and " & "[firstname]= "
& "'" & strFirst & "'"
strsearch = "[lastname] = " & "'" & strLast & "' and " &
"[firstname]= " & "'" & strFirst & "'"
varContact = DLookup("[ContactID]", "tblContact", strsearch)

If IsNull(varContact) Then ' not in list either way

If vbYes = MsgBox("'" & NewData & "' is not a current Contact."
& vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, "Add Contact.")
Then
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("SELECT * FROM [tblContact] WHERE
1=2;")

With rs
.AddNew
![LastName] = strLast
![FirstName] = strFirst
lngContactID = ![ContactID]
![ContactType] = [ContactType]
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.OpenForm "frmContact", , , "[ContactID]=" &
lngContactID, acFormEdit, acDialog, 1
Response = acDataErrAdded
Me.cboContactID = varContact
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
'Response = acDataErrAdded
'lngContactID = varContact
'Me.cboContactID = varContact
Exit Sub
End If
blnRecurse = False
End If

End Sub

The point is that you want to prevent the NotInList code from running only
if the NotInList is already running. So you don't want to set the
blnRecurse when you open the form. Instead, you want it to be true while
your code runs and set it to false again after the code is run.

HTH,
Josh

Hello again,

As per your suggestion I placed the following declaration in the modGlobals
module

Public blnRecurse As Boolean

And on the form open event for the Task form I inserted the following line
of code:

blnRecurse = True

The rest of my code is as follows:

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

Dim db As Database
Dim rs As Recordset
Dim lngContactID As Long
Dim strLast As String
Dim strFirst As String
Dim varContact As Variant

If Not blnRecurse Then
blnRecurse = True

If InStr(1, NewData, ",") > 0 Then
'Comma Last, First Name
strLast = ParseLast(NewData)
strFirst = ParseFirst(NewData)
ElseIf InStr(1, NewData, " ") > 0 Then
strLast = RParse(NewData, , " ")
strFirst = Parse(NewData, , " ")
Else
' no comma First Last Name
strLast = NewData
End If
strLast = SetUpper(strLast)
strFirst = SetUpper(strFirst)

'dlookup against table and set name values
fred = "[lastname] = " & "'" & strLast & "' and " & "[firstname]= "
& "'" & strFirst & "'"

strsearch = "[lastname] = " & "'" & strLast & "' and " &
"[firstname]= " & "'" & strFirst & "'"
varContact = DLookup("[ContactID]", "tblContact", strsearch)
If IsNull(varContact) Then ' not in list either way

If vbYes = MsgBox("'" & NewData & "' is not a current Contact." & vbCrLf &
"Do you wish to add it?", vbQuestion + vbYesNo, "Add Contact.") Then
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("SELECT * FROM [tblContact] WHERE
1=2;")

With rs
.AddNew
![LastName] = strLast
![FirstName] = strFirst
lngContactID = ![ContactID]
![ContactType] = [ContactType]
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.OpenForm "frmContact", , , "[ContactID]=" &
lngContactID, acFormEdit, acDialog, 1
Response = acDataErrAdded
Me.cboContactID = varContact
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
'Response = acDataErrAdded
'lngContactID = varContact
'Me.cboContactID = varContact
Exit Sub

End If
End If
blnRecurse = False

End Sub

Now when I attempt to add a contact, the NotInList event doesn't fire at
all. Instead I get the message "The text you entered isn't in the list...."
If

When I put a breakpoint at the start of the code and step through it, it
will trip the NotInList event, but the results are the same..."Not in list"
message box. Actually I just checked on something and the NotInList will
kick in, but not on the first attempt. When it does work, it allows me to
add the Contact on the Contact form, but when I close the form, I get the
same message that "The text you entered..."

Any ideas?

Thanks in advance

--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

Coder,

Please post your new code so we can see how you inserted the recursion
check.

Also, make sure blnRecurse is a module level variable not a procedure level.

Don't use this in the sub:

dim blnrecurse as boolean

Instead, use this a the top of the module (after Option Explicit)

public blnRecurse as boolean

HTH,
Josh

Hi,

Thanks for your reply. I tried both of your suggestions, and neither ended
up with any change in my previous results. Any other ideas?

--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"


:

Coder,

This line is causing the notinlist event to fire again.

Me.cboContactID = varContact

What happens if you leave it out? If the value entered the first

time
is

the same as in the table then you shouldn't need to set the combo

again.
I

guess your double formatting thing could make this necessary. In

that
case,

you need to stop the recursion by setting a variable to true and

check
for

it before running notinlist like this:

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

if not blnRecurse then
blnRecurse = true
'Your Code Here.....
blnRecurse = false
End if

End Sub

HTH,
Josh


Hi,
I am hoping one of you gurus may be able to help me out.
I have a combo box on a Task form that brings back the ContactID

from
the

Contact table, but displays a concatenation of the last name a

comma
and

the
first name (i.e. Doe, John). The user is able to enter the

Contact
name

as
either "John Doe" or "Doe, John". There is code in place that

will
take

the
user's entered data and parse the last name and the first name regardless
of
how they entered it. A comparison of the last name and first name

is
then

made against those in the Contact table and if the user's information
matches
the proper name is selected. The only difference in the behaviour

is
if

they
entered Doe, John, the list jumps to the first name that matches

the
text

being typed, i.e. typing the D will bring the user to the first Name
beginning with D etc. If they enter John Doe when they attempt to leave
the
combo box the name Doe, John will be highlighted with the list

part of
the

combo box being open. As near as I can figure out, this would be the
normal
way for it to behave and I don't have any issue with the user

having
to

tab
twice to get out of the combo box. The situation I am having

trouble
with

is
when the contact is not in the list.

I have the combo box Limit To List property set to yes and if a user
enters
a value that isn't in the list a message box opens asking if they

want
to

add
the contact to the list. On an affirmative reply, the Contact

form
opens

and
inserts the names that they had typed on the Task form into their
respective
text boxes. Everything works flawlessly as far as triggering the
NotInList
event, the Contact form opens and the proper fields are filled

based
on

the
information that was passed from the Task form. The user can save the
record
and close the form, but here is where things get hairy. If the user
entered
the Contact name as "Doe, John" on the Task form, then the new Contact
information is brought back and fills the combo box as it should

and
they

can
proceed with the rest of their data. However, if the user entered "John
Doe"
into the combo box, the NotInList event fires and as I mentioned the
information is inserted into the appropriate text boxes, but when

the
user

saves the data and closes the form they get the following message box:

"The text you entered isn't an item in the list.
Select an item from the list, or enter text that matches one of

the
listed

items."

When the user clicks the corresponding OK button the combo box

list
opens

and the new data is shown in the list (not necessarily

highlighted,
but it

is
in the list). I am not sure why the combo box isn't having the new
Contact
information displayed in this circumstance. It would appear that

it
is

comparing the First Name to the Last Name and getting a mismatch,

but
what

doesn't make sense is why it can find the Contact when the name is already
in
the list, but not when it has just been added.

I have enclosed the NotInList code in hopes someone might be able

to
point

out what I am missing. Thanks.


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

Dim db As Database
Dim rs As Recordset
Dim lngContactID As Long
Dim strLast As String
Dim strFirst As String
Dim varContact As Variant

If InStr(1, NewData, ",") > 0 Then
'Comma Last, First Name
strLast = ParseLast(NewData)
strFirst = ParseFirst(NewData)
ElseIf InStr(1, NewData, " ") > 0 Then
strLast = RParse(NewData, , " ")
strFirst = Parse(NewData, , " ")
Else
' no comma First Last Name
strLast = NewData
End If

strLast = SetUpper(strLast)
strFirst = SetUpper(strFirst)

'dlookup against table and set name values
strsearch = "[lastname] = " & "'" & strLast & "' and " & "[firstname]=
"
& "'" & strFirst & "'"
varContact = DLookup("[ContactID]", "tblContact", strsearch)

If IsNull(varContact) Then ' not in list either way

If vbYes = MsgBox("'" & NewData & "' is not a current Contact." &
vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, "Add Contact")
Then
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("SELECT * FROM [tblContact] WHERE
1=2;")

With rs
.AddNew
![LastName] = strLast
![FirstName] = strFirst
lngContactID = ![ContactID]
![ContactType] = [ContactType]
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.OpenForm "frmContact", , , "[ContactID]=" &
lngContactID,
acFormEdit, acDialog, 1
Response = acDataErrAdded
Me.cboContactID = varContact
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
Me.cboContactID = varContact
Exit Sub
End If
Me.cboContactID.Value = lngContactID
NewData = Me.cboContactID.Text
End Sub

--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think

and
you

shall be dinner"

 

[Joshua A. Booker]

And you should probably remove the Exit Sub line too.

Josh

Coder,

First remove the bnlRecurse = true in the form_open event then try this:

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

Dim db As Database
Dim rs As Recordset
Dim lngContactID As Long
Dim strLast As String
Dim strFirst As String
Dim varContact As Variant

If Not blnRecurse Then
blnRecurse = True

If InStr(1, NewData, ",") > 0 Then
'Comma Last, First Name
strLast = ParseLast(NewData)
strFirst = ParseFirst(NewData)
ElseIf InStr(1, NewData, " ") > 0 Then
strLast = RParse(NewData, , " ")
strFirst = Parse(NewData, , " ")
Else
' no comma First Last Name
strLast = NewData
End If
strLast = SetUpper(strLast)
strFirst = SetUpper(strFirst)

'dlookup against table and set name values
fred = "[lastname] = " & "'" & strLast & "' and " & "[firstname]= "
& "'" & strFirst & "'"
strsearch = "[lastname] = " & "'" & strLast & "' and " &
"[firstname]= " & "'" & strFirst & "'"
varContact = DLookup("[ContactID]", "tblContact", strsearch)

If IsNull(varContact) Then ' not in list either way

If vbYes = MsgBox("'" & NewData & "' is not a current Contact."
& vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, "Add Contact.")
Then
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("SELECT * FROM [tblContact] WHERE
1=2;")

With rs
.AddNew
![LastName] = strLast
![FirstName] = strFirst
lngContactID = ![ContactID]
![ContactType] = [ContactType]
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.OpenForm "frmContact", , , "[ContactID]=" &
lngContactID, acFormEdit, acDialog, 1
Response = acDataErrAdded
Me.cboContactID = varContact
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
'Response = acDataErrAdded
'lngContactID = varContact
'Me.cboContactID = varContact
Exit Sub
End If
blnRecurse = False
End If

End Sub

The point is that you want to prevent the NotInList code from running only
if the NotInList is already running. So you don't want to set the
blnRecurse when you open the form. Instead, you want it to be true while
your code runs and set it to false again after the code is run.

HTH,
Josh

Hello again,

As per your suggestion I placed the following declaration in the modGlobals
module

Public blnRecurse As Boolean

And on the form open event for the Task form I inserted the following line
of code:

blnRecurse = True

The rest of my code is as follows:

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

Dim db As Database
Dim rs As Recordset
Dim lngContactID As Long
Dim strLast As String
Dim strFirst As String
Dim varContact As Variant

If Not blnRecurse Then
blnRecurse = True

If InStr(1, NewData, ",") > 0 Then
'Comma Last, First Name
strLast = ParseLast(NewData)
strFirst = ParseFirst(NewData)
ElseIf InStr(1, NewData, " ") > 0 Then
strLast = RParse(NewData, , " ")
strFirst = Parse(NewData, , " ")
Else
' no comma First Last Name
strLast = NewData
End If
strLast = SetUpper(strLast)
strFirst = SetUpper(strFirst)

'dlookup against table and set name values
fred = "[lastname] = " & "'" & strLast & "' and " &

"[firstname]=

"
& "'" & strFirst & "'"

strsearch = "[lastname] = " & "'" & strLast & "' and " &
"[firstname]= " & "'" & strFirst & "'"
varContact = DLookup("[ContactID]", "tblContact", strsearch)
If IsNull(varContact) Then ' not in list either way

If vbYes = MsgBox("'" & NewData & "' is not a current Contact." & vbCrLf &
"Do you wish to add it?", vbQuestion + vbYesNo, "Add Contact.") Then
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("SELECT * FROM [tblContact] WHERE
1=2;")

With rs
.AddNew
![LastName] = strLast
![FirstName] = strFirst
lngContactID = ![ContactID]
![ContactType] = [ContactType]
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.OpenForm "frmContact", , , "[ContactID]=" &
lngContactID, acFormEdit, acDialog, 1
Response = acDataErrAdded
Me.cboContactID = varContact
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
'Response = acDataErrAdded
'lngContactID = varContact
'Me.cboContactID = varContact
Exit Sub

End If
End If
blnRecurse = False

End Sub

Now when I attempt to add a contact, the NotInList event doesn't fire at
all. Instead I get the message "The text you entered isn't in the list...."
If

When I put a breakpoint at the start of the code and step through it, it
will trip the NotInList event, but the results are the same..."Not in list"
message box. Actually I just checked on something and the NotInList will
kick in, but not on the first attempt. When it does work, it allows me to
add the Contact on the Contact form, but when I close the form, I get the
same message that "The text you entered..."

Any ideas?

Thanks in advance

--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

Coder,

Please post your new code so we can see how you inserted the recursion
check.

Also, make sure blnRecurse is a module level variable not a procedure level.

Don't use this in the sub:

dim blnrecurse as boolean

Instead, use this a the top of the module (after Option Explicit)

public blnRecurse as boolean

HTH,
Josh

Hi,

Thanks for your reply. I tried both of your suggestions, and neither
ended
up with any change in my previous results. Any other ideas?

--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"


:

Coder,

This line is causing the notinlist event to fire again.

Me.cboContactID = varContact

What happens if you leave it out? If the value entered the first time
is
the same as in the table then you shouldn't need to set the combo again.
I
guess your double formatting thing could make this necessary. In that
case,
you need to stop the recursion by setting a variable to true and check
for
it before running notinlist like this:

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

if not blnRecurse then
blnRecurse = true
'Your Code Here.....
blnRecurse = false
End if

End Sub

HTH,
Josh


message
Hi,
I am hoping one of you gurus may be able to help me out.
I have a combo box on a Task form that brings back the ContactID from
the
Contact table, but displays a concatenation of the last name a comma
and
the
first name (i.e. Doe, John). The user is able to enter the Contact
name
as
either "John Doe" or "Doe, John". There is code in place that will
take
the
user's entered data and parse the last name and the first name
regardless
of
how they entered it. A comparison of the last name and first

name

is

behaviour

is

be

the

they

want

save

the

when

the

message

box:

that

it

mismatch,

but

able

to

point
out what I am missing. Thanks.


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

Dim db As Database
Dim rs As Recordset
Dim lngContactID As Long
Dim strLast As String
Dim strFirst As String
Dim varContact As Variant

If InStr(1, NewData, ",") > 0 Then
'Comma Last, First Name
strLast = ParseLast(NewData)
strFirst = ParseFirst(NewData)
ElseIf InStr(1, NewData, " ") > 0 Then
strLast = RParse(NewData, , " ")
strFirst = Parse(NewData, , " ")
Else
' no comma First Last Name
strLast = NewData
End If

strLast = SetUpper(strLast)
strFirst = SetUpper(strFirst)

'dlookup against table and set name values
strsearch = "[lastname] = " & "'" & strLast & "' and " &
"[firstname]=
"
& "'" & strFirst & "'"
varContact = DLookup("[ContactID]", "tblContact", strsearch)

If IsNull(varContact) Then ' not in list either way

If vbYes = MsgBox("'" & NewData & "' is not a current
Contact." &
vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, "Add
Contact")
Then
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("SELECT * FROM [tblContact]
WHERE
1=2;")

With rs
.AddNew
![LastName] = strLast
![FirstName] = strFirst
lngContactID = ![ContactID]
![ContactType] = [ContactType]
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.OpenForm "frmContact", , , "[ContactID]=" &
lngContactID,
acFormEdit, acDialog, 1
Response = acDataErrAdded
Me.cboContactID = varContact
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
Me.cboContactID = varContact
Exit Sub
End If
Me.cboContactID.Value = lngContactID
NewData = Me.cboContactID.Text
End Sub

--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and
you
shall be dinner"

 

[Klingon Coder]

Hi Joshua,

I tried your suggestions and understandably the NotInList event is working
properly when the user leaves the combo box, however, I am still getting the
same result after adding the new contact. The name appears in the list, but
is not selected, therefore causing the same message about the text entered
not being in the list. Would it be helpful if I posted the code used for
parsing the first and last names, or does the problem lie with the N.I.L.
code?


--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

And you should probably remove the Exit Sub line too.

Josh

Coder,

First remove the bnlRecurse = true in the form_open event then try this:

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

Dim db As Database
Dim rs As Recordset
Dim lngContactID As Long
Dim strLast As String
Dim strFirst As String
Dim varContact As Variant

If Not blnRecurse Then
blnRecurse = True

If InStr(1, NewData, ",") > 0 Then
'Comma Last, First Name
strLast = ParseLast(NewData)
strFirst = ParseFirst(NewData)
ElseIf InStr(1, NewData, " ") > 0 Then
strLast = RParse(NewData, , " ")
strFirst = Parse(NewData, , " ")
Else
' no comma First Last Name
strLast = NewData
End If
strLast = SetUpper(strLast)
strFirst = SetUpper(strFirst)

'dlookup against table and set name values
fred = "[lastname] = " & "'" & strLast & "' and " & "[firstname]= "
& "'" & strFirst & "'"
strsearch = "[lastname] = " & "'" & strLast & "' and " &
"[firstname]= " & "'" & strFirst & "'"
varContact = DLookup("[ContactID]", "tblContact", strsearch)

If IsNull(varContact) Then ' not in list either way

If vbYes = MsgBox("'" & NewData & "' is not a current Contact."
& vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, "Add Contact.")
Then
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("SELECT * FROM [tblContact] WHERE
1=2;")

With rs
.AddNew
![LastName] = strLast
![FirstName] = strFirst
lngContactID = ![ContactID]
![ContactType] = [ContactType]
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.OpenForm "frmContact", , , "[ContactID]=" &
lngContactID, acFormEdit, acDialog, 1
Response = acDataErrAdded
Me.cboContactID = varContact
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
'Response = acDataErrAdded
'lngContactID = varContact
'Me.cboContactID = varContact
Exit Sub
End If
blnRecurse = False
End If

End Sub

The point is that you want to prevent the NotInList code from running only
if the NotInList is already running. So you don't want to set the
blnRecurse when you open the form. Instead, you want it to be true while
your code runs and set it to false again after the code is run.

HTH,
Josh

 

[Joshua A. Booker]

Coder,

Try changing this line:

Me.cboContactID = varContact

to this:

Me.cboContactID = lngContactID

I can't see where you set varcontact so I think it's still null after the
dlookup.

Also, just so I know, which format is the combo in? Comma or space?

HTH,
Josh

Hi Joshua,

I tried your suggestions and understandably the NotInList event is working
properly when the user leaves the combo box, however, I am still getting the
same result after adding the new contact. The name appears in the list, but
is not selected, therefore causing the same message about the text entered
not being in the list. Would it be helpful if I posted the code used for
parsing the first and last names, or does the problem lie with the N.I.L.
code?


--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

And you should probably remove the Exit Sub line too.

Josh

Coder,

First remove the bnlRecurse = true in the form_open event then try this:

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

Dim db As Database
Dim rs As Recordset
Dim lngContactID As Long
Dim strLast As String
Dim strFirst As String
Dim varContact As Variant

If Not blnRecurse Then
blnRecurse = True

If InStr(1, NewData, ",") > 0 Then
'Comma Last, First Name
strLast = ParseLast(NewData)
strFirst = ParseFirst(NewData)
ElseIf InStr(1, NewData, " ") > 0 Then
strLast = RParse(NewData, , " ")
strFirst = Parse(NewData, , " ")
Else
' no comma First Last Name
strLast = NewData
End If
strLast = SetUpper(strLast)
strFirst = SetUpper(strFirst)

'dlookup against table and set name values
fred = "[lastname] = " & "'" & strLast & "' and " &

"[firstname]=
"

& "'" & strFirst & "'"
strsearch = "[lastname] = " & "'" & strLast & "' and " &
"[firstname]= " & "'" & strFirst & "'"
varContact = DLookup("[ContactID]", "tblContact", strsearch)

If IsNull(varContact) Then ' not in list either way

If vbYes = MsgBox("'" & NewData & "' is not a current Contact."
& vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, "Add Contact.")
Then
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("SELECT * FROM [tblContact] WHERE
1=2;")

With rs
.AddNew
![LastName] = strLast
![FirstName] = strFirst
lngContactID = ![ContactID]
![ContactType] = [ContactType]
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.OpenForm "frmContact", , , "[ContactID]=" &
lngContactID, acFormEdit, acDialog, 1
Response = acDataErrAdded
Me.cboContactID = varContact
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
'Response = acDataErrAdded
'lngContactID = varContact
'Me.cboContactID = varContact
Exit Sub
End If
blnRecurse = False
End If

End Sub

The point is that you want to prevent the NotInList code from running only
if the NotInList is already running. So you don't want to set the
blnRecurse when you open the form. Instead, you want it to be true while
your code runs and set it to false again after the code is run.

HTH,
Josh

 

[Klingon Coder]

Hi Joshua,

I tried changing the code to
Me.cboContactID = lngContactID
as per your suggestion, but it had no effect. As for the format of the
combo box, I'm not quite sure what you mean, but if my guess is correct, then
the combo box is set to bring back the ContactID, but to display LastName,
FirstName

Does this answer your question?


--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

Coder,

Try changing this line:

Me.cboContactID = varContact

to this:

Me.cboContactID = lngContactID

I can't see where you set varcontact so I think it's still null after the
dlookup.

Also, just so I know, which format is the combo in? Comma or space?

HTH,
Josh

Hi Joshua,

I tried your suggestions and understandably the NotInList event is working
properly when the user leaves the combo box, however, I am still getting the
same result after adding the new contact. The name appears in the list, but
is not selected, therefore causing the same message about the text entered
not being in the list. Would it be helpful if I posted the code used for
parsing the first and last names, or does the problem lie with the N.I.L.
code?


--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

And you should probably remove the Exit Sub line too.

Josh

Coder,

First remove the bnlRecurse = true in the form_open event then try this:

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

Dim db As Database
Dim rs As Recordset
Dim lngContactID As Long
Dim strLast As String
Dim strFirst As String
Dim varContact As Variant

If Not blnRecurse Then
blnRecurse = True

If InStr(1, NewData, ",") > 0 Then
'Comma Last, First Name
strLast = ParseLast(NewData)
strFirst = ParseFirst(NewData)
ElseIf InStr(1, NewData, " ") > 0 Then
strLast = RParse(NewData, , " ")
strFirst = Parse(NewData, , " ")
Else
' no comma First Last Name
strLast = NewData
End If
strLast = SetUpper(strLast)
strFirst = SetUpper(strFirst)

'dlookup against table and set name values
fred = "[lastname] = " & "'" & strLast & "' and " & "[firstname]=
"
& "'" & strFirst & "'"
strsearch = "[lastname] = " & "'" & strLast & "' and " &
"[firstname]= " & "'" & strFirst & "'"
varContact = DLookup("[ContactID]", "tblContact", strsearch)

If IsNull(varContact) Then ' not in list either way

If vbYes = MsgBox("'" & NewData & "' is not a current
Contact."
& vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, "Add Contact.")
Then
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("SELECT * FROM [tblContact]
WHERE
1=2;")

With rs
.AddNew
![LastName] = strLast
![FirstName] = strFirst
lngContactID = ![ContactID]
![ContactType] = [ContactType]
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.OpenForm "frmContact", , , "[ContactID]=" &
lngContactID, acFormEdit, acDialog, 1
Response = acDataErrAdded
Me.cboContactID = varContact
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
'Response = acDataErrAdded
'lngContactID = varContact
'Me.cboContactID = varContact
Exit Sub
End If
blnRecurse = False
End If

End Sub

The point is that you want to prevent the NotInList code from running only
if the NotInList is already running. So you don't want to set the
blnRecurse when you open the form. Instead, you want it to be true while
your code runs and set it to false again after the code is run.

HTH,
Josh

 

[Joshua A. Booker]

Coder,

Try setting the cboContactId.Text = strLast & ", " & strFirst

If this doesn't work, then it's probably time to remove some layers of
complexity to solve this one. I'd suggest using two combos on top of each
other instead. You can have one that uses Last, First and one the uses
First Last. You can add an option group for the user to choose which format
before entering and show/hide the appropriate combo based on their choice.
This will allow you to deal with the NotInList event for each of the combos
individually and will allow you to eliminate your duplicate check in the NIL
code.

HTH,
Josh

Hi Joshua,

I tried changing the code to
Me.cboContactID = lngContactID
as per your suggestion, but it had no effect. As for the format of the
combo box, I'm not quite sure what you mean, but if my guess is correct, then
the combo box is set to bring back the ContactID, but to display LastName,
FirstName

Does this answer your question?


--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

Coder,

Try changing this line:

Me.cboContactID = varContact

to this:

Me.cboContactID = lngContactID

I can't see where you set varcontact so I think it's still null after the
dlookup.

Also, just so I know, which format is the combo in? Comma or space?

HTH,
Josh

Hi Joshua,

I tried your suggestions and understandably the NotInList event is working
properly when the user leaves the combo box, however, I am still

getting
the

same result after adding the new contact. The name appears in the

list,
but

is not selected, therefore causing the same message about the text entered
not being in the list. Would it be helpful if I posted the code used for
parsing the first and last names, or does the problem lie with the N.I.L.
code?


--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"


:

And you should probably remove the Exit Sub line too.

Josh

Coder,

First remove the bnlRecurse = true in the form_open event then try this:

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

Dim db As Database
Dim rs As Recordset
Dim lngContactID As Long
Dim strLast As String
Dim strFirst As String
Dim varContact As Variant

If Not blnRecurse Then
blnRecurse = True

If InStr(1, NewData, ",") > 0 Then
'Comma Last, First Name
strLast = ParseLast(NewData)
strFirst = ParseFirst(NewData)
ElseIf InStr(1, NewData, " ") > 0 Then
strLast = RParse(NewData, , " ")
strFirst = Parse(NewData, , " ")
Else
' no comma First Last Name
strLast = NewData
End If
strLast = SetUpper(strLast)
strFirst = SetUpper(strFirst)

'dlookup against table and set name values
fred = "[lastname] = " & "'" & strLast & "' and " & "[firstname]=
"
& "'" & strFirst & "'"
strsearch = "[lastname] = " & "'" & strLast & "' and " &
"[firstname]= " & "'" & strFirst & "'"
varContact = DLookup("[ContactID]", "tblContact", strsearch)

If IsNull(varContact) Then ' not in list either way

If vbYes = MsgBox("'" & NewData & "' is not a current
Contact."
& vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, "Add Contact.")
Then
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("SELECT * FROM [tblContact]
WHERE
1=2;")

With rs
.AddNew
![LastName] = strLast
![FirstName] = strFirst
lngContactID = ![ContactID]
![ContactType] = [ContactType]
.Update
End With
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.OpenForm "frmContact", , , "[ContactID]=" &
lngContactID, acFormEdit, acDialog, 1
Response = acDataErrAdded
Me.cboContactID = varContact
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
'Response = acDataErrAdded
'lngContactID = varContact
'Me.cboContactID = varContact
Exit Sub
End If
blnRecurse = False
End If

End Sub

The point is that you want to prevent the NotInList code from

running
only

if the NotInList is already running. So you don't want to set the
blnRecurse when you open the form. Instead, you want it to be

true
while

your code runs and set it to false again after the code is run.

HTH,
Josh