Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2011
    Posts
    47

    Unanswered: Passing calculations from one from to another?

    I refer you to one of my previous posts:

    http://www.dbforums.com/microsoft-ac...t-problem.html

    I have decided to create a pop-up form where they disbursement can be calculated(i.e. they can select the right amount of vat and calculate it) and then be sent to the continuous form as a separate entry(to the original form).

    How would i go about doing this? Is there a better way of doing this?

    Thanks for any help in advance!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    That could be a solution. What kind of problem do you have with it?
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd argue the better way to do that is to store VAT codes and associate VAT codes with a specific item.
    see:- http://www.dbforums.com/microsoft-ac...t-problem.html

    you can pass data to and from forms using two styles
    'push' when a form or report sends data to another
    'pull' when a form or report retrieves data from another

    to push data
    forms!myreceivingform!myreceivingcontrol.value = mysendingcontrol.value
    to pull data
    myreceivingcontrol.value = forms!mysendingform!mysendingcontrol.value

    If ytou arer reluctant to do the job properly then I'd guess you'd want to use a push approach in your modal forms on close event
    ..I'm guessing you'd have 4 boxes
    VATtable goods & services
    non VATtable goods & services
    applicable VAT rate
    Calcualted VAT
    two buttons (Accept & Cancel)
    possibly a third to calculate the VAT

    some code that validates the data (ie range checks, makes certain all values are sane, no negatives, VAT rate >= 0 and so on.
    some code that decides what to do when the user presses the buttons
    if accept then push the data back to the calling form
    if cancel pushes back 0

    unless you are storing the data in the parent table then you are making a rod for your own back, especially if HMRC do come calling.. they have very strogn views as to the right way to handle VAT.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jul 2011
    Posts
    47
    That could be a solution. What kind of problem do you have with it?
    The problem I have is that I need a way to calculate different rates of VAT on disbursements. I am thinking of creating a modal form where the user can enter in the disbursement, select the VAT % and then VAT and the total including VAT will be produced.

    I am aware that I cannot store calculate fields, but I am planning on "sending" the VAT rate applicable to the back to the original form and the amount of disbursement so these fields can be stored for a later calculation.

    (Sinndo, if you can recall the SQL code you helped me with earlier. I need these fields there. I am planning to store this information in the tblegalaidledger for use to produce a a summary sheet from all the other tables.).


    I'd argue the better way to do that is to store VAT codes and associate VAT codes with a specific item.
    Healdem, I appreciate your input but I cannot do that in this instance due to the fact that a disbursement can be anything from a fee paid to the other side or a travelling cost. I cannot assign VAT codes because I cannot "limit" what a disbursement is.

    Any other ideas? Of course I want to do this the proper way. I am not experienced at this at all, so my ideas are limited to what I have been taught in college(this is not a coursework project or anything of that sort).

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by rasghar View Post
    I am aware that I cannot store calculate fields
    Nothing can refrain you from doing so, however its against the rules in a normalized database and it's considered as a bad practice.

    Quote Originally Posted by rasghar View Post
    I am planning on "sending" the VAT rate applicable to the back to the original form and the amount of disbursement so these fields can be stored for a later calculation.
    Is the problem you're facing about establishing a communication link between the modal form and the (main) form opening it? Or, if you prefer, is it about passing the value(s) entered in the modal form back to the main form?
    Have a nice day!

  6. #6
    Join Date
    Jul 2011
    Posts
    47
    I'm confused on how to achieve this, I've made the modal form.
    To enter a disbursement, I've added a button which will open the modal form.

    I'm not to sure what to do from there onwards.

    I will need to make a link between the two forms based on primary/foreign keys.

    Then i'm stuck.

    Can you advise me what to do next please?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Passing data from a form (let's call it "Frm_Parent") to another form (let's call it "Frm_Child") that is open as modal is quite easy.

    1. If both forms share some part of a data set (e.g. Primary Key/Foreign Key, or any other common element of information) you can pass a criteria when you open the form "Frm_Child". This criteria, which is named "WhereCondition" in Access, is similar to the "WHERE" clause of a SQL statement without the word "WHERE". For instance:
    Code:
    "RowID = " & Me.RowID
    This criteria is the (optional) fourth parameter of the DoCmd.OpenForm command.

    a) In the class module of "Frm_Parent":
    Code:
    Private Sub Command_OpenChildForm_Click()
    
        Dim strChildFormName As String
        Dim strCriteria As String
        
        strChildFormName = "Frm_Child"
        strCriteria = "RowID = " & Me.Text_RowID.Value
        DoCmd.OpenForm strChildFormName, , , strCriteria, , acDialog
        
    End Sub
    If the criteria is valid, Access automatically affects it to the Filter property of "Frm_Child", which we can easily verify (see the attached picture Frm_Child001.jpg).

    b) In the class module of "Frm_Child":
    Code:
    Private Sub Form_Load()
    '
    ' This is the Form_Load event handler of the form Frm_Child.
    '
        MsgBox "Filter: " & Me.Filter, vbInformation, "Frm_Child"
        
    End Sub
    Notice that the criteria does not need to uniquely identify a record in the data set of "Frm_Child". If there are more than one row matching the criteria, the row set of "Frm_Child" will have several rows (i.e. several records, see the attached picture Frm_Child002.jpg):
    Code:
    Private Sub Command_OpenChildForm_Click()
    
        Dim strChildFormName As String
        Dim strCriteria As String
        
        strChildFormName = "Frm_Child"
        strCriteria = "Engineer ='Acquaviva'"
        DoCmd.OpenForm strChildFormName, , , strCriteria, , acDialog
        
    End Sub

    2. If there is no common data between both forms, or if "Frm_Child" is not bound to any data set, you can use the (optional) seventh parameter of the DoCmd.OpenForm command. This parameter, named "OpenArgs" in Access, can be anything you want but must be a String or a Variant of String type.

    Unfortunately, Access does not provide any automatic mechanism to use this parameter and it's up to you to process it (i.e. you must write code for using it).

    Let's rebuild the equivalent mechanism as the one described is my first example with this "OpenArgs" parameter instead of using the "WhereCondition" parameter.

    a) In the class module of "Frm_Parent":
    Code:
    Private Sub Command_OpenChildForm_Click()
    
        Dim strChildFormName As String
        Dim strCriteria As String
    
        strChildFormName = "Frm_Child"
        strCriteria = "Engineer ='Acquaviva'"
        DoCmd.OpenForm strChildFormName, , , , , acDialog, strCriteria
    
    End Sub
    b) In the class module of "Frm_Child":
    Code:
    Private Sub Form_Load()
    '
    ' This is the Form_Load event handler of the form Frm_Child.
    '
        If Not IsNull(Me.OpenArgs) Then
            Me.Filter = OpenArgs
            Me.FilterOn = True
        End If
        MsgBox "Filter: " & Me.Filter & " (" & Me.Recordset.RecordCount & " records)", vbInformation, "Frm_Child"
        
    End Sub
    This, of course, yields the same message box as the one in the attached picture Frm_Child002.jpg.

    Notice that the "OpenArgs" parameter can be used for a totally different purpose, and also that both parameters ("WhereCondition" and "OpenArgs") can be used simultaneously, as in this example:

    a) In the class module of "Frm_Parent":
    Code:
    Private Sub Command_OpenChildForm_Click()
    
        Dim strChildFormName As String
        Dim strCriteria As String
        Dim strChildFormTitle As String
    
        strChildFormName = "Frm_Child"
        strCriteria = "Engineer = 'Acquaviva'"
        strChildFormTitle = "The title of this form was passed using the Openargs parameter."
        DoCmd.OpenForm strChildFormName, , , strCriteria, , acDialog, strChildFormTitle
    End Sub
    b) In the class module of "Frm_Child":
    Code:
    Private Sub Form_Open(Cancel As Integer)
    '
    ' This is the Form_Open event handler of the form Frm_Child.
    '
        If Not IsNull(Me.OpenArgs) Then Me.Caption = OpenArgs
    
    End Sub
    
    Private Sub Form_Load()
    '
    ' This is the Form_Load event handler of the form Frm_Child.
    '
        MsgBox "Filter: " & Me.Filter & " (" & Me.Recordset.RecordCount & " records)", vbInformation, "Frm_Child"
        
    End Sub
    This time, the message box in the attached picture Frm_Child002.jpg is displayed and the Title Bar of "Frm_Child" looks like in the attached picture Frm_Child003.jpg.

    If you need to pass several values to the child form, you can build a string list in the parent form and parse it when the child form is open:

    a) In the class module of "Frm_Parent":
    Code:
    Private Sub Command_OpenChildForm_Click()
    
        Dim strChildFormName As String
        Dim strArgumentList As String
    
        strChildFormName = "Frm_Child2"
        strArgumentList = "Argument #1;Argument #2;Argument #3"
        DoCmd.OpenForm strChildFormName, , , , , acDialog, strArgumentList
    
    End Sub
    b) In the class module of "Frm_Child2":
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        Dim varArguments As Variant
        
        If Not IsNull(Me.OpenArgs) Then
            varArguments = Split(Me.OpenArgs, ";")
            Me.Text_Arg1 = varArguments(0)
            If UBound(varArguments) >= 1 Then Me.Text_Arg2 = varArguments(1)
            If UBound(varArguments) >= 2 Then Me.Text_Arg3 = varArguments(2)
        End If
      
    End Sub
    When "Frm_Child2" is opened it looks like shown in the attached picture Frm_Child004.jpg.

    Using this technique, you can even mimic named parameters. This allows you to pass them in any order or omit any of them, such as in this example:
    a) In the class module of "Frm_Parent":
    Code:
    Private Sub Command_OpenChildForm_Click()
    
        Dim strChildFormName As String
        Dim strArgumentList As String
    
        strChildFormName = "Frm_Child2"
        strArgumentList = "Arg1=Argument #1;Arg3=Argument #3;Arg2=Argument #2"
        DoCmd.OpenForm strChildFormName, , , , , acDialog, strArgumentList
    End Sub
    b) In the class module of "Frm_Child2":
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        Dim varArguments As Variant
        Dim varArgument As Variant
        Dim i As Long
        
        If Not IsNull(Me.OpenArgs) Then
            varArguments = Split(Me.OpenArgs, ";")
            For i = 0 To UBound(varArguments)
                varArgument = Split(varArguments(i), "=")
                Select Case varArgument(0)
                    Case "Arg1":    Me.Text_Arg1 = varArgument(1)
                    Case "Arg2":    Me.Text_Arg2 = varArgument(1)
                    Case "Arg3":    Me.Text_Arg3 = varArgument(1)
                    Case Else:      MsgBox "Unknown named argument: " & varArgument(0), vbExclamation, "Frm_Child2"
                End Select
            Next i
        End If
        
    End Sub

    Just for memory, I'll mention that you can also declare a Global (or Public) variable that both forms can have access to and use. This is my least favourite method. Global or public variables are accessible from anywhere in the application and its very easy to inadvertantly change their value in a portion of code that's not supposed to handle them, this is the reason why I try not to use them, except in circumstances where there is no other option, which is very rare.

    This enumeration is not exhaustive and there are other methods that can be used, but the ones described here are those I usually implement.

    In a second post, we'll examine how it's possible to send data from 'Frm_Child' back to 'Frm_Parent' and how we can create a bidirectional communication channel between both forms.
    Attached Thumbnails Attached Thumbnails Frm_Child001.jpg   Frm_Child002.jpg   Frm_Child003.jpg   Frm_Child004.jpg  
    Have a nice day!

  8. #8
    Join Date
    Jul 2011
    Posts
    47
    Quote Originally Posted by Sinndho View Post
    Passing data from a form (let's call it "Frm_Parent") to another form (let's call it "Frm_Child") that is open as modal is quite easy.

    1. If both forms share some part of a data set (e.g. Primary Key/Foreign Key, or any other common element of information) you can pass a criteria when you open the form "Frm_Child". This criteria, which is named "WhereCondition" in Access, is similar to the "WHERE" clause of a SQL statement without the word "WHERE". For instance:
    Code:
    "RowID = " & Me.RowID
    This criteria is the (optional) fourth parameter of the DoCmd.OpenForm command.

    a) In the class module of "Frm_Parent":
    Code:
    Private Sub Command_OpenChildForm_Click()
    
        Dim strChildFormName As String
        Dim strCriteria As String
        
        strChildFormName = "Frm_Child"
        strCriteria = "RowID = " & Me.Text_RowID.Value
        DoCmd.OpenForm strChildFormName, , , strCriteria, , acDialog
        
    End Sub
    If the criteria is valid, Access automatically affects it to the Filter property of "Frm_Child", which we can easily verify (see the attached picture Frm_Child001.jpg).

    b) In the class module of "Frm_Child":
    Code:
    Private Sub Form_Load()
    '
    ' This is the Form_Load event handler of the form Frm_Child.
    '
        MsgBox "Filter: " & Me.Filter, vbInformation, "Frm_Child"
        
    End Sub
    Notice that the criteria does not need to uniquely identify a record in the data set of "Frm_Child". If there are more than one row matching the criteria, the row set of "Frm_Child" will have several rows (i.e. several records, see the attached picture Frm_Child002.jpg):
    Code:
    Private Sub Command_OpenChildForm_Click()
    
        Dim strChildFormName As String
        Dim strCriteria As String
        
        strChildFormName = "Frm_Child"
        strCriteria = "Engineer ='Acquaviva'"
        DoCmd.OpenForm strChildFormName, , , strCriteria, , acDialog
        
    End Sub

    2. If there is no common data between both forms, or if "Frm_Child" is not bound to any data set, you can use the (optional) seventh parameter of the DoCmd.OpenForm command. This parameter, named "OpenArgs" in Access, can be anything you want but must be a String or a Variant of String type.

    Unfortunately, Access does not provide any automatic mechanism to use this parameter and it's up to you to process it (i.e. you must write code for using it).

    Let's rebuild the equivalent mechanism as the one described is my first example with this "OpenArgs" parameter instead of using the "WhereCondition" parameter.

    a) In the class module of "Frm_Parent":
    Code:
    Private Sub Command_OpenChildForm_Click()
    
        Dim strChildFormName As String
        Dim strCriteria As String
    
        strChildFormName = "Frm_Child"
        strCriteria = "Engineer ='Acquaviva'"
        DoCmd.OpenForm strChildFormName, , , , , acDialog, strCriteria
    
    End Sub
    b) In the class module of "Frm_Child":
    Code:
    Private Sub Form_Load()
    '
    ' This is the Form_Load event handler of the form Frm_Child.
    '
        If Not IsNull(Me.OpenArgs) Then
            Me.Filter = OpenArgs
            Me.FilterOn = True
        End If
        MsgBox "Filter: " & Me.Filter & " (" & Me.Recordset.RecordCount & " records)", vbInformation, "Frm_Child"
        
    End Sub
    This, of course, yields the same message box as the one in the attached picture Frm_Child002.jpg.

    Notice that the "OpenArgs" parameter can be used for a totally different purpose, and also that both parameters ("WhereCondition" and "OpenArgs") can be used simultaneously, as in this example:

    a) In the class module of "Frm_Parent":
    Code:
    Private Sub Command_OpenChildForm_Click()
    
        Dim strChildFormName As String
        Dim strCriteria As String
        Dim strChildFormTitle As String
    
        strChildFormName = "Frm_Child"
        strCriteria = "Engineer = 'Acquaviva'"
        strChildFormTitle = "The title of this form was passed using the Openargs parameter."
        DoCmd.OpenForm strChildFormName, , , strCriteria, , acDialog, strChildFormTitle
    End Sub
    b) In the class module of "Frm_Child":
    Code:
    Private Sub Form_Open(Cancel As Integer)
    '
    ' This is the Form_Open event handler of the form Frm_Child.
    '
        If Not IsNull(Me.OpenArgs) Then Me.Caption = OpenArgs
    
    End Sub
    
    Private Sub Form_Load()
    '
    ' This is the Form_Load event handler of the form Frm_Child.
    '
        MsgBox "Filter: " & Me.Filter & " (" & Me.Recordset.RecordCount & " records)", vbInformation, "Frm_Child"
        
    End Sub
    This time, the message box in the attached picture Frm_Child002.jpg is displayed and the Title Bar of "Frm_Child" looks like in the attached picture Frm_Child003.jpg.

    If you need to pass several values to the child form, you can build a string list in the parent form and parse it when the child form is open:

    a) In the class module of "Frm_Parent":
    Code:
    Private Sub Command_OpenChildForm_Click()
    
        Dim strChildFormName As String
        Dim strArgumentList As String
    
        strChildFormName = "Frm_Child2"
        strArgumentList = "Argument #1;Argument #2;Argument #3"
        DoCmd.OpenForm strChildFormName, , , , , acDialog, strArgumentList
    
    End Sub
    b) In the class module of "Frm_Child2":
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        Dim varArguments As Variant
        
        If Not IsNull(Me.OpenArgs) Then
            varArguments = Split(Me.OpenArgs, ";")
            Me.Text_Arg1 = varArguments(0)
            If UBound(varArguments) >= 1 Then Me.Text_Arg2 = varArguments(1)
            If UBound(varArguments) >= 2 Then Me.Text_Arg3 = varArguments(2)
        End If
      
    End Sub
    When "Frm_Child2" is opened it looks like shown in the attached picture Frm_Child004.jpg.

    Using this technique, you can even mimic named parameters. This allows you to pass them in any order or omit any of them, such as in this example:
    a) In the class module of "Frm_Parent":
    Code:
    Private Sub Command_OpenChildForm_Click()
    
        Dim strChildFormName As String
        Dim strArgumentList As String
    
        strChildFormName = "Frm_Child2"
        strArgumentList = "Arg1=Argument #1;Arg3=Argument #3;Arg2=Argument #2"
        DoCmd.OpenForm strChildFormName, , , , , acDialog, strArgumentList
    End Sub
    b) In the class module of "Frm_Child2":
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        Dim varArguments As Variant
        Dim varArgument As Variant
        Dim i As Long
        
        If Not IsNull(Me.OpenArgs) Then
            varArguments = Split(Me.OpenArgs, ";")
            For i = 0 To UBound(varArguments)
                varArgument = Split(varArguments(i), "=")
                Select Case varArgument(0)
                    Case "Arg1":    Me.Text_Arg1 = varArgument(1)
                    Case "Arg2":    Me.Text_Arg2 = varArgument(1)
                    Case "Arg3":    Me.Text_Arg3 = varArgument(1)
                    Case Else:      MsgBox "Unknown named argument: " & varArgument(0), vbExclamation, "Frm_Child2"
                End Select
            Next i
        End If
        
    End Sub

    Just for memory, I'll mention that you can also declare a Global (or Public) variable that both forms can have access to and use. This is my least favourite method. Global or public variables are accessible from anywhere in the application and its very easy to inadvertantly change their value in a portion of code that's not supposed to handle them, this is the reason why I try not to use them, except in circumstances where there is no other option, which is very rare.

    This enumeration is not exhaustive and there are other methods that can be used, but the ones described here are those I usually implement.

    In a second post, we'll examine how it's possible to send data from 'Frm_Child' back to 'Frm_Parent' and how we can create a bidirectional communication channel between both forms.

    WOW! Great detail! Thanks Sinndo!
    Very informative! I've learnt a lot from that!!!

    Just for the record, there is a common field between both forms. So I will be using your first method.
    But thanks for such a detailed post, I have learnt a lot!
    Also, I appreciate all the extra detail. It has answered a few questions that I had in the back of my head, now I just need to practice these methods!!

    I'm looking forward to part two!

    Just one quick question, just to confirm:
    Is frm_child is "referring" to the open modal form?

    Again thanks!!
    Last edited by rasghar; 12-25-11 at 13:35.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome !

    Note: "Frm_child" is indeed "referring" to the open modal form, while "Frm_Parent" is the form from which "Frm_child" was being opened.
    Have a nice day!

  10. #10
    Join Date
    Jul 2011
    Posts
    47
    Quote Originally Posted by Sinndho View Post
    You're welcome !

    Note: "Frm_child" is indeed "referring" to the open modal form, while "Frm_Parent" is the form from which "Frm_child" was being opened.
    Hi Sinndho,

    Just a quick question, what if the modal form was open from the parent form? and the information from the modal form was to be sent back to the parent form?

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    another example of pushing and pulling data in between forms
    open the db, open the form frmThatForm
    Attached Files Attached Files
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jul 2011
    Posts
    47
    Quote Originally Posted by healdem View Post
    another example of pushing and pulling data in between forms
    open the db, open the form frmThatForm
    Thanks for the help healdem, I've tried to use your example on my database. But i've got about 20 different error messages, i cannot get it to work.

    I've uploaded a sample from my database. The bit i need help on(could you or Sinndo please look at it?).

    I need help on the following:


    Once the form has loaded please go to :
    “legal aid” tab
    “County court” tab
    And then click “add disbursement”
    What I need help on:
    I need help on:
    Linking the modal form to the parent ( I think I’ve done it with the aid of Sinndo’s advice/help)
    Sending the value of disbursement and Vat (rate) back to the parent in the subform(i.e. frmlegalaidledger213 in frmprofile.legalaidtab.frmlegalaidledger213)
    Saving the entries(on both forms). and if possible refreshing the parent form to show the new entries?

    Please note that all the forms are in a continuous form.
    Also, please note that on frmlegalaidledger213 the VAT field is there but it is hidden.


    Thanks for your time and help!
    Attached Files Attached Files

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    For returning data from a form ("Frm_CustInputBox") that is open as modal to another form ("Frm_Parent"), we shall examine several cases.

    1. As a consequence of some event (usually an action of the user), the form "Frm_CustInputBox" (modal) sends data back to the form "Frm_Parent" then is closed. As an example, let's imagine an input box with 2 textboxes: one for entering the first name and one for entering the last name of a person (see the attached image file CustInputBox_001.jpg).

    We can declare two public (i.e. accessible from another module) properties: 'FirstName' and 'LastName' in the class module of the form 'Frm_Parent', like this:
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_strFirstName As String
    Private m_strLastName As String
    
    Public Property Get FirstName() As String
    
        FirstName = m_strFirstName
    
    End Property
    
    Public Property Let FirstName(ByVal Value As String)
    
        m_strFirstName = Value
    
    End Property
    
    Public Property Get LastName() As String
    
        LastName = m_strLastName
    
    End Property
    
    Public Property Let LastName(ByVal Value As String)
    
        m_strLastName = Value
    
    End Property
    If the "identity" of both forms is known and never changes, i.e. a form named "Form_Parent" always opens a form named "Frm_CustInputBox" (modal child) that always sends data back to the form named "Form_Parent", the code in the class modules of both forms is quite simple:

    a) In the class module of "Frm_Parent":
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_strFirstName As String
    Private m_strLastName As String
    
    Private Sub Command_OpenChildForm_Click()
    
        Const c_Msg As String = "Values received from Frm_CustInPutBox: " & vbNewLine & "First name: " & "@F" & vbNewLine & "Last name: " & "@L"
        
        Dim strChildFormName As String
        
        strChildFormName = "Frm_CustInputBox"
        DoCmd.OpenForm strChildFormName, , , , , acDialog
        MsgBox Replace(Replace(c_Msg, "@F", Me.FirstName), "@L", Me.LastName), vbInformation, "Returned values"
        
    End Sub
    
    Public Property Get FirstName() As String
    
        FirstName = m_strFirstName
    
    End Property
    
    Public Property Let FirstName(ByVal Value As String)
    
        m_strFirstName = Value
    
    End Property
    
    Public Property Get LastName() As String
    
        LastName = m_strLastName
    
    End Property
    
    Public Property Let LastName(ByVal Value As String)
    
        m_strLastName = Value
    
    End Property
    b) In the class module of "Frm_CustInputBox":
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Command_Cancel_Click()
    
        DoCmd.Close acForm, Me.Name
    
    End Sub
    
    Private Sub Command_OK_Click()
    
        If IsOpen("Frm_Parent") = True Then
            Forms("Frm_Parent").FirstName = Me.Text_FirstName.Value
            Forms("Frm_Parent").LastName = Me.Text_LastName.Value
        End If
        DoCmd.Close acForm, Me.Name
        
    End Sub
    
    Private Sub Form_Load()
    
        SetOKStatus
        
    End Sub
    
    Private Function IsOpen(ByVal FormName As String) As Boolean
    
        Dim frm As Form
        
        For Each frm In Application.Forms
            If frm.Name = FormName Then
                IsOpen = True
                Exit For
            End If
        Next frm
        Set frm = Nothing
        
    End Function
    
    Private Sub SetOKStatus()
    
        Dim ctl As Control
        Dim booEmpty As Boolean
        
        For Each ctl In Me.Controls
            If ctl.ControlType = acTextBox Then
                If Nz(ctl.Value, "") = "" Then
                    booEmpty = True
                    Exit For
                End If
            End If
        Next ctl
        Me.Command_OK.Enabled = Not booEmpty
        
    End Sub
    
    Private Sub Text_FirstName_AfterUpdate()
    
        SetOKStatus
        
    End Sub
    
    Private Sub Text_LastName_AfterUpdate()
    
        SetOKStatus
        
    End Sub
    2. If several parent forms can open a single child form, we can transmit the name of the parent form when opening the child form:

    a) In the class module of "Frm_Parent", we only need to change a single line:
    Code:
    Private Sub Command_OpenChildForm_Click()
    
        Const c_Msg As String = "Values received from Frm_CustInPutBox: " & vbNewLine & "First name: " & "@F" & vbNewLine 
    
    & "Last name: " & "@L"
        
        Dim strChildFormName As String
        
        strChildFormName = "Frm_CustInputBox"
        DoCmd.OpenForm strChildFormName, , , , , acDialog, Me.Name
        MsgBox Replace(Replace(c_Msg, "@F", Me.FirstName), "@L", Me.LastName), vbInformation, "Returned values"
        
    End Sub
    b) The class module of "Frm_CustInputBox" needs to be modified a little more, but not much:
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_strParent As String
    
    Private Sub Form_Open(Cancel As Integer)
    
        If Not IsNull(Me.OpenArgs) Then
            If IsOpen(Me.OpenArgs) Then m_strParent = Me.OpenArgs
        End If
    
    End Sub
    
    Private Sub Command_OK_Click()
    
        If Len(m_strParent) > 0 Then
            Forms(m_strParent).FirstName = Me.Text_FirstName.Value
            Forms(m_strParent).LastName = Me.Text_LastName.Value
        End If
        DoCmd.Close acForm, Me.Name
        
    End Sub
    (To be continued)
    Attached Thumbnails Attached Thumbnails CustInputBox_001.jpg  
    Have a nice day!

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    3. The last example will demonstrate a rather seldom used feature of the VB/VBA language which is declaring custom Events.

    Events are used extensively in Access: every control uses a set of predefined events to communicate with its "parent" form (i.e. the form on which it is located).

    We can define our own events and work with them by using three keywords:
    - We use the Event instruction to declare a custom event in the module where this event will be raised:
    Code:
    (Public|Private) Event EventName (argument list)
    - We use the RaiseEvent instruction to actually raise (trigger) an event from the module where this event was declared:
    Code:
    RaiseEvent EventName (argument list)
    - We declare an instance of the object (Form, Class Module, ...) raising the event with the WithEvents keyword in the module that will receive the event and process it:
    Code:
    (Public|Private) WithEvents VarName As ObjectType
    We can create an event handler, that is a Sub procedure that will be activated when the event is raised, in the same way we create event handlers for standard events in forms, modules and their controls (Form_Current, AfterUpdate, BeforeInsert, etc.). In summary:

    a) In the (class) module where the event will be raised:
    Code:
    Option Compare Database
    Option Explicit
    '
    ' Declare the custom event.
    '
    Public Event MyEvent(ByVal Arg1 As String, ByVal Arg2 As Long, ByVal Arg3 As Variant)
    
    '
    ' Raise the event.
    '
    Private Sub RaiseMyEvent()
    
        If SomeCondition = True Then    
            RaiseEvent MyEvent(Form.Name, RowId, Null)
        End If
    
    End Function
    b) In the (class) module where the event will be received and processed:
    Code:
    Option Compare Database
    Option Explicit
    '
    ' Declare the member variable of the proper type.
    '
    Private WithEvents m_clsEvt As Cls_MyClassWithEvents
    
    Private Sub Initialize()
    '
    ' Create the instance of the class.
    '
        Set m_clsEvt = New Cls_MyClassWithEvents
    
    End Sub
    
    Public Sub m_clsEvt_MyEvent(ByVal Arg1 As String, ByVal Arg2 As Long, ByVal Arg3 As Variant)
    '
    ' Process the received event.
    '
        MsgBox "Event MyEvent was received from: " & Arg1, vbInformation, "New Event"
    
    End Sub
    The following example uses this technique. For several reasons that would be too long to explain here, an independant class "Cls_CustInputBox" will be used. An instance of this class is "attached" to the form "Frm_CustInputBox". Both forms ("Form_Parent" and "Frm_CustInputBox") have a reference to this class. This mechanism uses a concept named "Dynamic External Event Processing" ("DEEP" for short). For more information about this concept, see: http://smsconsulting.spb****/shamil_...s/deepcnpt.htm.

    Notice that the form "Frm_CustInputBox" does not need to be modal in this case and also that it can remain open after an event is raised by its associated class. Both forms can work asynchronuously while being able to communicate.

    a) In the class module of "Frm_Parent":
    Code:
    Option Compare Database
    Option Explicit
    
    Private WithEvents m_clsCustInputBox As Cls_CustInputBox
    Private m_strChildFormName As String
    
    Private Sub Command_OpenChildForm_Click()
    
        m_strChildFormName = "Frm_CustInputBox"
        DoCmd.OpenForm m_strChildFormName, , , , , acDialog, Me.Name
        
    End Sub
    
    Public Function Hook(ByVal Child As Object)
    
        If Child.FormName = "Frm_CustInputBox" Then Set m_clsCustInputBox = Child
    
    End Function
    
    Public Sub m_clsCustInputBox_CallBack(ByVal Sender As String, ByVal Message As String, ByVal Extra As Variant)
    
        Const c_Msg As String = "Message received from Frm_CustInPutBox: " & vbNewLine & "First name: " & "@F" & vbNewLine & "Last name: " & "@L"
        
        Dim strMessage As String
        
        strMessage = "Message received from " & m_clsCustInputBox.FormName & ": " & Message
        If IsArray(Extra) Then strMessage = strMessage & vbNewLine & "- First name: " & Extra(0) & vbNewLine & "- Last name: " & Extra(1)
        MsgBox strMessage, vbInformation, "Returned values"
        Set m_clsCustInputBox = Nothing
        DoCmd.Close acForm, m_strChildFormName
        
    End Sub
    b) In the class module of "Frm_CustInputBox":
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_clsCustInputBox As Cls_CustInputBox
    
    Private Function CommandClick()
    
        m_clsCustInputBox.ParentCallBack Me.ActiveControl.Name
        
    End Function
    
    Private Function DataChanged()
    
        m_clsCustInputBox.SetOKStatus
        
    End Function
    
    Private Sub Form_Open(Cancel As Integer)
    
        Set m_clsCustInputBox = New Cls_CustInputBox
        m_clsCustInputBox.DEEP_Attach Me
        
    End Sub
    c) In the independant Class Module "Cls_CustInputBox
    Code:
    Option Compare Database
    Option Explicit
    '
    ' Declare a custom event.
    '
    Public Event CallBack(ByVal Sender As String, ByVal Message As String, ByVal Extra As Variant)
    '
    ' Declare a member variable for the attached form.
    '
    Private WithEvents Form As Form
    
    Public Sub DEEP_Attach(ByRef frm As Form)
    
        Dim strParentName As String
        Dim ctl As Control
        '
        ' Initialize the handle to the attached form.
        '
        Set Form = frm
        '
        ' Initialize the event handlers for the controls of the attached form.
        '
        For Each ctl In Form.Controls
            If ctl.ControlType = acTextBox Then
                ctl.AfterUpdate = "=DataChanged()"
            ElseIf ctl.ControlType = acCommandButton Then
                ctl.OnClick = "=CommandClick()"
            End If
        Next ctl
        SetOKStatus
        '
        ' Register ("Hook") this object to the "parent" form.
        '
        If Not IsNull(Form.OpenArgs) Then strParentName = Form.OpenArgs
        If IsFormOpen(strParentName) Then Forms(strParentName).Hook Me
    
    End Sub
    
    Public Property Get FormName() As String
    '
    ' Returns the name of the attached form.
    '
        FormName = Form.Name
        
    End Property
    
    Private Function IsFormOpen(ByVal FormName As String) As Boolean
    '
    ' When a form is open as an independant form (not a subform),
    ' it is added to the Forms collection of the Application object.
    ' It is then possible to know if a form is open by scanning this collection.
    '
        Dim frm As Form
        
        For Each frm In Application.Forms
            If frm.Name = FormName Then
                IsFormOpen = True
                Exit For
            End If
        Next frm
        Set frm = Nothing
        
    End Function
    
    Public Sub ParentCallBack(CommandName As String)
    '
    ' Raise the custom event to be processed by the parent of the attached form.
    '
        Dim strMessage As String
        Dim varRetVal As Variant
        
        If CommandName = "Command_Cancel" Then
            strMessage = "No Values"
            varRetVal = Null
        ElseIf CommandName = "Command_OK" Then
            strMessage = "New Values"
            varRetVal = Array(Form.Text_FirstName.Value, Form.Text_LastName.Value)
        End If
        RaiseEvent CallBack(Form.Name, strMessage, varRetVal)
    
    End Sub
    
    Public Sub SetOKStatus()
    '
    ' Enable the OK button of the attached form
    ' only if every textbox is not empty.
    '
        Dim ctl As Control
        Dim booEmpty As Boolean
        
        For Each ctl In Form.Controls
            If ctl.ControlType = acTextBox Then
                If Nz(ctl.Value, "") = "" Then
                    booEmpty = True
                    Exit For
                End If
            End If
        Next ctl
        Form.Command_OK.Enabled = Not booEmpty
        
    End Sub
    Attached Thumbnails Attached Thumbnails CustInputBox_001.jpg  
    Have a nice day!

  15. #15
    Join Date
    Jul 2011
    Posts
    47
    First of all

    THANK YOU SINNDHO for such a detailed response.

    Secondly, thank-you for all the information. It is not excess information as I can learn from it and call on it later if I ever need it! Thanks again!

    Thirdly, please ignore my last post. I want to have ago at doing this and see if I can get it working. If I can't, I want to see what I'm doing wrong rather than having it put on a plate for me!

    Thanks!!! really appreciate it!

Posting Permissions

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