Recordset - Limiting input for Specific data

[Sdirby]

Good Morning all,

I hope everyone enjoyed their easter, and now for business.
I have a recordset based off of Loan numbers. ITs for holding different document statuses and types. The thing is, that i need to limit the type of document that can be stored in a field with that loan number. For instance, if loan number "11111" has the document "TP" once, it cannot have it again. BUT, it's status "sent","Received"," Or "Accepted" can be changed in that recordset.

Also, there is one document that CAN appear multiple times. "ASSN" may appear more than one for each loan number.

So in conclusion, the basics of it are.

I have a recordset based off of loan numbers. Each loan number can have only 1 "TP" and "MTG", but can have multiple "ASSN"'s. I need to know how to limit this in a recordset. Thank you.

 

[jksmi]

Hi, hold ASSN information in a different table including your loan number ID and any information relevant to ASSN...

 

[jksmi]

Hi, that's not a very full answer is it!? assuming that you table LU_LOANS has fields like LOAN_ID, DOCUMENT_TYPE and STATUS,

first, make LOAN_ID a primary key - this means that the number in LOAN_ID can only be used once in this table.

in the DOCUMENT_TYPE field properties change validation rule to ="TP" Or ="MTG", you can also add something like "Enter only TP or MTG" in Validation text as you error message for when the enter something different.

do something similar for STATUS, except use ="sent" or ="Received" Or ="Accepted"

Create a new table for ASSN with fields such as LOAN_ID and ASSN_ID and create a view of both tables joined on the field LOAN_ID. using an inner join will show you only those loans with ASSN's, using an outer join (right click join and select include all records from LU_LOANS and only those from TBL_ASSN where the joined fields are equal) will show all loans and ASSN information where available...

Hope this helps, Jamie

 

[jksmi]

Did this work for you? just curious...