NotInList Warning

  • Thread starter Thread starter m stroup
  • Start date Start date
M

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?
 
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
 
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
 
m stroup said:
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?
Click to expand...

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'.
 
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!
 
Back
Top