Not On List Error

[Brian]

I have copied some code from an Access help-site to enable a new "Supplier"
to be added to a combo box through a user form without having to go back to
the source table.

The code is :-
Private Sub Supplier_NotInList(NewData As String, Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Supplier
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If

End Sub

When a new value is added , I get an error message - "Characters found
after end of SQL statement". The help box says to delete anything after the
semi-colon [;]

I assume the SQL statement is the LookUP Select statement in the underlying
table which is :-
SELECT [tbSuppliers].[Supplier ID], [tbSuppliers].[Supplier] FROM
[tbSuppliers] ORDER BY [Supplier];

I can't figure out what the problem is. Any assistance much appreciated.
ps If the control name is a two-parter ie "Supplier Town" what is the
correct format - To enclose in [ ] or " " or join with an underscore?

 

[Douglas J. Steele]

There's more than one way to populate a combo box or list box. RowSourceType
can be one of "Table/Query", "Value List" or "Field List"

If it's "Table/Query", then the RowSource is a table name, query name or SQL
statement.

If it's "Value List", the RowSource is a list of items with semicolon
separators.

If it's "Field List", the RowSource is a table name, query name or SQL
statement, and what appears in the combo or list box is a list of the
fields.

In other words, Brian's code is perfectly legitimate for a RowSourceType of
"Value List". Given the error he's getting, though, I suspect that his
RowSourceType isn't "Value List"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Most importantly, I'm not seeing your SQL to add the new value. It
doesn't
just automatically happen.

I'm also not sure why the following line is there:
"ctl.RowSource = ctl.RowSource & ";" & NewData"
(just a .requery woud do the trick ... after the add is done correctly.

Go here:

http://www.everythingaccess.com/tutorials.asp?ID=NotInList:-Adding-values-to-lookup-tables


And find the sections that start with:

"Programming the NotInList Event" & "Offer to Accept the Entry By Adding
It
to the List"

I think it does a pretty good job of explaining it.

I have copied some code from an Access help-site to enable a new
"Supplier"
to be added to a combo box through a user form without having to go back
to
the source table.

The code is :-
Private Sub Supplier_NotInList(NewData As String, Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Supplier
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If

End Sub

When a new value is added , I get an error message - "Characters found
after end of SQL statement". The help box says to delete anything after
the
semi-colon [;]

I assume the SQL statement is the LookUP Select statement in the
underlying
table which is :-
SELECT [tbSuppliers].[Supplier ID], [tbSuppliers].[Supplier] FROM
[tbSuppliers] ORDER BY [Supplier];

I can't figure out what the problem is. Any assistance much appreciated.
ps If the control name is a two-parter ie "Supplier Town" what is the
correct format - To enclose in [ ] or " " or join with an underscore?

 

[Marshall Barton]

I have copied some code from an Access help-site to enable a new "Supplier"
to be added to a combo box through a user form without having to go back to
the source table.

The code is :-
Private Sub Supplier_NotInList(NewData As String, Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Supplier
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If

End Sub

When a new value is added , I get an error message - "Characters found
after end of SQL statement". The help box says to delete anything after the
semi-colon [;]

I assume the SQL statement is the LookUP Select statement in the underlying
table which is :-
SELECT [tbSuppliers].[Supplier ID], [tbSuppliers].[Supplier] FROM
[tbSuppliers] ORDER BY [Supplier];

I can't figure out what the problem is. Any assistance much appreciated.
ps If the control name is a two-parter ie "Supplier Town" what is the
correct format - To enclose in [ ] or " " or join with an underscore?

THe problem appears to be that you are mixing a list type
row source and a table/query type row source.

If the list box's RowSourceType is Value List then you can
use concatenation (or AddItem) to add a new item to the
list.

If the RowSourceType is Table/Query, then you need to add
the the new item to the table. In your simple situation,
this can be just:

CurrentDb.Execute "INSERT INTO tbSuppliers (Supplier) " _
& "VALUES(""" & NewData & """)"

 

[Brian]

Thanks for the information. The earlier example was copied direct from the
in-built Access helpfiles. The one you directed me to does work fine with a
little tweaking.
Much obliged to you Mr kelley

Most importantly, I'm not seeing your SQL to add the new value. It doesn't
just automatically happen.

I'm also not sure why the following line is there:
"ctl.RowSource = ctl.RowSource & ";" & NewData"
(just a .requery woud do the trick ... after the add is done correctly.

Go here:

http://www.everythingaccess.com/tutorials.asp?ID=NotInList:-Adding-values-to-lookup-tables


And find the sections that start with:

"Programming the NotInList Event" & "Offer to Accept the Entry By Adding It
to the List"

I think it does a pretty good job of explaining it.

I have copied some code from an Access help-site to enable a new "Supplier"
to be added to a combo box through a user form without having to go back to
the source table.

The code is :-
Private Sub Supplier_NotInList(NewData As String, Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Supplier
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If

End Sub

When a new value is added , I get an error message - "Characters found
after end of SQL statement". The help box says to delete anything after the
semi-colon [;]

I assume the SQL statement is the LookUP Select statement in the underlying
table which is :-
SELECT [tbSuppliers].[Supplier ID], [tbSuppliers].[Supplier] FROM
[tbSuppliers] ORDER BY [Supplier];

I can't figure out what the problem is. Any assistance much appreciated.
ps If the control name is a two-parter ie "Supplier Town" what is the
correct format - To enclose in [ ] or " " or join with an underscore?

 

[Brian]

Thank you gentlemen, as you point, I had used the wrong type of code for the
source as Table/Query. I am relatively new to DB's and perhaps I got a bit
ahead of myself "A little learning etc..." but this is a great site for
getting help.

I have copied some code from an Access help-site to enable a new "Supplier"
to be added to a combo box through a user form without having to go back to
the source table.

The code is :-
Private Sub Supplier_NotInList(NewData As String, Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Supplier
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If

End Sub

When a new value is added , I get an error message - "Characters found
after end of SQL statement". The help box says to delete anything after the
semi-colon [;]

I assume the SQL statement is the LookUP Select statement in the underlying
table which is :-
SELECT [tbSuppliers].[Supplier ID], [tbSuppliers].[Supplier] FROM
[tbSuppliers] ORDER BY [Supplier];

I can't figure out what the problem is. Any assistance much appreciated.
ps If the control name is a two-parter ie "Supplier Town" what is the
correct format - To enclose in [ ] or " " or join with an underscore?

THe problem appears to be that you are mixing a list type
row source and a table/query type row source.

If the list box's RowSourceType is Value List then you can
use concatenation (or AddItem) to add a new item to the
list.

If the RowSourceType is Table/Query, then you need to add
the the new item to the table. In your simple situation,
this can be just:

CurrentDb.Execute "INSERT INTO tbSuppliers (Supplier) " _
& "VALUES(""" & NewData & """)"