Create new record in related table from combo box 1

[KornGeek]

Here's the setup:
I have two tables, tblItems and tblDescriptions. They are linked on a field called DescID, which is the primary key of tblDescriptions. On my data entry form where users can create new items, I have a combo box to allow them to select a description. The combo box only displays the description, not the ID. Currently, if the user enters a description not in the table, they get an error message. My customer wants this changed to allow the user to create a new description record if the description is not in the table.

I'm using the NotInList event to create a new record in the tblDescriptions table using SQL. However, instead of the text entered in the combo box, I'm getting the number 0.

Does anybody know how to reference the text that was entered? I believe it gives me the 0 because it doesn't have a DescID to link.

I'm open to other suggestions on how to do this as well.

Thanks in advance.

 

[KornGeek]

I got this working (thanks to a lot of help from previous posts and FAQs)!

Here is my code:

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

Dim temp
DoCmd.SetWarnings 0
temp = MsgBox("Description not found. Create new description?", _
(vbYesNo + vbDefaultButton2 + vbQuestion))
If temp = vbYes Then
DoCmd.RunSQL "INSERT INTO tblDescriptions(ItemTypeName) " _
& "VALUES ('" & DescID.Text & "') ;"
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
DoCmd.SetWarnings 1
End Sub

The key field in my Descriptions table is an autonumber, so I don't need to worry about that. Hope this helps someone else.