chantalcookware
asked on
Access Forms Drill Down
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
...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
ASKER
Thank you for any help.