Automatic numbering

[Guest]

Not sure where to post this. I have a database for making occasional
reports. The first report of this year is numbered RPT-05-01, the second
RPT-05-02, etc. My table (tblReport) contains fields for RPT_Number (such as
05-01) and RPT_Sequence (the last two digits of RPT_Number). A form (frmRPT)
is based on the table. I have placed the following code into the form's On
Current event:

If IsNull(Me.RPT_Sequence) Then
Me.RPT_Sequence = Nz(DMax("RPT_Sequence", "tblRPT"), 0) + 1
End If

If IsNull(Me.RPT_Number) Then
Me.RPT_Number = Format(Date, "yy") & "-" & _
Format(Me.RPT_Sequence, "00")
Else: Me.txtRPT_Number.Locked = True
End If

txtRPT_Number is the text box bound to RPT_Number. People can't change the
number after the record is created.

The above code assigns 05-01, 05-02, and so forth in the proper sequence,
and the number is retained when returning to the record, but of course it
will not restart at 06-01 next year. How can I assign the correct number at
the start of next year? In general is there a better way of assigning the
number, or are there any potential problems with the approach beyond what I
have mentioned?

 

[Allen Browne]

Dim strWhere As String
Dim varResult As Variant

strWhere = "RPT_Sequence Like """" & Format(Date, "yy") & "*"""
varResult = DMax("RPT_Sequence", "tblRPT", strWhere)

If IsNull(varResult) Then
Me.RPT_Sequence = Format(Date, "yy") & "-01"
Else
Me.RPT_Sequence = Left(varResult, 3) & _
Format(Val(Right(varResult,2))+1, "00")
End If

 

[Tim Ferguson]

How can I assign the correct number at
the start of next year? In general is there a better way of assigning
the number, or are there any potential problems with the approach
beyond what I have mentioned?

Yes to both.

My table (tblReport) contains fields for RPT_Number (such as
05-01) and RPT_Sequence (the last two digits of RPT_Number).

The first problem with this is that it's only a matter of time until you
have a RTP_Number that says 05_23 and a RPT_Sequence that says 32 -- oh,
and a short time after that the report date will say 2004-xx-xx and the
Number will say 06-yy; and so on.

Take some time to sort out second normal form: this means that a single
fact is recorded exactly one time. If the "05" refers to the date of the
report, and assuming you have that date stored, then it's child's play to
get it out in a query:

ShortYearNumber: Year(CreateDate) Mod 100

This leaves the Sequence number in the second field as a plain integer.
You can display the entire report number like this:

ReportNumber: "RPT_" & Format(Year(CreateDate) Mod 100, "00") &
"_" & Format(Sequence, "00")

Allocating a new sequence number is pretty much as you have done, except
for using a criterion on the DMax() function:

' calculate the current year from the record
varTemp = DMax("Sequence","MyTable", _
"Year(CreateDate)=" & Year(Me!CreateDate))

If IsNull(varTemp) Then
NextSequence = 1
Else
NextSequence = varTemp + 1
End If


Hope that helps

Tim F

 

[Guest]

I appreciate the quick response. I had to do some tweaking, but the end
result is that it seems to work. When I changed my computer clock to next
year I was suddenly late for just about everything (according to Outlook
Calendar), but the numbers fell in line starting with 06-01.
In the line strWhere = "RPT_Sequence Like """" & Format(Date, "yy") & "*"""
there seemed to be an extra quote after Like, so I removed one.
RPT_Number is the concatenated value that is the official document number
(05-01, 05-02, etc.). RPT_Sequence is the incrementing value that forms the
last two digits of RPT_Number. RPT_Sequence is a number field. I changed
RPT_Sequence to RPT_Number everywhere in your code. Apparently I do not need
the RPT_Sequence field, which just incremented 01, 02, 03, etc.
I have tried to find out more about Like (in the strWhere = ... line), but
Help isn't very helpful, and I cannot find a way to frame the search in
Google groups (because Like is a very common word, I suppose). Not to impose
on your generously offered assistance, but could you either say a little more
about the strWhere = line or point me to a resource that can provide some
clarity. Thanks again.

 

[Guest]

I had imagined originally that when a new year started RPT_Sequence would
restart from 01. When asking a question I usually try to show I have been
working on the problem, even if I suspect I am not on track. I have
implemented another suggestion that seems to work (from Allen Browne), but I
am interested in your approach too. In the future I need to design a similar
database except with the department code in the number (D-05-01, D-05-02,
S-05-01, D-05-03, Q-05-01) so maybe your suggestion will come into play yet,
except that I am having trouble understanding it. As I understand your
suggestion, CreateDate is a field containing the date the report was created.
That would have an advantage over Format(Date(),"yy") in that a handwritten
report created on the last day of the year but entered into the system in the
new year would contain the correct report date. How does MOD 100 fit in? As
I understand it MOD is a sort of whole number division sign.
On another matter, I usually use autonumber PKs, but in this case I wonder
if there is any advantage (or disadvantage) to using the concatenated number
(RPT_Number) as the PK. I have no plans just now for relationships with
other tables, but am I correct in thinking that the FK (if there is one)
would have to be of data type Text? If so, any potential problems with that?

 

[Guest]

I have to take back what I waid earlier. It works up to a point, but
RPT_Number keeps changing for the same record. The problem is with the On
Current event, which reassigns the number every time the record is current.
Maybe I will need to exit the sub if there is already a value in that field.

 

[Allen Browne]

No: don't use Form_Current. You don't need to assign the number once it
exists.

Use Form_BeforeUpdate. This is the last possible moment you can assign the
value before the record is saved (which reduces the change of duplicates
being given to different users attempting to enter new records at the same
time.)

You only need to do this is if it a new record, so:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
'put the code in here.
End If
End Sub

 

[Guest]

Thanks again. One concern, though, is that this report, while important in
the big picture, is generally issued no more than 30 times per year. This
means that some users will only use it every few months, if that. I can
explain that the number is not assigned until they are done, but they will
forget, and then they will call me and say that they can't enter a report
number, and I will tell them it is assigned when they are done, and they will
mutter and sigh and fill out a report suggesting that they be able to see the
number right away. In fairness, it is not their job to remember
administrative details like that.
What I have done is to place the code into the On Current event, which
produces a RPT_Number value, then I have placed the same code into Before
Update, except that the first line there (after If Me.NewRecord) is to clear
the text box containing RPT_Number (I allowed zero length strings in table
design to make that possible). I have tested this with two users, and it
seems to work. I could probably find a way to generate a message advising
the user that the record number will be changed. It is not likely to occur
often. Similarly, in the very unlikely event that two users attempt to save
a record at the same time, I could probably set the RPT_Number field to not
allow duplicates, and in case of the specific error of trying to assign a
duplicate where it is not allowed I could have the code to assign the number
run again. I would have to figure out which error number that is.
Am I on the right track, or am I taking the long way around or otherwise
causing myself difficulties?

 

[Allen Browne]

If it important that this field is entered and unique, then you really do
need to set these properties for the field in table design:
Required Yes
Allow Zero Length No
Indexed Yes (No Duplicates)

Now that the data is known to be unique and valid, you can do whatever
pleases you in the interface. If you don't like the idea of leaving the
value to the last minute, then use the form's BeforeInsert event so it gets
assigned as soon as the user *starts* to add a new record, instead of at the
end of the process. This does increase the chance of duplicates of course.

If the field is NOT required, you can clear the text box by setting its
value to Null, i.e.:
Me.RPT_Number = Null
which saves you the bother of having test test of both null and a
zero-length-string everywhere in the database where you use this field.

 

[Guest]

Thanks again. I couldn't care less whether the number appears until the end,
but if it is blank I will get calls from users who can't enter a number. The
text box is locked, by the way. Whether or not the field is required may not
be relevant since the value is assigned programatically (it will contain a
value in any case), but I appreciate your comments on Null values and other
field properties, and will certainly apply those suggestions. I plan to use
an autonumber PK, although at this point I do not anticipate that the table
will be part of any relationships. It is essentially a flat database for
recording twenty-five or so items that appear on a report. I can change the
PK later if need be.
Thanks again for the prompt and concise replies to my varied questions.

 

[Tim Ferguson]

I had imagined originally that when a new year started RPT_Sequence
would restart from 01.

So was I: that's what I suggested.

I have implemented another suggestion that seems to work (from Allen
Browne), but I am interested in your approach too.

It's not very different -- we mortals seldom disagree with the MVPs and
especially so with the very experienced people like Allen. What I did
suggest was a slightly more rigorously-normalised design, but either can
work quite happily.

need to design a similar database except with the department code in
the number (D-05-01, D-05-02, S-05-01, D-05-03, Q-05-01) so maybe your
suggestion will come into play yet,

OK: this would need three separate fields: one for the department code,
one for the year number, and one for the sequence number.

As I understand your suggestion, CreateDate is a
field containing the date the report was created.

Only assuming that you are already storing this date: if you aren't, then
you just need the YearNumber field on its own.

That would have an advantage over Format(Date(),"yy") in that a
handwritten
report created on the last day of the year but entered into the system
in the new year would contain the correct report date.

That is an example; another one is where some applications' years start
in April or September, etc.

How does MOD 100 fit in?

This year is 2005; you wanted 05 on its own. The mathematics says that
the conversion function is Modulo-100. It's up to you how you code the
hangovers from 1998; and you just have to hope it's not running in 2101.

On another matter, I usually use autonumber PKs, but in this
case I wonder if there is any advantage (or disadvantage) to using the
concatenated number (RPT_Number) as the PK.

Well, AFAICS, RPT_Number is not in first normal form and shouldn't be in
the table at all. Since you have gone to all this trouble create a unique
combination (YearNumber, SequenceNumber) then it seems a bit of a waste
to maintain another unique key based on Access' autonumber system. Then
again, I don't intend to get involved in another autonumber war over it!


Hope that helps

Tim F

 

[Guest]

I had imagined originally that when a new year started RPT_Sequence

So was I: that's what I suggested.

Trouble is, I only imagined it. I had no idea how to bring it about.

Well, AFAICS, RPT_Number is not in first normal form and shouldn't be in
the table at all. Since you have gone to all this trouble create a unique
combination (YearNumber, SequenceNumber) then it seems a bit of a waste
to maintain another unique key based on Access' autonumber system. Then
again, I don't intend to get involved in another autonumber war over it!

I really must learn more about VBA. I understand most of what you and Allen
suggested, but I have not learned VBA systematically, so things like:
"Year(CreateDate)=" & Year(Me!CreateDate)
continue to puzzle me. For instance, I've never quite sorted out when to
use Me. and when to use Me!, and I only understand & as a concatenation
operator (which it doesn't seem to be in the code snippet above). Sometimes
field names are in brackets and sometimes in quotes (as in DMax), and field
name syntax is different in Format and in DMax, and so forth to my continual
bewilderment, but I'm picking up a bit more each day. I'm still working on
"Like", as in:
strWhere = "RPT_Sequence Like """ & Format(Date, "yy") & "*"""
and maybe someday I will figure out why there are so many quotation marks.
Until I attain such wisdom I am very appreciative of the explanations and
suggestions offered on this forum. Thanks again.

 

[Tim Ferguson]

I really must learn more about VBA. I understand most of what you and
Allen suggested, but I have not learned VBA systematically, so things
like: "Year(CreateDate)=" & Year(Me!CreateDate)
continue to puzzle me.

If I remember correctly, this is a string that is going to be passed to
the DMax() function. Going from the right-hand-end:

Me is the form that "owns" the currently running code, so
Me!CreateDate refers to a control on the current form
... and since it does not mention any particular property
of that control, we'll assume it's meant to be the .Value
property.

Year(Me!CreateDate) means that the value must be interpreted as
a date value, and then the year part of that date is to be
used as a number, like 2005 for example

& quite right, this is a string concatenation. In this example
it also forces the number 2005 into a string like "2005".

"Year(CreateDate) = " goes on the front of what we calculated
just now, so the whole thing looks like:
"Year(CreateDate) = 2005"

What happens next is that DMax passes the whole lot to the
database engine to interpret as a filter. The engine will
check all the records and carry out the "Year()" function on
the CreateDate field for each of them, and remember only the
records where the Year() function returned 2005. Then it works
out and returns the maximum whatever, which is what you asked
for; in this case, the largest SequenceNumber for the appropriate
year so far.

(An extra point to note: it's good practice to rename all the
controls on the form so that they are not the same as their
fields. It would have been much less confusing to have

strWhere = "Year(CreateDate) = " & Year(Me!txtCreateDate)

because it immediately reminds you one is a control and the
other one is a field in a record. On the other hand, not all
of us always follow our own advice...)

Sometimes field names are in brackets and sometimes
in quotes (as in DMax)

That's because DMax is expecting strings as arguments. The data types of
all functions are always listed in the help files.

but I'm
picking up a bit more each day.

Aren't we all?

I'm still working on "Like", as in:
strWhere = "RPT_Sequence Like """ & Format(Date, "yy") & "*"""
and maybe someday I will figure out why there are so many quotation
marks.

VBA has to know when you want _it_ to see a quote mark, and when you want
to keep a quote mark inside the string. The rule turns out to be that if
there's one, then VBA uses it; if there's two then VBA passes one of them
on. In this expression:

"RPT_Sequence LIKE """

The first quote starts a string;
The second one could be used by VBA to close the string, but
The third one makes a pair, so one of them stays inside the string
The fourth one is used by VBA to close the string, so we get the value

RPT_Sequence LIKE "

and so on. If you parse out the entire expression, you get

RPT_Sequence LIKE "05*"

which is what you want: compare and find all strings that begin with
zero-five.

Until I attain such wisdom I am very appreciative of the
explanations and suggestions offered on this forum. Thanks again.

No problem. If you have any specific questions then come back here again.
In the meantime, you might find a step-by-step teach yourself book handy,
or else a friendly mentor who can watch over you shoulder in a somewhat
more systematic way.

All the best


Tim F

 

[Guest]

Thanks again. The part about quotes is especially helpful. I am going to
try putting it to use in another database in which I am using SendObject to
send an e-mail containing various fields and text strings combined ('Tim
Ferguson has initiated Report 05-03 on the subject "VBA Tips" ', where name,
report number, and subject are fields; single quotes are for this posting
only). I have put the string together OK, but it would be good to have
quotes within it. Thanks too for the explanation about values to be
considered as strings being in quotes, such as in DMax (and quite a few other
things, now that I am aware of it).
By the way, I invariably use naming conventions (txtTextBox, lblLabel,
cboComboBox, cmdCommandButton, etc.) for controls, to the extent of naming
labels, lines, graphics (which I use sparingly, and usually just for company
logos and such), and even detail sections and headers. When I review the
list of controls I can easily spot anything that is named differently. It
also makes VBA a lot easier in terms of the autocomplete feature. In the
example you gave I was unsure where CreateDate came from, but it does raise
the larger issue of when to refer to a field and when to refer to a control.
I'm glad you specified which is the field and which the control in DMax. I
think I see now that as the criterium for the DMax expression it is looking
for records in which the field matches the control for that field in the
current record. Makes perfect sense.
I have been doing some investigating on Me!Something vs. Me.Something. As I
understand it the dot refers to a property and the exclamation mark refers to
a collection, but somehow Access is able to regard a control as a property of
the form in many cases, even though it is also a collection. I tend to use
the dot because the choices show up automatically, then change it to an
exclamation mark as needed. Some people say that using the dot when
referring to a control leads to corruption, but they were not specific about
that, so I don't know if they are in effect pumping the brake pedal on a car
with antilock brakes. I have noticed that when using the full reference such
as [Forms]![frmMain]![StartDate] the exclamation mark is needed in all cases.
Apparently the control can be a property of the form, but the individual
form cannot be a property of Forms, and use of the exclamation mark must be
continued once started. It can get to be a bit bewildering when in one
instance the syntax is [Field] Is Null and in another IsNull[Field], and
sometimes it's Is Null and at other IsNull.
I have gotten a lot out of this exchange. I will keep an eye out for your
postings as I scan the newsgroup. Much of what I have learned has been by
reading other people's questions.

 

[Tim Ferguson]

I have been doing some investigating on Me!Something vs. Me.Something.
As I understand it the dot refers to a property and the exclamation
mark refers to a collection,

The easy part: a dot _always_ refers to a property. Examples:
Application.Visible, Me.Caption, ctl.Value etc etc

(Nearly) every object has a default property, i.e the one that is used if
you don't specify which property you actually want. Therefore,
strSomething=ctl

is shorthand for the same thing:
strSomething=ctl.Value

Most objects have collections, which are accessed a bit like properties,
so,
set colControls = Me.Controls ' the controls collection

and you can get at any one of them using the Item property of the
collection so:
Set ctl = Me.Controls.Item("txtFirstName")

Now, because the Item is the default property for the collection object,
it can be omitted using the same shorthand as above:
Set ctl = Me.Controls("txtFirstName")

(and in fact you very rarely see the Item() property spelled out).
Now, a second useful shorthand is where one of the object's collections
is declared as the Default Collection - and that is where the ! notation
comes in. The default collection for the Form object is the Controls
collection, so the above expression could be rewritten using
Set ctl = Me!txtFirstName

Yes, this time the quotes are unneeded because it's going straight to the
actual namespace.

Final complication: a quirk of the Form object is that it exposes some
things that are not really properties as if they were. In the old days,
there was no access to the underlying recordset, so you could not do

Forms("Teachers").Recordset.Fields("FirstName")

(note: we are going for the field, not the control! Therefore, the form
exposed all the underlying fields as pseudo-properties, so that you could
read instead

Forms("Teachers").FirstName

Somewhere along the line, though, this all changed to the controls rather
than the fields, which I find confusing. I tend to stick to the longhand
methods so I stand some chance of reading the code some months later!!

brakes. I have noticed that when using the full reference such as
[Forms]![frmMain]![StartDate] the exclamation mark is needed in all
cases.

It would not make much sense, but Forms("frmMain)!StartDate is pefectly
legal...

when in one instance the syntax is [Field] Is Null and in another
IsNull[Field], and sometimes it's Is Null and at other IsNull.

'Something IS NULL' is SQL, 'IsNull(Something)' is VBA

I have gotten a lot out of this exchange. I will keep an eye out for
your postings as I scan the newsgroup. Much of what I have learned
has been by reading other people's questions.

That's what we're here for...

All the best


Tim F

 

[Guest]

Thanks once again. Maybe when some organizational changes are complete where
I work I will be able to use a newsreader such as Outlook Express, but for
now I can't get permission to connect to anything other than the default
servers. If that change comes about I will have a much easier time with
individual message threads, but for now I am limited to the web-based
newsreader, so finding threads from several days ago can become a bit of a
chore. Therefore I will probably not be returning to this thread, but I
thank you again for your generosity with your time and expertise.

I have been doing some investigating on Me!Something vs. Me.Something.
As I understand it the dot refers to a property and the exclamation
mark refers to a collection,

The easy part: a dot _always_ refers to a property. Examples:
Application.Visible, Me.Caption, ctl.Value etc etc

(Nearly) every object has a default property, i.e the one that is used if
you don't specify which property you actually want. Therefore,
strSomething=ctl

is shorthand for the same thing:
strSomething=ctl.Value

Most objects have collections, which are accessed a bit like properties,
so,
set colControls = Me.Controls ' the controls collection

and you can get at any one of them using the Item property of the
collection so:
Set ctl = Me.Controls.Item("txtFirstName")

Now, because the Item is the default property for the collection object,
it can be omitted using the same shorthand as above:
Set ctl = Me.Controls("txtFirstName")

(and in fact you very rarely see the Item() property spelled out).
Now, a second useful shorthand is where one of the object's collections
is declared as the Default Collection - and that is where the ! notation
comes in. The default collection for the Form object is the Controls
collection, so the above expression could be rewritten using
Set ctl = Me!txtFirstName

Yes, this time the quotes are unneeded because it's going straight to the
actual namespace.

Final complication: a quirk of the Form object is that it exposes some
things that are not really properties as if they were. In the old days,
there was no access to the underlying recordset, so you could not do

Forms("Teachers").Recordset.Fields("FirstName")

(note: we are going for the field, not the control! Therefore, the form
exposed all the underlying fields as pseudo-properties, so that you could
read instead

Forms("Teachers").FirstName

Somewhere along the line, though, this all changed to the controls rather
than the fields, which I find confusing. I tend to stick to the longhand
methods so I stand some chance of reading the code some months later!!

brakes. I have noticed that when using the full reference such as
[Forms]![frmMain]![StartDate] the exclamation mark is needed in all
cases.

It would not make much sense, but Forms("frmMain)!StartDate is pefectly
legal...

when in one instance the syntax is [Field] Is Null and in another
IsNull[Field], and sometimes it's Is Null and at other IsNull.

'Something IS NULL' is SQL, 'IsNull(Something)' is VBA

I have gotten a lot out of this exchange. I will keep an eye out for
your postings as I scan the newsgroup. Much of what I have learned
has been by reading other people's questions.

That's what we're here for...

All the best


Tim F