Not In List procedure -having problems

[Ray Todd Jr]

Form (frmNEWPROPERTYentry)
subform (subDEFENDANTentry)

on subform there is a control (cboDEFENDANTSNAME) whose recordsource is
DefendantNamesID and is bound to taDEFENDANTSNAME.

cboDEFENDANTSNAME combines the defendants name in this format:
Last, First Middle Suffix

If I enter a name into the control, such as "Test, Linda" and that name
isn't in the taDEFENDANTSNAME table, I have it set to open the dataentry form
via the following code:

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

If MsgBox("subDEFENDANTentry: Would you like to add a new Defendant?",
vbYesNo) = vbYes Then
DoCmd.OpenForm "frmNEWDEFENDANT", , , , acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.Undo
End If

End Sub


On the newly opened form, I enter the required names, address, etc. I then
click on the save data button and close the form.

"I then get the error The Text You entered isn't an item in the list"

I have watched the execution of the code and via the locals windows and the
data is as follows:

NewData=Test,Linda
Response=1

At this point, it loops back around to asking me if I want to enter a new
defendant.

Someone, please tell me what I am doing wrong.

Thanks,

 

[Jeff Boyce]

Ray

If the spelling of the data you enter into the data entry form isn't an
exact match for what was entered in the combobox (that triggered the
NotInList), Access will inform you that "The text you entered is not ..."

Make sure the spelling matches, and that the way you feed rows to the
combobox can generate an exact duplicate.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Graham Mandeno]

Hi Ray

If you enter "Test,Linda" and the expression populating the combo box
RowSource shows "Test, Linda" then it will not be seen as a match. Also,
you may have slightly changed the spelling, or added a middle initial of
suffix, which would further confuse the match.

The workaround for this is fiddly, but I've found it to be fairly foolproof.
The first two steps are optional, but they reduce the need to re-enter data.

1. When you open your "add a record" form, pass NewData through OpenArgs.

2. In the Form_Load event, parse Me.OpenArgs out into the most likely fields
and set the DefaultValue for LastName, FirstName, MiddleName, Suffix as
appropriate.

3. Have the Cancel button on your form do:
Me.Undo
DoCmd.Close acForm, Me.Name

4. Have the OK button do:
Me.Dirty = False
Me.Visible = False

5. Your NotInList code then checks to see if the form is still open (OK was
clicked) and constructs the string that *should* be in the combo box list.
It then closes the form.

6. If this is different from the text types in the combo box then change
..Text to match.

7. [This is the messy part] Changing the .Text property causes NotInList to
fire again, so you need a static variable to tell NotInList to exit without
doing anything.

All together, your code should look something like this:

Private Sub cboDefendantName_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("subDEFENDANTentry: 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 cboDefendantName.Text <> NewData Then
fDataChanged = True
cboDefendantName.Text = NewData
fDataChanged = False
End If
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
End If
End Sub

 

[Ray Todd Jr]

Graham:

Thanks for your help. I have placed the following code into the NotInList
event of the form frmDEFENDANTentry.

On the data entry form 'frmNEWDEFENDANT' I created

'OK' button with:
me.dirty=false
me.visible=false

'Cancel' button with:
me.undo
docmd.close acform, me.name

I have stepped through the code and the variables are getting the
information correctly, however, the cboDEFENDANTNAME never get's updated. I
have looked at the table and the name and information is there.



I am purposly not using the openargs at this point, as I wanted to get the
basic function working first.


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

'Code provided by Graham Mandeno
'July 17, 2008

Static fDataChanged As Boolean
Const cFormName = "frmNEWDEFENDANT"

If fDataChanged Then
Response = acDataErrAdded
Exit Sub
End If

If MsgBox("subDEFENDANTentry: 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 cboDefendantName.Text <> NewData Then
fDataChanged = True
cboDefendantName.Text = NewData
fDataChanged = False
End If
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
Else
Response = acDataErrContinue
End If

End Sub

 

[Ray Todd Jr]

One thing that I failed to add to my last reply, when I click on the 'OK'
button, the NotInList event fires again asking if I want to add the name...

 

[Ray Todd Jr]

Values from the Locals Window when I step through the code:

: fDataChanged : False : Boolean
: NewData : "Test, Linda" : String
: Response : 1 : Integer
: cFormName : "testNEWDEFENDANT" : String

This is the result when the code executes:

Response=AcDataErrAdded

: Response : 2 : Integer

When the sub exits, it refires the notinlist event and displays the generic
notinlist message ("The text you entered isn't an item in the list") not the
custom message in the code.

 

[Cheese_whiz]

Hi Ray,

I don't see where the combo box was ever requeried after the new record was
added to the underlying recordsource. I'm pretty sure that's a problem.

I hope I didn't miss it somewhere....

If not, then I'd try putting it just above the 'If syscmd' line that checks
to see of the popup form is opened.

I could be off-base here.

CW

 

[Graham Mandeno]

Hi Ray

My apologies - somehow I missed your follow-up posts to this thread. I hope
you're still listening

As you step through the code, which line is the last to execute before
NotInList gets fired for the second time?

Also, exactly what is the expression in your combo box RowSource query that
makes up the visible text field? It should match the expression in the
NewData= line.