?
Solved

AutoNumber Variation

Posted on 1998-09-16
11
Medium Priority
?
425 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 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:
 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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 …
Suggested Courses

764 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