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 in Pop-Up Form. Command6 executes the save operation. There is also a cancel button.
Also see an earlier post by sredworb using the not-in-list event.
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.