Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Call 2nd DB and open form to specific record

Discussion in 'Information Technology' started by NMThoman, Jul 28, 2006.

  1. NMThoman

    NMThoman
    Expand Collapse
    Guest

    I have two calculation databases. When I am in database A, I need to open
    database B. Done that, working well. I then need to set the form in
    database B to the same record (keyed off of SSN) as database A. Tried any
    number of things, not getting it.

    Any suggestions?
     
  2. Loading...

    Similar Threads Forum Date
    Hard Talk Shamelessly Ungrateful People . What We Call In Punjabi "Akirtghan" Hard Talk Jun 19, 2016
    Your Calling Get Involved Jun 5, 2016
    Does Reading Baani Magically Affect Our Body And Psyche? And Does The Effect Depend On The Bani Read Intellectual Translations by SPNers Apr 30, 2016
    How Harnarayan Singh Found His Calling Sikh Personalities Apr 13, 2016
    Controversial Concept of so-called Jhootah and Sikhism Hard Talk Dec 10, 2015

  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Do not use separate databases but separate tables and forms. Have query used
    as record source for second form to use data from first form text box for
    criteria like this --
    [Forms]![YourFirstForm]![TextBoxWithSSN]

    "NMThoman" wrote:

    > I have two calculation databases. When I am in database A, I need to open
    > database B. Done that, working well. I then need to set the form in
    > database B to the same record (keyed off of SSN) as database A. Tried any
    > number of things, not getting it.
    >
    > Any suggestions?
     
  4. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "NMThoman" <NMThoman@discussions.microsoft.com> wrote in message
    news:891DD028-5C8A-4A3B-82BC-A30DB8F43902@microsoft.com
    > I have two calculation databases. When I am in database A, I need to
    > open database B. Done that, working well. I then need to set the
    > form in database B to the same record (keyed off of SSN) as database
    > A. Tried any number of things, not getting it.
    >
    > Any suggestions?


    I'm curious about why you would need to do this. However, you should be
    able to automate a second instance of Access, using code along these
    lines:

    '----- start of "air code" -----
    Dim appOther As Access.Application

    Set appOther = New Access.Application

    appOther.OpenCurrentDatabase "C:\Path\To\YourDB.mdb"

    appOther.DoCmd.OpenForm "frmYourForm", _
    WhereCondition:="SSN='" & Me.SSN & "'"

    Set appOther = Nothing
    '----- end of "air code" -----

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  5. NMThoman

    NMThoman
    Expand Collapse
    Guest

    Thanks for the input. I don't have a choice. These are not new db's that I
    am setting up. They are existing and I have to work within the confines of
    what already exists.

    Thanks again,
    Nichole

    "KARL DEWEY" wrote:

    > Do not use separate databases but separate tables and forms. Have query used
    > as record source for second form to use data from first form text box for
    > criteria like this --
    > [Forms]![YourFirstForm]![TextBoxWithSSN]
    >
    > "NMThoman" wrote:
    >
    > > I have two calculation databases. When I am in database A, I need to open
    > > database B. Done that, working well. I then need to set the form in
    > > database B to the same record (keyed off of SSN) as database A. Tried any
    > > number of things, not getting it.
    > >
    > > Any suggestions?
     
  6. NMThoman

    NMThoman
    Expand Collapse
    Guest

    First db has a pension calculation based on a persons benefit when they reach
    age 65. The benefit is based on how many years of service a participant has
    earned. The second db houses all the service history for the participant. I
    didn't set it up this way, just dealing with what already exists.

    Both db's are huge with 5,000+ lines of code. Can't put them together very
    easily. Each performs many, many calculations - especially the service.
    It's not just Date of Hire through Date of Term. It is based on each
    position, during each time period and what happens in the next time period
    can affect how previous time periods are calculated. Very confusing, trust
    me.

    If a user makes a change in the first db, they need to be able to go back to
    the service db and recalculate everything to ensure that all figures are the
    most accurate. The users don't want to have to navigate to the second db to
    open it and then recalc. I am tasked with trying to find a way to
    automatically open the second db from within the first (which I have done
    very successfully), then navigate to the correct record, so the recalc can be
    done, then close the second db and come back to the first.

    I had already used the OpenCurrentDB command, but the second db has an auto
    open command that opens the db to the first record in a table. I have made
    three attempts at navigating to the correct record.

    First attempt was the openform, (I had used a different form of SSN=SSN).
    Didn't work.

    Second attempt was to attempt a DoCmd.FindRecord command, which gives me no
    errors, but doesn't navigate to the correct record. Here is the command that
    I am using: svcmacro.DoCmd.FindRecord svcmacro.Forms![frmService].SSN =
    Me.SSN. I have tried a variety of wording on this phrase, none work and
    provide a variety of errors.

    Third attempt was to use your command with the wherecondition. Didn't work,
    the second db auto opens the form on db open so I added a docmd.close (with
    and without acsaveyes and acsaveno) to close the form first (which does close
    the form), then tried to open it using your command, but I get the following
    error: THE OPENFORM ACTION WAS CANCELED.

    Any more ideas?

    Thanks,
    Nichole


    "Dirk Goldgar" wrote:

    > "NMThoman" <NMThoman@discussions.microsoft.com> wrote in message
    > news:891DD028-5C8A-4A3B-82BC-A30DB8F43902@microsoft.com
    > > I have two calculation databases. When I am in database A, I need to
    > > open database B. Done that, working well. I then need to set the
    > > form in database B to the same record (keyed off of SSN) as database
    > > A. Tried any number of things, not getting it.
    > >
    > > Any suggestions?

    >
    > I'm curious about why you would need to do this. However, you should be
    > able to automate a second instance of Access, using code along these
    > lines:
    >
    > '----- start of "air code" -----
    > Dim appOther As Access.Application
    >
    > Set appOther = New Access.Application
    >
    > appOther.OpenCurrentDatabase "C:\Path\To\YourDB.mdb"
    >
    > appOther.DoCmd.OpenForm "frmYourForm", _
    > WhereCondition:="SSN='" & Me.SSN & "'"
    >
    > Set appOther = Nothing
    > '----- end of "air code" -----
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
    >
     

Share This Page