Creating a unique item number using two existing fields

[JLongo]

Hi All,

Access 2003.

I am creating a new database to track items on my online shop.

I have an input form to add a new item into the database. What I need help with is creating a unique item number using a autonumber field and a category field.

For example, I have a field named Item Number which is autonumbered so I never have the same number twice (this is my primary key). This field is called "ItemNumber". I have another field called "Category" where the user selects what category the item being added falls under. (i.e Cart, Chair, Computer, Desk, Table...ect).

Here's the tricky part, I want to create a "NewItemNumber" using these two fields with an abbreviation of the Category going in front of the Item Number (autonumber).

I've given some examples of categories but I'm unsure how to assign the abbreviation (i.e Cart = ct, chair = c, desk = d...) to the autonumber since my category list has no abbreviations in them.

Just to further clarify:
Category List:
Cart
Chair
Computer
Desk
Table

Autonumber/item number is 5 (5th item being added to the database) and its a desk.

I'd like the form to generate in the newitemnumber field: "d5"

Another example, autonumber/item number 6 (next item after the desk) and its a computer. So i'd like "cpu6" generated.



I hope this is clear enough for someone to help me out. Thank you in advance

Josh

[weekend00]

How does the computer know you want cpu6 or c6 or com6 for computer if you don't have abbreviations in the table?

if you want the computer to decide the abbr., it may be hard for user to understand.

[JLongo]

I dont have the abbreviations yet, but I could add them as another column in the table.

I'm just unsure how to code to let the system know to pull the abbreviation name/field and combine it with the autonumber.

I will set up my category table to have category and categoryAbbr..but then what?

[JLongo]

OK.

On my input form, when the user selects a category, the autonumber floods in and i created an additional field next to category that floods in the abbreviation when the category is selected.

Now all I need is this autonumber and abbreviation to be combined in a text box.

Now I tried putting as a control source of this text box =[itemnumber] + [text35]

Note text35 is the abbreviation field but this doesnt work because it is trying to add these values together like a math addition. I just want the values to be combined text wise...does any one know how to achieve this?

[weekend00]

use: this text box =[text35] & [itemnumber]
or this text box =[text35] & cstr([itemnumber])

[JLongo]

Ok we are almost there.

Let me explain my two category text fields.

The first one is the "Category" Field with row source:
SELECT [tblCategory].[Category], [tblCategory].[CategoryAbbr] FROM [tblCategory] ORDER BY [Category];

The second text field is one i created "text35" with control source Category with row source:
SELECT [tblCategory].[Category], [tblCategory].[CategoryAbbr] FROM [tblCategory] ORDER BY [Category];

Which is the same, but I changed what column was showing. The first field has the first column, the second field has the second column.

When I do [text35] & [itemnumber] it is still giving me the full category name with the autonumber. I need the abbreviation with the autonumber and im unsure how to manipulate what we have so far to make that happen..

[weekend00]

are you using listbox to show the list? Maybe you can change the bound column from 1 to 2 (in property:data page) to get the abbr. value

[JLongo]

I'm using combo boxes. I have both fields with the columns set to 2 already..and when I select a category, the abbr field floods in perfect. I hide the abbrev column in the first field and then vice versa in the 2nd.

I just can't get this abbrev to flood into the 3rd field (NewItemNumber)

[weekend00]

I am not sure how to match two combo box, I would use some code to populate the combined field.

if you want to try, just delete the second combo box "text35", and put following code in change event of combo box "category"

Private Sub Category_Change()
dim abbr as string
abbr=dlookup("CategoryAbbr","tblcategory","categor y='"+category+"'")
newitemnumber=abbr+itemnumber
End Sub

[JLongo]

Heres my DB, take a look at the Input Form..

Thanks for your help so far.

[weekend00]

I think it's about the control souce field. the control souce is "category" which make the value of text35 is category instead of categoryabbr.

I am not sure how to fix that, but put this formula in the box "GenerateItemNumber" works:

=DLookUp("CategoryAbbr","tblcategory","category='" +[category]+"'") & [itemnumber]
to replace =[Text35] & [itemnumber]

[JLongo]

I got -1 as the item number, something is wrong

[weekend00]

did you mean itemnumber or new item number?

[JLongo]

the newitemnumber aka the generateditemnumber..

[weekend00]

I got the right number