Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Access 2007 - copy calculated reference number to underlying table


(!)

JoeBlack999's Avatar
JoeBlack999 JoeBlack999 is offline
Computer Specs
Member with 77 posts.
THREAD STARTER
 
Join Date: May 2008
Experience: Intermediate
11-Dec-2008, 08:00 AM #1
Question Solved: Access 2007 - copy calculated reference number to underlying table
I haven't used Access for about a year and rust has set in. I have been asked to generate a reference number to identify clients. The reference number uses the first three letters from each of the clients first and last name and the unique (autonumber).

I can do this on a form view easily enough:

=Left([fsub_Staff].[Form]![FirstName],3) & Left([fsub_Staff].[Form]![LastName],3) & [fsub_Staff].[Form]![ID]

However, I would really need this to be hard coded back into the underlying database table, consisting of the first, last and autonumber field (as reference).

Any assistance would be appreciated.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,441 posts.
 
Join Date: Mar 2005
Location: UK
11-Dec-2008, 08:42 AM #2
I am not sure why they need this reference number, as the Autonumber does the job as far as the database is concerned.
But as you have the job to do, just add the new Reference Number field to the Data Input Form and then use the equivalent VBA version of your Formula in the First and Last Name Fields After Update Event Procedures.
The VBA Version is
me.referencenumber = Left(me.[FirstName],3) & Left(me.[LastName],3) & me.[ID]
where referencenumber is the actual name of your new field.
It needs to be in both fields in case someone changes either field later.

You can also create an update Query to update any current data.
__________________
OBP
I do not give up easily
JoeBlack999's Avatar
JoeBlack999 JoeBlack999 is offline
Computer Specs
Member with 77 posts.
THREAD STARTER
 
Join Date: May 2008
Experience: Intermediate
11-Dec-2008, 11:09 AM #3
Access 2007 - copy calculated reference number to underlying table
Quote:
Originally Posted by OBP View Post
I am not sure why they need this reference number, as the Autonumber does the job as far as the database is concerned.
But as you have the job to do, just add the new Reference Number field to the Data Input Form and then use the equivalent VBA version of your Formula in the First and Last Name Fields After Update Event Procedures.
The VBA Version is
me.referencenumber = Left(me.[FirstName],3) & Left(me.[LastName],3) & me.[ID]
where referencenumber is the actual name of your new field.
It needs to be in both fields in case someone changes either field later.

You can also create an update Query to update any current data.


I assume its the code builder, option on the afer update event procedure?

Private Sub FirstName_AfterUpdate()
Me.Reference = Left(Me.[FirstName], 3) & Left(Me.[LastName], 3) & Me.[ID]
End Sub


Access spits out some errors, what or where have I goofed up?
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,441 posts.
 
Join Date: Mar 2005
Location: UK
12-Dec-2008, 06:42 AM #4
Sorry , I missed your reply yesterday, what errors do you get?
You may need to set some Library references to use the VBA code.
JoeBlack999's Avatar
JoeBlack999 JoeBlack999 is offline
Computer Specs
Member with 77 posts.
THREAD STARTER
 
Join Date: May 2008
Experience: Intermediate
19-Dec-2008, 12:27 PM #5
RE: Access 2007 - copy calculated reference number to underlying table
Quote:
Originally Posted by JoeBlack999 View Post
I assume its the code builder, option on the afer update event procedure?

Private Sub FirstName_AfterUpdate()
Me.Reference = Left(Me.[FirstName], 3) & Left(Me.[LastName], 3) & Me.[ID]
End Sub


Access spits out some errors, what or where have I goofed up?

Sorry for the delay in responding - the code is working - must have not pasted it in correctly - thanks for your help - problem now solved.l
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
2007, access, autonumber, calculated, reference

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑