combo box NotInList Event

[Gus Chuch]

Hello Need Help Again
Trying to set up a combo box to add a new customer name, got this code from
the Access help. I get a SQL; error ?? Also I will need to add a new number
to my primary key field (this is set to auto number in the table)

Private Sub Customer_NotInList(NewData As String,Response As Integer)
Dim ctl As Control
Set ctl = Me!cboCustomer
If MsgBox("Value is not in list. Add it?", vbOKCancel) = vbOK Then
Response = acDataErrAdded
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
Response = acDataErrContinue
ctl.Undo
End If
End Sub

Any ides?

 

[Klatuu]

First, you don't add a number to an autonumber field. The next number is
assigned when the record is create, so you don't need to worry about that
part.

I am suspisous of this line:
ctl.RowSource = ctl.RowSource & ";" & NewData

It should not work at all. The correct way to add an item to a combo
depends on the row source type. If it is a value list:

ctl.AddItem(NewData)

If the row source is a query or table you requery the combo after you have
inserted the new record in the table, which you don't even do in your event.

 

[Douglas J. Steele]

Actually, if the RowSourceType is "Value List", then ctl.RowSource =
ctl.RowSource & ";" & NewData should work: you're adding another entry to
the value list.

However, if Gus is getting a SQL error, that implies (at least to me) that
the RowSourceType is set to Table/Query.

 

[Gus Chuch]

Yes my RowSourceType is set to Table/Query. So how would I fix the -
Characters found after end of SQL statement. Problem? Or should I go back
trying to get this code to work?

Private Sub Customer_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
If MsgBox("Value is not in list. Add it?", vbOKCancel + vbQuestion,
"Confirm")
= vbOK Then
strSQL = "INSERT INTO tblCustomer (Name) Values('" & Me.cboCustomer
& "');"
DoCmd.RunSQL strSQL
End If
Response = acDataErrContinue
Me.cboCustomer.Requery
End Sub

It stops at the last line (Me.cboCustomer.Requery)
And if I go this way I would like to shut down the “You are about to append
# row(s)†message.

 

[Klatuu]

There are two ways to supress the message.
prior to issuing the RunSQL:

Docmd.SetWarnings False

Or, my preference would be to use the Execute method rather than the RunSQL.

CurrentDb.Execute(strSQL), dbFailOnError

It executes faster than RunSQL, it will not raise the warning message
because it bypasses the Access UI and goes straight to Jet which is why it
runs faster. Hardly worth mentioning for a one record append, but very
useful if you are doing any bulk action query.