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?

[saranmc]

yes thats right.....

[jforbes]

In that case, if you have put this code in a code module:

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

You can use some code like this as the DefaultValue Property of your Project Number field on the Form that you want use to add new Projects:

=getKey("NameOfTheProjectTable", "NameOfTheProjectNumberField", 4, "D")

You'll have to replace NameOfTheProjectTable and NameOfTheProjectNumberField with the actual Table and Column names.

[saranmc]

thanks for the code jforbes...
just one question.. the second code, to call the getkey..

i put it into the defaultvalue property of the text box right?? into the Expression builder??

Its giving me an error..

[saranmc]

Ahh... I figured it out now...

now there is no error but the text box just Shows " #Name? "...

is it not able to recognise the value??
I entered D001 into the table and refreshed the form but D002 still does not appear....

[saranmc]

I used the functions Option from Expression elements in the Expression builder and it gave me a similar code to yours.

=getKey([DATA_Table];[Datacode];4;[D])

[jforbes]

That's not going to work. The Function is expecting a string for the Table Name and another one for the Column Name as well as the Prefix to use. So putting a references in there will just confuse Access. When Access sees something like that, it tries to evaluate it and when it sees those Brackets instead of Quotes it attempts to go out and get the Value that is included in the brackets. In the case of DateCode, it will look on the current record of the current form for the value stored in the field Datecode and error out if it's not there. Same goes for Data_Table and D and if any of those three can't be resolved, the whole expression errors out and that #Name error is displayed as the value.

So in your case, you'll probably want to pass string values to the Function instead of values from the current recordset. The following code will more likely work, but I hope you are trying to understand this code as you will have a much better time supporting it if you understand it:

=getKey("DATA_Table";"Datacode";4;"D")

Lastly, you don't have to use the expression builder to put a value in a property. You can copy and paste the code, or type it in directly.

[saranmc]

sorry for the late reply jforbes....i tried again by pasting the exact code into the Default value property.....
but again it just says invalid Syntax.....

[jforbes]

What happens if you type:

?getKey("DATA_Table";"Datacode";4;"D")

into the immediate window?

[saranmc]

i tried again and now i realised something....
the code works.... but it only works starting with D002...
that means it Fails for the first value...when the table is empty...
it should actually Show D001 but it gives an error... "INVALID use of Null"..
your orginal code in #21....

[jforbes]

Nice job troubleshooting.

The following change should get you up and running. The code expected to find at least one key. The following change should deal with a blank table:

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 = Nz(DMax(sField, sTable), sPrefix & "0")
      getKey = sPrefix & Right("00000" & Val((Right(sLastKey, Len(sLastKey) - Len(sPrefix))) + 1), iKeyLen - Len(sPrefix))
  End Function

Glad to see you are taking ownership of the code.

[saranmc]

ahh.. you beat me to the solution... i just figured it out... the usage of " Nz " to tackle this issue... i came back to tell you that i figured it out but i see you have posted your solution....

great.. now it works perfectly!!
i learnt something new today...

thanks a lot jforbes.. Keep up the good work!!