NotInList Without a MsgBox

  • Thread starter Thread starter Dan Seibert
  • Start date Start date
D

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?
 
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?
Click to expand...

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?
 
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.
 
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
 
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.
 
Back
Top