Solved

AutoNumber Variation

Posted on 1998-09-16
11
422 Views
Last Modified: 2008-02-26
I have a trip number field on my form that is (1) the first and last initials of the employee (2) the first three letters of the destination city and (3) a four digit sequential number beginning with 0001.  I'm thinking Access 7 can put these together for me.  Can it, and can you tell me how to implement it without using code or programming language?
0
Comment
Question by:cindybr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
11 Comments
 

Author Comment

by:cindybr
ID: 1962482
Edited text of question
0
 

Author Comment

by:cindybr
ID: 1962483
Edited text of question
0
 
LVL 8

Expert Comment

by:Helicopter
ID: 1962484
You can create this as each record is being saved but you are going to need at least some code I think, if only to calculate the next sequential number, unless you use the autonumber as your sequence generator. It depends how important these sequential numbers are. If they just need to be unique then it's OK but if you use an auto number and then delete a record the sequence will go out.

Is the sequence applicable to the subset of records , i.e. could you have

ABLON0001
and
ACLON0001

or would you want the 2nd to be ACLON0002?


0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:cindybr
ID: 1962485
The sequence is not applicable.  It only has to be unique.
0
 
LVL 8

Expert Comment

by:Helicopter
ID: 1962486
Well, I can do it with a line of code if you like. Tell me what the textboxes are and what fields they are bound to in your table.
0
 
LVL 4

Expert Comment

by:raoool
ID: 1962487
Methinks you could do this basing the form on an update query using an expression to create the field. Something like this...
=Left([NameFirst],1) & Left ([NameLast],1) & Left ([Destination],3) & RIGHT ([AutoSeqNum],4).

.with your field names, of course ;)

Access help file has decent examples under 'combining fields'.

Hope this is useful!

Raoool
0
 

Author Comment

by:cindybr
ID: 1962488
[Employee] (First and Last Initials) + [Destination City] (First three letters) + [Autonumber] (Four digit sequence beginning with 0001) = [Trip Number].  I hope this is what you need.  Will I be able to just copy the code and where do I put it?  Sorry to complicate things.  
0
 

Author Comment

by:cindybr
ID: 1962489
To raoool:  thanks for your comment.  If I can figure out how to grade both responses, I will certainly give you some credit.  I'm not sure what basing the form on an update query will do with the other fields in the form however.  I guess I'm not sure what the benefit of basing any form on an update query would be but then again, maybe I am really missing something.  Anyway thanks.
0
 
LVL 8

Accepted Solution

by:
Helicopter earned 50 total points
ID: 1962490
Put this in the before update event of your form

Me.tripnumber = Left(Me.employee,1) + Right(Me.employee, 1) & Left(Me.Destinationcity, 3) & Format(Me.ID, "0000")


This assumes your form is bound to a table with an autonumber and a text field called tripnumber

On your form you have 3 text boxes called employee,destinationcity and tripnumber bound to the relevant fields in your table.

Your autonumber field in my example is called ID so you will need to change the last bit to:
 Format(Me.(your autonumber fieldname here), "0000")

This doesn't actually use the autonumber for any other purpose than generating the unique sequence. If you want to use this created field as an autonumber it would require knowing the contents of a new record before you move to the record, which is obviously not possible.

It looks more complicated than it is....anyway give it a go and let me know.
0
 
LVL 4

Expert Comment

by:raoool
ID: 1962491
cindybr

Was mainly pointing out the expression possibilities... the update query thing was a half-thought-through way to make the update happen. *shrug*
tho i do think it'd work. (shouldn't affect the other fields -- you'd just have to include those in the query)

raoool
 
0
 

Author Comment

by:cindybr
ID: 1962492
And you were so right at least about the expression.  I have been so uptight about using code but it looks like there is just no way to move forward without it.  Better get used to it.  (First of all, I guess i'd better learn to use it.)  Anyway, the expression/code both you and Helicopter came up works great.  Thanks much!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question