Creating a sequence number

[Red via AccessMonster.com]

Good Morning all,
I have been trying to create a sequence number with a unique last three
digits. The number has a total of 14 digits. I can concatenate the first 11
numbers with no problem but its the last three that I can't figure out. The
last three numbers needs to be the first, second, third entry of the day for
example, 001, 002, 003 etc... This number will need to reset back to 001 the
next day. I have looked all through the messages in this news group and on
the web for something I can use to get me pointed in the right direction, but
no luck. I will include the form and query information I am currently using.
Any help will be greatly apprecaited.

Query SQL:

SELECT Invoices.[CONSULTANT NAME], tblConsultants.providerCo, tblConsultants.
address1, tblConsultants.address2, tblConsultants.addressCityZip,
Authorization.[AUTHORIZATION NUMBER], [Patient Info].SSN, [Patient Info].
[LAST NAME], [Patient Info].[FIRST NAME], [Patient Info].ADDRESS1, [Patient
Info].ADDRESS2, [Patient Info].ZIP_CODE, [Patient Info].[HOME PHONE],
[Patient Info].DOB, Authorization.[TYPE OF APPOINTMENT], Authorization.
[REFERRING PROVIDER], Authorization.[REFERRING CLINIC], [Patient Info].RANK,
Invoices.[HOSP INVOICE #], Invoices.[APPT DATE], Invoices.[ACTUAL COST POUNDS]
, Authorization.[CIVILIAN SPECIALTY], "F2P4MD6" AS Exp1, Format(DatePart('y',
Date()),"000") AS JulDate, "HH" AS Exp2, DMax(0,1) AS SeqNum
FROM [Patient Info] LEFT JOIN (([Authorization] LEFT JOIN Invoices ON
Authorization.[AUTHORIZATION NUMBER] = Invoices.[AUTHORIZATION NUMBER]) LEFT
JOIN tblConsultants ON Invoices.[CONSULTANT NAME] = tblConsultants.
[Consultant Name]) ON [Patient Info].[PT ID] = Authorization.[PT ID];


Form field:
Control Source =[Exp1] & "" & [JulDate] & "" & [Exp2] & "" & [SeqNum]

Its the SeqNum I can't get to work. Thanks again for any suggestions and if
I need to provide more information, please let me know.

VR,

Red

 

[TC]

You're approaching this in the wrong way.

In a database product like Access, there's a big difference between
/presentation/ issues - how things look on the screen - and /storage/
issues - how they are actually stored in the database.

To take a simple example: you might want to display a person's age on a
form or report. But there is *absolutely no way* that you would ever
store that value in a properly designed database. This is because, if
you store a person's age, that value could be out of date, the first
day afterwards. Instead, you would store the person's /date of birth/,
and then /compute/ their age, at runtime, when-ever you wanted to show
thier age on a form or report.

So - what you see, is not always what you store!

In your case, it is posible that you should not be storing the 3-digit
sequence number, at all. You may be able to compute it from other data
that is in your database.

If you want to pursue this line of suggestion: tell me about your data.
What data are you trying to store, & what do you need to do with it?
Try to answer that question without using any technical terms like
table, record, field & so on.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Red via AccessMonster.com]

Thanks for the reply TC,
I am not set in anyway how to create this number, and any suggestions will be
appreciated. What I have is form that has all patient and dr infromation
displayed. From this form, I am using two command buttons to display an
invoice, one if the patient is going to be billed, or second if the insurance
company is being billed. One of the requirements to have on this invoice is
a invoice number with specific requirements; the first 7 characters are
static F2P4MD6. The next three characters are the current julian date and
the next two characters are again static HH which make up the first 12
characters of the invoice number. The last requirement is the last three
characters needs to be the first, second, third etc... entry of the day for
example, 001, 002, 003 etc... This number will need to reset back to 001 the
next day.

What I'm trying to do, when the user clicks on the command button, it brings
up the invoice form having thie invoice number created.

Hope this makes sense to you and again, it is appreciated.

VR,

Red

 

[BruceM]

Just wondering if by "Julian Date" you mean the number of days since the
start of the calendar year. If so, numbers will repeat next year. Does the
full number need to be unique?

 

[Red via AccessMonster.com]

Hi Bruce,
Yes, you are right in your assumption of the Julian Date. But the uniqueness
will come from the static number as it will change next year so number will
still be unique. Do you have any suggestions or direction I should persue?

Many Thanks,

Red

Just wondering if by "Julian Date" you mean the number of days since the
start of the calendar year. If so, numbers will repeat next year. Does the
full number need to be unique?

Thanks for the reply TC,
I am not set in anyway how to create this number, and any suggestions will

[quoted text clipped - 53 lines]

 

[BruceM]

I tend to view this matter somewhat differently than does TC, who is an
Access MVP with far more experience than I, and whose views are derived from
a far larger body of experience. It is certainly possible that in
formulating my views on this matter I have overlooked something important.
I am following this thread because it is a situation I encounter from time
to time, and I am interested in learning more about the different approaches
to the problem.
Having said that, I completely agree about storing such things as age, but I
do not see a problem with storing a sequence number that includes the date
or other "real" information. A unique number can be random, incremented by
1, incremented by 10, autonumber, or any number of valid choices. If it is
valid to store something like a seven-digit invoice number (0000001,
0000002, etc.), I believe it is valid to store a fixed-length number that is
derived by means other than simple incrementing.
I have posted on this subject before. A search that includes "increment"
and the name I use in the group will probably bring you to some of those
postings. However, I encourage you to await TC's response.

Hi Bruce,
Yes, you are right in your assumption of the Julian Date. But the
uniqueness
will come from the static number as it will change next year so number
will
still be unique. Do you have any suggestions or direction I should
persue?

Many Thanks,

Red

Just wondering if by "Julian Date" you mean the number of days since the
start of the calendar year. If so, numbers will repeat next year. Does
the
full number need to be unique?

Thanks for the reply TC,
I am not set in anyway how to create this number, and any suggestions
will

[quoted text clipped - 53 lines]

TC (MVP Access)
http://tc2.atspace.com

 

[Red via AccessMonster.com]

Thanks Bruce for your thoughts. I will waite to see the response from TC but
will take a look at your earlier posts as well.

Red

I tend to view this matter somewhat differently than does TC, who is an
Access MVP with far more experience than I, and whose views are derived from
a far larger body of experience. It is certainly possible that in
formulating my views on this matter I have overlooked something important.
I am following this thread because it is a situation I encounter from time
to time, and I am interested in learning more about the different approaches
to the problem.
Having said that, I completely agree about storing such things as age, but I
do not see a problem with storing a sequence number that includes the date
or other "real" information. A unique number can be random, incremented by
1, incremented by 10, autonumber, or any number of valid choices. If it is
valid to store something like a seven-digit invoice number (0000001,
0000002, etc.), I believe it is valid to store a fixed-length number that is
derived by means other than simple incrementing.
I have posted on this subject before. A search that includes "increment"
and the name I use in the group will probably bring you to some of those
postings. However, I encourage you to await TC's response.

Hi Bruce,
Yes, you are right in your assumption of the Julian Date. But the

[quoted text clipped - 19 lines]

 

[TC]

I tend to view this matter somewhat differently than does TC, who is an
Access MVP with far more experience than I, and whose views are derived from
a far larger body of experience.

Bruce, thanks for the compliment.

In my opinion, anyone who has a view, and can express it clearly, and
give some arguments to support it, is 100% justified in putting it
forward in these newsgroups.

It's the Easter break here, & I've been tied up with some social
things. So I'm just doing some quick answering right now. But I will
post back to this thread (again) within a few hours, when I've had a
bit more time to construct a full answer.

Cheers,
TC (MVP Access)
http://tc2.atspace.com

 

[TC]

Hmm, I hope my statement reads the way I meant it to!

I meant, you need not apologize for expressing a competing opinion.

Cheers,
TC

 

[TC]

Hi Red

So you need to generate Invoice numbers of the following form:

F2P4MD6jjjHH999

where:
- the first 7 characters "F2P4MD6" are fixed for any given year;
- the next 3 digits are the "julian date";
- the next two characters "HH" are fixed, and
- the last 3 digits reflect the "entry number" on that day: 001, 002,
003, and so on; restarting from 001 on the next day.

(Note: You've said 14 characters in some places, and 15 in others. But
the difference is not important, to the thrust of this discussion.)

First, you should *not* embody the 7-character prefix values into your
database program code. Instead, you should get those values from a
database table like the following:

tblPrefixCodes:
YearNumber (primary key)
PrefixCode

Then, when the system wants the current code - for example, to display
in an Invoice number on a form or report - it just gets the current
year, using a simple VBA function call, then goes into that table "by
year", to get the relevant prefix code. By this means, you can add the
next year's code, just by adding a record to this table - no
programming changes are required. The following VBA code would get the
current year's prefix code, from that table:

DLookup("[PrefixCode]", "[tblPrefixCodes]", "[YearNumber]=" &
Year(Date()))

Second, you say the next 3 digits are for the "julian date". What is
his the date *of*? If it is the date of a record that you entered into
the database, you should "stamp" each record with the date & time of
data entry. Then the system can *compute* the julian date of that
record, when it needs to display that value on a form or report. *It
does not need to store than value*.

Third, the same comment applies to the daily sequence number. It really
is not correct to *store* that number. Instead, each record should be
"stamped" with the date & time of data entry. Then, you can *compute*
each record's sequence numbers, from the date/time stamp in each
record.

You can "stamp" each record in a table with the date & time of data
entry, by adding a Date/Time field to that table, and setting the
Default Value of that field to: =Date() Then Access will
automagically store the current date & time in that field, in each new
record that you add to that table.

If you're skeptical of the benefit of computing the daily sequence
numbers instead of actually storing them - consider what happens if the
user deletes a previously entered record. If you *stored* the daily
sequence number, you now have a gap in the numbers: they go 001, 002,
and 004 - 003 has been deleted! But if you *compute* those numbers,
this problem simply doesn't occur.

In summary, I believe that you should *store* the 7-character prefix
numbers, in a table, one for each year; but you should *compute* the
daily sequence numbers, at runtime, as & when required.

As for the julian date and "HH" fields, those are somewat unclear at
this time. But the same principles as described abive, apply: you
should not *store* and data that can be computed from other data, such
as, the date/time value that ius stored in each record.

Does that help - or just confuse?

TC (MVP Access)
http://tc2.atspace.com

 

[TC]

"In summary, I believe that you should *store* the 7-character prefix
numbers, in a table, one for each year"

should of course be:

"In summary, I believe that you should *store* the 7-character prefix
numbers, in a table, one RECORD for each year"

TC

 

[Red via AccessMonster.com]

Hi TC and first thanks for your information and explaination and please
forgive my ignorance as I'm fairly new at this but...
I'm following you on the first 7 characters storing them in a table, no
problem. Now I start to get confused from this point on; the julian date is
the date that the invoice is created (I failed to mention that in my original
post. Sorry) so how could I compute this without storing the date in a table?
As well as the next two static characters? This brings me to my last
question on the sequence number. How would I compute this number? In VBA?
If so, where would I incorporate it? I'm starting to understand the
relational database concept and if the information is available somewhere
else, I dont need to store it again but I'm missing the plot (probably due to
my novice status) on this one :<(

Again, sorry for my ignorance and your help is gratly appreciated.

Hi Red

So you need to generate Invoice numbers of the following form:

F2P4MD6jjjHH999

where:
- the first 7 characters "F2P4MD6" are fixed for any given year;
- the next 3 digits are the "julian date";
- the next two characters "HH" are fixed, and
- the last 3 digits reflect the "entry number" on that day: 001, 002,
003, and so on; restarting from 001 on the next day.

(Note: You've said 14 characters in some places, and 15 in others. But
the difference is not important, to the thrust of this discussion.)

First, you should *not* embody the 7-character prefix values into your
database program code. Instead, you should get those values from a
database table like the following:

tblPrefixCodes:
YearNumber (primary key)
PrefixCode

Then, when the system wants the current code - for example, to display
in an Invoice number on a form or report - it just gets the current
year, using a simple VBA function call, then goes into that table "by
year", to get the relevant prefix code. By this means, you can add the
next year's code, just by adding a record to this table - no
programming changes are required. The following VBA code would get the
current year's prefix code, from that table:

DLookup("[PrefixCode]", "[tblPrefixCodes]", "[YearNumber]=" &
Year(Date()))

Second, you say the next 3 digits are for the "julian date". What is
his the date *of*? If it is the date of a record that you entered into
the database, you should "stamp" each record with the date & time of
data entry. Then the system can *compute* the julian date of that
record, when it needs to display that value on a form or report. *It
does not need to store than value*.

Third, the same comment applies to the daily sequence number. It really
is not correct to *store* that number. Instead, each record should be
"stamped" with the date & time of data entry. Then, you can *compute*
each record's sequence numbers, from the date/time stamp in each
record.

You can "stamp" each record in a table with the date & time of data
entry, by adding a Date/Time field to that table, and setting the
Default Value of that field to: =Date() Then Access will
automagically store the current date & time in that field, in each new
record that you add to that table.

If you're skeptical of the benefit of computing the daily sequence
numbers instead of actually storing them - consider what happens if the
user deletes a previously entered record. If you *stored* the daily
sequence number, you now have a gap in the numbers: they go 001, 002,
and 004 - 003 has been deleted! But if you *compute* those numbers,
this problem simply doesn't occur.

In summary, I believe that you should *store* the 7-character prefix
numbers, in a table, one for each year; but you should *compute* the
daily sequence numbers, at runtime, as & when required.

As for the julian date and "HH" fields, those are somewat unclear at
this time. But the same principles as described abive, apply: you
should not *store* and data that can be computed from other data, such
as, the date/time value that ius stored in each record.

Does that help - or just confuse?

TC (MVP Access)
http://tc2.atspace.com

 

[BruceM]

You say that "F2P4MD6" is fixed for this year, but you don't say how it
changes next year. The following is in the form's Current event. This
approach assumes that the "6" is for this year, except I have formatted it
as "06". The "Julian Date" is the number of days since the first of the
year. In my test I called the table tblDateTime, and the field DateTimeID.

If Me.NewRecord Then
Dim strPrefix As String
Dim strJulDate As String
Dim strWhere As String
Dim varResult As Variant

strPrefix = Format(Date, "yy")
strJulDate = Format(Format(Date, "y"), "000")
strWhere = "DateTimeID Like """ & strPrefix & strJulDate & "HH" &
"*"""
varResult = DMax("DateTimeID", "tblDateTime", strWhere)

If IsNull(varResult) Then
MsgBox "varResult is null"
Me.DateTimeID = strPrefix & strJulDate & "HH" & "001"
Else
Me.DateTimeID = Left(varResult, 7) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If
End If

This number could be concatenated as needed with the prefix. If the prefix
changes in some other way than I have guessed the code would need to be
adjusted. Depending on the details, the best choice may be to format the
number along these lines, then combine the rightmost eight characters
(leaving out the two-digit year that occupies the leftmost two characters)
with the prefix. Without knowing more about the prefix I can't be more
specific.
My thinking here is that since you will store a primary key, it may as well
be the unique number you are already using. This code only comes into
effect when there is a new record. In a multi-user environment you would
have to make provisions for two users trying to enter a record at the same
time. One approach would be to apply the code in the form's Before Update
event, but there are a number of options.

Hi TC and first thanks for your information and explaination and please
forgive my ignorance as I'm fairly new at this but...
I'm following you on the first 7 characters storing them in a table, no
problem. Now I start to get confused from this point on; the julian date
is
the date that the invoice is created (I failed to mention that in my
original
post. Sorry) so how could I compute this without storing the date in a
table?
As well as the next two static characters? This brings me to my last
question on the sequence number. How would I compute this number? In
VBA?
If so, where would I incorporate it? I'm starting to understand the
relational database concept and if the information is available somewhere
else, I dont need to store it again but I'm missing the plot (probably due
to
my novice status) on this one :<(

Again, sorry for my ignorance and your help is gratly appreciated.

Hi Red

So you need to generate Invoice numbers of the following form:

F2P4MD6jjjHH999

where:
- the first 7 characters "F2P4MD6" are fixed for any given year;
- the next 3 digits are the "julian date";
- the next two characters "HH" are fixed, and
- the last 3 digits reflect the "entry number" on that day: 001, 002,
003, and so on; restarting from 001 on the next day.

(Note: You've said 14 characters in some places, and 15 in others. But
the difference is not important, to the thrust of this discussion.)

First, you should *not* embody the 7-character prefix values into your
database program code. Instead, you should get those values from a
database table like the following:

tblPrefixCodes:
YearNumber (primary key)
PrefixCode

Then, when the system wants the current code - for example, to display
in an Invoice number on a form or report - it just gets the current
year, using a simple VBA function call, then goes into that table "by
year", to get the relevant prefix code. By this means, you can add the
next year's code, just by adding a record to this table - no
programming changes are required. The following VBA code would get the
current year's prefix code, from that table:

DLookup("[PrefixCode]", "[tblPrefixCodes]", "[YearNumber]=" &
Year(Date()))

Second, you say the next 3 digits are for the "julian date". What is
his the date *of*? If it is the date of a record that you entered into
the database, you should "stamp" each record with the date & time of
data entry. Then the system can *compute* the julian date of that
record, when it needs to display that value on a form or report. *It
does not need to store than value*.

Third, the same comment applies to the daily sequence number. It really
is not correct to *store* that number. Instead, each record should be
"stamped" with the date & time of data entry. Then, you can *compute*
each record's sequence numbers, from the date/time stamp in each
record.

You can "stamp" each record in a table with the date & time of data
entry, by adding a Date/Time field to that table, and setting the
Default Value of that field to: =Date() Then Access will
automagically store the current date & time in that field, in each new
record that you add to that table.

If you're skeptical of the benefit of computing the daily sequence
numbers instead of actually storing them - consider what happens if the
user deletes a previously entered record. If you *stored* the daily
sequence number, you now have a gap in the numbers: they go 001, 002,
and 004 - 003 has been deleted! But if you *compute* those numbers,
this problem simply doesn't occur.

In summary, I believe that you should *store* the 7-character prefix
numbers, in a table, one for each year; but you should *compute* the
daily sequence numbers, at runtime, as & when required.

As for the julian date and "HH" fields, those are somewat unclear at
this time. But the same principles as described abive, apply: you
should not *store* and data that can be computed from other data, such
as, the date/time value that ius stored in each record.

Does that help - or just confuse?

TC (MVP Access)
http://tc2.atspace.com

 

[Red via AccessMonster.com]

Thanks Bruce,
I will try your code and idea's and will let you know how I get along. It
is about quitting time here so it might be tomorrow before I get a chance.
Thanks for your help it is appreciated.

Red

You say that "F2P4MD6" is fixed for this year, but you don't say how it
changes next year. The following is in the form's Current event. This
approach assumes that the "6" is for this year, except I have formatted it
as "06". The "Julian Date" is the number of days since the first of the
year. In my test I called the table tblDateTime, and the field DateTimeID.

If Me.NewRecord Then
Dim strPrefix As String
Dim strJulDate As String
Dim strWhere As String
Dim varResult As Variant

strPrefix = Format(Date, "yy")
strJulDate = Format(Format(Date, "y"), "000")
strWhere = "DateTimeID Like """ & strPrefix & strJulDate & "HH" &
"*"""
varResult = DMax("DateTimeID", "tblDateTime", strWhere)

If IsNull(varResult) Then
MsgBox "varResult is null"
Me.DateTimeID = strPrefix & strJulDate & "HH" & "001"
Else
Me.DateTimeID = Left(varResult, 7) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If
End If

This number could be concatenated as needed with the prefix. If the prefix
changes in some other way than I have guessed the code would need to be
adjusted. Depending on the details, the best choice may be to format the
number along these lines, then combine the rightmost eight characters
(leaving out the two-digit year that occupies the leftmost two characters)
with the prefix. Without knowing more about the prefix I can't be more
specific.
My thinking here is that since you will store a primary key, it may as well
be the unique number you are already using. This code only comes into
effect when there is a new record. In a multi-user environment you would
have to make provisions for two users trying to enter a record at the same
time. One approach would be to apply the code in the form's Before Update
event, but there are a number of options.

Hi TC and first thanks for your information and explaination and please
forgive my ignorance as I'm fairly new at this but...

[quoted text clipped - 90 lines]

 

[Red via AccessMonster.com]

Hi Bruce,

Sorry it has taken a couple of days to get back to you. Tried your code
below, but keep getting the following error;

Compile error: Method or data member not found in the following line.

Me.DateTimeID = strPrefix & strJulDate & "HH" & "001"

It seems to error out at Me.DateTImeID

I have tried several variations to see if I could get it to run, but still
errors. I set up my table and field as per your example. Any help will be
truely appreciated.

VR,

Red

You say that "F2P4MD6" is fixed for this year, but you don't say how it
changes next year. The following is in the form's Current event. This
approach assumes that the "6" is for this year, except I have formatted it
as "06". The "Julian Date" is the number of days since the first of the
year. In my test I called the table tblDateTime, and the field DateTimeID.

If Me.NewRecord Then
Dim strPrefix As String
Dim strJulDate As String
Dim strWhere As String
Dim varResult As Variant

strPrefix = Format(Date, "yy")
strJulDate = Format(Format(Date, "y"), "000")
strWhere = "DateTimeID Like """ & strPrefix & strJulDate & "HH" &
"*"""
varResult = DMax("DateTimeID", "tblDateTime", strWhere)

If IsNull(varResult) Then
MsgBox "varResult is null"
Me.DateTimeID = strPrefix & strJulDate & "HH" & "001"
Else
Me.DateTimeID = Left(varResult, 7) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If
End If

This number could be concatenated as needed with the prefix. If the prefix
changes in some other way than I have guessed the code would need to be
adjusted. Depending on the details, the best choice may be to format the
number along these lines, then combine the rightmost eight characters
(leaving out the two-digit year that occupies the leftmost two characters)
with the prefix. Without knowing more about the prefix I can't be more
specific.
My thinking here is that since you will store a primary key, it may as well
be the unique number you are already using. This code only comes into
effect when there is a new record. In a multi-user environment you would
have to make provisions for two users trying to enter a record at the same
time. One approach would be to apply the code in the form's Before Update
event, but there are a number of options.

Hi TC and first thanks for your information and explaination and please
forgive my ignorance as I'm fairly new at this but...

[quoted text clipped - 90 lines]

 

[BruceM]

You need to use your actual table and field names. Where I have written
tblDateTime you need to use your table name; likewise where I have written
DateTimeID you need to use your field name. I didn't know what those names
were, so I substituted something in their place, but neglected to be
specific about the need for you to use actual names from your database.

If you are using those names, is the form bound to a record source (table or
query) that includes the field DateTimeID? Is DateTimeID a text field? Do
you see DateTimeID after you type Me. in the code window (assuming that the
auto-complete feature, or whatever exactly it's called, is enabled)? If
not, do you see the name of the control (text box). If the text box and the
field have the same name, try changing the text box to txtDateTimeID. If
you still do not see DateTimeID as an auto-complete choice, remove the
form's record source (by deleting it in the Property window). Save the
form, close it, reopen it, add the record source, save the form, and try the
code again.

Another thing you could do to experiment is to create tblDateTime containing
only DateTimeID as a text field. Make a form based on tblDateTime, add the
code to the form's Current event, and try again.

Let me know how it comes out. I'm interested in discovering all I can about
automatic sequence numbering.

Hi Bruce,

Sorry it has taken a couple of days to get back to you. Tried your code
below, but keep getting the following error;

Compile error: Method or data member not found in the following line.

Me.DateTimeID = strPrefix & strJulDate & "HH" & "001"

It seems to error out at Me.DateTImeID

I have tried several variations to see if I could get it to run, but still
errors. I set up my table and field as per your example. Any help will
be
truely appreciated.

VR,

Red

You say that "F2P4MD6" is fixed for this year, but you don't say how it
changes next year. The following is in the form's Current event. This
approach assumes that the "6" is for this year, except I have formatted it
as "06". The "Julian Date" is the number of days since the first of the
year. In my test I called the table tblDateTime, and the field
DateTimeID.

If Me.NewRecord Then
Dim strPrefix As String
Dim strJulDate As String
Dim strWhere As String
Dim varResult As Variant

strPrefix = Format(Date, "yy")
strJulDate = Format(Format(Date, "y"), "000")
strWhere = "DateTimeID Like """ & strPrefix & strJulDate & "HH" &
"*"""
varResult = DMax("DateTimeID", "tblDateTime", strWhere)

If IsNull(varResult) Then
MsgBox "varResult is null"
Me.DateTimeID = strPrefix & strJulDate & "HH" & "001"
Else
Me.DateTimeID = Left(varResult, 7) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If
End If

This number could be concatenated as needed with the prefix. If the
prefix
changes in some other way than I have guessed the code would need to be
adjusted. Depending on the details, the best choice may be to format the
number along these lines, then combine the rightmost eight characters
(leaving out the two-digit year that occupies the leftmost two characters)
with the prefix. Without knowing more about the prefix I can't be more
specific.
My thinking here is that since you will store a primary key, it may as
well
be the unique number you are already using. This code only comes into
effect when there is a new record. In a multi-user environment you would
have to make provisions for two users trying to enter a record at the same
time. One approach would be to apply the code in the form's Before Update
event, but there are a number of options.

Hi TC and first thanks for your information and explaination and please
forgive my ignorance as I'm fairly new at this but...

[quoted text clipped - 90 lines]

TC (MVP Access)
http://tc2.atspace.com

 

[Red via AccessMonster.com]

Hi Bruce,
I am working with your code and it is working ok except for a couple of
things... When I open the invoice form, the sequence number is blank. If I
scroll using the mouse ball, I get the message MsgBox "varResult is null"
then if I click ok, the correct sequence number apprears. But if I look at
the table this number is stored in, I notice that it is creating a seperate
record vs adding it to the record selected. Also, when I cut and paste the
code to a second form (patient bill, same form, just invoicing the patient vs
the insurance company), it errors out but works fine in the insurance invoice.
Doesn't make much sense to me. I hope this all makes sense to you and hope
you can shed some light on this problem and as always, it is greatly
appreciated.

VR,

Red

You need to use your actual table and field names. Where I have written
tblDateTime you need to use your table name; likewise where I have written
DateTimeID you need to use your field name. I didn't know what those names
were, so I substituted something in their place, but neglected to be
specific about the need for you to use actual names from your database.

If you are using those names, is the form bound to a record source (table or
query) that includes the field DateTimeID? Is DateTimeID a text field? Do
you see DateTimeID after you type Me. in the code window (assuming that the
auto-complete feature, or whatever exactly it's called, is enabled)? If
not, do you see the name of the control (text box). If the text box and the
field have the same name, try changing the text box to txtDateTimeID. If
you still do not see DateTimeID as an auto-complete choice, remove the
form's record source (by deleting it in the Property window). Save the
form, close it, reopen it, add the record source, save the form, and try the
code again.

Another thing you could do to experiment is to create tblDateTime containing
only DateTimeID as a text field. Make a form based on tblDateTime, add the
code to the form's Current event, and try again.

Let me know how it comes out. I'm interested in discovering all I can about
automatic sequence numbering.

Hi Bruce,

[quoted text clipped - 65 lines]

 

[BruceM]

I added the message box line when I was testing the code, and forgot to
remove it. Sorry about that. Just remove the line:
MsgBox "varResult is null"
If you scroll forward from the most recently entered record you are moving
to a new record. That was why the message box line kicked in. If the field
in which the sequence number appears is set in table design view to
Indexed - No Duplicates you will not be able to move past the new record
unless the field is completed.
The code is designed for new records. Once the record has been created it
is no longer new, so the existing number will remain. Are you trying to
apply the code to existing records that do not contain a sequence number?
When you add the code to another form you need to adjust the field and table
names to match the new record source. But are you adding the same code
separately to records from different tables? Is the sequence number part of
relationships? Maybe you explained all of this earlier in the thread, but I
can't find it. Please explain briefly the database's purpose and structure.
I can't quite sort out all of your questions.

Hi Bruce,
I am working with your code and it is working ok except for a couple of
things... When I open the invoice form, the sequence number is blank.
If I
scroll using the mouse ball, I get the message MsgBox "varResult is null"
then if I click ok, the correct sequence number apprears. But if I look
at
the table this number is stored in, I notice that it is creating a
seperate
record vs adding it to the record selected. Also, when I cut and paste
the
code to a second form (patient bill, same form, just invoicing the patient
vs
the insurance company), it errors out but works fine in the insurance
invoice.
Doesn't make much sense to me. I hope this all makes sense to you and
hope
you can shed some light on this problem and as always, it is greatly
appreciated.

VR,

Red

You need to use your actual table and field names. Where I have written
tblDateTime you need to use your table name; likewise where I have written
DateTimeID you need to use your field name. I didn't know what those
names
were, so I substituted something in their place, but neglected to be
specific about the need for you to use actual names from your database.

If you are using those names, is the form bound to a record source (table
or
query) that includes the field DateTimeID? Is DateTimeID a text field?
Do
you see DateTimeID after you type Me. in the code window (assuming that
the
auto-complete feature, or whatever exactly it's called, is enabled)? If
not, do you see the name of the control (text box). If the text box and
the
field have the same name, try changing the text box to txtDateTimeID. If
you still do not see DateTimeID as an auto-complete choice, remove the
form's record source (by deleting it in the Property window). Save the
form, close it, reopen it, add the record source, save the form, and try
the
code again.

Another thing you could do to experiment is to create tblDateTime
containing
only DateTimeID as a text field. Make a form based on tblDateTime, add
the
code to the form's Current event, and try again.

Let me know how it comes out. I'm interested in discovering all I can
about
automatic sequence numbering.

Hi Bruce,

[quoted text clipped - 65 lines]

TC (MVP Access)
http://tc2.atspace.com

 

[Red via AccessMonster.com]

I'm sorry Bruce, I'll try and be more clear in my descriptions. as far as
the MsgBox code, I thought that was your thinking when you put the line of
code in was to test it. I'm at a very early stage of writing code and I will
use your practice to test code in this manner when I'm writing future code if
you dont mind. Now to my problem. What I have is a form that has patient
info ie Dr info, appt details, insurance details, personal information etc.
On the botton of the form I have two Cmd Buttons to create an invoice, one if
the invoice is being sent to the patient and the other if the invoice is
being sent to an insurance co (utilizing same table as Record Source for
either Invoice form). What I want to do is when I click either button, the
sequence number we have been talking about will populate. When I added the
code in the current event, nothing displays until I scroll forward. (I've
been thinking and will test it, but maybe I need to add this code to another
event? Perhaps "on click"? I'll do some experimenting). On the Insurance
Invoice the sequence number does display after scrolling forward, but on the
patient Invoice (like I said, same form except where to send bill having same
record source) the code errors. I hope I have explained this a bit better
this time. I do appreciate you help. in-turn, one day I hope to be good
enough to help others.

VR,

Red

I added the message box line when I was testing the code, and forgot to
remove it. Sorry about that. Just remove the line:
MsgBox "varResult is null"
If you scroll forward from the most recently entered record you are moving
to a new record. That was why the message box line kicked in. If the field
in which the sequence number appears is set in table design view to
Indexed - No Duplicates you will not be able to move past the new record
unless the field is completed.
The code is designed for new records. Once the record has been created it
is no longer new, so the existing number will remain. Are you trying to
apply the code to existing records that do not contain a sequence number?
When you add the code to another form you need to adjust the field and table
names to match the new record source. But are you adding the same code
separately to records from different tables? Is the sequence number part of
relationships? Maybe you explained all of this earlier in the thread, but I
can't find it. Please explain briefly the database's purpose and structure.
I can't quite sort out all of your questions.

Hi Bruce,
I am working with your code and it is working ok except for a couple of

[quoted text clipped - 58 lines]

 

[BruceM]

Do what you like with test message boxes. It may not be the most efficient
method, but it works for me.
The code I provided works when a record is created. At any time the record
is New (hasn't been saved by navigating away from the record or by
explicitly saving it), it can be referred to in VBA as NewRecord. The best
time to create a unique number is usually when the record is new.
You say you have a from with patient information such as doctor, insurance
details, etc. How many tables, and in what relation to each other, go into
this form? In general you should have a table for personal information
(which could include primary insurance information, I suppose, although it
would be better to have such information in a separate table in case
somebody has several insurance plans). Doctor information should be in a
separate table that is related to the patient table. Each doctor can have
many patients, and presumably a patient can have several specialists.
Invoice information needs to be in its own table. When a new invoice record
is created, the number is added. If the invoice information (including
invoice number) for a particular visit or treatement is the same for the
patient as it is for the insurance company, patient and Insurance invoice
information should be in the same table. Each invoice is a separate record
in that table. If I knew how the two invoices differ I could probably
suggest a way to make that happen.
As for the number not appearing right away, I'm not sure what to suggest
other than to add a Me.Refresh line of code, or Me.Dirty = False after
creating the number (which saves the record). I haven't used this method
with subform data, where it may behave differently than in a main form data.
As for the error, without knowing what it is I cannot suggest what is
causing it. If you are creating a separate invoice number for Patient and
for Insurance, you need to verify that everything is exactly the same in
both invoice tables for things such as data type (in table design view) of
the DateTimeID field, field names, etc.
To help identify the error, try putting this line of code just under Private
Sub in the code window (I will assume the Sub is identified: Private Sub
Form_Current()):

On Error GoTo ProcErr

Just before End Sub, put the following:

ProcExit:
Exit Sub

ProcErr:
msgbox "Error #" & Err.Number & ", " & Err. Description & ":
Form_Current"
Resume ProcExit

This will identify the exact error and which event is causing it.

I'm sorry Bruce, I'll try and be more clear in my descriptions. as far as
the MsgBox code, I thought that was your thinking when you put the line of
code in was to test it. I'm at a very early stage of writing code and I
will
use your practice to test code in this manner when I'm writing future code
if
you dont mind. Now to my problem. What I have is a form that has
patient
info ie Dr info, appt details, insurance details, personal information
etc.
On the botton of the form I have two Cmd Buttons to create an invoice, one
if
the invoice is being sent to the patient and the other if the invoice is
being sent to an insurance co (utilizing same table as Record Source for
either Invoice form). What I want to do is when I click either button,
the
sequence number we have been talking about will populate. When I added
the
code in the current event, nothing displays until I scroll forward. (I've
been thinking and will test it, but maybe I need to add this code to
another
event? Perhaps "on click"? I'll do some experimenting). On the
Insurance
Invoice the sequence number does display after scrolling forward, but on
the
patient Invoice (like I said, same form except where to send bill having
same
record source) the code errors. I hope I have explained this a bit better
this time. I do appreciate you help. in-turn, one day I hope to be good
enough to help others.

VR,

Red

I added the message box line when I was testing the code, and forgot to
remove it. Sorry about that. Just remove the line:
MsgBox "varResult is null"
If you scroll forward from the most recently entered record you are moving
to a new record. That was why the message box line kicked in. If the
field
in which the sequence number appears is set in table design view to
Indexed - No Duplicates you will not be able to move past the new record
unless the field is completed.
The code is designed for new records. Once the record has been created it
is no longer new, so the existing number will remain. Are you trying to
apply the code to existing records that do not contain a sequence number?
When you add the code to another form you need to adjust the field and
table
names to match the new record source. But are you adding the same code
separately to records from different tables? Is the sequence number part
of
relationships? Maybe you explained all of this earlier in the thread, but
I
can't find it. Please explain briefly the database's purpose and
structure.
I can't quite sort out all of your questions.

Hi Bruce,
I am working with your code and it is working ok except for a couple of

[quoted text clipped - 58 lines]

TC (MVP Access)
http://tc2.atspace.com