errant notinlist event

[richaluft]

I'm using following notinlist event code for a number of cbo's on a
form (that are all very similar), yet this particular one fails to
work properly.
After event is triggered, form is opened, and data entered, my cbo
shows a blank value, and remains uneditable. Re-entering the same
info does not help.
I have a requery command event in the ongotfocus event of the cbo (as
I have for all of the properly working cbo's).
Can anyone suggest a possible cause (and remedy) for this problem?
TIA, Richard

 

[richaluft]

I'm using following notinlist event code for a number of cbo's on a
form (that are all very similar), yet this particular one fails to
work properly.
After event is triggered, form is opened, and data entered, my cbo
shows a blank value, and remains uneditable. Re-entering the same
info does not help.
I have a requery command event in the ongotfocus event of the cbo (as
I have for all of the properly working cbo's).
Can anyone suggest a possible cause (and remedy) for this problem?
TIA, Richard

Sorry, but I forgot to include the code:
Private Sub CoPolicyType_NotInList(NewData As String, Response As
Integer)

' Add a new policy type by typing a name in
' CoPolicy Type combo box.

Dim NewCoPolicyType As Integer, TruncateName As Integer, Title As
String, MsgDialog As Integer
Const MB_OK = 0
Const MB_YESNO = 4
Const MB_ICONQUESTIONMARK = 32
Const MB_ICONEXCLAMATION = 64
Const MB_DEFBUTTON1 = 0, IDYES = 6, IDNO = 7
' Display message box asking if user wants to add a
' new insurer.
Title = "Policy Type Not In List"
MsgDialog = MB_YESNO + MB_ICONQUESTIONMARK + MB_DEFBUTTON1
NewCoPolicyType = MsgBox("Do you want to add a new Policy Type?",
MsgDialog, Title)

If NewCoPolicyType = IDYES Then
' Display message box and adjust length of
value entered in
' PolicyType combo box.
Title = "Name Too Long"
MsgDialog = MB_OK + MB_ICONEXCLAMATION

If Len(NewData) > 25 Then
TruncateName = MsgBox("Policy names can be no longer than
25 characters. The name you entered will be truncated.", MsgDialog,
Title)
NewData = Left$(NewData, 25)
End If
' Open Add Policy form.
DoCmd.OpenForm FormName:="FPolicyTypeCo", DataMode:=acAdd,
WindowMode:=acDialog, openargs:=NewData
' Continue without displaying default error message.
Response = acDataErrContinue
End If

End Sub

 

[Graham Mandeno]

Hi Richard

Your NotInList event procedure should return a Response value of
acDataErrAdded to indicate that the new value has been added so the combo
box should be automatically requeried.

The Response value acDataErrContinue should be used if the new value has NOT
been added (the user answered "No" to the question), otherwise you will get
the default "not in list" message as well as your own one.

Also, there are built-in constants that you should be using instead of the
ones you are declaring. They are: vbOKOnly, vbYesNo, vbQuestion,
vbExclamation, vbDefaultButton1, vbYes and vbNo.

 

[richaluft]

Hi Richard

Your NotInList event procedure should return a Response value of
acDataErrAdded to indicate that the new value has been added so the combo
box should be automatically requeried.

The Response value acDataErrContinue should be used if the new value has NOT
been added (the user answered "No" to the question), otherwise you will get
the default "not in list" message as well as your own one.

Also, there are built-in constants that you should be using instead of the
ones you are declaring. They are: vbOKOnly, vbYesNo, vbQuestion,
vbExclamation, vbDefaultButton1, vbYes and vbNo.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Sorry, but I forgot to include the code:
Private Sub CoPolicyType_NotInList(NewData As String, Response As
Integer)

' Add a new policy type by typing a name in
' CoPolicy Type combo box.

Dim NewCoPolicyType As Integer, TruncateName As Integer, Title As
String, MsgDialog As Integer
Const MB_OK = 0
Const MB_YESNO = 4
Const MB_ICONQUESTIONMARK = 32
Const MB_ICONEXCLAMATION = 64
Const MB_DEFBUTTON1 = 0, IDYES = 6, IDNO = 7
' Display message box asking if user wants to add a
' new insurer.
Title = "Policy Type Not In List"
MsgDialog = MB_YESNO + MB_ICONQUESTIONMARK + MB_DEFBUTTON1
NewCoPolicyType = MsgBox("Do you want to add a new Policy Type?",
MsgDialog, Title)

If NewCoPolicyType = IDYES Then
' Display message box and adjust length of
value entered in
' PolicyType combo box.
Title = "Name Too Long"
MsgDialog = MB_OK + MB_ICONEXCLAMATION

If Len(NewData) > 25 Then
TruncateName = MsgBox("Policy names can be no longer than
25 characters. The name you entered will be truncated.", MsgDialog,
Title)
NewData = Left$(NewData, 25)
End If
' Open Add Policy form.
DoCmd.OpenForm FormName:="FPolicyTypeCo", DataMode:=acAdd,
WindowMode:=acDialog, openargs:=NewData
' Continue without displaying default error message.
Response = acDataErrContinue
End If

End Sub

Graham: I'll give your suggestions a try, but I remain confused, as
follows.
I'm using essentially identical notinlist events in 4 other cbos on
the same form (all with dataerrcontinue), and in the other 4 my coding
works perfectly (includeng the old constants. I can't explain why
opnly 2 don't function properly.
Ill get back to you with the results of your suggestions.
Richard

 

[richaluft]

Hi Richard

Your NotInList event procedure should return a Response value of
acDataErrAdded to indicate that the new value has been added so the combo
box should be automatically requeried.

The Response value acDataErrContinue should be used if the new value has NOT
been added (the user answered "No" to the question), otherwise you will get
the default "not in list" message as well as your own one.

Also, there are built-in constants that you should be using instead of the
ones you are declaring. They are: vbOKOnly, vbYesNo, vbQuestion,
vbExclamation, vbDefaultButton1, vbYes and vbNo.

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham: I'll give your suggestions a try, but I remain confused, as
follows.
I'm using essentially identical notinlist events in 4 other cbos on
the same form (all with dataerrcontinue), and in the other 4 my coding
works perfectly (includeng the old constants. I can't explain why
opnly 2 don't function properly.
Ill get back to you with the results of your suggestions.
Richard

Tried to use Added, rather than continue, but now get constant msg on
entering new data of:
:the text you entered isnt an item in the list". Needless to say,
recordsource table for data is also not being updated with new data.
Any other suggestions from anyone?

 

[Graham Mandeno]

Sorry Richard - I missed your follow-up here.

Tried to use Added, rather than continue, but now get constant msg on
entering new data of:
:the text you entered isnt an item in the list". Needless to say,
recordsource table for data is also not being updated with new data.
Any other suggestions from anyone?

If the "recordsource table for data is also not being updated with new data"
then the problem is with the form you are opening to add the new data record
(FPolicyTypeCo). If it is now successfully adding a new record to your
RowSource, with a text field exactly matching NewData then you will get the
"isn't an item in the list" message.

Just to clarify:
acDataErrContinue means "assume this error has been handled and pass
control back to the user interface (but don't let me leave this combo box if
the error has not been rectified)"
and
acDataErrAdded means "I have added a new row to the data source which
matches what the user entered, so requery it and IF everything is OK then
quit bugging me"

 

[Graham Mandeno]

(FPolicyTypeCo). If it is now successfully adding a new record to your

Sorry - that should be "If it is NOT ..."