Not In List With a Pop-Up Form and SubForm

Steve R.

Retired
Local time
Today, 16:43
Joined
Jul 5, 2006
Messages
5,439
I have a mainform which includes a subfrom that lists fishery management plans. Since the list of plans is long, I have a combobox that lists them. In the event a new plan has to be added a pop-up form is triggered through the not-in-list event. After entering the required data in the pop-up form the combobox is requeried (acDataErrAdded) and the subform (child6) is requeried. Dcount is used to verify that the table was actually updated.

Code in Main Form triggered by the not-in-list event.
Code:
Private Sub Combo12_NotInList(NewData As String, response As Integer)
    Dim stDocName As String    
    stDocName = "NMFSFisheryConfirmation_PopUP"
    If IsNull(NewData) Or Trim(NewData) = "" Then '#1
            Rem error condition
        Else '#1
            Rem open pop-up to enter new data. bolResponse is a GLOBAL variable defined in a module
            DoCmd.OpenForm stDocName, , , , , acDialog, NewData
            Rem code below is executed after the pop-up form closes.
            If bolResponse Then '#2
                    Rem data succesfully added. Combobox and Subform are requeried 
                    response = acDataErrAdded
                    Me.Child6.Requery
                Else '#2
                    Rem operation canceled or data failed to be added
                    response = acDataErrContinue
                End If '#2
        End If '#1
End Sub

Code in Pop-Up Form. Command6 executes the save operation. There is also a cancel button.
Code:
Private Sub Command6_Click()
    Rem save and close
    If IsNull(Me.Combo3) Or Trim(Me.Combo3) = "" Then
            Rem error condition bolResponse is a GLOBAL variable defined in a module
            bolResponse = False
        Else
            Rem add data to the table
            Dim NMFSRS As DAO.Recordset
            Dim intCount As Integer
            Set NMFSRS = CurrentDb.OpenRecordset("Select * from NMFSPlanList", dbOpenDynaset, dbAppendOnly + dbSeeChanges)
            NMFSRS.AddNew
                NMFSRS!NMFSregionnum = Me.Combo3
                NMFSRS!NMFSFisheryName = Me.OpenArgs
            NMFSRS.Update
            NMFSRS.Bookmark = NMFSRS.LastModified
            intCount = DCount("NMFSFisheryName", "NMFSPlanList", "NMFSPlanIDNUM = " & NMFSRS!NMFSPlanIDnum)
            If intCount = 1 Then bolResponse = True Else bolResponse = False
            Rem code using acDataErrContinue and acDataERRAdded is (must be) in the underlying form.
            NMFSRS.Close
            Set NMFSRS = Nothing
            DoCmd.Close
        End If
End Sub

Also see an earlier post by sredworb using the not-in-list event.
 

Users who are viewing this thread

Back
Top Bottom