Strange results for NotInList combo

[forcefield via AccessMonster.com]

Hi Everyone,
My NotInList combo works almost perfectly (no error message) except that the
record stored in the table is weird.

My NotInList code is :

Private Sub cboScoreBand_NotInList(NewData As String, Response As Integer)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sMsg As String

sMsg = "Do You wish to add new '" & NewData & "' in this list?"
If NewData = "" Then Exit Sub
SQuest = MsgBox(sMsg, vbQuestion + vbYesNo, "Add new name?")
If SQuest = vbNo Then
Response = acDataErrContinue
Else
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblScore", dbOpenDynaset)
On Error Resume Next

strSQL = "INSERT INTO tblScore([ScoreBand]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Response = acDataErrAdded

End If

rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub

The control source for the combo is ScoreBand and the row source is

SELECT DISTINCT tblScore.ScoreBand FROM tblScore WHERE (((tblScore.ScoreBand)
Is Not Null)) ORDER BY tblScore.ScoreBand;

tblScore has 3 text fields FirstName,LastName and ScoreBand and 1 autonumber
ID.

After keying the scoreband for the combo, tblScore result reads like this
ID FirstName LastName ScoreBand
138 Tim Fodger Green
139 Green
140 Peter Seller Red
141 Red

It appears that a duplicated new record is inserted with the same scoreband
Can someone correct my mistakes so that my scoreband is not repeated.?

Thanks

 

[Alex Dybenko]

Hi,
as I understand - you don;t need NotInList at all. just delete is and it
should work fine.

It would have a sense if you have a separate table for ScoreBand entries

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Klatuu]

I see nothing in your code that would cause an additional record to be added.
It looks like perhaps Green was already in the table, but is not in the
combo's list. I would suggest you do some testing to determine if this is
happening.

As to your code. You are mixing two different methods of adding a record in
the Not In List event. If you use the SQL, you don't need to open the table.
Also, if you use the Execute method with an Action query, you don't have to
set the warnings on and off. Here is how I would do it:

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

sMsg = "Do You wish to add new '" & NewData & "' in this list?"
If NewData = "" Then Exit Sub
If MsgBox("Do You wish to add new " & NewData & " in this list?", _
vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Currentdb.Execute("INSERT INTO tblScore([ScoreBand]) " & _
"VALUES ('" & NewData & "');"), dbFailOnError
Response = acDataErrAdded
Me.cboScoreBand.Requery
End If

End Sub

Note the Me.cboScoreBand.Requery line. That causes the newly added record
to be added to the combo's list.

--
Dave Hargis, Microsoft Access MVP

Hi Everyone,
My NotInList combo works almost perfectly (no error message) except that the
record stored in the table is weird.

My NotInList code is :

Private Sub cboScoreBand_NotInList(NewData As String, Response As Integer)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sMsg As String

sMsg = "Do You wish to add new '" & NewData & "' in this list?"
If NewData = "" Then Exit Sub
SQuest = MsgBox(sMsg, vbQuestion + vbYesNo, "Add new name?")
If SQuest = vbNo Then
Response = acDataErrContinue
Else
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblScore", dbOpenDynaset)
On Error Resume Next

strSQL = "INSERT INTO tblScore([ScoreBand]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Response = acDataErrAdded

End If

rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub

The control source for the combo is ScoreBand and the row source is

SELECT DISTINCT tblScore.ScoreBand FROM tblScore WHERE (((tblScore.ScoreBand)
Is Not Null)) ORDER BY tblScore.ScoreBand;

tblScore has 3 text fields FirstName,LastName and ScoreBand and 1 autonumber
ID.

After keying the scoreband for the combo, tblScore result reads like this
ID FirstName LastName ScoreBand
138 Tim Fodger Green
139 Green
140 Peter Seller Red
141 Red

It appears that a duplicated new record is inserted with the same scoreband
Can someone correct my mistakes so that my scoreband is not repeated.?

Thanks