NotInList Without a MsgBox

[Dan Seibert]

I need to use the notinlist on a combo box but I do not want a message
box to ask me if I want to add it. I would just like to have it see
that the item is not there and add it.

I also need it to add the id from the idem selected in another combo box
on the form. So in all, it should add 2 fields to the new record, not
just one.

Can someone help me out in doing this?

 

[fredg]

I need to use the notinlist on a combo box but I do not want a message
box to ask me if I want to add it. I would just like to have it see
that the item is not there and add it.

I also need it to add the id from the idem selected in another combo box
on the form. So in all, it should add 2 fields to the new record, not
just one.

Can someone help me out in doing this?

Dan,
This is not a wise move.
People mis-spell names and words all the time.
Having a list of names, for instance, in a combo box, and having
someone type 'McDanials' when they meant 'McDaniels' is going to load
your table and combo box with useless, space wasting, names.

Give the user a chance to correct the error and make a decision as to
what has to be done. Otherwise, why use the Limit to List feature?

 

[Dan Seibert]

Ok, so lets say I go with the msgbox to verify if they want to add the
record... I still am confussed as to how to add both fields to the
table. Any help would be appreciated.

 

[John Spencer (MVP)]

So you would need code something like the UNTESTED AIR CODE below.

This code only works if you have a reference to DAO in your references (Tools:References)

'SAMPLE CODE

Private Sub Combo2_NotInList(NewData As String, Response As Integer)
Dim strSQL As String

If vbYes = MsgBox(NewData & " is not in current list." & _
" Add it?", vbYesNo, "New Value") Then

strSQL = "Insert into YourTable (FirstField,SecondField)" & _
" Values(" & Chr(34) & NewData & Chr(34) & "," & _
Chr(34) & Me.SomeComboBox & Chr(34) & ")"
'Remove the "Chr(34) & " if the fields are not text fields,
'but number fields
strSQL = CurrentDb().Execute(strSQL)

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If


End Sub

 

[fredg]

Gee, you posted this on the 21st and I did't get it until today, the
25th. So much for Hi Tech! >

One way is to use an Append SQL.

If MsgBox("Product is not in list. Add it?", vbYesNo) = vbYes Then
Response = acDataErrAdded
Dim strSQL as String

strSQL = " INSERT INTO YourTable(FieldName1,FieldName2) SELECT " &
Chr(34) & NewData & Chr(34) & "," & Me!OtherComboName & ";"

CurrentDb.Execute strSQL, dbFailOnError

Else
' Do something else
Response = acDataErrContinue
Me!ComboBoxName = Null
End If

The above assumes the other combo box bound column is a Number
Datatype, and the NewData to be entered is text datatype.