Creating a sequence number

"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

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.

TC wrote:
Hi Red

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

F2P4MD6jjjHH999

whe
- 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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200604/1

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.

"Red via AccessMonster.com" u15475@uwe wrote in message
news:5ee912809296c@uwe...
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.

TC wrote:
Hi Red

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

F2P4MD6jjjHH999

whe
- 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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200604/1

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



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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200604/1

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

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200604/1

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.

"Red via AccessMonster.com" u15475@uwe wrote in message
news:5f10398961800@uwe...
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

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200604/1

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

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200604/1

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.

"Red via AccessMonster.com" u15475@uwe wrote in message
news:5f426d4911e81@uwe...
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

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200604/1

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




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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200604/1

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.

"Red via AccessMonster.com" u15475@uwe wrote in message
news:5f4b7d95a426f@uwe...
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




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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200604/1