AfterUpdate to populate field after data entry into two other fields.

[ivchisholm]

I have:

tbl_Consolidated with [LName], [FName], [Last4], [EmpID]

qry_EmpID with "EmpID: UCase([L Name]&[Last 4])"

frm_AdminData to fill in the last name, first name, and last four.

What I want is this:

After I fill in LName and Last4 on the form, I want it to automatically populate the EmpID query and fill in the form's EmpID field.

Example: I have Smith, Mike 1234. After I type in Smith and 1234 into their appropriate fields on the form, I want the EmpID field to show SMITH1234

I know this would be an AfterUpdate function, but cannot figure out exactly how to compose this action.

[patjones]

My immediate reaction when reading your request was that you don't need the query. This however then led me to consider if you want this process to occur only for people who are in the table. Is this the case?

I ask because it's possible to write code that will concatenate the values from the first two text boxes, upper case the value, and write it to the third box...regardless of whether the first two values are in the table or not.

Pat

[ivchisholm]

I already have people in the database. I would like for this to populate the [EmpID] field for already existing records, as well as for new records as they are created.

Furthermore, I don't just want it to concatenate the two fields into the third field on the form...I want to store this within the [EmpID] field on the table, as well.

[patjones]

You should first write an UPDATE query that will populate the EmpID field for all existing records. Once that's done...

On the form, you will want to do as I suggest in post #2, paragraph 2. At that point, there are two possibilities. Either the value in the third text box is already in the table, in which case you do nothing...OR that value is not found (new employee) - in which case you need to run an append query to add that person to the table.

By the way, if this is for primary key generation, it's not a good choice for a primary key. I would suggest finding other possibilities.

Pat

[ivchisholm]

Yes, unfortunately, this is to generate a primary key. I had a discussion about this with someone earlier today about this.

I say unfortunately, because, even though I understand the lack of uniqueness of last names and last four, it is necessary/possible for 2 primary reasons: (1) It will only have about 300 records at a given time, so likelihood of duplicating is minimal/negligible and (2) people in higher position want it that way, although advised on the possibility. They look at the chances as being minimal, therefore acceptable.

By the way, in the next few years we will have a more permanent database to replace this one that (hopefully) will be better designed than the one I am trying to work with. This one was here when I got here, and I have ripped out a bunch of needless and senseless stuff and made it less complicated.

[patjones]

I see; at least you're aware of what you're doing and that it could present a problem somewhere down the road. It sounds like other arrangements will be made when the permanent database comes online.

So, I would proceed as suggested before: concatenate the values into the third text box and then do a check to see if it's in the table, with an INSERT taking place if it is not in the table. Let us know if you need assistance with this process.

[NeoPa]

This thread was essentially a duplicate of one already in play at Combine two fields into another field within SAME table.

To leave this as tidily as possible I'm locking this thread, so anyone involved can continue in the other one.