We help IT Professionals succeed at work.

Access Forms Drill Down

Medium Priority
287 Views
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)
            Borrower
            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:
 
CollType:
 
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.
 
Motel:
 
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.  
Comment
Watch Question

Author

Commented:
I am using Access 2007, and updating an existing database from version 2003.
Thank you for any help.
Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
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 Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
chantalcookware

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

JeffCoachman
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.