473,320 Members | 2,012 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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.
Oct 6 '11 #1
6 2459
patjones
931 Expert 512MB
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
Oct 6 '11 #2
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.
Oct 6 '11 #3
patjones
931 Expert 512MB
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
Oct 6 '11 #4
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.
Oct 6 '11 #5
patjones
931 Expert 512MB
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.
Oct 6 '11 #6
NeoPa
32,556 Expert Mod 16PB
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.
Oct 6 '11 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Iain Miller | last post by:
Struggling a bit here & would be grateful for any help. I have a table which has a list of people in it. Each person has a unique ID automatically allocated by Access but also belongs to one of 5...
2
by: Kevin Myers | last post by:
Hello, Have yet another problem on a form that has me stumped. In a table that I am working with, the values for one of the fields are stored in meters. However the values that are supplied for...
2
by: edworboys | last post by:
I have designed a data entry form with a number of fields and a sub form. The first field (Country) is a combo box and the user selects a country. This, in turn reduces the number of options in the...
16
by: jhwagner | last post by:
I need to use double data entry with an MS Access database. I have read many arguments and reasons against this on this group but I have to do this. I have seen various tips on how this can be...
2
by: filbennett | last post by:
Hi Everyone, I'm generally unfamiliar with Access form design, but have programmed Cold Fusion applications for a couple of years. I'd like to build a data entry form in Access that allows the...
17
by: Timothy.Rybak | last post by:
Hello all, This is my first attempt at an application, so kid gloves are appreciated. I need to make a very simple form that only has a few elements. One is TraceCode - a text field that is...
4
by: Birky | last post by:
I’m hoping you can help me get my form to work. I have a few issues that I need to get squared away. 1. I am using a form for data entry only therefore I need the form to pop with the next...
20
by: hippomedon | last post by:
Hello everyone, I'm looking for some advice on whether I should break the normalization rule. Normally, I would not consider it, but this seems to be a special case. I have created an...
8
by: Wingot | last post by:
Hey, I have a program I am trying to write using Visual C#, SQL Server 2005/2008, and Visual Studio 2008, and one part of it includes a Schema called Client. Inside this schema, three tables...
5
by: Dan Tallent | last post by:
I have a scenerio when my forms are first opened that the user cannot modify the data. The fields are disabled to prevent them from modifying any of the data. If a user wishes to modify the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.