Help fixing my NotInList code for a combo box

[forest8]

Hi there

I'm trying to see what I'm doing wrong with this NotInList code.

In my T_Programs (table called Programs), I have a Coach field which is
populated by a Combo Box that I created in a separated table called CB_Coach.


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

End If

End Sub

At first, I couldn't add a new coach. But then I did the following:

Column Count 2
Column Widths 0
Bound Column 2
Limit to List No
Allow Value List Edits Yes
Inherit List Value Yes

Now I can put a new name in the field but it's not added to the CB_Coach
table.

What am I doing wrong?

Thank you in advance.

 

[Marshall Barton]

In my T_Programs (table called Programs), I have a Coach field which is
populated by a Combo Box that I created in a separated table called CB_Coach.

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

End If

End Sub

At first, I couldn't add a new coach. But then I did the following:

Column Count 2
Column Widths 0
Bound Column 2
Limit to List No
Allow Value List Edits Yes
Inherit List Value Yes

Now I can put a new name in the field but it's not added to the CB_Coach
table.

Can't be sure, but using RunSQL could be the problem. It
might not finish running before the combo box is requeried
(via acDataErrAdded).

Try replacing:
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

with:
CurrentDb.Execute strSQL, dbFailOnError

 

[forest8]

Hi there

It still doesn't work.

Thanks

In my T_Programs (table called Programs), I have a Coach field which is
populated by a Combo Box that I created in a separated table called CB_Coach.

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

End If

End Sub

At first, I couldn't add a new coach. But then I did the following:

Column Count 2
Column Widths 0
Bound Column 2
Limit to List No
Allow Value List Edits Yes
Inherit List Value Yes

Now I can put a new name in the field but it's not added to the CB_Coach
table.

Can't be sure, but using RunSQL could be the problem. It
might not finish running before the combo box is requeried
(via acDataErrAdded).

Try replacing:
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

with:
CurrentDb.Execute strSQL, dbFailOnError

 

[Marshall Barton]

"It still doesn't work." is a singulaly uninformative
statement.

Did you get an error message?
If there was no error, have you opened the table to see what
was added?

A two column combo box would typically have an ID field in
the invisible bound column. Because your invisible column
is the bound column, I suspect you have the wrong bound
column or you are making the wrong column invisible.

I would also expect LimitToList to be set to Yes.
--
Marsh
MVP [MS Access]

It still doesn't work.

In my T_Programs (table called Programs), I have a Coach field which is
populated by a Combo Box that I created in a separated table called CB_Coach.

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

End If

End Sub

At first, I couldn't add a new coach. But then I did the following:

Column Count 2
Column Widths 0
Bound Column 2
Limit to List No
Allow Value List Edits Yes
Inherit List Value Yes

Now I can put a new name in the field but it's not added to the CB_Coach
table.

Can't be sure, but using RunSQL could be the problem. It
might not finish running before the combo box is requeried
(via acDataErrAdded).

Try replacing:
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

with:
CurrentDb.Execute strSQL, dbFailOnError