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
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