Not On List Error

  • Thread starter Thread starter Brian
  • Start date Start date
B

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?
 
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!)


tkelley via AccessMonster.com said:
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?
Click to expand...
Click to expand...
 
Brian said:
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?
Click to expand...


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 & """)"
 
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

tkelley via AccessMonster.com said:
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?
Click to expand...
Click to expand...
 
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.
Marshall Barton said:
Brian said:
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?
Click to expand...


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 & """)"
Click to expand...
 
Back
Top