Incrementing a field

[Wayne311]

I need to rebuild the sub-form and see how it now works..



Are the old case numbers you refer to above in the same format as your example (C11-123)? Yes, and stored for a future look-up table.

Do you need to reset the sequential number at 1 at the start of each new year? Yes, I am looking at a way to automate that as well.

Wayne

[jzwp11]

Since the old case numbers are in the same format, you still do not need to store the constructed string, just the components from which it is constructed. You can construct the string whenever you need it in a form, query, report etc. To get the old numbers in, you can manually enter them, but you will need to modify the code to reset the sequential number based on the year. If you already have the data in an electronic format, it might be easier to append them directly into the table. You can use some built-in functions to separate out the components.

In my example database I posted, I assumed that each case had a date associated with it (i.e. when the case was created) & that you would extract the year from that date. Do you have a date associated with each case?

[Wayne311]

I only associate the year with the case number. The creation of the case number is the date on the first receipt.

I am not sure that I understand when you say not to store the constructed string...even if it is needed down the road? The look-up table would have to be constructed on the fly every time a case number needs to be verified (they maybe more than a year old)?

[jzwp11]

I am not sure that I understand when you say not to store the constructed string...even if it is needed down the road?

Exactly, why store something that can be constructed from components that are already stored in the table. It is redundant, and it violates data integrity principles. What would happen if someone puts in C11-199 for a case that was created in 2010? Or if someone changes the C11-199 to something totally outside of your format? The company where I work had what they called coded part numbers similar to your C11-123 but acutally even more intricate. Since multiple users were involved, not everyone understood or followed the prescibed format, so now we have a complete mess of part numbers that could mean something other than what was originally intended.

BTW, if you had used the constucted string as the key field on which you made joins to other tables and someone changed that value in the main table, you would end up having orphaned records in the detail table. That is another reason for having key fields that don't have any significance to your users. In fact, the users should never see the key fields. I hide them on all my forms.

The look-up table would have to be constructed..

You would create a query that shows the constructed value (and carries the primary key value) and use that as the row source for your combo or list box. The expression I used in the form (in the DB I provided) can also be used in a query.

[Wayne311]

I have not had any luck creating the case number in Access so I gave up. I do have one last question...I tried to create the same from you did. Your form returns the case number "C11-123" mine returns "#Name?". What is that? An error?

Thanks
Wayne

[jzwp11]

The example showed how to increment the value. Is it possible to post your database with any sensitive data removed of course?

The #name generally means a syntax error in the expression.