NotInList Warning

[m stroup]

I have marked Limit to List as yes. I have entered the following code:

Private Sub cboCompany_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboCompany_NotInList

Dim NewCompany As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

If MsgBox("Would you like to add this company?") = vbNo Then
MsgBox ("Please enter a valid company from the drop down box.")
Else
'Open a new recordset in the Company table.
NewCompany = cboCompany.Text
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCompanies", dbOpenDynaset)
rs.AddNew
rs![Company] = NewCompany
rs.Update

'Set cboCompany to NewCompany
cboCompany.Value = NewCompany
End If

Exit_cboCompany_NotInList:
Exit Sub

Err_cboCompany_NotInList:
MsgBox Err.Description

End Sub

I continue to get the 'the item is not on the list' message. Any suggestions?

 

[Douglas J. Steele]

You forgot to set the Response variable to acDataErrContinue when they
select No from the message box, or acDataErrAdded when they don't select No.

Of course, I don't believe your If statement can work the way you want,
since it doesn't give them a Yes/No option! Try

If MsgBox("Would you like to add this company?", vbYesNo) = vbNo Then

or, to be a little fancier,

If MsgBox("Would you like to add this company?", _
vbYesNo + vbQuestion) = vbNo Then

 

[Roger Carlson]

There's two parts to this.

First of all, you need to add
Response = acDataErrContinue
in your IF clause and
Response = acDataErrAdded
in your ELSE clause

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "NotInList.mdb" which illustrates this and some other
alternatives.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Stuart McCall]

I have marked Limit to List as yes. I have entered the following code:

Private Sub cboCompany_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboCompany_NotInList

Dim NewCompany As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

If MsgBox("Would you like to add this company?") = vbNo Then
MsgBox ("Please enter a valid company from the drop down box.")
Else
'Open a new recordset in the Company table.
NewCompany = cboCompany.Text
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCompanies", dbOpenDynaset)
rs.AddNew
rs![Company] = NewCompany
rs.Update

'Set cboCompany to NewCompany
cboCompany.Value = NewCompany
End If

Exit_cboCompany_NotInList:
Exit Sub

Err_cboCompany_NotInList:
MsgBox Err.Description

End Sub

I continue to get the 'the item is not on the list' message. Any
suggestions?

At the end of your Else block, add the line:

Response = acDataErrAdded

Also, for completeness, add the this line after the MsgBox line:

Response = acDataErrContinue

The technique is well covered in the help topic 'NotInList Event'.

 

[m stroup]

Thanks, Doug. You are right. I only get an ok message box. I had caught
the response piece but not the incorrect code for the box!