New number for same item name

[syedadnan]

Regards,

I am having a query where i want to get new number for same item like i am doing it for asset tagging for example;

LCD it should be 1
then if repeat LCD then its number should be 2

same as if AC then 1
if repeat then 2 and so on

 

[GinaWhipp]

Going to need a wee bit more information...

1. Where does the LCD and the AC come from?
2. Are you using a Form and a Command Button to get the next ID?
3. Are you storing the newly created ID?

Note, to do the above going to need a little VBA.

 

[syedadnan]

Going to need a wee bit more information...

1. Where does the LCD and the AC come from?
2. Are you using a Form and a Command Button to get the next ID?
3. Are you storing the newly created ID?

Note, to do the above going to need a little VBA.

Thanks for the reply

I have a table asset management where i have a dropdown list and all items are listed there once i select LCD from there means i purchase it and then enter other information same if i purchase new LCD again then the same process again i want to get number for specific item after purchase means if i purchase LCD once it shoud generate 1 in query the 2 for second and so on

 

[GinaWhipp]

1. So, I am assuming LCD and AC are in the table
2. This cannot be done in the Table because there is going to be some VBA involved. Remember Tables in Access are not like Excel spreadsheets.

So, if I understand you correctly you are using a Table with Look-Up Fields which in itself presents its own problem. So, to do this so it work you rre going to need a Form. Now, that said, I might be able to come up with an UPDATE query but you would still have to close the Table every time and run the Query. (And that is a big MIGHT because it is not the standard way to accomplish this.)

Do you have any Forms in this database?

 

[syedadnan]

1. So, I am assuming LCD and AC are in the table
2. This cannot be done in the Table because there is going to be some VBA involved. Remember Tables in Access are not like Excel spreadsheets.

So, if I understand you correctly you are using a Table with Look-Up Fields which in itself presents its own problem. So, to do this so it work you rre going to need a Form. Now, that said, I might be able to come up with an UPDATE query but you would still have to close the Table every time and run the Query. (And that is a big MIGHT because it is not the standard way to accomplish this.)

Do you have any Forms in this database?

Yes i am having a form name Asset Form where i am entring info of asset purchasing

 

[GinaWhipp]

Okay, so now I need...

1. The name of the Table you want these ID's added to
2. The name of the field that contains the LCD and AC information
3. The name of the field you want to place the newly created ID in.

 

[syedadnan]

Okay, so now I need...

1. The name of the Table you want these ID's added to
2. The name of the field that contains the LCD and AC information
3. The name of the field you want to place the newly created ID in.

Thanks for your time..

1. Table name where i want to add item is "asset management"
2. Field name is "Asset name"
3. field name "Tag"

 

[GinaWhipp]

Oops , as I was writing the line I realized I forgot to ask the name of the field and table that contains the Part Number.

 

[pr2-eugin]

DMax + 1, by Rainlover seems to address this issue would you not think GinaWhipp?

Just trying to save you the hassle.

 

[GinaWhipp]

@pr2-eugin

Simple DMax() won't work in the case because the OP want to increment if ordering the same item, so...

LCD123-1
LCD123-2
LCD123-3
AC456-1
LCD123-4
etc...

 

[pr2-eugin]

Funny me ! I for some reason thought that was covered.

The attached is for use in a Multi User environment where it is possible for more than one user to request a new number at exactly the same time. The error is handled and a unique number is allocated to each user.

Groups are handled. E.g. Smith 1, Smith 2, Jones 1, Brown 1 etc.

 

[GinaWhipp]

I didn't realize you had actually posted a link! You are right it is covered. I really need to get some more coffee in me...