automatic update of count

[saranmc]

i am using access 2010 and i have a question.

I have a form and all data entered into this form is saved into a table (as a new record).
so each entry has a IDnumber (which is not unique).
this ID has a text field in the form, where the user enters the ID.
What i want is, the next time a user wants to enter some data, the text field for this ID automatically has the last ID plus one.

Is there anyway to do this??

Thanks a lot!! hope I was clear enough...

[jforbes]

That's what the AutoNumber datatype is designed for. Will it not work for you?

[saranmc]

I do know about the autonumber, but that is not suitable in this situation. this ID has to be entered by the user. it goes like this "D-001, D-002, D-003......"

so if D001 is already filled in the table, i want D002 to show up on the form the next time the user enters new information...

[jforbes]

This Question is very similar: Autonumber Using alphanumeric (text and number) data type.

If you struggle adapting it, please post back what code you have developed so far, table and column names, and what problems you are experiencing along with anything else you think may be helpful.

[NeoPa]

SaranMC:
so each entry has a IDnumber (which is not unique).

Are you sure this is what you meant to say? That would be extremely unusual.

Assuming not, JForbes gave a link that should tell you all you need to know.

[saranmc]

@NeoPa, it is what i meant to say.

Because, the IDnumber is not exactly an ID number.
I have a list of part numbers and Project numbers.
so many part numbers can belong to a Project.
so it is the Project number that i am talking about.

so the first 10 part numbers can belong to D001, the next 5 belong to D002 and so on.....

so i want the last Project number + 1 to appear on the form.
for the next list of part numbers to be saved.

I hope this makes things a Little more clear..
sorry for confusing you.
thanks.

[saranmc]

@jforbes i tried out the code.

im getting an error " wrong Syntax "

actually the code is not too clear to me...
where do I put in my table and field Name??

1.Public Function getKey(ByRef sTable As String, ByRef sField As String, ByRef iKeyLen As Integer, ByRef sPrefix As String) As String
     Dim sLastKey As String
     sLastKey = DMax(sField, sTable)
     getKey = sPrefix & Right("00000" & Val((Right(sLastKey, Len(sLastKey) - Len(sPrefix))) + 1), iKeyLen - Len(sPrefix))
 End Function

i used this code as the Default property of the text box in my form where the data code is supposed to appear..

thanks

[jforbes]

I'm confused myself. I thought you were attempting to populate a Primary Key with an Alphanumeric value, but after reading your last couple of posts, I really don't understand what you are attempting to do.

What is it that you are trying to accomplish?

[saranmc]

no no... ist not a Primary key... Thats why i mentioned that its not unique.

ok, there are Projects, numbered D001, D002, D003......
then there are part numbers... they are Long numbers like L021457812, L3147925100.... ..

many part numbers can belong to one Project.

the table is supposed to contain part numbers with their corresponding Project number.
the Project number the user writes in the form. if the last Project was D009 this would be D010..

for eg, i say part number L0346512787 belongs to D001. this is saved. I have just named the Project D001.

so the next time I have a new part number and i want to put it into a new Project, i have to Name the Project D002.
But i want this number to appear automatically. the code should check the table for the last Project number, add 1 to it and repopulate the text field where i have to write the Project number.

I hope now its clear..

[jforbes]

So you don't a have a table for Projects?

[saranmc]

yes i do... the user enters the Details of the Project and the Project number and it saves it to a table.

aah now i understand your confusion!!! so silly of me... i was looking at the wrong table!

So i could look it up from that Project table. in this table the Project number can be said as a Primary key as it only appears once.
now i tried your code again as the Default property of my text box.. but same error. what am i doing wrong??

thanks jforbes for pointing it out..

[saranmc]

i dont know where in the code to specify my table and field Name....

[jforbes]

Well, in that case, I wouldn't use the code I posted, as it probably won't do anything for you, but mess up your data.

So, when you are entering the Project Number and Part Number, has the Project Number you are attempting to use already been populated in the Project Table?

[saranmc]

no.. in the Project table the Project data is going to be entered new with a new Project number (the next number).

please Forget the part number, that is another table which has both the part number and Project number repeatingly.. my mistake i was wrongly looking at that table.

the Project table has only the Project number, Name, date..... all ONLY ONCE....

[jforbes]

OK, so we are back to creating a new Primary Key for the Project Number?