Solved

AutoNumber Variation

Posted on 1998-09-16
11
403 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
  • 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
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.  

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

837 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