Input value from a table that won't change when table value does

[westendal]

Not sure I'm in the right forum. I am very much a beginner to this...if I'm in the wrong place, please let me know. I need to have a field in my form(which is stored in a column in a table) look up a value from another table, input the value into the field on the form, and not change, when the value changes in the source table. i.e. I want to look up a Sales Rep from the Sales Rep table and insert it into a Loan application table, for tracking commissions. If the Sales Rep changes in the future, I still need the old Rep to appear in the old Loan Application, while any new loan applications input, would show the new Rep. I have a field that will look up the Sales Rep when an account ID is entered, however it updates, when I change the Sales Rep. Any help would be greatly appreciated.

 

[smandoli9]

So SalesRep is stored in the SalesRep table but also is stored in the LoanApp table. For a new LoanApp record, SalesRep is inserted from the SalesRep table, to serve as the opening default. From there the LoanApp record may be revised.

Is that correct? Do you have the SalesRep field established in the LoanApp table?

From there you can make the Create LoanApp form have an unbound field for SalesRep, and use code to place the right value; upon execute it is written to the LoanApp record. Or you can make it a bound form, maybe that's better -- let me know if any of this sounds right so far --

[purple]_______________________________
_{Never confuse movement with action -- E. Hemingway} [/purple]

 

[westendal]

Yes the Sales Rep is stored in both the Repairer Table & the Loan App Table, however, currently, as a band-aid solution, I have two fields in the Loan App Table. One imports the Sales Rep from the Repairer table, when I input an ID number. This field will update any time I change a Rep, so to get around that, I have another field where someone can enter the name of the Rep manually by looking at the imported field. This 2nd field is stored in the table with it's unique Loan App # . As this field is dependant upon data entry, it will not automatically update like the other one. Ideally, I would like to have one field that looks up the Rep, then shuts off the look up instruction. (code I assume). I'm not sure I understand what you are asking with the Bound/Unbound items?

Thank you for your response, I hope this help you help me.

 

[smandoli9]

Sorry about two weeks going by. My computer went south. I assume you probably have made progress but post a new reply if issues are still there.

The way I handle this in a similar situation is the LoanApp table has a field, SalesRep, that is text. The code starts a new LoanApp record with the name of the SalesRep, text, which it looked up from the SalesRep table. This field can later be updated -- again, referencing from the table, but inserting as text.

That way the Rep field in the Loan table is truly independent.

If I used an ID number, I'd have to keep a historical record of all IDs ever ... which does not serve my purpose.

[purple]_______________________________
_{Never confuse movement with action -- E. Hemingway} [/purple]