Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012
    Posts
    28

    Post Unanswered: Repeat On Not in List event for multiple forms

    Hello,

    I have code in a form named "frmFootball" in the On not in list event that allows me to add a new customer if a customer is not listed in a combobox named "customer".

    What it does is asks me if I want to add a new customer, I click Yes, then a customer form ("frmCustomers") pops up and I enter the customer info. When I click close, I return back to my football form and that new customer is already entered in the customer field.

    Everything works great. However, I want to know if there's a way to let this work on other forms. I have 2 more forms named
    "frmBasketball" and "frmSoccer" that have different information and can't be combined into one form.

    Is it possible to modify the if statement to work with all 3 forms? Only 1 form could be opened at a time.



    Code:
    Private Sub Command15_Click()
    On Error GoTo errline
    
    Const conObjStateClosed = 0
    Const ConDesignView = 0
    Dim IsFormLoaded As Boolean
    DoCmd****nCommand acCmdSaveRecord
    
    'Check if the original data entry form with the list is open
    If SysCmd(acSysCmdGetObjectState, acForm, "frmFootball") <> conObjStateClosed Then
        If Forms("frmFootball").CurrentView <> ConDesignView Then
            IsFormLoaded = True
        End If
    End If
    
    
    If IsFormLoaded = True Then 'Check if the original data entry form with the list is open
        If Forms![frmFootball]![txtHiddenField] = "Addok" Then 'Checks if flag is active
           Forms![frmFootball]![Customer].Requery 'Refresh the list
           Forms![frmFootball]![Customer] = Me.CustRecID 'Set the List control
           Forms![frmFootball]![txtHiddenField] = "AddDone" 'Turn off the Not in list Flag
           DoCmd.Close acForm, "frmCustomers" 'Close the current lookup maintenance for the customers form
           Forms![frmFootball].SetFocus
           Forms![frmFootball]![Customer].SetFocus
        Else
            DoCmd.Close acForm, "frmCustomers"
        End If
    Else
        DoCmd.Close acForm, "frmCustomers"
    End If
    exitline:
    Exit Sub
    errline:
    Select Case Err.Number
    Case 2501
    End Select
    End Sub
    Thanks in advance!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the code used to open frmCustomers ?

    Generally speaking, you need to pass the name of the calling form (i.e. the form that opens frmCustomers) in the OpenArgs argument of frmCustomers. Probably something like (in the class modules of frmFootball, frmBasketball and frmSoccer):
    Code:
    DoCmd.OpenForm "FrmCustomers", , , , , acDialog, Me.Name
    Then, in the class module of FrmCustomers:
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_strFormName As String
    
    Private Function IsFormLoaded(ByVal FormName As String) As Boolean
    
        Dim obj As AccessObject
        
        For Each obj In Application.CurrentProject.AllForms
            If obj.Name = FormName Then
                IsFormLoaded = (obj.CurrentView > acCurViewDesign)
                Exit For
            End If
        Next obj
        
    End Function
    
    Private Sub Command15_Click()
    
        Me.Dirty = False
        DoCmd.Close acForm, Me.Name, acSaveNo
    
    End Sub
    
    Private Sub Form_Close()
    
        If Len(m_strFormName) > 0 Then
            With Forms(m_strFormName)
                If .txtHiddenField = "Addok" Then
                   .Customer.Requery
                   .Customer = Me.CustRecID
                   .txtHiddenField = "AddDone"
                   .SetFocus
                   .Customer.SetFocus
                End If
            End With
        End If
        
    End Sub
    
    
    Private Sub Form_Load()
    
        If Len(Nz(Me.OpenArgs)) > 0 Then
            If IsFormLoaded(Me.OpenArgs) = True Then m_strFormName = Me.OpenArgs
        End If
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Dec 2012
    Posts
    28
    Quote Originally Posted by Sinndho View Post
    What's the code used to open frmCustomers ?
    This is what I have in each of the sports form

    Code:
    Private Sub Customer_NotInList(NewData As String, Response As Integer)
    On Error GoTo errline
    
    Dim MsgBoxAnswer As Variant
    
    Response = acDataErrContinue
    
    'Request Permission
    MsgBoxAnswer = MsgBox("Add New Customer?", vbYesNo, "Add New Customer?")
    
    If MsgBoxAnswer = vbNo Then 'No permission granted to add new customer
        Me.Customer = Null 'Make the list control empty for the time being
        DoCmd.GoToControl "Customer" 'Move the cursor back to the list control
        GoTo exitline
            
    Else 'Persmission granted to add new consumer
            txtHiddenField = "Addok" ' Set the not in list flag for this coded solution
            DoCmd.OpenForm ("frmCustomers") ' Persmission granted to add new consumer
            DoCmd.GoToRecord , , acNewRec 'to a new record
            Forms![frmCustomers]![LastName] = NewData ' fill in the value the user tried to key
            Me.Customer = Null 'Make the list control empty for the time being
            DoCmd.GoToControl "LastName"  ' Move to the next desired field
    End If
    
    exitline:
    Exit Sub
    errline:
    Select Case Err.Number
    Case 2467 'Form variable frmNot_In_List_Form references a form that is not currently open.
    End Select
    
    End Sub

  4. #4
    Join Date
    Dec 2012
    Posts
    28
    I have modified the code in the customers form to read from each of the sports form. However, it works half way.

    For the frmfootball, it works great.
    When I try it on the frmSoccer, it works but it doesn't auto refresh on the spot. I have to exit the form and reopen for the new customer to show up.

    Code:
    Private Sub Command15_Click()
    On Error GoTo errline
    
    Const conObjStateClosed = 0
    Const ConDesignView = 0
    Dim IsFormLoaded As Boolean
    DoCmd****nCommand acCmdSaveRecord
    
    'Check if the original data entry form with the list is open
        If SysCmd(acSysCmdGetObjectState, acForm, "frmFootball") <> conObjStateClosed Then
            If Forms("frmFootball").CurrentView <> ConDesignView Then
               IsFormLoaded = True
             End If
      End If
    
    'Check if the original data entry form with the list is open
        If SysCmd(acSysCmdGetObjectState, acForm, "frmSoccer") <> conObjStateClosed Then
            If Forms("frmSoccer").CurrentView <> ConDesignView Then
              IsFormLoaded = True
             End If
        End If
      
    
    If IsFormLoaded = True Then 'Check if the original data entry form with the list is open
        If Forms![frmFootball]![txtHiddenField] = "Addok" Then 'Checks if flag is active
           Forms![frmFootball]![Customer].Requery 'Refresh the list
           Forms![frmFootball]![Customer] = Me.CustRecID 'Set the List control
           Forms![frmFootball]![txtHiddenField] = "AddDone" 'Turn off the Not in list Flag
           DoCmd.Close acForm, "frmCustomers" 'Close the current lookup maintenance for the customers form
           Forms![frmFootball].SetFocus
           Forms![frmFootball]![Customer].SetFocus
        Else
            DoCmd.Close acForm, "frmCustomers"
        End If
    Else
        DoCmd.Close acForm, "frmCustomers"
    End If    
    
        
         If IsFormLoaded = True Then 'Check if the original data entry form with the list is open
            If Forms![frmSoccer]![txtHiddenField] = "Addok" Then 'See if Not in list flag
               Forms![frmSoccer]![Customer].Requery 'Refresh the list
               Forms![frmSoccer]![Customer] = Me.CustRecID 'Set the List control
               Forms![frmSoccer]![txtHiddenField] = "AddDone" 'Turn off the Not in list Flag
               DoCmd.Close acForm, "frmCustomers" 'Close the current lookup maintenance for the customers form
               Forms![frmSoccer].SetFocus
               Forms![frmSoccer]![Customer].SetFocus
             Else
               DoCmd.Close acForm, "frmCustomers"
          End If
        Else
          DoCmd.Close acForm, "frmCustomers"
        End If

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •