Prefix + AutoNumber as Item Code (1 Viewer)

[johannaellamay]

Hi, I'm making a new database and I want to assign Item Codes to my inventory. For example:

ItemCode (Primary Key) | Item | ItemDescription

I want to generate an automated item code containing the first letter of the Item (as prefix) followed by numbers. Example:

ItemCode: P001
Item: Paper
ItemDesciption: Ordinary white paper

Please help.
I'm a newbie.

 

[vbaInet]

If the item and item description will never change, then you can use the Left() function to get the first letter of the item and join it using ampersand "&". You also have to put some constraints on the length of the Item Description and ensure the Item and Item Description are not duplicated.

Welcome to the forum!

 

[GinaWhipp]

Welcome to the Forum!

Hmm, what you want is to use Autonumber for the PK and then generate an ItemCode. Safer that way.

Now, a few things here. Your ItemCode, do you plan on never haiving more than 999 Items that start with *P*? See the problem? Because that will be the problem for the entire list, no letter can go beyond 999 without becoming a huge issue.

Are they going to click a button to add a new tiem or use a navigation button or maybe you're opening a seperate Form to add new?

Here's some air code...

[FONT=Segoe UI]        Dim lngMyNumber As Long[/FONT]
[FONT=Segoe UI] [/FONT]
[FONT=Segoe UI]        strMyNumber = Nz(DCount("Right(ItemCode,3),", "tblItems", "Left(ItemCode,1) = '" & Left(Me.txtItemCode, 1) & "'"), 0) + 1[/FONT]
[FONT=Segoe UI] [/FONT]
[FONT=Segoe UI]        Me.txtItemCode = StrConv(Trim(Left(Me.txtItemCode, 1)), vbUpperCase) & Format(lngMyNumber, "000")[/FONT]
[FONT=Segoe UI]        DoCmd.RunCommand acCmdSaveRecord[/FONT]

 

[vbaInet]

Oh so this isn't a combination of the Item and Item Description!

 

[GinaWhipp]

@vbaInet

Not the way I read it.

 

[vbaInet]

@vbaInet

Not the way I read it.

Yep, I re-read it and realised

 

[gemma-the-husky]

Johanna

I would not join the letter with the number. I would use two fields, one for number, and one for letter, and join them together when needed

a) much easier to manage
b) much more flexible

you can then decide whether you want a single sequence, or a sequence for each prefix.

 

[johannaellamay]

If the item and item description will never change, then you can use the Left() function to get the first letter of the item and join it using ampersand "&". You also have to put some constraints on the length of the Item Description and ensure the Item and Item Description are not duplicated.

I know what you mean. But where do I put that expression? I mean, in which format property? Thanks by the way for the quick response.

 

[johannaellamay]

Welcome to the Forum!

Hmm, what you want is to use Autonumber for the PK and then generate an ItemCode. Safer that way.

Now, a few things here. Your ItemCode, do you plan on never haiving more than 999 Items that start with *P*? See the problem? Because that will be the problem for the entire list, no letter can go beyond 999 without becoming a huge issue.

Are they going to click a button to add a new tiem or use a navigation button or maybe you're opening a seperate Form to add new?

Here's some air code...

[FONT=Segoe UI]        Dim lngMyNumber As Long[/FONT]
[FONT=Segoe UI] [/FONT]
[FONT=Segoe UI]        strMyNumber = Nz(DCount("Right(ItemCode,3),", "tblItems", "Left(ItemCode,1) = '" & Left(Me.txtItemCode, 1) & "'"), 0) + 1[/FONT]
[FONT=Segoe UI] [/FONT]
[FONT=Segoe UI]        Me.txtItemCode = StrConv(Trim(Left(Me.txtItemCode, 1)), vbUpperCase) & Format(lngMyNumber, "000")[/FONT]
[FONT=Segoe UI]        DoCmd.RunCommand acCmdSaveRecord[/FONT]

Hi! Thanks for the overwhelming reply. To be honest, I have no idea about the codes that you jut wrote. I'm sorry.
Anyway, to answer your questions:

1. I'm not so worried about each item reaching 999. In fact, that's already a huge limit. I'm tracking Office Supplies in my office because my job is to be the Property Custodian. I'm pretty sure no office supply starting with letter P for example, would reach 999. So it's not a huge issue.

2. Yes, I want to be able to click a sort of button when I am adding new items. I can imagine it as a form with a submit button.

I'm still slowly starting to build my database. I have much to learn. ))

 

[johannaellamay]

Johanna

I would not join the letter with the number. I would use two fields, one for number, and one for letter, and join them together when needed

a) much easier to manage
b) much more flexible

you can then decide whether you want a single sequence, or a sequence for each prefix.

Yeah, I get your point. But doing so would totally defeat the purpose of extracting the first letter of an Item and combining it with a number. It's just gonna add more fields and a lot more confusion.

Plus, I want the numbers to return to 1 after every letter, example:

Item Code Item
Adapter A001
Air freshener A002
Alcohol A003
Ballpen B001
Bandage B002
Cotton bud C001

And so on...

So if I follow your advise, it's gonna look like this:

ItemCode (PK) ItemNo (autonumber) Letter Item
A1 1 A Adapter
A2 2 A Air freshener
A3 3 A Alcohol
B4 4 B Ballpen
B5 5 B Bandage
C6 6 C Cotton bud

Since the ItemNo is autonumbered, then I wouldn't be able to reset the numbers for every letter, would I?

:banghead: HAHA. I'm sorry, I think too much sometimes. I appreciate your reply, really.

 

[GinaWhipp]

Okay, so let's not get overwhelmed. I am judging by your reply you have not built the Tables or the Forms yet. The air code I provided would go on a button BUT that is not (and should not) be your concern right now. You need to build the tables and post them for review. Now, for Items you can do something like...

Items
ItemID (PK - Autonumber)
ItemCode (However you want to store it)
ItemDescription

 

[johannaellamay]

So you guys could have a better understanding of what I'm building...

View attachment ItemsT.zip

Before, I'd have to manually input the Item Codes on Excel. I had to check if I had any duplicates and stuff and it's just a lot of work. One item could have many specifications like a binder could have different sizes, different colors, different brands, etc. So I wanted to have unique item codes.

The main reason why I wanted item codes is this:

I am the property custodian and I want to track all inventory of consumable supplies. I have an assistant, she's not very savvy with gadgets so I want to make things simpler so she could understand, and so she can be able to navigate around the database easily. I want to her to only see a sort of form where she's just gonna type the Item Code, Qty and Unit everytime a staff requests for a certain item. If she inputs that code, then the item specs will automatically follow. Etc, etc.

Not sure if I'm making sense but yeah.

 

[johannaellamay]

Okay, so let's not get overwhelmed. I am judging by your reply you have not built the Tables or the Forms yet. The air code I provided would go on a button BUT that is not (and should not) be your concern right now. You need to build the tables and post them for review. Now, for Items you can do something like...

Items
ItemID (PK - Autonumber)
ItemCode (However you want to store it)
ItemDescription

Hey, you are very much correct. HAHA. In fact, I'm only making my first table. So based on your reply, I would just have to settle with a different Field that's autonumbered, and I would still have to manually input my ItemCode, wouldn't I?

Bottomline question now is,

Is it possible for me to have an AutoNumber Field that has a Prefix of the first letter of a different field? If so, how?

I guess that's what I am trying to put across. If it's not possible, then I'm just gonna have to find a different way to generate item codes. But you see, I want my ItemCode to be the PK, but I don't want it to plainly be numbers, if that makes sense.

 

[GinaWhipp]

Okay well, once you've made your Table(s) post it here for use to have a look. However, seems lot a lot of work if you just want to list Items. Are you going to tie this to Purchase Orders or something?

 

[GinaWhipp]

The Autonumber is just for the Primary Key for Access, it should have no other use but to supply a unique identifier for each record. Use the ItemCode field for your *custom* key but it will not (and should not) be a Primary Key.

 

[johannaellamay]

Hey GinaWhipp,

Good points. Here's the process of how staff can request for items that I store in inventory.

1. Staff goes to the Property Custodian, that's me, to ask for certain items, let's say ItemCode is A001, Item is Adapter.
2. I let the staff sign an Acknowledgement Receipt that looks like this:

View attachment 54810
(disregard the SR No. column)

Every staff needs to sign an Acknowledgement Receipt as proof that I have indeed released the item and that the staff has received it.

3. I, or my assistant, encodes the released item at a certain period on the database, for example, every lunch break of before leaving for work. So that's going to be a sort of form.

My boss then, wants a report of each department's usage of supplies, and when we should reorder. But that's a different issue. That's all for now.

 

[johannaellamay]

The Autonumber is just for the Primary Key for Access, it should have no other use but to supply a unique identifier for each record. Use the ItemCode field for your *custom* key but it will not (and should not) be a Primary Key.

I see your point. Thanks for this.

So what you're saying is (regardless of PK), I won't be able to have a field that's autonumbered and that contains a prefix?

 

[GinaWhipp]

You can have a key that is autonumbered with a prefix, use ItemCode. Your key does not have to be the Primary Key. And, just because it is not the PK does not mean you can't use it for look-ups and the such.

 

[GinaWhipp]

Cannot view attachment in Post 16, it says invalid.

 

[johannaellamay]

You can have a key that is autonumbered with a prefix, use ItemCode. Your key does not have to be the Primary Key. And, just because it is not the PK does not mean you can't use it for look-ups and the such.

Okay, I get it. So anyway, how do I do that? How can I have an autonumber with a prefix? I'll just have that and a different PK then.

Here are my considerations:

1. I want the first letter of the Item plus the autonumber.
2. I want the number to reset to 1 after every letter, if possible.
3. I want it to be automatic everytime I add a new item through a Form.