Access Forms Drill Down

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.  
Who is Participating?
Dale FyeConnect With a Mentor Commented:
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
chantalcookwareAuthor Commented:
I am using Access 2007, and updating an existing database from version 2003.
Thank you for any help.
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.
Jeffrey CoachmanMIS LiasonCommented:

...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?)...

All Courses

From novice to tech pro — start learning today.