Help with this Event Procedure

  • Thread starter Thread starter forest8
  • Start date Start date
F

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.
 
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.
Click to expand...

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
 
Hi

There is a matching EndIf.

How do I requery the Combobox to update the list?

Thanks

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

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

Message posted via AccessMonster.com


.
Click to expand...
 
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

J_Goddard via AccessMonster.com said:
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 -
Click to expand...
[quoted text clipped - 32 lines]
Any help will be greatly appreciated. Thank you for your help in advance.
Click to expand...
Click to expand...
Click to expand...
 
Hi John

No new records are added to the table.

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

Thanks


J_Goddard via AccessMonster.com said:
Hi John -
You should not need to requery the combobox.
Click to expand...

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



John said:
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
Click to expand...
[quoted text clipped - 13 lines]
[quoted text clipped - 32 lines]
Any help will be greatly appreciated. Thank you for your help in advance.
Click to expand...
Click to expand...

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



.
Click to expand...
 
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
 
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
 
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
 
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.
 
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!
 
Back
Top