Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# AutoNumber Variation

Posted on 1998-09-16
Medium Priority
434 Views
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
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
• 6
• 3
• 2

Author Comment

ID: 1962482
Edited text of question
0

Author Comment

ID: 1962483
Edited text of question
0

LVL 8

Expert Comment

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

Author Comment

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

LVL 8

Expert Comment

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

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

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

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

Helicopter earned 200 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:

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

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

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

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which â€¦
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a qâ€¦
###### Suggested Courses
Course of the Month11 days, 2 hours left to enroll