Automatic numbering

=?Utf-8?B?QnJ1Y2VN?= wrote in
:

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

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

So was I: that's what I suggested.

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

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!

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.

=?Utf-8?B?QnJ1Y2VN?= wrote in
:


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

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" wrote:

=?Utf-8?B?QnJ1Y2VN?= wrote in
:


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

=?Utf-8?B?QnJ1Y2VN?= wrote in
:

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

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.

"Tim Ferguson" wrote:

=?Utf-8?B?QnJ1Y2VN?= wrote in
:

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