Compile error, NotinList event for combo

[kdirvin]

I have a form to enter bank info, and a combo on this form (cbo1) lists bank directors. The combobox is based on tbl_Directors containing three fields: DirectorID (AutoNumber), LastName, and FirstName. Cbo1 has two columns, DirectorID (the bound column, not shown) and an expression that displays the directors’ full names by combining LastName and FirstName.
Users add new directors regularly, so I am using a NotInList event for cbo1. More than one field in tbl_Directors will be updated, so the NotInList code should bring up the form to enter directors (frm_Directors). Once entered, I want new directors to show up on cbo1 in the original form.


My Not in List code for cbo1 looks like this:
Private Sub cbo1_NotInList(NewData As String, Response As Integer)

Dim strTask As String
Dim Msg As String
' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
' Message prompt for entries not in list.
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add them?
If MsgBox = (Msg, vbQuestion + vbYesNo) = vbYes
Then
DoCmd.OpenForm "frm_ Directors", , , , acAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub


My OnLoad property of frm_Directors contains this code:


Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me![DirectorID] = Me.OpenArgs
End If
End Sub


Access throws me a compile error for the line
MsgBox = (Msg, vbQuestion + vbYesNo) = vbYes

The debugger highlights the comma after Msg and says “Expected: ).” How am I erring in my code? (Likely, it’s in more than this one place).
Thanks always for your help. J

 

[RuralGuy]

Ditch the 1st "=" sign:
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes

 

[kdirvin]

I deleted the extra equals sign and that gave me my message box. I received a 'Run Time Error' 2113 after that, so in my form's OnLoad event I changed Me![DirectorID] to Me![Director]. That allows me to enter the new data in a form - perfect.

The only thing is that when I click out of the entry form dialog and return to my original form, Access throws a "The text you entered isn't in the list" error. I thought coding a NotinList event prevented such a message from appearing?

Thanks for your help!

 

[RuralGuy]

You would need to set the Response value like: Response = acDataErrAdded

 

[kdirvin]

Oh, no. Changing the OnLoad event to Me![Director] didn't work after all. I now have a compile error saying "Method or data member not found." What field should go between the brackets? I originally used DirectorID since I thought I needed to pass the bound column but obviously that didn't fly.

Regarding the Response = acDataErrAdded. This needs to go in my OnForm event? I have this string in my NotinList code, but I am not certain it is in the correct place.

Thank you!

 

[RuralGuy]

You have it in the correct location in the NotInList event. As for the OnLoad event, use a period "." instead of the bang "!" and it will invoke intellisense after you type the "." and show you the choices available.