Generating custom sequential numbers

Question

I would need a log number in the following format (Text-yy-nnn) where text is fixed series of letters denoting our office, yy is the last 2 digits of the year and nnn is a sequential number restarting each year. I am using Access 2007. The table is called Document List, the date is stored in a field called Document Date formatted as Date() by default, and I have field for the log numbers - currently geenrated manually which is set as the primary key.

I have seen the blogs and the posting by Scott, and thus I have created a new field called Sequence set to number/long integer data type, and tried to insert the following code in the control bound to Sequence in the form:

Me.txtSequence=Nz(DMax("[Sequence]","tblDocument List","Year([Document Date])="&Year(Me.[txtDocument Date])),0)+1

The immediate window gave me the following error message: Expected:)

I also tried the following variant:

Me.Sequence=Nz(DMax("[Sequence]","Document List","Year([Document Date])="&Year(Me.Document Date)),0)+1

which returned the following: Expected: list separator or )

I am not sure where a ) can be missing, as all the brackets haev been closed, but there is apparently a problem with the syntaxt. I have seen that other bloggers trying this code have reported that they did not have any success either. I would really appreciate fi you could help me with the above.

Also, if I understand Scott's instructions properly, the

=Format([Document Date],"yy")&"-"&Format([Sequence],"000")

code would then go into the field for the log number. Can I just insert it into the control in design mode?

Do I need the square brackets? Do I need to leave spaces anywhere?

Thanks for your help in advance!

ZsuM

Answer 1

Typing it into the immediate window may not work. You can test it running code. You set the Sequence control just before you commit the record.

The Format function is used as the Controlsource of a control to display the combination of codes.

Answer 2

What does it mean to "commit the record"? Also, which version of the code I metioned above should work and what is the difference between them from a functional point of view?

Is this what's supposed to be in the code window?:

Private Sub Sequence_Click()

Me.txtSequence = Nz(DMax("[Sequence]", "tblDocument List", "Year([Document Date])=" & Year(Me.[txtDocument Date])), 0) + 1

End Sub

As I mentioned, I have never done this before, and step-by-step help maybe a screen shot, or the whole sequence fo code typed up would be most welcome.

Thanks a million!

Answer 3

One thing you haven't said is how the first part of the structured log number, the text office code, is stored?  This is data, so as such should be stored as an explicit value at a column position in a row in a table and in no other way; that has been a fundamental principle of the database relational model, the Information Principle, since it was first introduced by Codd in 1970.  So you'd have a one column, one row table OficeCode say with a single column, OfficeCodeText say, and enter the value into this.  Then in your form include an unbound text box, txtOfficeCode say, and in the form's Open event procedure out

    Me.txtOfficeCode=DLookup("OfficeCodeText", "OfficeCode")

Set its Visible property to False (No) to hide it.  The unbound log number control would then have a ControlSource property of:

    =[txtOfficeCode] & "-" & Format([Document Date],"yy") & "-" & Format([Sequence],"000")

In a report you'd do it slightly differently, by including the OfficeCode table in the query used as the report's RecordSource, but without joining it to any other tables.  This creates what's known as the Cartesian product of the tables, joining the one row from the OfficeCode  table to every row in the other table(s).  So you can then show the structured log number in a combo box in the report with a ControlSource property of:

    =[OfficeCode] & "-" & Format([Document Date],"yy") & "-" & Format([Sequence],"000")

The reason you don't do it this way in a form is because a query which returns the Cartesian product of two or more tables is not updatable, so you would not be able to add or edit data in the form.

Answer 4

I was typing it into the immediate window to test it, and that's as far as I got.

Otherwise I was going to insert it in the form design view, into the control bound to "Sequence" via the property sheet, event tab, code builder...

Is that not where it should go? Any help would be greatly appreciated, as I have never done any programming in VBA before.

I thought since the date is automatically filled in with today's date when a new record is being entered, the sequence (invisible) and log number (visible alphanumeric sequence) will promptly be generated without the person having to do anything. Is that so? Or how/when will the number actually appear?

Of course first I should solve the problem of getting the sequence filed populated...

Answer 5

You say "tried to insert the following code in the control bound to Sequence " - where in the "control"? This would be a line in an Event Procedure, probably in the Form's BeforeInsert event. What's the context? Where were you putting the code?