Last Name Plus Number Autonumbering (1 Viewer)

abbyh

New member
Local time
Today, 01:53
Joined
Nov 8, 2021
Messages
4
Hi. I am really new to Access, and I need some help. I am trying to figure out how to add the first four letters of a last name column to the autonumber setting for my ID column. I know a lot of people don't suggest doing this in case the last name changes, or there are multiple people with the same last name, but that is part of why the business I am working for is looking to do this. We are a financial management firm, and some of our clients have multiple different accounts with us, using the same name. We need to differentiate between the different accounts, while still referencing who's account it is. I know it is done in the format portion of the autonumber information, but then people mention myID, and concantenation and I get a bit lost. Can someone walk me through how to add the first four of the last name to the autonumber account ID column? For example, WALL-128 or HALL-40. Thank you.
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:53
Joined
Sep 21, 2011
Messages
14,048
Leave the autonumber well alone.
If you want that format at any time you can just concatenate.

Code:
strMyCode = Left(Me.Lastname,4) & "-" & Me.TableID
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:53
Joined
Oct 29, 2018
Messages
21,358
Hi. Take my bank account for example. I might have both a savings and a checking account with different account numbers. None of them include any letters from my last name. So, if you use an Autonumber field as the primary key for the client accounts, then no two of them should have the same account number. That should be enough to tell you one account from another. I hope...
 

abbyh

New member
Local time
Today, 01:53
Joined
Nov 8, 2021
Messages
4
I get what you are saying, however, this is a set up my boss prefers, because we use one similar to it on other documents, and it would be nice if I could have it autopopulate with the correct information and number when putting in a new customer.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:53
Joined
Oct 29, 2018
Messages
21,358
I get what you are saying, however, this is a set up my boss prefers, because we use one similar to it on other documents, and it would be nice if I could have it autopopulate with the correct information and number when putting in a new customer.
In that case, you can do what @Gasman said, leave the Autonumber field alone and simply add the "calculated" account number as a new column in your query or record source for your form or report.
 

abbyh

New member
Local time
Today, 01:53
Joined
Nov 8, 2021
Messages
4
Okay, I can try that, but how exactly do I do that? Honestly, I've only been working with Access for a few weeks, and this may be a bit beyond me at this point, but I am willing to try?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:53
Joined
Oct 29, 2018
Messages
21,358
Okay, I can try that, but how exactly do I do that? Honestly, I've only been working with Access for a few weeks, and this may be a bit beyond me at this point, but I am willing to try?
Let's say you have a table called tblAccounts and you have a form called frmAccounts where the Record Source of the form is tblAccounts. What you could do is create a query called qryAccounts based on tblAccounts and use that query as the Record Source for your form. Everything should work exactly the same as when you had the table as your record source for the form. Now, all you have to do is add a new calculated column in that query, using a similar expression as what @Gasman showed you in post #2. You can then display that column on your form to show the combined account number and last four chars of the last name. Hope that helps...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:53
Joined
Feb 19, 2002
Messages
42,981
The question is - do you want the letters to change if the name changes or do you want the letters to stay the same forever even if the name changes?

If the answer is "a", then you would save the the actual concatenated value when you first create the record. If the answer is "b", you would use a calculated column and it will always have the current abbreviation of the client's name.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 05:53
Joined
Sep 12, 2006
Messages
15,614
I get what you are saying, however, this is a set up my boss prefers, because we use one similar to it on other documents, and it would be nice if I could have it autopopulate with the correct information and number when putting in a new customer.

Seriously. You do it the right way, which does both. You present the data in the way your boss wants without compromising the way the database works. So you use two fields to construct the presented value, as others have said.

In passing, note that an autonumber is not reliable if you want an unbroken continuous sequence of serial numbers.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:53
Joined
Feb 19, 2002
Messages
42,981
The question is - do you want the letters to change if the name changes or do you want the letters to stay the same forever even if the name changes?

If the answer is "a", then you would save the the actual concatenated value when you first create the record. If the answer is "b", you would use a calculated column and it will always have the current ab
 

oleronesoftwares

Passionate Learner
Local time
Yesterday, 22:53
Joined
Sep 22, 2014
Messages
1,159
I will suggest the following

1. have a name field, this will be a combo box with row source from current table,same field.set the allow value edits to yes

2. have a combo box with options, primary, secondary. by default the primary option should be selected,



in vba code

requery name field in 1 above anytime name field is updated.

So when ever you are creating a new record and you enter a name,the combo box will lookup if the name exists, if it exists, this means this is not the first account the customer has with you, you will pick the customer name from the combo box.

Then go to options combo box and choose secondary, this means this a secondary account.

So when you run a report you can get the occurrence of each customer name and whether its primary or secondary account.



But if when your are creating a new record and you enter a name,the combo box will lookup if the name exists, if it does not exist(this means this is the first account),you will enter the customer name,

No need to change the options field, since by default it is primary.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:53
Joined
Feb 19, 2002
Messages
42,981
Can someone walk me through how to add the first four of the last name to the autonumber account ID column?
No. There are better ways to do this. You just need to answer my questions in #10
 

Users who are viewing this thread

Top Bottom