Next sequential number

[Jetski]

I am trying to edit a form to autofill the next sequential number avalable for that field. I'm not sure if this is an issue or not, but the numbers used for this field are a combination of text and numbers, i.e. Kse0544. So if Kse0544 was the last number used for that field, I would like to be able to have Kse0555 autofilled for that field on a new record. Would this be possible.

Thank you.

[ssanfu]

Welcome to the forum.

Yes, it is possible.

One way is to have a one record, one field table to store the last used number.
Each time you add a record you would get the number from the table, add 1, store the new number in the table, concatenate "Kse" to the number, then store it in the field for the sequential number.

Or you could calculate it on-the-fly using a VBA function.


To see other examples, Google: Next sequential number Access VBA

[Jetski]

I have very limited database knowledge and no VBA knowledge I've been searching most of the afternoon for a solution and I've failed so far. I've seen other examples that seem to be close to what I am looking for but I can't make them work. This ID that I am using must stay as is i.e. Kse0544. The text portion will never change but this is a feature ID used by a GIS department to associate shapefiles with the database portion.

Steve, it sounds like your suggestion would work well but I wouldn't know how to implement this suggestion.

If more information is needed for what I am trying to accomplish, please let me know. Any and all help is appreciated.

[ssanfu]

Is it possible for you to attach your database (change any sensitive data - just need a few records for testing) for analysis?

(Compact & repair, WinZip, click on "Go advanced" in the reply pane, scroll down to Manage attachments)

[Jetski]

Steve,

I'm attaching a copy of one of the databases. Hopefully this works. The only two tables with any information are the Kse_co and Kse_su tables. I have simple forms set up for these for data entry.

Thank you,
Kraig

[rpeare]

First, a couple of cautions. If you have more than 1 concurrent user (the more you have the more risky it becomes) there is a chance that the numbers generated will each create the same number. It's a small chance but it could happen.

If you are going to do this I would recommend you do it on an UNBOUND data entry form, that way your data is only changed on a button click or other event that makes it harder for events to coincide.

At any rate what you want to do is going to include some VBA on your data entry form.

If you're using a bound form this will work as well I just do not like using bound forms if at all possible

Dim MaxID As String

If IsNull(FE_ID) Then
    MaxID = DMax("[FE_ID]", "kse_co")
    FE_ID = "Ksec" & Right("0000" & CLng(Right(MaxID, Len(MaxID) - 4)) + 1, 4)
End If

This assumes you have a field on your form called FE_ID, the same as it appears on your table

Another note of caution, if you delete any records (particularly if it's the most recent record) and you already have data in other tables associated with that record you are going to create incorrect links between tables. So... never delete data from these tables or you will corrupt your data.

[Jetski]

This seems to work well for me. I appreciate the time. A couple of things. I am the only person using these database files so there won't be an issue with coinciding events.

If I don't have any linked tables then I shouldn't have a problem with deleting files correct?

One other thing, is it possible to search through a couple of different tables in the above string to find the next available number? As an example, the Kse_en_co and Kse_en_su both use the same FE_ID (Ksee0001, etc.). I guess I can try and see what happens but I'm wondering if I add a comma after "kse_co" and add another table if it will search both for the last number used (i.e. for the tables with the same FE_ID; MaxID = DMax("[FE_ID]", "kse_en_co", "kse_en_su").

Thank you, this has already helped.

Kraig

[rpeare]

it doesn't matter if you are the only user or if your tables are linked. If you delete the most recent record and you've created records in other tables relating to that record you are going to screw up your referential integrity. The best option is NEVER to delete data. It's a far better practice to put a yes/no value in your table that is something akin to a 'void'. In other words any any forms/reports you produce you would filter out any 'voided' item.

let's take a look at a specific example.

KSE_CO has one record where the FE_ID is Ksec0001
KSE_EN_CO has one record where the FE_ID is Ksec0002
KSE_EN_SU has one record where the FE_ID is Ksec0003

If you are attempting to find the maximum value of FE_ID on three different tables you are doing something wrong with your data structure.

What you should have is a 'master' table with all of your FE_ID's and any data related directly to unique FE_ID's. Then store the data related to the FE_ID's in your separate tables. (KSE_CO, KSE_EN_CO, KSE_EN_SU)

I'd create a KSE_MASTER that stores the list of master FE_ID's and only check that when adding new records.

[Jetski]

Well, I really need a database class. I've taught myself what I know, which is very little.

These databases were set up before I came in and took over. Since I've taken over, I'm changing my specific process a little to better fit a few things I'm implimenting with my work routine. Part of this process involves changing up my database structure some. This would probably be a good time to try to "fix" some of the incorrect procedures that are in place, unfortunately for me, I have no idea what those might be (going back to your example of never deleting a file, I never thought about that). I'll have to have some conversations with our GIS department to see what kind of flexability I have to make some changes to my database files.
I don't have a clue why it was decided that all tables that have the _en designation have the same FE_ID. There are three of those tables and they are the only ones with the exact FE_ID. The rest have a diffent fourth character that differentiate between different property types.

Anyways, I think I'm getting a little to far into this for right now. You've got me thinking though. We have some basis access classes available online through my employer, but I'm not sure how for in depth they go. Since you brought up data structure, would you be able to recomend any type of online class (even if it cost) that I could take that might be able to begin to steer me in the correct direction?

Thank you for you time.
Kraig

[rpeare]

I've never taken an access class so I am not going to be much help there. anything I know is a product of a lot of trial and error

[ssanfu]

would you be able to recomend any type of online class

Not a class, but there are lots of tutorials.
Start off by going to Rogers Access Library an work through the tutorials: (there are 3)
http://www.rogersaccesslibrary.com/f...s_forum46.html

Crystal has tutorials and Access Basics:
http://www.accessmvp.com/Strive4Peace/

DataPig Technologies has a lot of video clips to help with specific problems:
http://datapigtechnologies.com/AccessMain.htm

[Jetski]

Not a class, but there are lots of tutorials.
Start off bu going to Rogers Access Library an work through the tutorials: (there are 3)
http://www.rogersaccesslibrary.com/f...s_forum46.html

Crystal has tutorials and Access Basics:
http://www.accessmvp.com/Strive4Peace/

DataPig Technologies has a lot of video clips to help with specific problems:
http://datapigtechnologies.com/AccessMain.htm

Thank you for the info!