Adding Combo Box Value

[isorders]

I have a NotInList issue.
I want the value to be added to the list...which it is and then automatically filled in.
So where it says ctl = Empty, I want it to say ctl = NewData.
The problem is that the bound column is an autonumber, so NewData may be Bob but the unique TestID for Bob is 5.
How do I get the 5 to fill in?

I have this working on another form that opens a second form to enter the new data and then closes and adds the new value into the original form using OpenArgs. This works great, but I don't really understand why or how it's working and I can't get it to work if I add the new values from the original form as in the example below.


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

Dim sql As String
Dim RS As Recordset, db As Database
Dim ctl As Control
Set ctl = Me.TestID
Response = acDataErrContinue

If MsgBox(UCase(NewData) & " is not a listed Test. Would you like to add it now?", vbYesNo + vbQuestion + vbDefaultButton1, "Catagory not found...&quot = vbYes Then
Set db = CurrentDb()
sql = "Select Test FROM stblLabTests"
Set RS = db.OpenRecordset(sql, dbOpenDynaset)
RS.AddNew
RS!Test = NewData
RS.Update
RS.Close
ctl = Empty
ctl.Requery
ctl = NewData - Gives error (needs the unique TestID and not the TestName)
Else
ctl.Undo
End If

End Sub


Thanks

 

[isorders]

I figured out a solution.
I added a second SQL statement to find the TestID where Test = NewData and it worked.


Set db = CurrentDb()
sql = "Select Test FROM stblLabTests"
sqlNewData = "Select TestID FROM stblLabTests WHERE Test ='" & NewData & "';"
Set RS = db.OpenRecordset(sql, dbOpenDynaset)
RS.AddNew
RS!Test = NewData
RS.Update
RS.Close
ctl = Empty
ctl.Requery

Set RS = db.OpenRecordset(sqlNewData, dbOpenDynaset)
Me.TestID = RS!TestID
RS.Close