NotInList playing up (1 Viewer)

[nashaz]

Greetings,

I have a basic table, CourseNameT which looks like this:

In the corresponding form, I have txtCourseID, cboCourseName, cboType (can only be either External or Internal), and txtValidFor. I am trying to use NotInList event so that user can add new courses if it doesn't already exists (the check and balance comes from BeforeUpdate even where if combo of CourseID and Type exists, user won't be allowed to proceed with new entry). Following is the simple code I have for this purpose:

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

    Dim strTmp As String

    ' get confirmation
    strTmp = "'" & NewData & "' does not exist in the database." & vbCrLf & "Would you like to add '" & NewData & "' as a new course?"

    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Add new course?") = vbYes Then

        ' Append the NewData as a record in the CourseNameT
        strTmp = "INSERT INTO CourseNameT ( CourseName ) " & _
            "SELECT """ & NewData & """ AS CourseName;"
        
        CurrentDb.Execute strTmp
        Me.cboCourseName.Requery
        Response = acDataErrAdded

    Else
        Me.Undo

    End If

End Sub

The issue I am having can be best shown via images, as below:

Upon pressing Yes, I get following:

As you can see, there is now an additional record, CourseID 78, with the same name as the one I entered i.e., CourseID 77. Any idea why this might be happening?

Thanks to all in advance.

 

[cheekybuddha]

Why are you using a combo for CourseName?

Surely you don't want duplicate course names in your CourseNameT table?

When adding a new record, just type in the new course name directly.

Use the form BeforeUpdate event to check that it doesn't already exist in the table, and cancel the event if it does.

 

[Gasman]

Presumably the combo is bound?

NotInList Event [Access 2003 VBA Language Reference]

learn.microsoft.com

 

[nashaz]

Why are you using a combo for CourseName?

Surely you don't want duplicate course names in your CourseNameT table?

When adding a new record, just type in the new course name directly.

Use the form BeforeUpdate event to check that it doesn't already exist in the table, and cancel the event if it does.

True... However, just for learning's sake, any idea why this may be happening?

 

[arnelgp]

just maybe...
there are 2 columns in your combobox (and your combo is Bound to the first column, or the CourseID).

 

[cheekybuddha]

any idea why this may be happening?

When you add the new course name using the INSERT statement you are adding a whole new record with empty values for Type and ValidFor.

This is in addition to the new record that you were already working on.

Normally you would not run in to this situation, since you would add a new course directly as suggested in Post #2.

You would use the NotInList event for a combo that is looking up a value from a related table, not a field in the same table.

Otherwise, if you really want to use a combo here (???), you can just set the combo LimitToList property to False, and completely remove the INSERT query from the NotInList event.

 

[arnelgp]

Otherwise, if you really want to use a combo here (???), you can just set the combo LimitToList property to False, and completely remove the INSERT query from the NotInList event.

maybe need confirmation? since if you enter Garbage data, it will immediately save the Garbage.

 

[cheekybuddha]

Yes, true.

Untested, but perhaps something like:

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

    Dim strTmp As String

    ' get confirmation
    strTmp = "'" & NewData & "' does not exist in the database." & vbCrLf & "Would you like to add '" & NewData & "' as a new course?"

    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Add new course?") = vbYes Then
        Response = acDataErrAdded
    Else
        Me.Undo
    End If

End Sub