DoCmd.SetWarnings for NotInList

[famousb]

ok, i have a combo box pulling from a table.&nbsp;&nbsp;i'm using the NotInList property to prompt the user when he/she types in a new record that the record will be added (this is to give them an out if it was just a typo).<br>the recorded is then added to source table, and everything goes great, EXCEPT that the standard error message of <br>&quot;The Text you enterred isn't an item on the list.&quot;<br>comes up after my own message box appears.<br><br>i tried the DoCmd.SetWarnings method of stopping this, but i receive a message that the argument is not optional.<br><br>i also tried not limiting to list for the combo box, but this just seems to skip the NotInList property arguments all together.<br><br>any suggestions.<br><br>thanks. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[DougP]

Yo Brian,<br><br>You have a combo box whose recordsource is a table or query right.<br>Now are the users adding new items to the combo box or just selecting items from it?<br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[famousb]

Yes, the combo box is pulling from a table.&nbsp;&nbsp;the users are generally selecting items from the combo box, but occassionally they may have to add a new item as well.&nbsp;&nbsp;i was originally going to open another form to add the item, but decided it was just as easy to have the item automatically added.&nbsp;&nbsp;here's the code i'm using for the NotInList property.<br><br>Private Sub SourceCombo_NotInList(NewData As String, Response As Integer)<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim iAnswer As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database, rst As Recordset<br><br>&nbsp;&nbsp;&nbsp;&nbsp;iAnswer = MsgBox(&quot;Item is not currently in list, adding item&quot;, _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vbOKCancel + vbQuestion)<br>&nbsp;&nbsp;&nbsp;&nbsp;If iAnswer = vbOK Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Me.Source.Value = NewData<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set rst = db.OpenRecordset(&quot;tbfSource&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.AddNew<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.Fields(&quot;Source&quot = NewData<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Me.SourceCombo.Value = Me.Source.Value<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Me.Refresh<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Me.subMain.SetFocus<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub<br><br>tbfSource =the table that feeds the SourceCombo combo box.<br>Source = the name of the field in the main table that the information is then fed into (the table behind the form itself)<br>basically, after updating SourceCombo, the value is entered into Source, which then brings up other data in a subform, etc...<br>but if the record is not in tbfSource, i want it to be added, but have a message alerting the user that it's being added. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[Julio]

Hi Brian,<br><br>You must set the &quot;Response&quot; parameter to acDataErrAdded.<br>Basically, when you decide to run some code in the NotInList event, Access doesn't know what you are doing, so you must tell it. You do that with the &quot;Response&quot; parameter.<br><br>So, just add this to your code:<br>Response = acDataErrAdded<br><br>For info on the other possible values, search on help for 'Event NotInList'.<br><br>HTH<br><br>Julio<br>

 

[DougP]

I guess you see its a trade off in functionality.<br>And working with Access you already know that.<br><br>If you use the limit to list you have one issue<br>If you don't you have another.<br>i would look at how often do they need to key in a new item<br>Once a day, one a week <br>And then not use the limit to list.<br><br>I also guess you know that when you click in the combo box and type a character if its in the list it will jump to the first instance of that item.<br>As you type more it will move to a more defining search.<br>So if they key the whole item in and you are still at the top of the combo box the item is NOT in the list.<br><br>I know this doesn't help much. <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[samara]

Hi,<br>Private Sub SourceCombo_NotInList(NewData As String, Response As Integer)<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim iAnswer As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database, rst As Recordset<br><br>&nbsp;&nbsp;&nbsp;&nbsp;iAnswer = MsgBox(&quot;Item is not currently in list, adding item&quot;, _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vbOKCancel + vbQuestion)<br>&nbsp;&nbsp;&nbsp;&nbsp;If iAnswer = vbOK Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Me.Source.Value = NewData<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set rst = db.OpenRecordset(&quot;tbfSource&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.AddNew<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.Fields(&quot;Source&quot = NewData<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'' You have to add the following line to update the combo box and cancel the error msg, it'll do all you wanna do with your previous code here<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Response = AcDataErrAdded<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub<br><br> <p>Mohamed Aly<br><a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a><br><a href= > </a><br>

 

[famousb]

thank you so much, the <br>Response = AcDataErrAdded<br>worked great.<br><br>Doug,<br>i realize there is a trade-off, but then end-user would like a message to appear when a new item is added, and i kind of figured that this would be the easiest way to handle the need.<br><br>thanks to all. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[elizabeth]

FYI, a similar question has been asked a couple of times before (although I couldn't find the right keywords in search) so I added a FAQ on it to this Forum. My method is slightly different. Because it's so easy for a user to miskey a value, I prefer to have them review the list of valid values before they add the new one. Also sometimes my lookup value is in a table that requires additional fields in the record, such as a value for location might require a building, a floor and a room. So I open a form and let them rekey the value. This isn't much trouble for them if they add new values infrequently.

 

[ComputorMike]

This Code works. Some of the code was taken from famousb. Thank you.
Mike

Function MyAddtoList(NewData As String, Response As Integer, sFrmName As Form)
'MSB 06/14/01 MyAddtoList function in module1

'Private Sub ComboName_NotInList(NewData As String, Response As Integer)
'MyAddtoList NewData, Response, Me 'add this line to NotInList [Event Procedure]
'End Sub

Dim db As Database
Dim rst As Recordset
Dim MyForm As Form
Dim ctlCurrentControl As Control
Dim iAnswer As Integer
Dim TableName As String
Dim FieldName As String

Set MyForm = [sFrmName]
Set ctlCurrentControl = MyForm.ActiveControl
Set db = CurrentDb
TableName = ctlCurrentControl.RowSource
iAnswer = MsgBox(NewData & &quot; is not currently not in the list!&quot; & _
&quot; Would you like to add it!&quot;, vbYesNo + vbQuestion)
If iAnswer = vbYes Then
Set rst = db.OpenRecordset(TableName)
FieldName = rst.Fields(1).Name
Set rst = db.OpenRecordset(TableName)
rst.AddNew
rst.Fields(FieldName) = NewData
rst.Update
rst.MoveLast
rst.Close
End If
Response = acDataErrAdded
Set db = Nothing
End Function