NotInList Error (2000 Access Bible version)

[ILoveAccess via AccessMonster.com]

I currently am working in Access 97, but will be upgrading soon to 2000.

I have a Form called "frmLeads" with a combo box called "TerritoryCodeLU".
It looks up the "TerritoryCode" from the table called "tblTerritoryLU". The
form for the territory entry is called "frmTerritoryLU". I would like when
a user enters a TerritoryCode that is not in the list, for the specific
following actions to take place:

1.) Say, "Territory is not in the list. Would you like to add a new
territory?"
2.) If the user chooses no, then the procedure ends and the user must
choose a valid item from the list.
3.) If the user chooses yes, then allow the user to enter a new territory
record in the form called "frmTerritoryLU"
4.) Requery the combo box and go back to "frmLeads" so they may continue
entering the lead.

I found the following event procedure in Access Bible 2000;I am currently
using 97. I set the Limit to List property for "TerritoryCode" to "yes".
Then I copied this procedure into "OnNotInList" event for "TerritoryCodeLU".

******************************
Private Sub TerritoryCodeLU_NotInList(NewData As String, Response As
Integer)
On Error GoTo TerritoryCodeLU_NotInList_Err
'Add a new record to the TerritoryLU table
'and requery the TerritoryLU combobox
Dim NewTerritory As Integer, MsgTitle As String, MsgDialog As Integer
Const MB_YESNO = 4
Const MB_ICONEXLAMATION = 48
Const MB_DEFBUTTON1 = 0, IDYES = 6, IDNO = 7
'Make sure the user really wants to add it
MsgTitle = "Territory is not in the list"
MsgDialog = MB_YESNO + MB_ICONEXCLAMATION + MB_DEFBUTTON1
NewTerritory = MsgBox("Do you want to add the new territory?",
MsgDialog, MsgTitle)
If NewTerritory = IDNO Then
Response = DATA_ERRCONTINUE
Else
DoCmd.OpenForm "frmTerritoryLU", acNormal, , , acAdd, acDialog
Response = DATA_ERRADDED
End If
TerritoryCodeLU_Exit:
Exit Sub
TerritoryCodeLU_NotInList_Err:
MsgBox Err.Description
Resume TerritoryCodeLU_Exit

End Sub
**************************************
I did the best I could to customize the procedure to fit my database. But,
when I test the form, the (2) following lines are highlighted and I receive
an error message:

1.) "Private Sub TerritoryCodeLU_NotInList(NewData As String, Response As
Integer)"

2.) "MB_ICONEXCLAMATION:

Error: "Compile Error: Variable not defined"

 

[Douglas J. Steele]

Try replacing that second line with

MsgDialog = vbYesNo + vbExclamation + vbDefaultButton1

If that doesn't make the other error go away, post back.

 

[Heather Laufman via AccessMonster.com]

You are AWESOME! Works perfect! Thanks!