ComboBox - Input with Key issue

[floyd]

2 databases:


"tblCourse" DB linked to "Course Pack" DB via CourseID primary Key in "Course Pack". (Both numerical)
"Course Pack" DB has two fields: CouseID / Course (text)

"tblCourse" acquires the specific Course text and uses it in reporting.

I have created a combo box for the purposes of finding and ultimately inputting new records into the "Course Pack / Course" DB record.

I have this code which aids in this process. The problem is... the code is tied to the linkages which are numeric as opposed to placing the new record in "Course" which is TXT.

Any help would be FANTASTIC!

Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer
On Error GoTo Err_Append2Table
' Purpose:   Append NotInList value to combo's recordset.
' Assumes:   ControlSource of combo has the same name as the foreign key field.
' Return:    acDataErrAdded if added, else acDataErrContinue
' Usage:     Add this line to the combo's NotInList event procedure:
'                Response = Append2Table(Me.MyCombo, NewData)
    Dim rst As
    Dim sMsg As String
    Dim vField As Variant      ' Name of the field to append to.

    Append2Table = acDataErrContinue
    vField = cbo.ControlSource
    If Not (IsNull(vField) Or IsNull(NewData)) Then
        sMsg = "Do you wish to add the entry " & NewData & " for " & cbo.Name & "?"
        If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
            Set rst = CurrentDb.    (cbo.RowSource)
            rst.AddNew
                rst(vField) = NewData
            rst.Update
            rst.Close
            Append2Table = acDataErrAdded
        End If
    End If

Exit_Append2Table:
    Set rst = Nothing
    Exit Function

Err_Append2Table:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbInformation, "Append2Table()"
    Resume Exit_Append2Table
End Function

[June7]

When you say 'DB', do you mean table?

Don't understand table relationships nor purpose of tables. CoursePack is a 'lookup' table of courses? What is tblCourse for?

If user types in a course name not in combobox list, you want to allow this to be added to CoursePack? This is an approach I've never seen.

Is CourseID an autonumber field?

Variable vField is set to the combobox ControlSource ([CourseID]) but don't you need the text field [Course]?

Why is the Dim statement for rst incomplete? That should trigger compile error.