locked
Help on Creating a Link between a form in one database to another form in a seperate database form RRS feed

  • Question

  • Here is an overview of where I currently am in my databases:

    I have several databases for property management. They are all for different purposes; one for former tenants, one for current tenants, etc. The ones I need assistance in are the two mentioned. Right now I have a database for for both. The current tenants form has a check box within that allows us to check it if the tenant is no longer "active" within our system. Once they become "inactive", they disappear from the form (not from the table) as they are no longer a "Current Tenant". From that point forward, we have to MANUALLY enter them into our "Former Tenants" database. I would like to know if there is a way to make it so that as soon as a user checks the "Inactive" box, instead of the tenant disappearing from the form, it automatically is sent to the "Former Tenant" database (which is a separate database).

    Is there a way to link my two separate database forms so that the "Inactive" tenants from the "Current Tenants" database are sent to the "Former Tenants" database? The Former tenants database has many of the same fields (names, units, start/end of tendency, etc) id that matters. Do I need to include the "former tenants" database form in the Current Tenants for this to work? And if so how would I get it to work after that?

    Any and all help would be greatly appreciated!

    Sunday, January 12, 2014 7:29 PM

Answers

  • You don't mention what version you are using and I can't tell how much experience you have with Access, but just from what you have said, it sounds like you should simply combine the two databases into one. As far as I know, there is no way to "link" forms from one database to another. But two databases can be combined with relative ease and the tables linked to an external Back-End file so multiple users can use the database at the same time.

    Use the Access database import facility to import all of one databases objects (tables, forms, reports, queries, modules) into the other. Probably doesn't matter which one you use to do the importing (as long as they were developed using the same version). After that, one database serves both purposes.

    If you have multiple users needing to access and enter data into the resulting single Front-End (application) database, then split it into a Front-End and Back-End files using the built-in database splitting functionality. Next, move the Back-End database file to a central network shared file location. After that, open the application database and re-link its tables to the Back-End using the built-in database Linked Table Manager functionality. Most experienced Access developers split their databases in any event.

    Hope this helps.

    • Marked as answer by Marvin_Guo Monday, January 20, 2014 2:10 AM
    Sunday, January 12, 2014 8:36 PM
  • The best strategy would without a doubt be to merge the data into a single back end file, with a single Tenants table in which former and current tenants are distinguished by a such means as you are currently doing in the current tenants table.  You can then either have separate front ends for former and current tenants, or a single front end in which there are separate forms etc for former and current tenants.  Failing that you should at least split the two files into front and back ends each.  Even if the databases are not opened in a multi-user environment doing this has many advantages.  It is easily done with the built in databases splitter wizard.

    If you do continue with the two separate databases, each split into front and back ends, you can then create a link in the current tenants front end to the relevant table in the former tenants back end.  An 'append' query can then be created which references the primary key of the current tenants form's recordset so that data from the current record only is appended to the former tenants table, and executed in the AfterUpdate event procedure of the 'inactive' check box in the form if the value of the checkbox is True.  You should also ensure that the current record is saved before doing so.  The code would therefore be along these lines:

        Dim qdf As DAO.QueryDef
        Dim prm As DAO.Parameter

        If Me.chkInactive = True Then
            ' ensure current recoprd is saved
            Me.Dirty = False
            ' return reference to query
            Set qdf = CurrentDb.QueryDefs("YourAppendQueryNameGoesHere")
            ' evaluate query's parameters
            For Each prm In qdf.Parameters
                prm = Eval(prm.Name)
            Next prm
            ' execute query
            qdf.Execute
        End If

    Ken Sheridan, Stafford, England

    • Marked as answer by Marvin_Guo Monday, January 20, 2014 2:10 AM
    Sunday, January 12, 2014 11:39 PM

All replies

  • Here is an overview of where I currently am in my databases:

    I have several databases for property management. They are all for different purposes; one for former tenants, one for current tenants, etc. The ones I need assistance in are the two mentioned. Right now I have a database for for both. The current tenants form has a check box within that allows us to check it if the tenant is no longer "active" within our system. Once they become "inactive", they disappear from the form (not from the table) as they are no longer a "Current Tenant". From that point forward, we have to MANUALLY enter them into our "Former Tenants" database. I would like to know if there is a way to make it so that as soon as a user checks the "Inactive" box, instead of the tenant disappearing from the form, it automatically is sent to the "Former Tenant" database (which is a separate database).

    Hi ang2206,

    If your table with the tenants contains an Active field, you HAVE already al what you want. Filtering on Active = TRUE yields all active teneants, filtering on Active = FALSE yields all inactive tenants. No need for an additional table, and sure not for an additional database.

    Instead of using an Active (Boolean) field, I would choose for two Date fields: StartDate and EndDate. In this way you can build up a history of all tenants in any time period. An empty EndDate then means an active tenants. Some tenants can even have more than one record because of different time periods.

    Imb.

    Sunday, January 12, 2014 8:24 PM
  • You don't mention what version you are using and I can't tell how much experience you have with Access, but just from what you have said, it sounds like you should simply combine the two databases into one. As far as I know, there is no way to "link" forms from one database to another. But two databases can be combined with relative ease and the tables linked to an external Back-End file so multiple users can use the database at the same time.

    Use the Access database import facility to import all of one databases objects (tables, forms, reports, queries, modules) into the other. Probably doesn't matter which one you use to do the importing (as long as they were developed using the same version). After that, one database serves both purposes.

    If you have multiple users needing to access and enter data into the resulting single Front-End (application) database, then split it into a Front-End and Back-End files using the built-in database splitting functionality. Next, move the Back-End database file to a central network shared file location. After that, open the application database and re-link its tables to the Back-End using the built-in database Linked Table Manager functionality. Most experienced Access developers split their databases in any event.

    Hope this helps.

    • Marked as answer by Marvin_Guo Monday, January 20, 2014 2:10 AM
    Sunday, January 12, 2014 8:36 PM
  • The best strategy would without a doubt be to merge the data into a single back end file, with a single Tenants table in which former and current tenants are distinguished by a such means as you are currently doing in the current tenants table.  You can then either have separate front ends for former and current tenants, or a single front end in which there are separate forms etc for former and current tenants.  Failing that you should at least split the two files into front and back ends each.  Even if the databases are not opened in a multi-user environment doing this has many advantages.  It is easily done with the built in databases splitter wizard.

    If you do continue with the two separate databases, each split into front and back ends, you can then create a link in the current tenants front end to the relevant table in the former tenants back end.  An 'append' query can then be created which references the primary key of the current tenants form's recordset so that data from the current record only is appended to the former tenants table, and executed in the AfterUpdate event procedure of the 'inactive' check box in the form if the value of the checkbox is True.  You should also ensure that the current record is saved before doing so.  The code would therefore be along these lines:

        Dim qdf As DAO.QueryDef
        Dim prm As DAO.Parameter

        If Me.chkInactive = True Then
            ' ensure current recoprd is saved
            Me.Dirty = False
            ' return reference to query
            Set qdf = CurrentDb.QueryDefs("YourAppendQueryNameGoesHere")
            ' evaluate query's parameters
            For Each prm In qdf.Parameters
                prm = Eval(prm.Name)
            Next prm
            ' execute query
            qdf.Execute
        End If

    Ken Sheridan, Stafford, England

    • Marked as answer by Marvin_Guo Monday, January 20, 2014 2:10 AM
    Sunday, January 12, 2014 11:39 PM