Need help with this NotInList code for a combo box

F

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.
 
T

Tom van Stiphout

On Fri, 15 Jan 2010 20:00:03 -0800, forest8

I can't answer that question without understanding your table layout.
A good design would be:
T_Programs
ProgramID autonumber PK
ProgramName text uniqueIndex
CoachID long int

T_Coaches
CoachID autonumber PK
FirstName text
LastName text
Phone text etc.

Then in the Relationships window create a relationship between
T_Coaches.CoachID and T_Programs.CoachID, and enforce this relation.

Is this what you have?

-Tom.
Microsoft Access MVP
 
F

forest8

Yes it is. I have a One to Many relationship.

Tom van Stiphout said:
On Fri, 15 Jan 2010 20:00:03 -0800, forest8

I can't answer that question without understanding your table layout.
A good design would be:
T_Programs
ProgramID autonumber PK
ProgramName text uniqueIndex
CoachID long int

T_Coaches
CoachID autonumber PK
FirstName text
LastName text
Phone text etc.

Then in the Relationships window create a relationship between
T_Coaches.CoachID and T_Programs.CoachID, and enforce this relation.

Is this what you have?

-Tom.
Microsoft Access MVP


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.
.
 
T

Tom van Stiphout

On Sat, 16 Jan 2010 15:01:01 -0800, forest8

Typically what I do is to open a modal dialog, where the user can fill
out a new record. I would pre-fill the new record with the value just
entered in the dropdown.
Reason for this approach is that a table typically should have several
required fields. In your case of a Coach record that's probably the
case as well.

But to come back to your original post, you should set a breakpoint
after the "strSQL =" line. Then in the immediate window type:
?strSQL
and hit Enter. It will show you the sql statement. Copy it to the
clipboard. Then create a new query, switch to SQL view, and paste in
the SQL statement. Then switch to design view and the parser will tell
you where the problem is.

One possible problem I can see is with names with embedded
single-quotes, such as O'Brien.
insert into T_Coach(LastName) values('O'Brien')
You can see where that won't work. You'll need to double-up on your
single-quotes:
strSQL = "INSERT INTO CB_Coach([CB_Coach]) " & _
"VALUES ('" & Replace(NewData,"'", "''") & "');"

Having field names the same as the tablename is another big no-no. Are
you really using CB_Coach for both the table name as a field name?

-Tom.
Microsoft Access MVP

Yes it is. I have a One to Many relationship.

Tom van Stiphout said:
On Fri, 15 Jan 2010 20:00:03 -0800, forest8

I can't answer that question without understanding your table layout.
A good design would be:
T_Programs
ProgramID autonumber PK
ProgramName text uniqueIndex
CoachID long int

T_Coaches
CoachID autonumber PK
FirstName text
LastName text
Phone text etc.

Then in the Relationships window create a relationship between
T_Coaches.CoachID and T_Programs.CoachID, and enforce this relation.

Is this what you have?

-Tom.
Microsoft Access MVP


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.
.
 
F

forest8

I can change the field name -- hopefully that will help.

I'll follow the suggestions here and let's see where we end up. Thanks.



Tom van Stiphout said:
On Sat, 16 Jan 2010 15:01:01 -0800, forest8

Typically what I do is to open a modal dialog, where the user can fill
out a new record. I would pre-fill the new record with the value just
entered in the dropdown.
Reason for this approach is that a table typically should have several
required fields. In your case of a Coach record that's probably the
case as well.

But to come back to your original post, you should set a breakpoint
after the "strSQL =" line. Then in the immediate window type:
?strSQL
and hit Enter. It will show you the sql statement. Copy it to the
clipboard. Then create a new query, switch to SQL view, and paste in
the SQL statement. Then switch to design view and the parser will tell
you where the problem is.

One possible problem I can see is with names with embedded
single-quotes, such as O'Brien.
insert into T_Coach(LastName) values('O'Brien')
You can see where that won't work. You'll need to double-up on your
single-quotes:
strSQL = "INSERT INTO CB_Coach([CB_Coach]) " & _
"VALUES ('" & Replace(NewData,"'", "''") & "');"

Having field names the same as the tablename is another big no-no. Are
you really using CB_Coach for both the table name as a field name?

-Tom.
Microsoft Access MVP

Yes it is. I have a One to Many relationship.

Tom van Stiphout said:
On Fri, 15 Jan 2010 20:00:03 -0800, forest8

I can't answer that question without understanding your table layout.
A good design would be:
T_Programs
ProgramID autonumber PK
ProgramName text uniqueIndex
CoachID long int

T_Coaches
CoachID autonumber PK
FirstName text
LastName text
Phone text etc.

Then in the Relationships window create a relationship between
T_Coaches.CoachID and T_Programs.CoachID, and enforce this relation.

Is this what you have?

-Tom.
Microsoft Access MVP



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.
.
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top