Generating my own AutoNumber? Maybe sequential Dmax...

[Pathetique]

I posted this in the tables forum (I think yesterday), but after searching other posts, I'm certain that it is a form issue. And, I think my solution will involve the Dmax function, but I'm not sure. Anyway, here's the posting:

I have a table that acts as a catalogue for the company's maps (its a land surveying company). I have many years worth of maps that are in desperate need of being organized. What I'm trying to do is order the maps according to an automatically generated MapIDCode. The autonumber feature in the tables is not going to work for my needs, as you'll see below.

First, it doesn't really matter what scheme is used to order the maps. I'll enter them roughly by year, but it isn't important that they be ordered by year. What is important is that they be in order according to this new MapIDCode, so that when someone uses the database they can find the location of a map.

Second, there are 2 sizes of maps one 24"x36"(standard) and one 18"x24"(oversize) that will be in seperate piles. This is the reason the regular Access Autonumber feature will not work. I want to use one form to add to the catalogue, regardless of whether the map is standard or oversize. I would like the standard size MapIDCode to have this format "00001" so I can have a maximum of 99999 maps (a number that is conveniently high enough to never reach yet minimizes the users entry time). The oversize MapIDCode could look something like "x0001". I'm not sure if it would be easier to have the same number of characters for each code or not. I would prefer to have just one MapIDCode field in the tblMaps table(so I could search the oversize and standard maps at the same time).

Finally, I can't have any skipped numbers. I do want to have a 00001 and a x0001 code for 2 different maps. The reason for this is so I don't have sequential skips in either size. I hope this makes sense. I have a form that has all the table's fields if this helps anyone. I would really appreciate some help here. I'm sure that it's possible, I'm just not good with programming.

1000000thx,
path

 

[Pathetique]

i've read quite a few posts now on incremental primary keys, and why it may not be a good idea to do it. i think i'll use an autonumber field as the primary key "in case" something ever goes wrong, and I'll use an automatically generated MapIDCode for the order (more like location) of the actual maps. however, i'm really lost with using Dmax. could someone go over the basics of the Dmax command...where the code goes, what the different parts refer to? I've read the Access 2000 help files on Dmax, but it never says where the code goes and i'm not sure i completely understand how to use it.

again the goal is to have 2 sets of maps, because there are two sizes. the MapIDCode must never have skips because it would make it very difficult to know whether we were missing any maps (you could technically check the database to see if the missing code exists, but that would be very time-consuming). i have and would prefer one form to do the entries, and the "IsOversized" check box could be used to generate the marker for each oversized MapIDCode... meaning if the check box is checked put the letters "BIG" in front of the incremental code(for example, the first oversize map would be "BIG00001" and the first standart size map would be just "00001") i can already see a problem in that the incremental part of the MapIDCode must "know" if the entry is oversized or not to know whether to use the last number from the standard sized maps or the last number from the oversized maps. Make sense? I thought this sounded so simple when I first came up with it, but this has turned into a daunting task for this newbie. any ideas, suggestions, thoughts, or comments are welcome.

Path

 

[Pathetique]

Progress

OK, so I've managed to get Dmax to work. I've posted the barebones of the database below so everyone can see what's going on.

The problem now is, as you will see, the user can only put one record in the table before the lame automatic code generation process that I made fails. this is because, I think, that there are letter characters intermingled with number characters after one record is saved, thus confusing the Dmax command. Am I right?

So the next step for me is to fix this problem. Is there a way that I can have the Dmax command ignore the first three letters or something. And, by the first 3 letters, I mean that the MapIDCode is either STD00001 or BIG00001 depending on whether the user checks the "Oversized?" checkbox. After that is solved, you will notice that the codes actually look like STD1 and not STD00001. Is it possible to have all these characters displayed so that every code is 8 characters in length? And, finally, even when these two problems are remedied, I can foresee that once STD00001 is used, the code BIG00001 will not be available. This would create skips in the oversized stack of maps that will result in problems (there is a fixed number of maps that will fit in each "book" that is numbered on the outside for us to know its contents, so the maps need to be in order with no skips and we would not know whether or not a map was missing or not, that is without going through the trouble of looking it up). I have a separate AutoNumber field in the table that can have skips without a problem, so that I can be assured to have a unique number with every record. I think that's all the info I have. I understand that this issue has been heavily debated by some members of this forum, as I've read almost all the posts on AutoNumbering, but the sole purpose of this simple database is to automatically create these map location codes. If there is a better way, please lead me down that path. I'm very open to anything at this point. Thanks in advance for you help.

Scott (path)

 

[Pathetique]

 

[Pathetique]

What about this? Could I have the IsOversized checkbox control where the record is saved? I'm thinking that I could have two tables, one for each size. But I'm not sure how I would have the MapIDCode detect from which table to get its next incremental number. I know not. Anybody... . . . . . . . . . .