Setting up a form number

[lowesthertz]

Hi folks, I am new to this forum. I appreciate any guidance you may have to offer.

So my question is...I am in the process of designing/implementing a database to replace a paper system. I would like to keep as many similarities as possible to prevent user backlash. I have a single table that stores the majority of the information entered. I have a form in which the information is entered. The form follows the old paper form as close s possible.

The previous system had a numbering scheme as follows: year-incrementing number


An example of the first record entered in 2014 would be "14-001". The next record would be 14-002, then 14-003 and so on until 2015 rolled around. Then it would go back to 15-001, then 15-002 and so on until 2016.

Anybody have any idea how to make this work? I don't think it needs to be the key. As long as I can display it and be able to search it. The number can not repeat.

Any help would be appreciated.
Thanks
Chris

[June7]

Generating custom unique identifier is common topic. Review https://www.accessforums.net/forms/a...ing-23329.html

[lowesthertz]

Where would the code in the example get attached? Would it be in the table that would contain the newly created ID, would it end up attached to the box on the form that displays it, or even to the trigger to create a new record. Not really sure where to start.
Thanks

[June7]

Code cannot be behind table. Otherwise, code goes where you need it. In my db it is in a general module so it can be called by 2 forms to create a new record.

In my process, the record is created with the new number and then form is opened filtered to that new record so user can complete data entry. I do this to minimize possibility of same number being generated by multiple users (also, users need to immediately see the new number). Code constructs the new number but until it is committed to table it doesn't exist in a record and another user could generate the same number, causing conflicts. If user decides to abort the new record entry (maybe they accidentally clicked button), the record is still in table but without data and it will be reused for the next 'new record' - this is to avoid gaps in sequence as every number must be accounted for. I even have a VOID procedure which will produce a report on a number explaining why the sample was not processed.

So the options are to create the number first and immediately commit record to table or have users do the data entry first and create new number at the end.

[lowesthertz]

June7, I have tried to modify the code from the linked webpage and I can not figure out what I am doing wrong. I get a "syntax error in INSERT INTO statement (underlined below). Just some basics of my setup: table= Main Data Table, key = LabNum, Date field = DateEnter
Help is greatly appreciated. Thanks



Public Function NewSample() As String
Dim strLabNum As String
'search for aborted lab number and use that record, else if none then create new record
strLabNum = Nz(DLookup("LabNum", "Main Data Table", "IsNull(DateEnter)"), "")
If strLabNum <> "" Then
CurrentDb.Execute "UPDATE Main Data Table SET DateEnter=#" & Date & "# WHERE LabNum='" & strLabNum & "'"
Else
strLabNum = Nz(DMax("LabNum", "Main Data Table"), "")
If strLabNum = "" Then
'this accommodates very first generated number of blank database
strLabNum = Year(Date) & "A-0001"
Else
'this accommodates change in year
If Left(strLabNum, 4) = CStr(Year(Date)) Then
strLabNum = Left(strLabNum, 6) & Format(Right(strLabNum, 4) + 1, "0000")
Else
strLabNum = Year(Date) & "A-0001"
End If
End If
CurrentDb.Execute "INSERT INTO Main Data Table(LabNum, DateEnter) VALUES('" & strLabNum & "', #" & Date & "#)"
End If
Form_CARForm.Requery
NewSample = strLabNum
End Function

[June7]

You have fields called LabNum and DateEnter - same as in my example?

You did not change the code to customize for your number scheme.

Table name has spaces so enclose in []: "[Main Data Table]".

Advise not to use spaces or special characters/punctuation (underscore is exception) in naming convention.