Help with this Event Procedure

[forest8]

Hi there

I currently have this code in my database but I can't seem to make it work
(i.e. no new names are added to my combo box). I don't think I have attached
the event procedure properyly either. Where should I put it?

Private Sub Coach_NotInList(NewData As String, Response As Integer)

intAnswer = MsgBox("This coach" & NewData & " is not currently in the list."
& vbCrLf & _
"Would you like to add this coach to the list now?" _
, vbQuestion + vbYesNo, "This coach")


If intAnswer = vbYes Then
strSQL = "INSERT INTO CB_Coach([CB_Coach]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "This coach has been added to the list." _
, vbInformation, "NewData"
Response = acDataErrAdded

Any help will be greatly appreciated. Thank you for your help in advance.

 

[XPS350]

Hi there

I currently have this code in my database but I can't seem to make it work
(i.e. no new names are added to my combo box). I don't think I have attached
the event procedure properyly either. Where should I put it?

Private Sub Coach_NotInList(NewData As String, Response As Integer)

intAnswer = MsgBox("This coach" & NewData & " is not currently in the list."
& vbCrLf & _
    "Would you like to add this coach to the list now?" _
    , vbQuestion + vbYesNo, "This coach")

If intAnswer = vbYes Then
    strSQL = "INSERT INTO CB_Coach([CB_Coach]) " & _
             "VALUES ('" & NewData & "');"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    MsgBox "This coach has been added to the list." _
        , vbInformation, "NewData"
    Response = acDataErrAdded

Any help will be greatly appreciated.  Thank you for your help in advance.

If this is the full code, you miss an "END":

If intAnswer = vbYes Then
strSQL = "INSERT INTO CB_Coach([CB_Coach]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "This coach has been added to the list." _
, vbInformation, "NewData"
Response = acDataErrAdded
End

Groeten,

Peter
http://access.xps350.com

 

[J_Goddard via AccessMonster.com]

Hi -

First, you need a matching Endif for your If... - otherwise your code won't
even compile.

Secondly, after adding the record to the CB_Coach table, you need to Requery
the combobox(Coach.requery) to update the list.

You have put the code in the proper event.

John

Hi there

I currently have this code in my database but I can't seem to make it work
(i.e. no new names are added to my combo box). I don't think I have attached
the event procedure properyly either. Where should I put it?

Private Sub Coach_NotInList(NewData As String, Response As Integer)

intAnswer = MsgBox("This coach" & NewData & " is not currently in the list."
& vbCrLf & _
"Would you like to add this coach to the list now?" _
, vbQuestion + vbYesNo, "This coach")

If intAnswer = vbYes Then
strSQL = "INSERT INTO CB_Coach([CB_Coach]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "This coach has been added to the list." _
, vbInformation, "NewData"
Response = acDataErrAdded

Any help will be greatly appreciated. Thank you for your help in advance.

 

[forest8]

Hi

There is a matching EndIf.

How do I requery the Combobox to update the list?

Thanks

Hi -

First, you need a matching Endif for your If... - otherwise your code won't
even compile.

Secondly, after adding the record to the CB_Coach table, you need to Requery
the combobox(Coach.requery) to update the list.

You have put the code in the proper event.

John

Hi there

I currently have this code in my database but I can't seem to make it work
(i.e. no new names are added to my combo box). I don't think I have attached
the event procedure properyly either. Where should I put it?

Private Sub Coach_NotInList(NewData As String, Response As Integer)

intAnswer = MsgBox("This coach" & NewData & " is not currently in the list."
& vbCrLf & _
"Would you like to add this coach to the list now?" _
, vbQuestion + vbYesNo, "This coach")

If intAnswer = vbYes Then
strSQL = "INSERT INTO CB_Coach([CB_Coach]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "This coach has been added to the list." _
, vbInformation, "NewData"
Response = acDataErrAdded

Any help will be greatly appreciated. Thank you for your help in advance.

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca




.

 

[J_Goddard via AccessMonster.com]

After the Response = acDataErrAdded line, put
coach.requery

John

Hi

There is a matching EndIf.

How do I requery the Combobox to update the list?

Thanks

Hi -

[quoted text clipped - 32 lines]

 

[John Spencer]

You should not need to requery the combobox. The line
Response = acDataErrAdded
should cause the requery to occur automatically.

Something else is happening here.
What is the rowsource of the combobox?
What column (number) is the bound column?
How many columns does the combobox have?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

After the Response = acDataErrAdded line, put
coach.requery

John

Hi

There is a matching EndIf.

How do I requery the Combobox to update the list?

Thanks

Hi -

[quoted text clipped - 32 lines]

Any help will be greatly appreciated. Thank you for your help in advance.

 

[J_Goddard via AccessMonster.com]

Hi John -

You should not need to requery the combobox.

An interesting comment. The reason I made the suggestion that I did was the
following, taken directly from the A2003 VB help for the notlinlist event.


"When you add an item to a bound combo box, you add a value to a field in the
underlying data source. In most cases you can't simply add one field in a new
record— depending on the structure of data in the table, you probably will
need to add one or more fields to fulfill data requirements. For instance, a
new record must include values for any fields comprising the primary key. If
you need to add items to a bound combo box dynamically, you must prompt the
user to enter data for all required fields, save the new record, and then
requery the combo box to display the new value. "

Forest8 : did you verify that a new record was in fact added to the CB_Coach
table (open the table from the database window)?

John

You should not need to requery the combobox. The line
Response = acDataErrAdded
should cause the requery to occur automatically.

Something else is happening here.
What is the rowsource of the combobox?
What column (number) is the bound column?
How many columns does the combobox have?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

After the Response = acDataErrAdded line, put
coach.requery

[quoted text clipped - 13 lines]

[quoted text clipped - 32 lines]
Any help will be greatly appreciated. Thank you for your help in advance.

 

[forest8]

Hi John

No new records are added to the table.

I'm new to programming and this issue is really confusing me.

Thanks

Hi John -

You should not need to requery the combobox.

An interesting comment. The reason I made the suggestion that I did was the
following, taken directly from the A2003 VB help for the notlinlist event.


"When you add an item to a bound combo box, you add a value to a field in the
underlying data source. In most cases you can't simply add one field in a new
record— depending on the structure of data in the table, you probably will
need to add one or more fields to fulfill data requirements. For instance, a
new record must include values for any fields comprising the primary key. If
you need to add items to a bound combo box dynamically, you must prompt the
user to enter data for all required fields, save the new record, and then
requery the combo box to display the new value. "

Forest8 : did you verify that a new record was in fact added to the CB_Coach
table (open the table from the database window)?

John

You should not need to requery the combobox. The line
Response = acDataErrAdded
should cause the requery to occur automatically.

Something else is happening here.
What is the rowsource of the combobox?
What column (number) is the bound column?
How many columns does the combobox have?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

After the Response = acDataErrAdded line, put
coach.requery

[quoted text clipped - 13 lines]

[quoted text clipped - 32 lines]
Any help will be greatly appreciated. Thank you for your help in advance.

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca



.

 

[J_Goddard via AccessMonster.com]

Hi -

I assume you do get the messages produced by msgbox? If so, there is
probably an error in the SQL code. Change your code to this:

Private Sub Coach_NotInList(NewData As String, Response As Integer)
on error goto Insert_Error
intAnswer = MsgBox("This coach" & NewData & " is not currently in the list."
& vbCrLf & _
"Would you like to add this coach to the list now?" _
, vbQuestion + vbYesNo, "This coach")

If intAnswer = vbYes Then
strSQL = "INSERT INTO CB_Coach([CB_Coach]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
currentdb.execute strSQL, dbfailonerror
DoCmd.SetWarnings True
MsgBox "This coach has been added to the list." _
, vbInformation, "NewData"
Response = acDataErrAdded
endif
exit sub
Insert_Error:
msgbox "The attempted insert produced the following error:" & vbcrlf & err.
description
response = acDataErrContinue
end sub

This will trap any error and tell you what the error is.

John

Hi John

No new records are added to the table.

I'm new to programming and this issue is really confusing me.

Thanks

Hi John -

[quoted text clipped - 36 lines]

[quoted text clipped - 32 lines]
Any help will be greatly appreciated. Thank you for your help in advance.

 

[JimBurke via AccessMonster.com]

Have you inserted a breakpoint and made sure that you are executing the code,
including the INSERT? I didn't see that you did this in any of these posts.
Also are you sure that both the name of the table and the name of the field
are CB_Coach? It's a bit unusual to see the table name and field name the
same.

Hi -

I assume you do get the messages produced by msgbox? If so, there is
probably an error in the SQL code. Change your code to this:

Private Sub Coach_NotInList(NewData As String, Response As Integer)
on error goto Insert_Error
intAnswer = MsgBox("This coach" & NewData & " is not currently in the list."
& vbCrLf & _
"Would you like to add this coach to the list now?" _
, vbQuestion + vbYesNo, "This coach")

If intAnswer = vbYes Then
strSQL = "INSERT INTO CB_Coach([CB_Coach]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
currentdb.execute strSQL, dbfailonerror
DoCmd.SetWarnings True
MsgBox "This coach has been added to the list." _
, vbInformation, "NewData"
Response = acDataErrAdded
endif
exit sub
Insert_Error:
msgbox "The attempted insert produced the following error:" & vbcrlf & err.
description
response = acDataErrContinue
end sub

This will trap any error and tell you what the error is.

John

Hi John

[quoted text clipped - 9 lines]

[quoted text clipped - 32 lines]
Any help will be greatly appreciated. Thank you for your help in advance.

 

[John Spencer]

You did not supply the additional information that I requested. So it is
difficult to trouble shoot this.

For instance, the structure of the table - CB_Coach - could have something to
do with your problem.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[forest8]

Hi

I just noticed that the table is called CB_Coach and the field name is also
CB_Coach.

I've just changed the field name to be Coach.

forest

 

[forest8]

Hi

It appears that the table name and field name is the same: CB_Coach.

How do I fix the code? I really don't understanding this aspect of Access.

Thanks
forest

 

[forest8]

Hi

Also, the message that this coach isn't in the list does not appear at all.

I currently have the event procedure in the Not In List area of my database.

 

[forest8]

Thank you to everyone who responded to my question.

Through reading all the responses and tweaking my code, I was able to get
this event to work.

Thank you once again!