Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: Check if Form is open/ If Else Else Statement

    I need you help to check if one of three forms are open and then paste the details from a fourth form into the open form.

    I have four forms each using their own underlying tables:
    -StaffForm uses StaffDetailsTable
    -SiteForm uses SiteDetailsTable
    -ProviderForm uses ProviderDetailsTable
    -PostCodeForm uses PostcodeTable


    Each of these forms have fields called:
    -PostCode
    -Suburb
    -State

    I have also created buttons on each of the following forms which opens up the PostCodeForm.
    -StaffForm uses StaffDetailsTable
    -SiteForm uses SiteDetailsTable
    -ProviderForm uses ProviderDetailsTable


    In the PostCodeForm I have created a Multiple Column List box which when I click on a selection (suburb) it pastes the column details into the PostCode, Suburb and State unbound text boxes on the same form. This is so that the user can check that they have selected the correct details.

    I have created a button on the PostCodeForm to paste the data in the PostCode, Suburb and State unbound text boxes into the same fields in the underlying form (which is still open).

    As the underlying form could be one of the three forms:
    -StaffForm
    -SiteForm
    -ProviderForm
    then I think I will need an if else else statement to check which form is open.
    E.G:

    IF underlying open form is StaffForm then copy the PostCode, Suburb and State fields from the PostCodeForm into the PostCode, Suburb and State fields of the StaffForm ELSE

    IF underlying open form is SiteForm then copy the PostCode, Suburb and State fields from the PostCodeForm into the PostCode, Suburb and State fields of the SiteForm ELSE

    IF underlying open form is ProviderForm then copy the PostCode, Suburb and State fields from the PostCodeForm into the PostCode, Suburb and State fields of the ProviderForm.

    Can any help me with the above code?

    Thanks heaps

    Karen

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here you are!

    Code:
    Function CopyIt()
    
        Dim frmSource As Form
        Dim frmDestination As Form
        
        Set frmSource = Forms!PostCodeForm
        If IsLoaded("StaffForm") Then
            Set frmDestination = Forms!StaffForm
        ElseIf IsLoaded("SiteForm") Then
            Set frmDestination = Forms!SiteForm
        ElseIf IsLoaded("ProviderForm") Then
            Set frmDestination = Forms!ProviderForm
        End If
        If Not frmDestination Is Nothing Then
            frmDestination!PostCode = frmSource!PostCode
            frmDestination!Suburb = frmSource!Suburb
            frmDestination!Suburb = frmSource!Suburb
        End If
    End Function
    
    Function IsLoaded(FormName As String) As Long
    
        If SysCmd(acSysCmdGetObjectState, acForm, FormName) <> 0 Then
            IsLoaded = -1
        End If
        
    End Function
    Have a nice day!

  3. #3
    Join Date
    Sep 2006
    Posts
    265
    You could put a combibox on your three entry forms and use Columns to populate the fields.

    Simon

  4. #4
    Join Date
    Oct 2004
    Location
    Grand Junction, Colorado
    Posts
    12
    First off sorry about the hijack...
    This fits exactly to what i am trying to accomplish and have tried the code that Sinndho offered. I am close with it but keep getting an error.
    I get a Run-Time Error
    Type Mismatch

    Here is my code:

    Code:
    Function CopyItZip()
    Dim frmDestination As Form
    Dim frmSource As Form
    
    
    
    Set frmSource = Forms!frmlookuptablezipcodes
    If Isloaded("frmDWGDetails") Then
       Set frmDestination = Forms!frmDWGDetails!fsubDWGAddress
        
    ElseIf Isloaded("frmContractors") Then
       Set frmDestination = Forms!frmContractors!fsubContractorAddress
        
    End If
    
    If Not frmDestenation Is Empty Then
        frmDestenation!PostalCode = frmSource!PostalCode
        End If
        
    End Function
    
    Function Isloaded(FormName As String) As Long
    
    If SysCmd(acSysCmdGetObjectState, acForm, FormName) <> 0 Then
    Isloaded = -1
    End If
    
    End Function
    
    Private Sub PostalCode_DblClick(Cancel As Integer)
    CopyItZip
    
    End Sub
    The debuger tags the "set frmDestination" lines...

    I would rather do this with code than a Combibox as my load time is already an issue.

    thanks,

    John

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It cannot work that way. If fsubDWGAddress is a subform of frmDWGDetails you must still use
    Code:
    Set frmDestination = Forms!frmDWGDetails
    Then address a control of the subform like this:
    Code:
    frmDestination!fsubDWGAddress.Form.ControlName
    If frmDWGDetails has no subform and fsubDWGAddress is a control, then address it like this:
    Code:
    Set frmDestination = Forms!frmDWGDetails
    frmDestination!fsubDWGAddress...
    Another, possibly more interesting, approach would be to pass the name of the calling form when you open the frmlookuptablezipcodes form and use this name to instantiate the calling form, like this :

    Code:
    Private Sub Button_Openfrmlookuptablezipcodes _Click()
     
        On Error GoTo Err_Button_Openfrmlookuptablezipcodes _Click
     
         DoCmd.OpenForm "frmlookuptablezipcodes", , , , , , Me.Name
     
    Exit_Button_Openfrmlookuptablezipcodes_Click:
        Exit Sub
    
    Err_Button_Openfrmlookuptablezipcodes_Click:
        MsgBox Err.Description
        Resume Exit_Button_Openfrmlookuptablezipcodes_Click
    
    End Sub
    Then in the module of frmlookuptablezipcodes :
    Code:
    Option Compare Database
    Option Explicit
    
    Public frmCallingForm As Form
     
    Private Sub Form_Close()
    
        frmCallingForm!PostCode = Me.PostCode.Value
        frmCallingForm!Suburb = Me.Suburb.Value
        frmCallingForm!State = Me.State.Value
    
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
    
        Dim strCallingFormName As String
    
        strCallingFormName = Me.OpenArgs
        Set frmCallingForm = Forms(strCallingFormName)
    
    End Sub
    Have a nice day !

  6. #6
    Join Date
    May 2002
    Posts
    157

    Update

    One piece of advice that seems to have helped me a lot in correcting some of my errors has been given thanks to Tom Wickerath (ADO and DAO Library References) .ADO and DAO Library References

    In relation to the code that Sinndho supplied, all I can say is thank the heavens for people like Sinndho who give up their time to assist us plebs who are new to this code writing stuff.

    Thanks a million to Sinndho who gave me this code and helped me tailor it to exactly what I needed.

    The code ended up looking like the following:
    ________________________
    Private Sub UpClsBtn_Click()

    On Error GoTo Err_UpClsBtn_Click

    'This piece of code is courtesy of Sinndho c/- dbForums
    'This first piece of code should be executed just before the aPostCodeSubF
    'is closed, so it could know to which form it has to send the three values
    '(PostCode, Suburb and State). So this code needs to be placed in the
    'OnClose event of this form or in the OnClick button of the button (if any)
    'that closes the form.


    Dim frmSource As Form
    Dim frmDestination As Form

    Set frmSource = Forms!aPostCodeSubF

    If IsLoaded("aSiteDetailsF") Then
    Set frmDestination = Forms!aSiteDetailsF
    ElseIf IsLoaded("aTrainingProviderF") Then
    Set frmDestination = Forms!aTrainingProviderF
    ElseIf IsLoaded("aStaffDetailsF") Then
    Set frmDestination = Forms!aStaffDetailsF
    End If



    'The meaning of the IIf( lines is :
    'If frmSource!PostCode is Null then frmDestination!PostCode = frmDestination!PostCode
    '(ie. frmDestination!PostCode keeps its value)
    'Else (ie. if frmSource!PostCode is not Null) frmDestination!PostCode = frmSource!PostCode
    '(frmDestination!PostCode receives the value of frmSource!PostCode).

    If Not frmDestination Is Nothing Then
    frmDestination!PostCode = IIf(IsNull(frmSource!PostCode), frmDestination!PostCode, frmSource!PostCode)
    frmDestination!Suburb = IIf(IsNull(frmSource!Suburb), frmDestination!Suburb, frmSource!Suburb)
    frmDestination!State = IIf(IsNull(frmSource!State), frmDestination!State, frmSource!State)
    End If


    'The following works but if the update button is clicked and
    'there is nothing in the field then the destination form fields will
    'be updated with nothing.

    'If Not frmDestination Is Nothing Then
    'frmDestination!PostCode = frmSource!PostCode
    'frmDestination!Suburb = frmSource!Suburb
    'frmDestination!State = frmSource!State
    'End If



    ' Close aPostCodeSubF Form
    DoCmd.Close acForm, "aPostCodeSubF"


    Exit_UpClsBtn_Click:
    Exit Sub

    Err_UpClsBtn_Click:
    MsgBox Err.Description
    Resume Exit_UpClsBtn_Click


    End Sub

    'The second function (IsLoaded) must be placed in an independant module
    '(ie. Not a module behind a form) because it has to be Public, or if
    'you prefer, it must be accessible from everywhere in your application.
    'To do this you need to open the database and under Objects select Forms.
    'Click on the Code Button located at the top toolbar.
    'Click on the Insert menu and select Module.
    'Enter the following Code then name and save the module eg call it Module1.

    'Function IsLoaded(FormName As String) As Long

    'If SysCmd(acSysCmdGetObjectState, acForm, FormName) <> 0 Then
    'IsLoaded = -1
    'End If

    'End Function

Posting Permissions

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