Updating Data in a Table as it is entered.

[AC5FF]

I originally started this question in the Tables forum here but the more I worked on it the more I thought it should be a query issue.

I had created a form to enter information into a table using the table alone, but I wanted one field in the table to be a combination of four other fields of the table and to auto-fill that information in the background - something the end user would never see. This 'combined' field is set to be my primary key throughout the database.

I knew I could use an update query to perform this, and I can make this work if I run it seperate. But because of the way I hope to run the interface, I need that combined field before forms are exited. (hope that makes sense).

Here is a copy of the query I have written to enter information into my table:

INSERT INTO Renter ( ID1, [Last], [First], MI, Nick, MoveIn, MoveOut, RenterID )
SELECT Renter.ID, Renter.Last, Renter.First, Renter.MI, Renter.Nick, Renter.MoveIn, Renter.MoveOut, [ID] & Left([last],2) & Left([first],2) & [MI] AS RenterID
FROM Renter
ORDER BY Renter.Last, Renter.First;

The table "Renter" has the field "RenterID". (FYI, ID field is an AutoNumber field.) When I run this query and enter in new information the RenterID field is filled in as the rest of the information is typed. Working perfectly. Except when I exit the query all the data that I entered into the query is in my Renter table EXCEPT for the RenterID field which is blank.

Any ideas on what I could be doing wrong? Or even if I am proceeding correctly???

Thanks!

 

[MarkK]

I would never store a calculation or re-combination of my data at storage time. A table is for raw data. Do your concatenation, calculation, processing, and so on when you retrieve data.