Issue with NotInList (1 Viewer)

[sra2786]

I am having an issue with my NotInList code. I need the NotInList to open a subform("frm_tblSpurList") because I need to update two fields (location and sequence) when a new location record is added.

The subform is called "frm_tblSpurList"
When I select the Save button on frm_tblSpurList, the value is saved to the table but the new value is not populated on the main form.

Can someone help me figure out what I am doing wrong?


Private Sub cboSFLocation_NotInList(NewData As String, Response As Integer)
Dim strMessage As String
Dim strSQL As String
Dim strFindCriteria As String
Dim answer As Variant

Response = acDataErrContinue
Call Location_Not_Found(NewData)
Me.cboSFLocation.Value = NewData

End Sub
Public Sub Location_Not_Found(NewData)
Dim answer As Variant
Dim gbl_exit_name As Boolean
Dim strMessage As String

gbl_exit_name = False

strMessage = "Do you want to add " & NewData & " to the list?"

answer = MsgBox(NewData & " is not an existing location. Do you want to add?", _
vbYesNo, "Add New Location?")

If answer = vbNo Then
Me.cboSFLocation = Null
DoCmd.GoToControl "cboSFLocation"
GoTo exit_it
End If

DoCmd.OpenForm ("frm_tblSpurList")
Form_frm_tblSpurList.strLocation = NewData
Me.cboSFLocation = Null
DoCmd.GoToControl "intSortSequence"

exit_it:

End Sub

Subform (frm_tblSpurList) coding
Private Sub Save_Click()
On Error GoTo err_Save_Click

DoCmd.RunCommand acCmdSaveRecord
Form_frmYardListRecords.cboSFLocation.Requery
DoCmd.Close

Exit_Save_Click:
Exit Sub

err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub

 

[June7]

Please post lengthy code within CODE tags to retain indentation and readability.

Should not have line:

Me.cboSFLocation.Value = NewData

You requery the combobox but you don't set its value afterwards.

Form_frmYardListRecords.cboSFLocation = Me.LocationID

You declare variables in NotInList event then don't use them. Could put the Location_Not_Found code within the event procedure.

 

[arnelgp]

Private Sub cboSFLocation_NotInList(NewData As String, Response As Integer)
    'Dim strMessage As String
    'Dim strSQL As String
    ''Dim strFindCriteria As String
    'Dim answer As Variant
    
    If Location_Not_Found(NewData) Then
        Response = acDataErrAdded
        
    Else
        Response = acDataErrContinue
        
    End If
    
    'Me.cboSFLocation.value = NewData
    
End Sub

Public Function Location_Not_Found(NewData) As Boolean
    Dim answer As Variant
    Dim gbl_exit_name As Boolean
    Dim strMessage As String
    
    gbl_exit_name = False
    
    strMessage = "Do you want to add " & NewData & " to the list?"
    
    answer = MsgBox(NewData & " is not an existing location. Do you want to add?", _
        vbYesNo, "Add New Location?")
    
    If answer = vbNo Then
        Me.cboSFLocation = Null
        DoCmd.GoToControl "cboSFLocation"
        Location_Not_Found = False
        GoTo exit_it
    End If
    
    DoCmd.OpenForm ("frm_tblSpurList")
    Form_frm_tblSpurList.strLocation = NewData
    Me.cboSFLocation = Null
    DoCmd.GoToControl "intSortSequence"
    Location_Not_Found = True
exit_it:

End Function