onNotInList combo ->> open form x

[carlchapman]

Sorry to bug you all again, but i have a little tweak i want to make to one of my forms. I have used a not in list event to call this vba ->

Private Sub jobTitId_NotInList(NewData As String, Response As Integer)
On Error GoTo jobTitId_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The type " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Time Saver")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblJobTit([jobTit]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
MsgBox "The new job title has been added to the list." _
, vbInformation, "Time Saver"
Response = acDataErrAdded
Else
MsgBox "Please choose a job title from the list." _
, vbInformation, "Time Saver"
Response = acDataErrContinue
'Reset the combo box.
Me.jobTitId = ""
End If
jobTitId_NotInList_Exit:
Exit Sub
jobTitId_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume jobTitId_NotInList_Exit
End Sub

For combos where i want to add to the table this works fine. No problems with this code. But instead of adding to a table, i am trying to open another form. If possible (i cnt work with global variables, i do not no enough to do this myself) i wanted to place the not in list into a .temp and then issert it into a field on the other form.

Any way, here is the modified VBA

Private Sub orgId_NotInList(NewData As String, Response As Integer)
On Error GoTo orgId_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The type " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Time Saver")
If intAnswer = vbYes Then
DoCmd.OpenForm "frmAddOrg"
Response = acDataErrContinue
'Reset the combo box.
Me.orgId = ""
Else
MsgBox "Please choose a organisation from the list." _
, vbInformation, "Time Saver"
Response = acDataErrContinue
'Reset the combo box.
Me.orgId = ""
End If
orgId_NotInList_Exit:
Exit Sub
orgId_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume orgId_NotInList_Exit
End Sub

only problem is i get an error meesage saying "cnt contain null value", can i compress this 1 so i can come back to it in a min?

 

[carlchapman]

the error message i get is that the field cannot contain a null value, well y is it doing it on this one? the other combo boxes do not give me the same problem.... I should add that their i a relation ship here, but i have not completed the form or set it to add the record, so it still should not be prompting this??