Insert On Not In List

  • Thread starter Thread starter Kevin Sprinkel
  • Start date Start date
K

Kevin Sprinkel

On entering a new value on a combo box, I intend to ask
the user if he'd like to open up a data entry form. Prior
to opening the form, I'd like to insert the new value AND
a value from another combo box on the form.

Can anyone tell me how to do this?

Thank you.

Kevin Sprinkel
 
On entering a new value on a combo box, I intend to ask
the user if he'd like to open up a data entry form. Prior
to opening the form, I'd like to insert the new value AND
a value from another combo box on the form.

Can anyone tell me how to do this?

Thank you.

Kevin Sprinkel
Click to expand...

Is it the Insert part or the Message and Insert that you need help on?
What is the datatype of the bound column of the combo box?
I'll assume the added item is a Text value.

In the NotInList event:

If MsgBox("Product is not in list. Add it?", vbOKCancel) = vbCancel
Then

' If user chooses Cancel, suppress error message
Response = acDataErrContinue
Me.YourCombo = Null
Else
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
Dim strSQL As String

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

CurrentDb.Execute strSQL, dbFailOnError

' Then continue on with opening the other form

End If



I've given you an example of adding a Text datatype value, and then a
ComboBox with a Number datatype.
If the combo box is also text, then use

& chr(34) & Me!OtherComboName & chr(34) & ";"
 
Is it the Insert part or the Message and Insert that you
need help on?
What is the datatype of the bound column of the combo box?
I'll assume the added item is a Text value.
Click to expand...

Thanks, Fred. Your assumption was correct, and the code
works beautifully.

When opening the form, I'd like to use the new
automatically-assigned key as the link criteria, rather
than the combination of the two inserted fields. Is there
any way to get this value?

Best regards.

Kevin Sprinkel
 
Back
Top