AutoNumber Variation

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?
cindybrAsked:
Who is Participating?
 
HelicopterConnect With a Mentor Commented:
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
 
cindybrAuthor Commented:
Edited text of question
0
 
cindybrAuthor Commented:
Edited text of question
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
HelicopterCommented:
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
 
cindybrAuthor Commented:
The sequence is not applicable.  It only has to be unique.
0
 
HelicopterCommented:
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
 
raooolCommented:
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
 
cindybrAuthor Commented:
[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
 
cindybrAuthor Commented:
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
 
raooolCommented:
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
 
cindybrAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.