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.
6 2459
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
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.
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
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.
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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |