Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Dec 2011
    Posts
    35

    Unanswered: How do i link two forms

    I have to two different forms, however i have a foreign key in one of my other forms however the data doesn't seem to be automatically linked to it. When i created the two forms into one they are linked however, so my question is how can i do the same but in two separate forms ???
    Attached Thumbnails Attached Thumbnails q.png  

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You need to implement a communication mechanism between both forms.

    In this example, there are two Forms: 'Form1' and 'Form2'. They share a common field name: 'SysCounter' which is the primary key for the RecordSource of 'Form1' and the foreign key for the RecordSource of 'Form2'. 'SysCounter' is defined as numeric (Long Integer) in the tables definition.

    In the module of Form1:
    Code:
    Private Sub Form_Current()
    
        Dim frm As Form
        
        For Each frm In Application.Forms
            If frm.Name = "Form2" Then
                frm.Synch Me.SysCounter.value
                Exit For
            End If
        Next frm
        Set frm = Nothing
        
    End Sub
    
    Public Sub Synch(ByVal RowId As Long)
    
        Dim rst As Dao.Recordset
        Dim strCriteria As String
    
        If Me.SysCounter.value <> RowId Then
            strCriteria = "SysCounter = " & RowId
            Set rst = Me.RecordsetClone
            rst.FindFirst strCriteria
            If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
            rst.Close
            Set rst = Nothing
        End If
    
    End Sub
    In the module of Form2:
    Code:
    Private Sub Form_Current()
    
        Dim frm As Form
        
        For Each frm In Application.Forms
            If frm.Name = "Form1" Then
                frm.Synch Me.SysCounter.value
                Exit For
            End If
        Next frm
        Set frm = Nothing
        
    End Sub
    
    Public Sub Synch(ByVal RowId As Long)
    
        Dim rst As Dao.Recordset
        Dim strCriteria As String
    
        If Me.SysCounter.value <> RowId Then
            strCriteria = "SysCounter = " & RowId
            Set rst = Me.RecordsetClone
            rst.FindFirst strCriteria
            If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
            rst.Close
            Set rst = Nothing
        End If
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Dec 2011
    Posts
    35
    Thank you i will try it now

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    Dec 2011
    Posts
    35
    i keep on getting a error on form 2 at frm.Synch Me.HGID.Value an error 13, has this ever happen to you before i already double checked the variable names they are the same.

    FORM1
    Code:
    Private Sub Form_Current()
    
        Dim frm As Form
        
        For Each frm In Application.Forms
            If frm.Name = "Create Delivery Windows" Then
                frm.Synch Me.HGID.Value
                Exit For
            End If
        Next frm
        Set frm = Nothing
        
        On Error GoTo Form_Current_Err
    
        If ChildFormIsOpen() Then FilterChildForm
    
    Form_Current_Exit:
        Exit Sub
    
    Form_Current_Err:
        MsgBox Error$
        Resume Form_Current_Exit
    
        
    End Sub
    
    Public Sub Synch(ByVal RowId As Long)
    
        Dim rst As Dao.Recordset
        Dim strCriteria As String
    
        If Me.HGID.Value <> RowId Then
            strCriteria = "HG ID = " & RowId
            Set rst = Me.RecordsetClone
            rst.FindFirst strCriteria
            If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
            rst.Close
            Set rst = Nothing
        End If
    
    End Sub
    FORM2
    Code:
    Private Sub Form_Current()
    
        Dim frm As Form
        
        For Each frm In Application.Forms
            If frm.Name = "Main-(Delivery Window)" Then
                frm.Synch Me.HGID.Value
                Exit For
            End If
        Next frm
        Set frm = Nothing
        
    End Sub
    
    Public Sub Synch(ByVal RowId As Long)
    
        Dim rst As Dao.Recordset
        Dim strCriteria As String
    
        If Me.SysCounter.Value <> RowId Then
            strCriteria = "HG ID = " & RowId
            Set rst = Me.RecordsetClone
            rst.FindFirst strCriteria
            If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
            rst.Close
            Set rst = Nothing
        End If
    
    End Sub

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Error 13 is a 'Type Mismatch' error, meaning that Access tries to perform an implicit conversion between data types and fails. Please specify on which line the error occurs. Moreover:

    1. Check that 'HGID' is defined or derives from a numeric data type (Long Integer) in both forms.

    2. Check that 'HGID' can never be Null or Empty. Should this be the case, you would have to test before trying to pass it to the 'Synch' procedure of the other form (or pass 'RowId' as a Variant and test it in the 'Synch' procedure itself).

    3. I don't like this:
    Code:
    strCriteria = "HG ID = " & RowId
    If there is a space or any other non-alphanumeric character in a name (which you should refrain from using), you must enclose this name between square brackets:
    Code:
    strCriteria = "[HG ID] = " & RowId
    Have a nice day!

  7. #7
    Join Date
    Dec 2011
    Posts
    35
    Ive check both fields they are both long integers, one as a primary key and other as a foreign, i placed this logic to avoid errors but the number is never synced

    Code:
    Private Sub Form_Current()
    
        Dim frm As Form
        
        For Each frm In Application.Forms
            If frm.Name = "Main-(Delivery Window)" Then
              If Me.HGID.Value <> Null Then
                frm.Synch Me.HGID.Value
            End If
                Exit For
            End If
        Next frm
        Set frm = Nothing
        
    End Sub

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You cannot use:
    Code:
    If Me.HGID.Value <> Null Then
    when testing for Null values. The correct way to test for Null (and the only one that works) consist in using the 'IsNull()' function. In this case:
    Code:
    If Not IsNull(Me.HGID.Value) Then
    which can be written:
    Code:
    If IsNull(Me.HGID.Value) = False Then
    You still don't specify on which line the error occurs.
    Have a nice day!

  9. #9
    Join Date
    Dec 2011
    Posts
    35
    It gets rid of the error but it doesn't work im confused this is the code in the first form can it be something is conflicting with it? when i but break points it does the If frm.Name = "Create Delivery Windows" Then once and never goes in.

    I changed the names HGID to HG ID
    Code:
    Option Compare Database
    
    Private Sub Form_Current()
    
        Dim frm As Form
        
        For Each frm In Application.Forms
            If frm.Name = "Create Delivery Windows" Then
                frm.Synch Me.[HG ID].Value
                Exit For
            End If
        Next frm
        Set frm = Nothing
        
        On Error GoTo Form_Current_Err
    
        If ChildFormIsOpen() Then FilterChildForm
    
    Form_Current_Exit:
        Exit Sub
    
    Form_Current_Err:
        MsgBox Error$
        Resume Form_Current_Exit
    
        
    End Sub
    
    Public Sub Synch(ByVal RowId As Long)
    
        Dim rst As Dao.Recordset
        Dim strCriteria As String
    
        If Me.HGID.Value <> RowId Then
            strCriteria = "[HG ID] = " & RowId
            Set rst = Me.RecordsetClone
            rst.FindFirst strCriteria
            If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
            rst.Close
            Set rst = Nothing
        End If
    
    End Sub
    
    
    Sub ToggleLink_Click()
    On Error GoTo ToggleLink_Click_Err
    
        If ChildFormIsOpen() Then
            CloseChildForm
        Else
            OpenChildForm
            FilterChildForm
        End If
    
    ToggleLink_Click_Exit:
        Exit Sub
    
    ToggleLink_Click_Err:
        MsgBox Error$
        Resume ToggleLink_Click_Exit
    
    End Sub
    Private Sub FilterChildForm()
    
        If Me.NewRecord Then
            Forms![Create Delivery Windows].DataEntry = True
        Else
            Forms![Create Delivery Windows].Filter = "[HG ID] = " & Me.[HG ID]
            Forms![Create Delivery Windows].FilterOn = True
        End If
    
    End Sub
    Private Sub OpenChildForm()
    
        DoCmd.OpenForm "Create Delivery Windows"
        If Not Me.[ToggleLink] Then Me![ToggleLink] = True
    
    End Sub
    Private Sub CloseChildForm()
    
        DoCmd.Close acForm, "Create Delivery Windows"
        If Me![ToggleLink] Then Me![ToggleLink] = False
    
    End Sub
    Private Function ChildFormIsOpen()
    
        ChildFormIsOpen = (SysCmd(acSysCmdGetObjectState, acForm, "Create Delivery Windows") And acObjStateOpen) <> False
    
    End Function

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In my example, 'SysCounter' is the name of a control (TextBox) in both forms ('Form1' and 'Form2'). In Each form the TextBox control named 'SysCounter' is bound to a column of the form RecordSet, column that contains the primary key in the case of 'Forms1' and that contains the foreign key in the case of 'Form2'. 'Me.SysCounter.Value' is then a reference to the contents (Value) of a control and, as the column containing the primary key can never be empty, there is no need to test for that when passing the value of 'SysCounter' from 'Form1' to the 'Synch' procedure of 'Form2'. In my example, the foreign key in 'form2' cannot be Null either, which means that the test is useless too.

    If you don't have a control named 'HGID' (or is it '[HG ID]'?, this becomes very confusing for everyone and probably for Access too) on both forms, or if both a control and a field have the same name, Access will try to find something with such a name: control or field in the RecordSet of the form, and the risk is that you'll never know what is actually used.

    In case of doubt, use a full reference notation:

    For a reference to a control of the form:
    Code:
    Me.Controls("HGID").Value    ' Or Me.Controls("HG ID").Value
    For a reference to a field of the form RecordSet:
    Code:
    Me.RecordSet.Fields("HGID").Value    ' Or Me.RecordSet.Fields("HG ID").Value
    In both cases shortcut notations exist, but first you must be sure of what you handle. In any case, try to get rid of spaces and any non-alphanumeric characters into the names of the objects as soon as possible: they are an unlimited source of problems.

    I can't be more specific without knowing precisely on which line the 'Error 13' occurs.
    Have a nice day!

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    just a thought
    are you using option explicit at the head of all modules, forms & reports code?
    VBA has a nasty quirk that it doesn't fall over if it hits an undeclared variable unless you use option explicit.

    If you use CamelCase variable names (ie MyVariableName not myvariablename) , but always type code in lower case apart form string literals you can quickly see if the variable / control / whatever is the one that exists in the form if ther IDE changes it to the camel case version. also the ide when using intellinsense will display the properties of a varaible / control when its recognised it.


    Id also strongly recommend that you get out of the habit of using spaces and other punctuation characters in your table, column, form & report names. if you want to fake a space use the underscore character

    eg

    my_spaced_out_variable_name
    for me I'd use CamelCase
    MySpacedOutVariableName

    soem woudl suggest you should prefix the datatype
    eg
    strMySpacedOutVariableName for a string variable
    intMySpacedOutVariableName for an integer variable
    datMySpacedOutVariableName for a date variable
    however for dates I'd expect the variabel name to be descriptive enough in its own right
    eg
    OrderDate

    Another thing to bear in mind id to try to avoid giving your varaibles the same name as your controls and or column names. Access tries to resolve such problems but sometimes its a leetle too clever for its own boots and throws a wobbler.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Dec 2011
    Posts
    35
    I maid a mini scale of my database with the same forms hopefully you can see what i mean, i commented the if so you can see where the error is from.

    1) Open Main Delivery Window

    + Enter Any Name, Provider and Date, the HG ID is auto generated

    + Then click create Delivery Windows you will see once you add and text in the window code the HG ID is not passed .
    Attached Files Attached Files

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    OK, I get it.

    The controls are named 'HG_ID' (with an underscore), while the fields are named 'HG ID' (with a space) and you forget to replace a 'SysCounter'. So we should have:
    Code:
    Private Sub Form_Current()
    '
    ' In form_Main(Delivery Window)
    '
        On Error GoTo Form_Current_Err
    
        If ChildFormIsOpen() Then FilterChildForm
    
    Form_Current_Exit:
        Exit Sub
    
    Form_Current_Err:
        MsgBox Error$
        Resume Form_Current_Exit
    
        Dim frm As Form
        
        For Each frm In Application.Forms
            If frm.Name = "Create Delivery Windows" Then
                frm.Synch Me.Controls("HG_ID").Value
                Exit For
            End If
        Next frm
        Set frm = Nothing
       
        
    End Sub
    
    Public Sub Synch(ByVal RowId As Long)
    
        Dim rst As Dao.Recordset
        Dim strCriteria As String
    
        If Me.HG_ID.Value <> RowId Then
            strCriteria = "[HG ID] = " & RowId
            Set rst = Me.RecordsetClone
            rst.FindFirst strCriteria
            If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
            rst.Close
            Set rst = Nothing
        End If
    
    End Sub
    And:
    Code:
    Private Sub Form_Current()
    '
    ' In  Create Delivery Window
    '
        Dim frm As Form
        
        For Each frm In Application.Forms
            If frm.Name = "Main-(Delivery Window)" Then
                If Not IsNull(Me.HG_ID.Value) Then frm.Synch Me.Controls("HG_ID").Value
                Exit For
            End If
        Next frm
        Set frm = Nothing
        
    End Sub
    
    Public Sub Synch(ByVal RowId As Long)
    
        Dim rst As Dao.Recordset
        Dim strCriteria As String
    
        If Me.HG_ID.Value <> RowId Then
            strCriteria = "[HG ID] = " & RowId
            Set rst = Me.RecordsetClone
            rst.FindFirst strCriteria
            If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
            rst.Close
            Set rst = Nothing
        End If
    
    End Sub
    As healdem rightfully suggested, you should force the explicit declaration of variables by adding:
    Code:
    Option Explicit
    in the declaration section of every module. You can also automatically activate this feature in all new modules: When in the VBA Editor, open the 'Tools' menu and select 'Options'. In the 'Options' dialog box, select the 'Editor' tab and check the 'Require Variable Declaration' line (see attachment).
    Attached Thumbnails Attached Thumbnails Require Variable Declaration.jpg  
    Have a nice day!

  14. #14
    Join Date
    Dec 2011
    Posts
    35
    Hey sorry i replaced, the code given to me in both forms however the HG ID is not passed into the other form? did it work for you when you changed in the example sent ?

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    First, and for a reason I cannot understand, in the form 'Main-(Delivery Window)' you placed the portion of code that calls the procedure 'Synch' of the form 'Create Delivery Windows' into an error handler:
    Code:
    Private Sub Form_Current()
    '
    ' In form_Main(Delivery Window)
    '
        On Error GoTo Form_Current_Err
    
        If ChildFormIsOpen() Then FilterChildForm
    
    Form_Current_Exit:
        Exit Sub
    
    Form_Current_Err:
        MsgBox Error$
        Resume Form_Current_Exit
    
        Dim frm As Form
        
        For Each frm In Application.Forms
            If frm.Name = "Create Delivery Windows" Then
                frm.Synch Me.Controls("HG_ID").Value
                Exit For
            End If
        Next frm
        Set frm = Nothing
    Which means that this portion of code will never be executed because when an error occurs (which is the condition for entering the error handler), the line 'Resume Form_Current_Exit' (which provokes the exit from the whole procedure) is executed BEFORE the portion of code calling the 'Synch' procedure is reached. You could as well comment everything after the 'Resume Form_Current_Exit' line. See for instance: Error Handling In VBA or: On Error Statement

    Second, because even if the line calling the 'Synch' procedure was executed, it would probably cause no changes in the form 'Create Delivery Windows' because it would interfere with a filter that was alreay set by the line:
    Code:
    If ChildFormIsOpen() Then FilterChildForm
    Actually, I don't understand at all what you try to achieve.
    Have a nice day!

Posting Permissions

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