Solved

Incrementing Text Field

Posted on 2003-11-13
17
510 Views
Last Modified: 2008-03-06
Okay, I am on the search for the answer to this question.  I know I can always count on you people to help me with the solution, while I am also searching through my "magical" books for the answer.  Here goes:

I have a primary key that is data type of text, 6 field size.  I need it to increment 1 value at a time.

For example:
AL001...keeping the place holder of 001, next one being AL002 and so on.  It will never go over 999.  But I need the 00# placeholders to keep it 6 characters always.

Now here is the "down and dirty part"...it goes by state.  So AL starts with 001 and keeps going for all Alabama clients.  Then CA starts with 001 and keeps going for all California clients, and so forth.
I was thinking of making the state and then a text field of 3 field size and increment it, both being the primary key, but then how does it know to start over when the state field is different, ya know what I mean?

Does anyone have any ideas on how to accomplish this task?  I would greatly appreciate it!
0
Comment
Question by:huffine
  • 7
  • 5
  • 2
  • +2
17 Comments
 
LVL 9

Expert Comment

by:svenkarlsen
Comment Utility
Hi huffine,

next value = Prefix & format(dmax("right(PK,3)","Table", "left(PK,3)='" & Prefix & "'") +1,"000")

'Prefix' = e.g. 'AL'
'PK' = field name of your prim. key.
'table' = your table name

Kind regards,
Sven
0
 
LVL 2

Author Comment

by:huffine
Comment Utility
Can you give me a little more information:
My table (for now just testing) is Table1
My state field (data type text 2 size) is called state
My Number field (data type text 3 size) is called Num

The num and the state are primary keys together.

So are you telling me, if I put this in the query:

next value: state & format(dmax("right(Num,3)","Table1", "left(num,3)='" & state & "'") +1,"000")

It will work?  Because when I run it, it only gives me the AL not the number also.  What am I doing wrong?  and does this automatically know to start over with each prefix (state)?

Thanks so much for your help Sven!
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
just a quick note....by my count AL006 is 5 characters, not 6.  so which is it, 5 or 6?

AW
0
 
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 100 total points
Comment Utility
Comment for your consideration.

Don't use it as your primary key.  Use, say, CustID (autonumber as PK) and Cust_ID (number/long)  instead to keep access functionning.  

For you own use, install in index on StatField and RecordID and increment RecordID using a user-defined function.

StatField    RecordID
CA             001
AL             001
CA            002
CA            004
.

Index will keep the combination unique (CA001, CA002, CA003, AL001 can be concatinated by a query when you need).

Also, let us know what happens when you delete say CA002 for some reason.  do you want the next number to start from CA004 or reuse CA002?

Regards,

Mike
0
 
LVL 2

Expert Comment

by:glennkerr
Comment Utility
I like Mike's approach.
I always use a unique ID field (and make it autonumber) to ensure (reasonably) that you never get a duplicate ID.

If you have the following fields:

CustID          Number    (Long)    Autonumber
State            Text  2
CustNo         Number    (Long)

Then place the following function in a module:

Public Function GetNextCustNumber(sState As String) As String
    Dim sql As String
    Dim rs As New ADODB.Recordset
   
    sql = "SELECT Max(Customers.CustNo) AS MaxOfCustNo " & _
        "FROM Customers " & _
        "GROUP BY Customers.State " & _
        "HAVING (((Customers.State)='" & sState & "'));"
    rs.Open sql, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    If rs.BOF And rs.EOF Then
        GetNextCustNumber = "001"
    Else
        GetNextCustNumber = Format(rs("MaxOfCustNo") + 1, "000")
    End If
End Function

Then, when you want the next number (without the state prefix), make the call like this...

sNextNumber = GetNextCustNumber(sState)

or add the state...

sNextNumber = sState & GetNextCustNumber(sState)
0
 
LVL 2

Expert Comment

by:glennkerr
Comment Utility
Sorry... the field CustNo should have been Text (3)
0
 
LVL 2

Expert Comment

by:glennkerr
Comment Utility
Opps, I forgot to add the brackets for the field names....

INSERT INTO tablename (field1, field2, ...)  VALUES (value1, "stringvalue2", ...)
0
 
LVL 9

Expert Comment

by:svenkarlsen
Comment Utility
Huffine:

1.:
> I have a primary key that is data type of text, 6 field size.  I need it to increment 1 value at a time.

2.:
> My table (for now just testing) is Table1
> My state field (data type text 2 size) is called state
> My Number field (data type text 3 size) is called Num

There is some conflict here, - what do you want: one field or two fields ?

My suggested solution was aimed at a single text field of 6 characters static size, - I have verified following against a test Db in Access:

Table Name: Table1
Field Name : PK

NxtValue = "XXX" & Format(DMax("right([PK],3)","Table1", "left(PK,3)='XXX'") +1,"000")
debug.print NxtValue


Regards,
Sven
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 2

Author Comment

by:huffine
Comment Utility
Thanks for your help everyone, I will try it out and get back to you in how it goes.  I am more leaning towards Mike and Glenn's suggestions.  But I do appreciate everyone's help in this.

Mike, I would say if you delete a record say, I would want to use that number again in the future.  Which might change the answers you and Glenn gave me, since these solutions are working with maximum values, right?

0
 
LVL 2

Expert Comment

by:glennkerr
Comment Utility
Yeah, my routine will give you the next highest value.

I'd recommend not replaceing "old" values, as it can get confusing for customers.  Users often learn the customer numbers and if one day they look up a deleted customer (say, "AB005"), and it's gone, then they probably just go looking for it by name, etc.  But, if it gets found, but the customer information is all different (because it's a different customer), then they get confused.

Also, users new to databases sometimes open a customer  and just start changing all the fields in order to create a new customer (obviously, they're actually changing the record that used to be some other customer).  Now, if you allow your system to reuse unused customer numbers, the you won't know if it was the "deleted, then system assigned the same number as the next available CustNo" scenario or the "user doesn't know how to create a new customer" scenario.

Treat customer numbers like you do unique IDs in your database - never reuse them as you never know what else might be left hanging in your db, still pointing to the old number.
0
 
LVL 2

Author Comment

by:huffine
Comment Utility
I didn't think about that Glenn, good point, very good point, you never know what those little "end-users" are going to do next, huh?  They do keep our jobs interesting though...challenging, but interesting!!

I'll do that then, not reuse.  Very good thought process and something I didn't think about.  I am just amazed but this is my first client that wants to make up their ID's themselves, usually no one cares (autonumber), or uses social security numbers for individual people... I have never had to face this issue.  Good lessons to be learn with this client's request!

Thanks again!
0
 
LVL 2

Expert Comment

by:glennkerr
Comment Utility
I get that all the time.  I've got one client that brings it up everytime he sees me on a table...

"Hey Glenn, let's make the ID field the first 3 characters of the customer's last name, the next six the date he was entered in the system, the next 2 the initials of his dog...blah, blah, blah"  almost enought to make me pass out.

Here's my philosophy:

Always use and ID field set to autonumber as the primary key.
Always use the customer's common name for tables and fields
(e.g.  If he calls them Clients, then use tblClients  with a PK of ClientID)
Always use an abbreviated Secondary Key in other tables so you never confuse the two.
(e.g. If we had a table of people who worked for the client called tdfContacts with a PK of ContactID, then the secondary key would be something like CID or CltID).
And last and most important - Never tell the customer anything about the primary keys!
(don't even let them SEE it!)

Always refer to "this client" or "That client"
If your program always refers to the ID's in the background, then the user can always change anything they like without fear of screwing up relationships.

Even if you do have a number that is guaranteed to be unique, don't use it as a primary key because there are so many other pit falls.  Here's a couple examples...

1.  You decide to use the SSN as a key.  So, the user creates a new client, adds the SSN, name, etc.  In the next month or so, continues to add data that relates to the new client, but realizes that he entered the SSN wrong and wants to change it.  There are a number of easy to hard ways to ensure that the SSN get's updated in all the related tables, but if you had used your own unique PK (with an autonumber), then it wouldn't matter and you don't need to account for anything.

2.  Same scenario as before.  User goes to create a new client, but the client doesnt' know his SSN.  It's a shame to force them to make up bogus values just to make your program work.  There's only one thing worse than missing data - wrong data (because you can't tell if it's wrong, but missing data is obvious).

3.  Same as before.  User A enters in an SSN incorrectly (last month sometime).  User B enters in a new client, but the system won't accept it because the SSN already exists!

The list goes on.

Don't get me wrong, not using the old autonumber has been a standard in the past, and still flourishes quite well with a lot of great software, but anytime I've strayed from the above, I've regretted it.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
Hi huffine,

Re:> Mike, I would say if you delete a record say, I would want to use that number again in the future.  Which might change the answers you and Glenn gave me, since these solutions are working with maximum values, right?

No. My proposed solution doesn't work with max. exist. value.  As a matter of fact, because I haven't any response from you regarding my suggestion "not to use this fied as your PK,..."  (see my last post), I haven't presented any solusion as to how we need to handle the Increments.

glennkerr, after reading my post, offered a solution.  He or I, could revise it to Increment according to your preferrences.  If you are interested in this approach, we will be gald to help out.  In a long run you will be happy to let Access to keep its sanity via PKs and FKs it greates automatically.

Regards,

Mike
0
 
LVL 2

Author Comment

by:huffine
Comment Utility
Mike or Glenn,
Since you both are helping me with this:  I am adding the Public Function, doing fine so far.  But I am a little confused with the statement from you glenn of:
"I forgot to add the brackets for the field names...
INSERT INTO tablename (field1, field2, ...) VALUES (value1, "stringvalue2,...)"

Not sure where to put this line of code....I do not use sql code, so I am a beginner in what you are trying to teach me here.  Where does this INSERT INTO line code....after HAVING line?  and what are the exact things I type...with my table name being tblOrganization, RecordID (being the field that will increment) and State being the state field (of course).

I usually always make my qry in the GUI and then do a DLookup instead of writing sql statements.  

Please help!!  and I greatly appreciate it.
0
 
LVL 2

Expert Comment

by:glennkerr
Comment Utility
Sorry Huffine - I think that comment was for another question (opps!!)
0
 
LVL 2

Author Comment

by:huffine
Comment Utility
Okay, so I don't need it?

I did all the code, set it up exactly as stated.  However, when I put the code on the state field (after update) for it to run the code line:
sNextNumber = GetNextCustNumber(sState)
(I changed sNextNumber to Me.RecordID, since that is the field's name for the increment number)
I get "variable not defined and it highlights the (sState) variable in the code line.

Why would that be?  I looked at the function, it is exactly as stated:
Public Function GetNextCustNumber (sState as String) as String

So why can't it see that and call it up?

Thanks for your help!
0
 
LVL 2

Accepted Solution

by:
glennkerr earned 400 total points
Comment Utility
Here's what it might look like..

Lets say you have a form bound to a table with the following fields...
CustomerState
CustomerNumber
CustomerName
... etc.

in the AfterInsert event (you want this code to run only once - when a new record is created, and not everytime the record is updated), you would write...

Private Sub Form_AfterInsert()
    Dim sState As String
    If Not IsNull(Me.State) Then
        sState = Me.State
    End If
   
    Me.CustNo = GetNextCustNumber(sState)
End Sub

The error you were getting is because you didn't declare and assign the variable "sState"

You could use the AfterUpdate event as well, but you will need to check to make sure you're not changing a previously assigned Customer Number.
(e.g.  If not isnull(Me.CustomerNumber) then...  )

I've updated the routine to allow for more conditions...

Public Function GetNextCustNumber(sState As String) As String
    Dim sql As String
    Dim rs As New ADODB.Recordset
   
    sql = "SELECT Max(Customers.CustNo) AS MaxOfCustNo " & _
        "FROM Customers " & _
        "GROUP BY Customers.State " & _
        "HAVING (((Customers.State)='" & sState & "'));"
    rs.Open sql, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    If IsNull(rs("MaxOfCustNo")) Then
        GetNextCustNumber = "001"
    Else
        If rs.BOF And rs.EOF Then
            GetNextCustNumber = "001"
        Else
            GetNextCustNumber = Format(rs("MaxOfCustNo") + 1, "000")
        End If
    End If
End Function

By the way, you should probably think about setting a standard for yourself on naming fields.  The idea is that you, (and anyone else that looks at your code) will be able to easily identify what type of data the field contains just by the Name.  I would have never guessed that "RecordID" would contain the CustomerNumber.  Instead I would have guessed that "RecordID" contains a unique identifier for each record (which it doesn't, since we're leaving the State out of the Customer number - right?).

Hope this helps.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now