NotInList Firing Problem

[RayToddJr]

Here is the current code:

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

Static fDataChanged As Boolean
Const cFormName = "frmNEWDEFENDANT"
If fDataChanged Then
Response = acDataErrAdded
Exit Sub
End If
If MsgBox("Would you like to add a new Defendant?", vbYesNo) = vbYes Then
DoCmd.OpenForm cFormName, _
Datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
If SysCmd(acSysCmdGetObjectState, acForm, cFormName) =
acObjStateOpen Then
With Forms(cFormName)
NewData = !LastName & ", " & !FirstName & (" " +
!MiddleName) & (" " + !Suffix)
End With
DoCmd.Close acForm, cFormName
If cboDefendantsName.Value <> NewData Then
fDataChanged = True
cboDefendantsName.Value = NewData
fDataChanged = False
End If
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
End If

End Sub


The problem:

When the user enters a name in the combo box, as an example, "Smith, John
T." and it isn't in the defendantsname table, the notinlist fires correctly.
During the process, the Entry Form opens so that the user can enter the name
into the table.

If the user, changes the name in anyway, as an example, removing the period
from the "T" and the default notinlist message is constantly displayed, as in
it is repeating.

What is possibly causing this?

Thanks,

Ray.

 

[Klatuu]

You are changing the value in your code. This is causing the refire. If
you want the new name as modified to show, all you need to do is requery the
combo.

 

[Marshall Barton]

Here is the current code:

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

Static fDataChanged As Boolean
Const cFormName = "frmNEWDEFENDANT"
If fDataChanged Then
Response = acDataErrAdded
Exit Sub
End If
If MsgBox("Would you like to add a new Defendant?", vbYesNo) = vbYes Then
DoCmd.OpenForm cFormName, _
Datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
If SysCmd(acSysCmdGetObjectState, acForm, cFormName) =
acObjStateOpen Then
With Forms(cFormName)
NewData = !LastName & ", " & !FirstName & (" " +
!MiddleName) & (" " + !Suffix)
End With
DoCmd.Close acForm, cFormName
If cboDefendantsName.Value <> NewData Then
fDataChanged = True
cboDefendantsName.Value = NewData
fDataChanged = False
End If
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
End If

End Sub

When the user enters a name in the combo box, as an example, "Smith, John
T." and it isn't in the defendantsname table, the notinlist fires correctly.
During the process, the Entry Form opens so that the user can enter the name
into the table.

If the user, changes the name in anyway, as an example, removing the period
from the "T" and the default notinlist message is constantly displayed, as in
it is repeating.

One thing jumps out at me here. You need to use the Text
property instead of the Value property:

If cboDefendantsName.Text <> NewData Then
and
cboDefendantsName.Text = NewData

 

[RayToddJr]

Klatuu:

At what point in the code would I requery the combo?

Thanks for your help....

Ray.

 

[RayToddJr]

Marshall:

Thanks for pointing out that error. It has been corrected.

Thanks,

Ray.

 

[Marshall Barton]

You're welcome. Did it resolve your problem?

Note that you should not need to requery the combo box.
It's automatic when Response is set to acDataErrAdded

 

[Marshall Barton]

There's is a problem with your nested IfThen. It's always set to "False".
What do you want to do with fDataChanged?

No, he has it right. The reason for fDataChanged and the If
is to avoid the infinite loop caused by changing the Text
property inside a procedure that is processing the Text
property.

Note my earlier post about using Text instead of Value in
this situation.

 

[RayToddJr]

Marshall:

Yes, I did make the correction that you previously pointed out.

No, I am still having the same problem.

On the original form if the the user enters a name in the combo box (Test,
Robert M.) they are asked if they want to add it to the database. Via
openargs, the text is then transferred to the adding form.

This form parses the text up into the Last, First, Middle and Suffix fields.

If the user changes the name is any way, as an example, removes the period
from the M thereby having Test, Robert M instead of the original Test, Robert
M., the default default notinlist message is endlessly displayed.

How is the best way of handling this?

Thanks,

Ray.

 

[RayToddJr]

Marshall:

Here is additional information that may provide you with more tidbits of
what is happening.

I have run the code line by line, each time, I will change something in the
name from what I originally entered.

Each time, the code runs all the way to the END SUB where it fires the
notinlist event again and gives me the default notinlist message.

The name is entered into the table correctly as it typed in and changed.

Just want repeated firings of the notinlist event.

Thanks,

Ray.

You're welcome. Did it resolve your problem?

Note that you should not need to requery the combo box.
It's automatic when Response is set to acDataErrAdded
--
Marsh
MVP [MS Access]

Thanks for pointing out that error. It has been corrected.

.

 

[Marshall Barton]

Yes, I did make the correction that you previously pointed out.

No, I am still having the same problem.

I was able to get logic similar to this to work:

Static fDataChanged As Boolean
Const cFormName = "frmNEWDEFENDANT"
If fDataChanged Then
Response = acDataErrAdded
Else
If MsgBox("Would you like to add a new Defendant?",
vbYesNo) = vbYes Then
DoCmd.OpenForm cFormName, _
Datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
If SysCmd(acSysCmdGetObjectState, acForm, cFormName)
= acObjStateOpen Then
With Forms(cFormName)
NewData = !LastName & ", " & !FirstName & ("
" + !MiddleName) & (" " + !Suffix)
End With
DoCmd.Close acForm, cFormName
If cboDefendantsName.Text <> NewData Then
fDataChanged = True
cboDefendantsName.Text = NewData
fDataChanged = False
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
End If
End If

 

[Marshall Barton]

fDataChanged is declared Static so it retains it value from
one call to another. The way Ray has it is necessary to
deal with the recursive nature of modifying the Text
property in this event.

Part of Ray's problem is because the If fDataChanged at the
top of the procedure needs all the other code in an Else
block so both paths can not happen. The other part of Ray's
problem is that Response needs to be acDataErrContinue when
the name was changed (the recursive call already caused the
requery by using acDataErrAdded).

This is a really nasty situation to understand what happens
when an event procedure can trigger itself. Trying to
follow the execution path can be mind bending, sometimes it
may seem like you're trapped in a twisty maze with no way
out.

 

[RayToddJr]

Marshall:

That fixed it. Thanks for you very much appreciated help on that.

Ray.

 

[Marshall Barton]

Thanks for pointing out the Static.

But I don't see why these line of code has any logic.

DoCmd.Close acForm, cFormName <<< Don't see why the need to automate closing
as it is close by the user or is the popup form is not in dialog mode?
Something I'm missing here?

The "close" button on the dialog form does not close the
form. Instead it make the form invisible, which drops it
out of dialog mode so the calling code can continue. The
calling code then extracts the value entered by the user on
the no longer dialog form. At this point the form can and
should be closed.

If cboDefendantsName.Value <> NewData Then
fDataChanged = True <<< Don't see why here
cboDefendantsName.Value = NewData
fDataChanged = False <<< Don't see why here
End If

The nested IfThen set the boolean to True than False. I don't see why you
need to set to True and False. If you want it to be False, why set it to True
and than False again where a single line will do.

After checking if the user modified the value that is being
added to the list, the boolean is set to True so the
procedure can tell that it has to behave differently the
next time it is called.

Now the really tricky part. The line of code:
cboDefendantsName.Text = NewData
(note the necessary use of the Text property) triggers the
NotInList procedure recursively so the line of code that is
executed next is the If at the top of the procedure. That
If statement checks the boolean and if True (as just set) it
just sets the Response argument to get Access to requery the
combo box with the changed new entry and proceeds to exit.
The line of code that executes next is the line that sets
the boolean back to False.

If you do not have a solid grasp of recursive procedures and
how that concept interacts with events all this is seriously
confusing. It is also critical to know that setting the
Text property will trigger some of the text box's events,
even though those events may still be running.