Macro to increase primary key by 1

[art2glass]

I'm sure this is a relatively simple matter, but I'm stumped.

My primary key is called "Item #". It's a long integer, fixed format, validation rule of ">1001000 And <9913000", required "no", indexed "Yes (No Duplicates)". I want to be able to use a shortcut such as "ctrl + m" to add one to the previous Item #, so that I don't have to type the new number every time I add a record to the table. I don't want the primary key to be an AutoNumber because I want the option to use an entirely different Item # for the next item.

I tried to create a macro, but I was just stymied. I would greatly appreciate any help.

 

[bradcccs]

I think you have missed the concept of an autonumber.

You are doing the correct thing using an autonumber as your PK, but you should not be trying to restrict the PK range or value.

I would suggest leaving the autonumber PK, and adding a seperate ItemNumber if you think it is required (ie don't use the ItemNumber as PK)

eg:

ItemID - Autonumber - PK
ItemNumber - Number(or Text)
ItemDetails - ???


Also, you should not be using embedded spaces or special characters in field or table names (eg: #)

HTH

Brad.

 

[dcx693]

The whole purpose, as I understood it, of a primary is uniquely "tag" each record with an identifier. The reason to use an autonumber is for convenience. And there's no reason the autonumbers have to increment. Access also gives you the choice of assigning random autonumbers within a given range - so it's not crazy that art2glass may want to restrict his numbering. In most cases, I wouldn't go through the trouble of assigning my own Item numbers, but sometimes it makes sense.

What DOES NOT make sense to me is why you'd set your primary key's "required" setting to "no". You are allowing users not to enter something in that field. Generally, a poor design choice, but if it works....

art2glass, I don't know if you can assign the "next" item # using a macro. I can tell you the general steps of how to do it via VBA code. Create a totals query and find the max of item #. Create a custom function that opens that totals query as a recordset and retrieves the max item #. The function will return the number. Just add 1 to it.

 

[FoFa]

This kind of thing is not new. Usually I create a Parameters table, stick the last value used in there. That also allows for easy changing the number if we need to. Write a VB function you can call that will get the last value used, add x to it, write the new value back to the parameter table, and return the new value to you. If it is a shared DB make sure you lock the record so two people to not "grab" the same value, although unlikely.

 

[bradcccs]

Perhaps I am just a bit old fashioned.

I never really understand why you would permit a user to have any interaction with a primary key. This only causes more background work for us poor programmers.

Then introduce the multi-user scenario, and you start playing with before update events and ahhhh....

Why not let access take care of the PK, and if you wish to have an "intuitive" item number etc as well, then include it as an extra field.

This way, you can use DCount, DMax to your hearts content on your intuitive item number.

But then again, that is only my humble opinion.

Brad.

 

[dcx693]

bradcccs, you are not old-fashioned! I generally don't let users do anything to primary keys either. The poster's situation may just be different from ours, that's all.

 

[art2glass]

Please let me add some additional details.

I am the only person who uses this database, so although the primary key is listed as not required, this is irrelevant. I could easily change it to required if it will not cause any problems.

The primary key item # is not an autonumber. I cannot use the highest number as the last number because it has the following format: dymm### where d is the decade (1 for the first decade), y is the year (3 for this year), mm is the month (06 for this month), and ### is the item number (001 for the first item). I changed my numbering system at the turn of the century. Last century my item numbers were yymm### (e.g. 9912001). The first item this month would be 1306001. Therefore older items would have higher numbers than newer ones.

What I would like to do is to be able to use a shortcut to add 1 to the previously entered item #. I presently use ctrl + ' to copy the previously entered field. I also use ctrl + ; to enter the date. I would like to be able to use ctrl + m to calculate 1 more than the previously entered item # and enter it.

Thanks again for any help.

 

[dcx693]

I don't know about macros, but VBA code cannot be run from a table (if that's how you're entering the numbers). If you use a query though, you can call some type of custom function. Or if you put it into a form, you can use a calculated text field.

 

[FoFa]

bradcccs
Here some departments are issued a block of PO numbers to use. They track their PO's in Access, and when they run out, they are issued a new block. They like autoincrement on the PO numbers, but when they get a new block, they have to go change the starting number, hence the primary key changes. The numbers are still unique. We could use a autonumber, but why since we would still have to index the PO number field with no duplicates?

 

[IgorB]

Create a function which will read a query.
In your query just select last record(Item #) using function "Last"
or "DLast".
You gonna get the last record, then +1.
In your macro you can easy attach this function.

Igor.