Access Forms Drill Down

Posted on 2011-04-29
Last Modified: 2012-06-21
Looking at how to due the following.
Master Table:
Fields:  RelationshipID - Primary Key (yes dupls)
            LoanNo - Primary Key (no dupls)
            Loan Information (Balance of various information fields in table)
Open this table in form view for editing.
Embedded into this form is the following form:
Field:  RelationshipID-Primary Key (yes dupls)
          LoanNo-Primary Key (yes dulps)
          CollType-Primary Key (no dupls)  This is a lookup field
I want to be able to lookup type collateral (ie motel, single family home, office building, etc).  Then double click on this field and open up a detail to input collateral type detail.
For example if motel is the collateral and then double click in done the following form is opened.
Fields:    AutoNo-Primary Key (no dupls)
              RelationshipID-Primary Key (yes dupls)
              LoanNo - Primary Key (yes dupls)
              Motel Information  (Balance of various information fields in table)
I need to open the MotelForm and 1st see if there is a RelationshipID match.  If so then I need to see if there is a LoanNo match.  Then pull those records into form view for editing or additional input.
If there is no RelationshipID match I need to add the RelationshipID as well as the LoanNo into form to allow addition of information..
If there is a RelationshipID but no LoanNo then I need to open and add new LoanNo and open form for addition of information.
I could have one LoanNo but it could have multiple collateral types ie- Motel, CD and Furniture & Fixtures.  Additionally a LoanNo could have multiple collateral pieces.  IE the loan could be secured with (3) motels.  
Question by:chantalcookware

    Author Comment

    I am using Access 2007, and updating an existing database from version 2003.
    Thank you for any help.
    LVL 47

    Accepted Solution

    Could you have more than one motel as collateral for a single loan?

    I think the way I would do this is to define a Criteria to be used in the OpenForm method.  By doing this, the form is opened with the records which match the Relationship/LoanNo ID values.  If there are matches, then you can see those matches.  If not, it will take you to a new record.

    Private Sub cbo_CollType_DblClick(Cancel as Integer)

        Dim strCriteria as string

        strCriteria = "[RelationshipID] = " & me.txt_Relationship & " AND " _
                     & "[LoanNo] = " me.txt_LoanNo

        if me.cbo_CollType = "Motel" then
            docmd.OpenForm "MotelForm",,,strCriteria,,acDialog
        elseif me.cbo_CollType = "Single Family Home" then
        End If

    End Sub

    Then, In the Current event of the "MotelForm", I would test to determine whether you are on a new record, and if so, I would insert the values of RelationshipID and LoanNo from the main form into these fields in MotelForm.  It might look like:

    Private Sub Form_Current

        if me.newrecord then
            me.txt_RelationshipID = Forms("yourOtherFormName").txt_RelationshipID
            me.txt_LoanNo = Forms("your OtherFormName").txt_LoanNo
        End If

    End Sub

    End Sub
    LVL 44

    Expert Comment

    In your question you state field RelationshipID is a primary key and has duplicates.  Some indexes may have duplicates but certainly not a primary key.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    ...Yes, your "Keys" are a bit confusing....

    If you have a Table named "CollType" then the primary key should be CollTypeID and this will have No Duplicates as GRayL states

    If you have a Table named "Motel" then the primary key should be MotelID and this will have No Duplicates.

    If you have a Table named "MasterTable" then the primary key should be MastedTableID and this will have No Duplicates.

    If you have a Table named "ZoomStock" then the primary key should be ZoomStockID and this will have No Duplicates.
    Make sense?

    It is not clear why you have two primary keys in your tables, or even if this is appropriate in this situation, but mostly (again, as GRayL states) how you have magically managed to create a *Primary key* field that allows duplicates...?

    In the long run I believe that fyed has indeed posted what you were looking for.
    But it would be helpful if you explained the reasoning behind your relationships (RelationshipID?)...


    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now