Adding new entry when "Not in List" (PLEASE HELP)

[Guest]

I have a table named "Camper"
using that table, I have a form that allows users to fill in each field.
One of the fields is titled "Enjoyed"
I have turned that field on the form into a combo box.
That combo box has a record source that is a secondary table - titled "Likes"
the "Likes" table has one field; called "likes1"
I have set the feild on the form to "Limit to List"

What I would like to do is have access add any new / unique entries to the
combo box list. I believe this would mean adding the entry into the records
held in the table named "Likes". I also believe I will be having to use the
"OnNotInList" feature by generating an event procedure...

I have read several posts but have been completley unable to get the
"OnNotInList" function to work.
What should my [event procedure] look like for this instance?

 

[Douglas J Steele]

There's an example of its use at
http://msdn.microsoft.com/library/en-us/vbaac11/html/acevtNotInList_HV05187720.asp

Note that that example is assuming that the values in the list have been
typed in, so the addition of the new value is accomplished by the line

ctl.RowSource = ctl.RowSource & ";" & NewData

If your combobox is getting its values from a table, you'll have to replace
that line with code to add the value to the underlying table.

 

[Guest]

Thanks for the help so far...

I switched things around and will no longer use an outside table to hold
info for the combo box. I will instead use a typed list of entries to choose
from.
On that note, I used the example code shown on the link you sent... and
tried to change the nescessairy parts of the code to fit my field names etc.

I now recieve the pop up window asking me if I would like to add the value
to the list.. and I am able to click "yes"...
now I recieve a second message window that informs me that

"The Recordsource ";your entry here" specified on this report does not exist"

I imagine that I have goofed up some portion of the code....
here is what I have... any noticable errors?


___________________________________________________________

Private Sub Enjoyed_NotInList(NewData As String, Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Enjoyed
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Do you wish to 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

_________________________________________________



Thanks for the time.... I appreciate it!!!

Cheers

There's an example of its use at
http://msdn.microsoft.com/library/en-us/vbaac11/html/acevtNotInList_HV05187720.asp

Note that that example is assuming that the values in the list have been
typed in, so the addition of the new value is accomplished by the line

ctl.RowSource = ctl.RowSource & ";" & NewData

If your combobox is getting its values from a table, you'll have to replace
that line with code to add the value to the underlying table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a table named "Camper"
using that table, I have a form that allows users to fill in each field.
One of the fields is titled "Enjoyed"
I have turned that field on the form into a combo box.
That combo box has a record source that is a secondary table - titled "Likes"
the "Likes" table has one field; called "likes1"
I have set the feild on the form to "Limit to List"

What I would like to do is have access add any new / unique entries to the
combo box list. I believe this would mean adding the entry into the records
held in the table named "Likes". I also believe I will be having to use the
"OnNotInList" feature by generating an event procedure...

I have read several posts but have been completley unable to get the
"OnNotInList" function to work.
What should my [event procedure] look like for this instance?

 

[Douglas J Steele]

Why not post what you've current got? It's kind of difficult to debug code
when you can't see it...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the help so far...

I switched things around and will no longer use an outside table to hold
info for the combo box. I will instead use a typed list of entries to choose
from.
On that note, I used the example code shown on the link you sent... and
tried to change the nescessairy parts of the code to fit my field names etc.

I now recieve the pop up window asking me if I would like to add the value
to the list.. and I am able to click "yes"...
now I recieve a second message window that informs me that

"The Recordsource ";your entry here" specified on this report does not exist"

I imagine that I have goofed up some portion of the code....
here is what I have... any noticable errors?


___________________________________________________________

Private Sub Enjoyed_NotInList(NewData As String, Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Enjoyed
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Do you wish to 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

_________________________________________________



Thanks for the time.... I appreciate it!!!

Cheers

There's an example of its use at
http://msdn.microsoft.com/library/en-us/vbaac11/html/acevtNotInList_HV05187720.asp

Note that that example is assuming that the values in the list have been
typed in, so the addition of the new value is accomplished by the line

ctl.RowSource = ctl.RowSource & ";" & NewData

If your combobox is getting its values from a table, you'll have to replace
that line with code to add the value to the underlying table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a table named "Camper"
using that table, I have a form that allows users to fill in each field.
One of the fields is titled "Enjoyed"
I have turned that field on the form into a combo box.
That combo box has a record source that is a secondary table - titled "Likes"
the "Likes" table has one field; called "likes1"
I have set the feild on the form to "Limit to List"

What I would like to do is have access add any new / unique entries to the
combo box list. I believe this would mean adding the entry into the records
held in the table named "Likes". I also believe I will be having to

use
the

"OnNotInList" feature by generating an event procedure...

I have read several posts but have been completley unable to get the
"OnNotInList" function to work.
What should my [event procedure] look like for this instance?

 

[Guest]

I enclosed the code at the base of my last message...

after the solid line...

at least im prety sure I did...

Why not post what you've current got? It's kind of difficult to debug code
when you can't see it...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the help so far...

I switched things around and will no longer use an outside table to hold
info for the combo box. I will instead use a typed list of entries to choose
from.
On that note, I used the example code shown on the link you sent... and
tried to change the nescessairy parts of the code to fit my field names etc.

I now recieve the pop up window asking me if I would like to add the value
to the list.. and I am able to click "yes"...
now I recieve a second message window that informs me that

"The Recordsource ";your entry here" specified on this report does not exist"

I imagine that I have goofed up some portion of the code....
here is what I have... any noticable errors?


___________________________________________________________

Private Sub Enjoyed_NotInList(NewData As String, Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Enjoyed
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Do you wish to 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

_________________________________________________



Thanks for the time.... I appreciate it!!!

Cheers

There's an example of its use at
http://msdn.microsoft.com/library/en-us/vbaac11/html/acevtNotInList_HV05187720.asp

Note that that example is assuming that the values in the list have been
typed in, so the addition of the new value is accomplished by the line

ctl.RowSource = ctl.RowSource & ";" & NewData

If your combobox is getting its values from a table, you'll have to replace
that line with code to add the value to the underlying table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a table named "Camper"
using that table, I have a form that allows users to fill in each field.
One of the fields is titled "Enjoyed"
I have turned that field on the form into a combo box.
That combo box has a record source that is a secondary table - titled
"Likes"
the "Likes" table has one field; called "likes1"
I have set the feild on the form to "Limit to List"

What I would like to do is have access add any new / unique entries to the
combo box list. I believe this would mean adding the entry into the
records
held in the table named "Likes". I also believe I will be having to use
the
"OnNotInList" feature by generating an event procedure...

I have read several posts but have been completley unable to get the
"OnNotInList" function to work.
What should my [event procedure] look like for this instance?

 

[Douglas J. Steele]

Sorry: I didn't look closely enough, and I thought that was the same code as
in on the web.

Your changes look correct.

The error you're reporting doesn't sound as though it has anything to do
with the combobox, though: it's talking about a report, and there's nothing
to do with reports in the code associated with the NotInList event.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I enclosed the code at the base of my last message...

after the solid line...

at least im prety sure I did...

Why not post what you've current got? It's kind of difficult to debug
code
when you can't see it...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the help so far...

I switched things around and will no longer use an outside table to
hold
info for the combo box. I will instead use a typed list of entries to choose
from.
On that note, I used the example code shown on the link you sent... and
tried to change the nescessairy parts of the code to fit my field names etc.

I now recieve the pop up window asking me if I would like to add the
value
to the list.. and I am able to click "yes"...
now I recieve a second message window that informs me that

"The Recordsource ";your entry here" specified on this report does not exist"

I imagine that I have goofed up some portion of the code....
here is what I have... any noticable errors?


___________________________________________________________

Private Sub Enjoyed_NotInList(NewData As String, Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Enjoyed
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Do you wish to 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

_________________________________________________



Thanks for the time.... I appreciate it!!!

Cheers



:

There's an example of its use at
http://msdn.microsoft.com/library/en-us/vbaac11/html/acevtNotInList_HV05187720.asp

Note that that example is assuming that the values in the list have
been
typed in, so the addition of the new value is accomplished by the
line

ctl.RowSource = ctl.RowSource & ";" & NewData

If your combobox is getting its values from a table, you'll have to replace
that line with code to add the value to the underlying table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I have a table named "Camper"
using that table, I have a form that allows users to fill in each field.
One of the fields is titled "Enjoyed"
I have turned that field on the form into a combo box.
That combo box has a record source that is a secondary table -
titled
"Likes"
the "Likes" table has one field; called "likes1"
I have set the feild on the form to "Limit to List"

What I would like to do is have access add any new / unique entries
to the
combo box list. I believe this would mean adding the entry into the
records
held in the table named "Likes". I also believe I will be having to use
the
"OnNotInList" feature by generating an event procedure...

I have read several posts but have been completley unable to get
the
"OnNotInList" function to work.
What should my [event procedure] look like for this instance?