Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    121

    Unanswered: Picking up Data in form fields, and using it in another Access Application

    All,
    The company I work for has an Access application that we enter trailer shipment information into. It is an MDE file, and they won't let us make changes at this time.

    I want to create a new application that can take data from the form currently open, and use it to print a label that uses data from that form.

    Is there an easy way to ask the new application to find data in a form on another application?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Bob.Carter.17 View Post
    Is there an easy way to ask the new application to find data in a form on another application?
    No!

    Here is a path:
    Code:
    Function ExploreMDE(ByVal MDEName As String)
    
        Dim app As Access.Application
        Dim frm As Form
        Dim ctl As Control
        
        If Len(Dir(MDEName)) Then
            Set app = New Access.Application
            app.OpenCurrentDatabase MDEName
            With app
                For Each frm In .Forms
                    Debug.Print frm.Name
                    For Each ctl In frm.Controls
                        If Eval(ctl.ControlType & "IN ( 107, 109, 110, 111 )") Then ' acOptionGroup, acTextBox, acListBox, acComboBox
                            Debug.Print , ctl.Name, ctl.Value
                        End If
                    Next ctl
                    Debug.Print
                Next frm
                .Quit
            End With
            Set app = Nothing
        End If
        
    End Function
    Have a nice day!

  3. #3
    Join Date
    Dec 2005
    Posts
    121
    Sinndho,
    Thanks for the code. I understand that this function I can call to populate a particular text field on my new form. But this is a bit beyond what I have worked with in the past. I am sure I need to add my unique mde file name (Yard.MDE) into the code, and possibly some other information that is unique to the mde form.

    To explain further, I need to open a form in my new application with only 3 fields. In those 3 fields, I need to pickup data from the second Access application called Yard.MDE. Those 3 fields are located on a form that will be open named YARD. The 3 fields are "TRAILER NUMBER", "SCAC" and "seal". TRAILER NUMBER and SCAC are Combo boxes, seal is a text box.

    I would simply try to pull the data from the source tables, but our system group has those linked in from multiple locations which I cannot access. So, the form is the only viable option I have to pick up these 3 pieces of information.

    Can you elaborate what I need to change in the code based on these criteria?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You first need to know the exact name of the form and the exact names of the three controls containing the data in the mde file. These names can be different from the associated labels you can read in the application interface. The code I sent should help you there.

    When you have the necessary information, you can write a function that will pick up the values present in the three controls of the mde and import them into your program.

    If the form in the mde application is named "Form1" and the three controls for which you want to read the values are named "Ctl1", "Ctl2" and Ctl3", your code should be something like:
    Code:
    Function PickUpMDEValues(ByVal MDEName As String)
    
        Dim app As Access.Application
        Dim frm As Form
        Dim Value1 As Variant
        Dim Value2 As Variant
        Dim Value3 As Variant
        
        If Len(Dir(MDEName)) Then
            Set app = New Access.Application
            app.OpenCurrentDatabase MDEName
            With app
                Set frm = .Forms("Form1")   ' Form1 must be open!
                Value1 = frm.Controls("Ctl1").Value    ' Ctl1 <--> TRAILER NUMBER
                Value2 = frm.Controls("Ctl2").Value    ' Ctl2 <--> SCAC
                Value3 = frm.Controls("Ctl3").Value    ' Ctl3 <--> seal
                Set frm = Nothing
                .Quit
            End With
            Set app = Nothing
            Set frm = Forms("YARD")     ' YARD must be open!
            With frm
                .Controls("TRAILER NUMBER").Value = Value1
                .Controls("SCAC").Value = Value2
                .Controls("seal").Value = Value3
            End With
            Set frm = Nothing
        End If
        
    End Function
    Of course, this suppose that "Form1" (whatever its real name can be) opens up when the mde is started and that it display the data you're looking for. Otherwise you'll be obliged to pilot the mde application from your program, and it's far from easy, that's the reason why my first answer was "no".
    Have a nice day!

  5. #5
    Join Date
    Dec 2005
    Posts
    121
    Your last sentence may be the key here, that I forgot to mention. The MDE application will already be open, and the form I need the data from will already be displayed with the correct record already pulled up (it is a data entry screen that enters a new trailer and assigns it a location). Once the trailer is entered, the form simply displays that trailer in its new location until a new trailer is entered. Since I cannot add a print function to the MDE application, I want to create a simple "second" application that looks at that open form, finds the trailer number, SCAC and seal fields and brings that into a form or query that I can use to print a small sticker to be placed on the delivery paperwork. So, I do not need to open the application, it will already be open and the correct form will also be open displaying the correct record.

    So, in a nutshell and make a couple of corrections, the MDE application YardMen.MDE will already be open, displaying form YARD. On the YARD form, the following 3 controls have the information already displayed that I need to print the sticker (Combo Box "TRAILER NUMBER", Combo Box "SCAC" and Text Box "seal"). I got these names by creating a query in the MDE file (which you can do in it's locked state) and then simply opening a build function box pointing to the open form and found the list of controls on the form in their proper names there. Those 3 pieces of information from the already open program are what I need to bring into application #2 to print my bill of lading sticker.

    If I can get this up and running, I will have about 50 gatehouse associates who will be ever grateful that I gave them a way to print this information instead of hand writing this on every bill of lading that they get. Huge time saver for us and also a big moral winner for the team. So very important for me to figure this out. And your help is greatly appreciated.

    I am sure this changes what you sent me, would you mind taking a look and giving me more direction? Sorry, I should have mentioned this earlier.
    Last edited by Bob.Carter.17; 11-22-10 at 11:28.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Unfortunately it's not so easy. With these two lines:
    Code:
            Set app = New Access.Application
            app.OpenCurrentDatabase MDEName
    You create a new instance of Access and load the database in it (don't be fooled by the "Current" in "OpenCurrentDatabase": it does not mean "running"). It's just as if you double-clicked on the mde file or launched Access and opened it through the File --> Open menu. All those techniques do not allow to connect to the instance of Access that's already running. Depending on how the mde application is protected (or not) you still have to open the form containing the data you're looking for, then position the Recordset (if a Recordset is used) to the proper row... if some protection mechanisms were not used to forbid it.

    The solution to your problem would be to connect to the already running instance of Access, unfortunately I don't know how you can do that nor if it's possible at all.
    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
  •