Calculating numbers, part 2

To Ken and others who can help...

I tried to follow Ken's instructions (reprinted below) very carefully but I
did not get them to work for me.

Here's what I did:

First, I created the DocumentsIssued table with the following
fields: Form_Number (which I tried to link to my Forms#_Title table), the
StartNumber, EndNumber and NumberIssued. Then I created a DocumentsIssued
Form and on the NunberIssued column AfterEvent, I entered the script Ken so
kindly provided. As my Form Numbers are alpha /numeric, I added the quotes
around the value. I also added the formatting to get 00001 for the Start and
End numbers.

My results? The form does not show the Form Numbers and it shows 00000 as
the StartNumber, 0 as the EndNumber and 000 as the NumberIssued (I programmed
this field to allow only three digits). Did I miss a step? When I issue ddo
forms for the first time, how will Access know that the StartNumber is 00001
and the EndNumber is 00025, if 25 forms were issued?

Also, from what I've told you, can you tell why the Form_Numbers from the
Forms#_Title table are not appearing in my DocumentIssued form? (The numbers
do appear in the Forms#_Title table .

Thanks, again Ken -- and all others who respond -- for your help.

Etta


Response from Ken...
Lets assume your table is called DocumentsIssued and it includes columns
DocumentID, StartNumber, EndNumber as well as the column for the
NumberIssued. In the data entry form, in the AfterUpdate event procedure of
the control bound to the NumberIssued column look up the last number issued,
and add 1 to get the next starting number and the value of the number issued
control to get the next ending number, assigning the two values to the
StartNumber and EndNumber controls, like so:

Dim intNumberIssued As Integer
Dim strCriteria as String

strCriteria = "[DocumentID] = " & Me.[DocumentID]

intNumberIssued = Nz(Dmax("[EndNumber]","[DocumentsIssued]", strCriteria),0)

Me.[StartNumber] = intNumberIssued + 1
Me.[EndNumber] = intNumberIssued + Me.[NumberIssued]

This assumes DocumentID is a number data type. If it were text you'd need
to wrap the value in quotes:

strCriteria = "[DocumentID] = """ & Me.[DocumentID] & """"

To show the numbers with leading zeros set the Format properties of the
StartNumber and EndNumber controls to 00000.

The above assumes that each document is issued to one department only rather
than to more than one department with the same number sequence used for each,
i.e. each starting with 1 rather than, for instance, one department using 1
to 25 and another department 26 to 50 and so on. If repetition of the same
numbers for the same document issued to different departments were the case
then the table would need to include a column such as DepartmentID and this
would need to be included in the criterion when calling the DMax function to
get the last number issued to that department, e.g.

strCriteria = "[DocumentID] = " & Me.[DocumentID] & " And [DepartmentID] = "
& Me.[DepartmentID]

Again this assumes both are of number data type.

Ken Sheridan
Stafford, England

"Etta" wrote:

I am new to creating Access databases and need to know how to set up a way to
track the number of documents issued to a department. That is, over the
course of a year, we’ll issue packages of documents to a department,
beginning with document 1. Each package will have set number (e.g., 25), so
the first package will have documents 00001 through 00025. When we issue a
second package, the document numbers, then, should start with 00026 and end
with 00050. I have a table holding the name of the document as well as a
unique identifying number. And I have a table that holds the number of
documents issued, the starting number and the ending number, as well as other
data. How can I automate this so when we enter the number of documents to be
issued, it auto populates the starting and ending numbers?

Let's take it step by step:

The form does not show the Form Numbers

You need to select the relevant Form Number first in the form. How you do
this depends on whether you want to be able to select the alphanumeric number
itself or a text description of the form which corresponds to the number and
which is stored in a text column in the Forms#_Title table, Form_Description
say, in which table Form_Number is the primary key column.

If you want to select the Form Number you'd have a combo box on the form,
bound to the Form_Number column, with a RowSource property of:

SELECT Form_Number FROM [Forms#_Title] ORDER BY Form_Number;

If, however, you want to select a text description then you'd again use a
combo box bound to the Form_Number column, but with a RowSource property of:

SELECT Form_Number, Form_Description FROM [Forms#_Title] ORDER BY
Form_Description;

You'd also have to set other properties as follows:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm

The last property will convert to inches automatically if you are using
imperial rather than metric measurements. If the description is a long one,
and the combo box thus a wide one, you might need to increase the second
dimension (its actual size is not important so long as its at least as wide
as the combo box), but the first should stay as zero to hide the first column
so you only see the text description.

0 as the EndNumber

This suggests that the control is not formatted 00000

When I issue ddo forms for the first time, how will Access know that the StartNumber is 00001 and the EndNumber is 00025

You'll first select the form in question from the combo box as described
above. The form must be in the Forms#_Title table already of course, though
there are ways you can add a new row to that table directly from the combo
box if necessary; we can perhaps revisit that later, but for now let's not
introduce a further complication. Once you've selected a form you then enter
the number issued. This causes the AfterUpdate event procedure of the
control bound to that column to fire and the code in its event procedure to
be executed. By means of the DMax function the code looks for the highest
EndNumber value in the table for the form in question ( a fully developed
application would include some validation to force a form to be selected
before entering a number issued, but that's again something we can revisit
later if you wish).

If this is the first time the selected form has been issued the DMax
function would return a Null. A Null is the absence of a value, an unknown,
and should not be confused with zero, so the Nz function is used to return a
zero in place of the Null. The code then adds 1 to this value to get the
StartNumber and also adds the value you entered as NumberIssued to get the
EndNumber.

Now, you might have noticed that there is some redundancy here as we don't
need to store both the start and end number in the table, but only one of
them as the other can be computed from whichever one is kept and the number
issued. Ideally one should be ditched as it does leave the door open to
possible inconsistencies in the data, but I'll leave that decision to you.
If you do decide to eliminate the redundancy, and thus the risk, I'd suggest
ditching the StartNumber column and using a computed control on the form with
a ControlSource of:

=[EndNumber] - [NumberIssued]

If you do this the code only needs a slight amendment, the deletion of one
line, so it would read:

Dim intNumberIssued As Integer
Dim strCriteria as String

strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """"

intNumberIssued = Nz(Dmax("[EndNumber]","[DocumentsIssued]", strCriteria),0)

Me.[EndNumber] = intNumberIssued + Me.[NumberIssued]

Ken Sheridan
Stafford, England

"Etta" wrote:

To Ken and others who can help...

I tried to follow Ken's instructions (reprinted below) very carefully but I
did not get them to work for me.

Here's what I did:

First, I created the DocumentsIssued table with the following
fields: Form_Number (which I tried to link to my Forms#_Title table), the
StartNumber, EndNumber and NumberIssued. Then I created a DocumentsIssued
Form and on the NunberIssued column AfterEvent, I entered the script Ken so
kindly provided. As my Form Numbers are alpha /numeric, I added the quotes
around the value. I also added the formatting to get 00001 for the Start and
End numbers.

My results? The form does not show the Form Numbers and it shows 00000 as
the StartNumber, 0 as the EndNumber and 000 as the NumberIssued (I programmed
this field to allow only three digits). Did I miss a step? When I issue ddo
forms for the first time, how will Access know that the StartNumber is 00001
and the EndNumber is 00025, if 25 forms were issued?

Also, from what I've told you, can you tell why the Form_Numbers from the
Forms#_Title table are not appearing in my DocumentIssued form? (The numbers
do appear in the Forms#_Title table .

Thanks, again Ken -- and all others who respond -- for your help.

Etta


Response from Ken...
Lets assume your table is called DocumentsIssued and it includes columns
DocumentID, StartNumber, EndNumber as well as the column for the
NumberIssued. In the data entry form, in the AfterUpdate event procedure of
the control bound to the NumberIssued column look up the last number issued,
and add 1 to get the next starting number and the value of the number issued
control to get the next ending number, assigning the two values to the
StartNumber and EndNumber controls, like so:

Dim intNumberIssued As Integer
Dim strCriteria as String

strCriteria = "[DocumentID] = " & Me.[DocumentID]

intNumberIssued = Nz(Dmax("[EndNumber]","[DocumentsIssued]", strCriteria),0)

Me.[StartNumber] = intNumberIssued + 1
Me.[EndNumber] = intNumberIssued + Me.[NumberIssued]

This assumes DocumentID is a number data type. If it were text you'd need
to wrap the value in quotes:

strCriteria = "[DocumentID] = """ & Me.[DocumentID] & """"

To show the numbers with leading zeros set the Format properties of the
StartNumber and EndNumber controls to 00000.

The above assumes that each document is issued to one department only rather
than to more than one department with the same number sequence used for each,
i.e. each starting with 1 rather than, for instance, one department using 1
to 25 and another department 26 to 50 and so on. If repetition of the same
numbers for the same document issued to different departments were the case
then the table would need to include a column such as DepartmentID and this
would need to be included in the criterion when calling the DMax function to
get the last number issued to that department, e.g.

strCriteria = "[DocumentID] = " & Me.[DocumentID] & " And [DepartmentID] = "
& Me.[DepartmentID]

Again this assumes both are of number data type.

Ken Sheridan
Stafford, England

"Etta" wrote:

I am new to creating Access databases and need to know how to set up a way to
track the number of documents issued to a department. That is, over the
course of a year, we’ll issue packages of documents to a department,
beginning with document 1. Each package will have set number (e.g., 25), so
the first package will have documents 00001 through 00025. When we issue a
second package, the document numbers, then, should start with 00026 and end
with 00050. I have a table holding the name of the document as well as a
unique identifying number. And I have a table that holds the number of
documents issued, the starting number and the ending number, as well as other
data. How can I automate this so when we enter the number of documents to be
issued, it auto populates the starting and ending numbers?

Thanks, Ken. Because of your assistance I can see light at the end of the
tunnel! But I do need your help again.

I followed your advice and removed the Start_Number from the Table and Form.
I don’t know how to set up a “computed control on the form with a
ControlSource of:
=[End_Number] - [Form_Issued]�

Also, I would like to add the validation feature you mention below. Can you
outline the steps I would need to follow?

~Etta
Providence, RI (USA)

"Ken Sheridan" wrote:

Let's take it step by step:

The form does not show the Form Numbers

You need to select the relevant Form Number first in the form. How you do
this depends on whether you want to be able to select the alphanumeric number
itself or a text description of the form which corresponds to the number and
which is stored in a text column in the Forms#_Title table, Form_Description
say, in which table Form_Number is the primary key column.

If you want to select the Form Number you'd have a combo box on the form,
bound to the Form_Number column, with a RowSource property of:

SELECT Form_Number FROM [Forms#_Title] ORDER BY Form_Number;

If, however, you want to select a text description then you'd again use a
combo box bound to the Form_Number column, but with a RowSource property of:

SELECT Form_Number, Form_Description FROM [Forms#_Title] ORDER BY
Form_Description;

You'd also have to set other properties as follows:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm

The last property will convert to inches automatically if you are using
imperial rather than metric measurements. If the description is a long one,
and the combo box thus a wide one, you might need to increase the second
dimension (its actual size is not important so long as its at least as wide
as the combo box), but the first should stay as zero to hide the first column
so you only see the text description.

0 as the EndNumber

This suggests that the control is not formatted 00000

When I issue ddo forms for the first time, how will Access know that the StartNumber is 00001 and the EndNumber is 00025

You'll first select the form in question from the combo box as described
above. The form must be in the Forms#_Title table already of course, though
there are ways you can add a new row to that table directly from the combo
box if necessary; we can perhaps revisit that later, but for now let's not
introduce a further complication. Once you've selected a form you then enter
the number issued. This causes the AfterUpdate event procedure of the
control bound to that column to fire and the code in its event procedure to
be executed. By means of the DMax function the code looks for the highest
EndNumber value in the table for the form in question ( a fully developed
application would include some validation to force a form to be selected
before entering a number issued, but that's again something we can revisit
later if you wish).

If this is the first time the selected form has been issued the DMax
function would return a Null. A Null is the absence of a value, an unknown,
and should not be confused with zero, so the Nz function is used to return a
zero in place of the Null. The code then adds 1 to this value to get the
StartNumber and also adds the value you entered as NumberIssued to get the
EndNumber.

Now, you might have noticed that there is some redundancy here as we don't
need to store both the start and end number in the table, but only one of
them as the other can be computed from whichever one is kept and the number
issued. Ideally one should be ditched as it does leave the door open to
possible inconsistencies in the data, but I'll leave that decision to you.
If you do decide to eliminate the redundancy, and thus the risk, I'd suggest
ditching the StartNumber column and using a computed control on the form with
a ControlSource of:

=[EndNumber] - [NumberIssued]

If you do this the code only needs a slight amendment, the deletion of one
line, so it would read:

Dim intNumberIssued As Integer
Dim strCriteria as String

strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """"

intNumberIssued = Nz(Dmax("[EndNumber]","[DocumentsIssued]", strCriteria),0)

Me.[EndNumber] = intNumberIssued + Me.[NumberIssued]

Ken Sheridan
Stafford, England

"Etta" wrote:

To Ken and others who can help...

I tried to follow Ken's instructions (reprinted below) very carefully but I
did not get them to work for me.

Here's what I did:

First, I created the DocumentsIssued table with the following
fields: Form_Number (which I tried to link to my Forms#_Title table), the
StartNumber, EndNumber and NumberIssued. Then I created a DocumentsIssued
Form and on the NunberIssued column AfterEvent, I entered the script Ken so
kindly provided. As my Form Numbers are alpha /numeric, I added the quotes
around the value. I also added the formatting to get 00001 for the Start and
End numbers.

My results? The form does not show the Form Numbers and it shows 00000 as
the StartNumber, 0 as the EndNumber and 000 as the NumberIssued (I programmed
this field to allow only three digits). Did I miss a step? When I issue ddo
forms for the first time, how will Access know that the StartNumber is 00001
and the EndNumber is 00025, if 25 forms were issued?

Also, from what I've told you, can you tell why the Form_Numbers from the
Forms#_Title table are not appearing in my DocumentIssued form? (The numbers
do appear in the Forms#_Title table .

Thanks, again Ken -- and all others who respond -- for your help.

Etta


Response from Ken...
Lets assume your table is called DocumentsIssued and it includes columns
DocumentID, StartNumber, EndNumber as well as the column for the
NumberIssued. In the data entry form, in the AfterUpdate event procedure of
the control bound to the NumberIssued column look up the last number issued,
and add 1 to get the next starting number and the value of the number issued
control to get the next ending number, assigning the two values to the
StartNumber and EndNumber controls, like so:

Dim intNumberIssued As Integer
Dim strCriteria as String

strCriteria = "[DocumentID] = " & Me.[DocumentID]

intNumberIssued = Nz(Dmax("[EndNumber]","[DocumentsIssued]", strCriteria),0)

Me.[StartNumber] = intNumberIssued + 1
Me.[EndNumber] = intNumberIssued + Me.[NumberIssued]

This assumes DocumentID is a number data type. If it were text you'd need
to wrap the value in quotes:

strCriteria = "[DocumentID] = """ & Me.[DocumentID] & """"

To show the numbers with leading zeros set the Format properties of the
StartNumber and EndNumber controls to 00000.

The above assumes that each document is issued to one department only rather
than to more than one department with the same number sequence used for each,
i.e. each starting with 1 rather than, for instance, one department using 1
to 25 and another department 26 to 50 and so on. If repetition of the same
numbers for the same document issued to different departments were the case
then the table would need to include a column such as DepartmentID and this
would need to be included in the criterion when calling the DMax function to
get the last number issued to that department, e.g.

strCriteria = "[DocumentID] = " & Me.[DocumentID] & " And [DepartmentID] = "
& Me.[DepartmentID]

Again this assumes both are of number data type.

Ken Sheridan
Stafford, England

"Etta" wrote:

I am new to creating Access databases and need to know how to set up a way to
track the number of documents issued to a department. That is, over the
course of a year, we’ll issue packages of documents to a department,
beginning with document 1. Each package will have set number (e.g., 25), so
the first package will have documents 00001 through 00025. When we issue a
second package, the document numbers, then, should start with 00026 and end
with 00050. I have a table holding the name of the document as well as a
unique identifying number. And I have a table that holds the number of
documents issued, the starting number and the ending number, as well as other
data. How can I automate this so when we enter the number of documents to be
issued, it auto populates the starting and ending numbers?

Another issue just arose, Ken. When I typed 25 in the Number_Issued field, I
got a syntax error message: Private Sub Number_Issued_AfterUpdate(). How
can I fix this?

"Etta" wrote:

Thanks, Ken. Because of your assistance I can see light at the end of the
tunnel! But I do need your help again.

I followed your advice and removed the Start_Number from the Table and Form.
I don’t know how to set up a “computed control on the form with a
ControlSource of:
=[End_Number] - [Form_Issued]�

Also, I would like to add the validation feature you mention below. Can you
outline the steps I would need to follow?

~Etta
Providence, RI (USA)

"Ken Sheridan" wrote:

Let's take it step by step:

The form does not show the Form Numbers

You need to select the relevant Form Number first in the form. How you do
this depends on whether you want to be able to select the alphanumeric number
itself or a text description of the form which corresponds to the number and
which is stored in a text column in the Forms#_Title table, Form_Description
say, in which table Form_Number is the primary key column.

If you want to select the Form Number you'd have a combo box on the form,
bound to the Form_Number column, with a RowSource property of:

SELECT Form_Number FROM [Forms#_Title] ORDER BY Form_Number;

If, however, you want to select a text description then you'd again use a
combo box bound to the Form_Number column, but with a RowSource property of:

SELECT Form_Number, Form_Description FROM [Forms#_Title] ORDER BY
Form_Description;

You'd also have to set other properties as follows:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm

The last property will convert to inches automatically if you are using
imperial rather than metric measurements. If the description is a long one,
and the combo box thus a wide one, you might need to increase the second
dimension (its actual size is not important so long as its at least as wide
as the combo box), but the first should stay as zero to hide the first column
so you only see the text description.

0 as the EndNumber

This suggests that the control is not formatted 00000

When I issue ddo forms for the first time, how will Access know that the StartNumber is 00001 and the EndNumber is 00025

You'll first select the form in question from the combo box as described
above. The form must be in the Forms#_Title table already of course, though
there are ways you can add a new row to that table directly from the combo
box if necessary; we can perhaps revisit that later, but for now let's not
introduce a further complication. Once you've selected a form you then enter
the number issued. This causes the AfterUpdate event procedure of the
control bound to that column to fire and the code in its event procedure to
be executed. By means of the DMax function the code looks for the highest
EndNumber value in the table for the form in question ( a fully developed
application would include some validation to force a form to be selected
before entering a number issued, but that's again something we can revisit
later if you wish).

If this is the first time the selected form has been issued the DMax
function would return a Null. A Null is the absence of a value, an unknown,
and should not be confused with zero, so the Nz function is used to return a
zero in place of the Null. The code then adds 1 to this value to get the
StartNumber and also adds the value you entered as NumberIssued to get the
EndNumber.

Now, you might have noticed that there is some redundancy here as we don't
need to store both the start and end number in the table, but only one of
them as the other can be computed from whichever one is kept and the number
issued. Ideally one should be ditched as it does leave the door open to
possible inconsistencies in the data, but I'll leave that decision to you.
If you do decide to eliminate the redundancy, and thus the risk, I'd suggest
ditching the StartNumber column and using a computed control on the form with
a ControlSource of:

=[EndNumber] - [NumberIssued]

If you do this the code only needs a slight amendment, the deletion of one
line, so it would read:

Dim intNumberIssued As Integer
Dim strCriteria as String

strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """"

intNumberIssued = Nz(Dmax("[EndNumber]","[DocumentsIssued]", strCriteria),0)

Me.[EndNumber] = intNumberIssued + Me.[NumberIssued]

Ken Sheridan
Stafford, England

"Etta" wrote:

To Ken and others who can help...

I tried to follow Ken's instructions (reprinted below) very carefully but I
did not get them to work for me.

Here's what I did:

First, I created the DocumentsIssued table with the following
fields: Form_Number (which I tried to link to my Forms#_Title table), the
StartNumber, EndNumber and NumberIssued. Then I created a DocumentsIssued
Form and on the NunberIssued column AfterEvent, I entered the script Ken so
kindly provided. As my Form Numbers are alpha /numeric, I added the quotes
around the value. I also added the formatting to get 00001 for the Start and
End numbers.

My results? The form does not show the Form Numbers and it shows 00000 as
the StartNumber, 0 as the EndNumber and 000 as the NumberIssued (I programmed
this field to allow only three digits). Did I miss a step? When I issue ddo
forms for the first time, how will Access know that the StartNumber is 00001
and the EndNumber is 00025, if 25 forms were issued?

Also, from what I've told you, can you tell why the Form_Numbers from the
Forms#_Title table are not appearing in my DocumentIssued form? (The numbers
do appear in the Forms#_Title table .

Thanks, again Ken -- and all others who respond -- for your help.

Etta


Response from Ken...
Lets assume your table is called DocumentsIssued and it includes columns
DocumentID, StartNumber, EndNumber as well as the column for the
NumberIssued. In the data entry form, in the AfterUpdate event procedure of
the control bound to the NumberIssued column look up the last number issued,
and add 1 to get the next starting number and the value of the number issued
control to get the next ending number, assigning the two values to the
StartNumber and EndNumber controls, like so:

Dim intNumberIssued As Integer
Dim strCriteria as String

strCriteria = "[DocumentID] = " & Me.[DocumentID]

intNumberIssued = Nz(Dmax("[EndNumber]","[DocumentsIssued]", strCriteria),0)

Me.[StartNumber] = intNumberIssued + 1
Me.[EndNumber] = intNumberIssued + Me.[NumberIssued]

This assumes DocumentID is a number data type. If it were text you'd need
to wrap the value in quotes:

strCriteria = "[DocumentID] = """ & Me.[DocumentID] & """"

To show the numbers with leading zeros set the Format properties of the
StartNumber and EndNumber controls to 00000.

The above assumes that each document is issued to one department only rather
than to more than one department with the same number sequence used for each,
i.e. each starting with 1 rather than, for instance, one department using 1
to 25 and another department 26 to 50 and so on. If repetition of the same
numbers for the same document issued to different departments were the case
then the table would need to include a column such as DepartmentID and this
would need to be included in the criterion when calling the DMax function to
get the last number issued to that department, e.g.

strCriteria = "[DocumentID] = " & Me.[DocumentID] & " And [DepartmentID] = "
& Me.[DepartmentID]

Again this assumes both are of number data type.

Ken Sheridan
Stafford, England

"Etta" wrote:

I am new to creating Access databases and need to know how to set up a way to
track the number of documents issued to a department. That is, over the
course of a year, we’ll issue packages of documents to a department,
beginning with document 1. Each package will have set number (e.g., 25), so
the first package will have documents 00001 through 00025. When we issue a
second package, the document numbers, then, should start with 00026 and end
with 00050. I have a table holding the name of the document as well as a
unique identifying number. And I have a table that holds the number of
documents issued, the starting number and the ending number, as well as other
data. How can I automate this so when we enter the number of documents to be
issued, it auto populates the starting and ending numbers?

I don’t know how to set up a “computed control on the form with a
ControlSource of:
=[End_Number] - [Form_Issued]

I assume that should be [Number_Issued] not [Form_Issued]. All you do is
add an unbound text box to the form from the toolbox, then enter
=[End_Number] - [Form_Issued] as its ControlSource property in its properties
sheet. I'd also suggest you change its Name property to something meaningful
like txtStartNumber.

Also, I would like to add the validation feature you mention below. Can you
outline the steps I would need to follow?

In the Change event procedure of the Number_Issued control put something
like the following code:

If IsNull(Me.[Number_Issued]) Then
MsgBox "Please select a form first.", vbExclamation, "Invalid
Operation"
Me.[Number_Issued].Undo
End If

Another issue just arose, Ken. When I typed 25 in the Number_Issued field, I
got a syntax error message: Private Sub Number_Issued_AfterUpdate(). How
can I fix this?

Sounds like there's a syntax error in the AfterUpdate event procedure's
code. It should be:

Dim intNumberIssued As Integer
Dim strCriteria as String

strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """"

intNumberIssued =
Nz(Dmax("[End_Number]","[DocumentsIssued]",strCriteria),0)

Me.[End_Number] = intNumberIssued + Me.[Number_Issued]

Check that the table and field names in the code are all correct.

Ken Sheridan
Stafford, England

Thanks again, Ken.

I still need your help.

After selecting a form number, which I was able to successfully link to
Forms#_Title table, I entered 25 in the Number Issued box, which resulted in
-00025 as the Start Number (which is an unbound text box) and 00000 box as
the End_Number box.

Here is what is in the After Update Even Procedure for End_Number:

Private Sub NumberIssued_AfterUpdate()
Dim intNumberIssued As Integer
Dim strCriteria As String
strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """"

intNumberIssued = Nz(DMax("[End_Number]", "[DocumentsIssued]", strCriteria),
0)
Me.[End_Number] = intNumberIssued + Me.[Number_Issued]

I don't have a DocumentsIssued field. Is that what's causing my problem?

"Ken Sheridan" wrote:

I don’t know how to set up a “computed control on the form with a
ControlSource of:
=[End_Number] - [Form_Issued]

I assume that should be [Number_Issued] not [Form_Issued]. All you do is
add an unbound text box to the form from the toolbox, then enter
=[End_Number] - [Form_Issued] as its ControlSource property in its properties
sheet. I'd also suggest you change its Name property to something meaningful
like txtStartNumber.

Also, I would like to add the validation feature you mention below. Can you
outline the steps I would need to follow?

In the Change event procedure of the Number_Issued control put something
like the following code:

If IsNull(Me.[Number_Issued]) Then
MsgBox "Please select a form first.", vbExclamation, "Invalid
Operation"
Me.[Number_Issued].Undo
End If

Another issue just arose, Ken. When I typed 25 in the Number_Issued field, I
got a syntax error message: Private Sub Number_Issued_AfterUpdate(). How
can I fix this?

Sounds like there's a syntax error in the AfterUpdate event procedure's
code. It should be:

Dim intNumberIssued As Integer
Dim strCriteria as String

strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """"

intNumberIssued =
Nz(Dmax("[End_Number]","[DocumentsIssued]",strCriteria),0)

Me.[End_Number] = intNumberIssued + Me.[Number_Issued]

Check that the table and field names in the code are all correct.

Ken Sheridan
Stafford, England

You say that the code is the AfterUpdate event procedure for the *End_Number*
control (though the procedure name suggests otherwise). It should be the
AfterUpdate event procedure for the *Number_Issued* control. If it is the
latter then I don't understand why this is happening. The intNumberIssued
variable should, when the code executes, be assigned a value of zero if no
documents of this type have yet been issued. This should then be added to
the 25 you entered to give a value of 25 as the End number. The Start Number
should be this less 25 to give a value of zero. If the code is in the
correct procedure, therefore, I don't at first sight see why you are getting
this behaviour and some debugging would be advisable, but if it’s the wrong
procedure then that would explain it.

Perhaps the simplest solution would be to delete the procedure completely
(wherever it is) then recreate it from the Number_Issued control's properties
sheet.

Do the records have a field for entering the date/time when the documents
are issued BTW? If so then that would possibly allow a totally different
approach to computing the numbers. It might not be in line with the 'rules'
on which the numbering is predicated, however, so I'd need to be sure of the
basis of these before suggesting this as an alternative approach.

Ken Sheridan
Stafford, England

"Etta" wrote:

Thanks again, Ken.

I still need your help.

After selecting a form number, which I was able to successfully link to
Forms#_Title table, I entered 25 in the Number Issued box, which resulted in
-00025 as the Start Number (which is an unbound text box) and 00000 box as
the End_Number box.

Here is what is in the After Update Even Procedure for End_Number:

Private Sub NumberIssued_AfterUpdate()
Dim intNumberIssued As Integer
Dim strCriteria As String
strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """"

intNumberIssued = Nz(DMax("[End_Number]", "[DocumentsIssued]", strCriteria),
0)
Me.[End_Number] = intNumberIssued + Me.[Number_Issued]

I don't have a DocumentsIssued field. Is that what's causing my problem?

"Ken Sheridan" wrote:

I don’t know how to set up a “computed control on the form with a
ControlSource of:
=[End_Number] - [Form_Issued]

I assume that should be [Number_Issued] not [Form_Issued]. All you do is
add an unbound text box to the form from the toolbox, then enter
=[End_Number] - [Form_Issued] as its ControlSource property in its properties
sheet. I'd also suggest you change its Name property to something meaningful
like txtStartNumber.

Also, I would like to add the validation feature you mention below. Can you
outline the steps I would need to follow?

In the Change event procedure of the Number_Issued control put something
like the following code:

If IsNull(Me.[Number_Issued]) Then
MsgBox "Please select a form first.", vbExclamation, "Invalid
Operation"
Me.[Number_Issued].Undo
End If

Another issue just arose, Ken. When I typed 25 in the Number_Issued field, I
got a syntax error message: Private Sub Number_Issued_AfterUpdate(). How
can I fix this?

Sounds like there's a syntax error in the AfterUpdate event procedure's
code. It should be:

Dim intNumberIssued As Integer
Dim strCriteria as String

strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """"

intNumberIssued =
Nz(Dmax("[End_Number]","[DocumentsIssued]",strCriteria),0)

Me.[End_Number] = intNumberIssued + Me.[Number_Issued]

Check that the table and field names in the code are all correct.

Ken Sheridan
Stafford, England

I deleted the AfterUpdate event procedure in End_Number and added the
following as an AfterUpdate event procedure in Number_Issued:

Option Compare Database

Private Sub End_Number_AfterUpdate()

End Sub


End Sub

Private Sub Number_Issued_AfterUpdate()

Dim intNumber_Issued As Integer
Dim strCriteria As String
strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """"

intNumber_Issued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0)
Me.[End_Number] = intNumber_Issued + Me.[Number_Issued]

End Sub

My results are still 025 for the number issued, -00025 as the start number,
and 00000 as the end number.

I do have a field for date&time when a package of forms is issued. The
"rules" for numbering are simple -- once a number is entered in the
Number_Issued field, Access should compute the Start_Number and the
End_Number. Right now, the Start_Number is being calculated by
"=[End_Number]-[Number_Issued]"

Thanks again, Ken, for you help. As a novice, I would not have been able to
get where I am now in my database without your assistance.

"Ken Sheridan" wrote:

You say that the code is the AfterUpdate event procedure for the *End_Number*
control (though the procedure name suggests otherwise). It should be the
AfterUpdate event procedure for the *Number_Issued* control. If it is the
latter then I don't understand why this is happening. The intNumberIssued
variable should, when the code executes, be assigned a value of zero if no
documents of this type have yet been issued. This should then be added to
the 25 you entered to give a value of 25 as the End number. The Start Number
should be this less 25 to give a value of zero. If the code is in the
correct procedure, therefore, I don't at first sight see why you are getting
this behaviour and some debugging would be advisable, but if it’s the wrong
procedure then that would explain it.

Perhaps the simplest solution would be to delete the procedure completely
(wherever it is) then recreate it from the Number_Issued control's properties
sheet.

Do the records have a field for entering the date/time when the documents
are issued BTW? If so then that would possibly allow a totally different
approach to computing the numbers. It might not be in line with the 'rules'
on which the numbering is predicated, however, so I'd need to be sure of the
basis of these before suggesting this as an alternative approach.

Ken Sheridan
Stafford, England

"Etta" wrote:

Thanks again, Ken.

I still need your help.

After selecting a form number, which I was able to successfully link to
Forms#_Title table, I entered 25 in the Number Issued box, which resulted in
-00025 as the Start Number (which is an unbound text box) and 00000 box as
the End_Number box.

Here is what is in the After Update Even Procedure for End_Number:

Private Sub NumberIssued_AfterUpdate()
Dim intNumberIssued As Integer
Dim strCriteria As String
strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """"

intNumberIssued = Nz(DMax("[End_Number]", "[DocumentsIssued]", strCriteria),
0)
Me.[End_Number] = intNumberIssued + Me.[Number_Issued]

I don't have a DocumentsIssued field. Is that what's causing my problem?

"Ken Sheridan" wrote:

I don’t know how to set up a “computed control on the form with a
ControlSource of:
=[End_Number] - [Form_Issued]

I assume that should be [Number_Issued] not [Form_Issued]. All you do is
add an unbound text box to the form from the toolbox, then enter
=[End_Number] - [Form_Issued] as its ControlSource property in its properties
sheet. I'd also suggest you change its Name property to something meaningful
like txtStartNumber.

Also, I would like to add the validation feature you mention below. Can you
outline the steps I would need to follow?

In the Change event procedure of the Number_Issued control put something
like the following code:

If IsNull(Me.[Number_Issued]) Then
MsgBox "Please select a form first.", vbExclamation, "Invalid
Operation"
Me.[Number_Issued].Undo
End If

Another issue just arose, Ken. When I typed 25 in the Number_Issued field, I
got a syntax error message: Private Sub Number_Issued_AfterUpdate(). How
can I fix this?

Sounds like there's a syntax error in the AfterUpdate event procedure's
code. It should be:

Dim intNumberIssued As Integer
Dim strCriteria as String

strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """"

intNumberIssued =
Nz(Dmax("[End_Number]","[DocumentsIssued]",strCriteria),0)

Me.[End_Number] = intNumberIssued + Me.[Number_Issued]

Check that the table and field names in the code are all correct.

Ken Sheridan
Stafford, England

Actually, the AfterUpdate event procedure attached to Number_Issued is:

Option Compare Database
Private Sub NumberIssued_AfterUpdate()

Dim intNumberIssued As Integer
Dim strCriteria As String

strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """"

intNumberIssued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0)

Me.[End_Number] = intNumberIssued + Me.[Number_Issued]

End Sub

The above code results in a Starting Number of -00025 and Ending Number of
00000 when I enter 25 in the Number Issued field.

I think I've looked at this too many times and am missing some obvious
problem with how I've copied your (Ken's) instructions.


"Etta" wrote:

I deleted the AfterUpdate event procedure in End_Number and added the
following as an AfterUpdate event procedure in Number_Issued:

Option Compare Database

Private Sub End_Number_AfterUpdate()

End Sub


End Sub

Private Sub Number_Issued_AfterUpdate()

Dim intNumber_Issued As Integer
Dim strCriteria As String
strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """"

intNumber_Issued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0)
Me.[End_Number] = intNumber_Issued + Me.[Number_Issued]

End Sub

My results are still 025 for the number issued, -00025 as the start number,
and 00000 as the end number.

I do have a field for date&time when a package of forms is issued. The
"rules" for numbering are simple -- once a number is entered in the
Number_Issued field, Access should compute the Start_Number and the
End_Number. Right now, the Start_Number is being calculated by
"=[End_Number]-[Number_Issued]"

Thanks again, Ken, for you help. As a novice, I would not have been able to
get where I am now in my database without your assistance.

"Ken Sheridan" wrote:

You say that the code is the AfterUpdate event procedure for the *End_Number*
control (though the procedure name suggests otherwise). It should be the
AfterUpdate event procedure for the *Number_Issued* control. If it is the
latter then I don't understand why this is happening. The intNumberIssued
variable should, when the code executes, be assigned a value of zero if no
documents of this type have yet been issued. This should then be added to
the 25 you entered to give a value of 25 as the End number. The Start Number
should be this less 25 to give a value of zero. If the code is in the
correct procedure, therefore, I don't at first sight see why you are getting
this behaviour and some debugging would be advisable, but if it’s the wrong
procedure then that would explain it.

Perhaps the simplest solution would be to delete the procedure completely
(wherever it is) then recreate it from the Number_Issued control's properties
sheet.

Do the records have a field for entering the date/time when the documents
are issued BTW? If so then that would possibly allow a totally different
approach to computing the numbers. It might not be in line with the 'rules'
on which the numbering is predicated, however, so I'd need to be sure of the
basis of these before suggesting this as an alternative approach.

Ken Sheridan
Stafford, England

"Etta" wrote:

Thanks again, Ken.

I still need your help.

After selecting a form number, which I was able to successfully link to
Forms#_Title table, I entered 25 in the Number Issued box, which resulted in
-00025 as the Start Number (which is an unbound text box) and 00000 box as
the End_Number box.

Here is what is in the After Update Even Procedure for End_Number:

Private Sub NumberIssued_AfterUpdate()
Dim intNumberIssued As Integer
Dim strCriteria As String
strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """"

intNumberIssued = Nz(DMax("[End_Number]", "[DocumentsIssued]", strCriteria),
0)
Me.[End_Number] = intNumberIssued + Me.[Number_Issued]

I don't have a DocumentsIssued field. Is that what's causing my problem?

"Ken Sheridan" wrote:

I don’t know how to set up a “computed control on the form with a
ControlSource of:
=[End_Number] - [Form_Issued]

I assume that should be [Number_Issued] not [Form_Issued]. All you do is
add an unbound text box to the form from the toolbox, then enter
=[End_Number] - [Form_Issued] as its ControlSource property in its properties
sheet. I'd also suggest you change its Name property to something meaningful
like txtStartNumber.

Also, I would like to add the validation feature you mention below. Can you
outline the steps I would need to follow?

In the Change event procedure of the Number_Issued control put something
like the following code:

If IsNull(Me.[Number_Issued]) Then
MsgBox "Please select a form first.", vbExclamation, "Invalid
Operation"
Me.[Number_Issued].Undo
End If

Another issue just arose, Ken. When I typed 25 in the Number_Issued field, I
got a syntax error message: Private Sub Number_Issued_AfterUpdate(). How
can I fix this?

Sounds like there's a syntax error in the AfterUpdate event procedure's
code. It should be:

Dim intNumberIssued As Integer
Dim strCriteria as String

strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """"

intNumberIssued =
Nz(Dmax("[End_Number]","[DocumentsIssued]",strCriteria),0)

Me.[End_Number] = intNumberIssued + Me.[Number_Issued]

Check that the table and field names in the code are all correct.

Ken Sheridan
Stafford, England

The only possible problem I can see is that Number_Issued seems to be the
name both of the table and a field in it as you've used it for both the field
name and, as the second argument of the DMax function, the table name. While
unusual, and I'd say inadvisable, I don't think it’s prohibited, however. In
my sample code I originally used DocumentsIssued as the table name. Unless
the use of Number_Issued here as the table name is incorrect (but I'd expect
a run-time error if so) the behaviour you are getting is as though the
AfterUpdate event procedure isn't executing at all. I'd suggest you debug it
by creating a breakpoint in the procedure; you can then see (a) if it is in
fact executing (if it isn't the debugger won't break into the code) and (b)
what value is assigned to the intNumberIssued variable, and hence to the
End_Number.

However, as you do have a date/time field for the issue of the document
there is an alternative approach which would mean you only need the
Number_Issued field in the table, and neither start nor end number fields as
both can be computed. You'd need to be happy with the implications of this,
which is what I was referring to by 'rules', as it would mean that if at any
time you amended the number issued value in any row in the table all later
start and end numbers for issues of that document would be re-computed,
rather than being static. This approach requires no code at all as both
values can be computed by expressions as the ControlSource properties of
unbound controls on a form (or in a report or as computed columns in a query).

Lets first be absolutely sure that we understand what is what, so for the
sake of the expressions below I'm assuming the following:

1. The table name is DocumentsIssued.
2. The date/time field's name is DateTimeIssued.
3. The number issued field's name is Number_Issued
4. The documents reference is a text field Form_Number
5. The unbound control for the start number is txtStartNumber.
6. The unbound control for the end number is txtEndNumber.

For the txtStartNumber control the ControlSource would be as follows. Note
that this should all be entered as a single line:

=Nz(DSum("Number_Issued","DocumentsIssued","Form_N umber =""" & [Form_Number]
& """ And DateTimeIssued #" & Format([DateTimeIssued],"mm/dd/yyyy
hh:nn:ss") & "#"),0)

And for txtEndNunumber:

=[txtStartNumber] + [Number_Issued]

The above does assume of course that all date/time values for the issue of a
particular form number are distinct.

I've also assumed you want the numbering to be zero-based, i.e. the start
number will be 0 for the first record per form number. If you want to start
from 1 you'd add 1 to each of the ControlSource expressions.

Ken Sheridan
Stafford, England

"Etta" wrote:

Actually, the AfterUpdate event procedure attached to Number_Issued is:

Option Compare Database
Private Sub NumberIssued_AfterUpdate()

Dim intNumberIssued As Integer
Dim strCriteria As String

strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """"

intNumberIssued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0)

Me.[End_Number] = intNumberIssued + Me.[Number_Issued]

End Sub

The above code results in a Starting Number of -00025 and Ending Number of
00000 when I enter 25 in the Number Issued field.

I think I've looked at this too many times and am missing some obvious
problem with how I've copied your (Ken's) instructions.


"Etta" wrote:

I deleted the AfterUpdate event procedure in End_Number and added the
following as an AfterUpdate event procedure in Number_Issued:

Option Compare Database

Private Sub End_Number_AfterUpdate()

End Sub


End Sub

Private Sub Number_Issued_AfterUpdate()

Dim intNumber_Issued As Integer
Dim strCriteria As String
strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """"

intNumber_Issued = Nz(DMax("[End_Number]", "[Number_Issued]", strCriteria), 0)
Me.[End_Number] = intNumber_Issued + Me.[Number_Issued]

End Sub

My results are still 025 for the number issued, -00025 as the start number,
and 00000 as the end number.

I do have a field for date&time when a package of forms is issued. The
"rules" for numbering are simple -- once a number is entered in the
Number_Issued field, Access should compute the Start_Number and the
End_Number. Right now, the Start_Number is being calculated by
"=[End_Number]-[Number_Issued]"

Thanks again, Ken, for you help. As a novice, I would not have been able to
get where I am now in my database without your assistance.

"Ken Sheridan" wrote:

You say that the code is the AfterUpdate event procedure for the *End_Number*
control (though the procedure name suggests otherwise). It should be the
AfterUpdate event procedure for the *Number_Issued* control. If it is the
latter then I don't understand why this is happening. The intNumberIssued
variable should, when the code executes, be assigned a value of zero if no
documents of this type have yet been issued. This should then be added to
the 25 you entered to give a value of 25 as the End number. The Start Number
should be this less 25 to give a value of zero. If the code is in the
correct procedure, therefore, I don't at first sight see why you are getting
this behaviour and some debugging would be advisable, but if it’s the wrong
procedure then that would explain it.

Perhaps the simplest solution would be to delete the procedure completely
(wherever it is) then recreate it from the Number_Issued control's properties
sheet.

Do the records have a field for entering the date/time when the documents
are issued BTW? If so then that would possibly allow a totally different
approach to computing the numbers. It might not be in line with the 'rules'
on which the numbering is predicated, however, so I'd need to be sure of the
basis of these before suggesting this as an alternative approach.

Ken Sheridan
Stafford, England

"Etta" wrote:

Thanks again, Ken.

I still need your help.

After selecting a form number, which I was able to successfully link to
Forms#_Title table, I entered 25 in the Number Issued box, which resulted in
-00025 as the Start Number (which is an unbound text box) and 00000 box as
the End_Number box.

Here is what is in the After Update Even Procedure for End_Number:

Private Sub NumberIssued_AfterUpdate()
Dim intNumberIssued As Integer
Dim strCriteria As String
strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """"

intNumberIssued = Nz(DMax("[End_Number]", "[DocumentsIssued]", strCriteria),
0)
Me.[End_Number] = intNumberIssued + Me.[Number_Issued]

I don't have a DocumentsIssued field. Is that what's causing my problem?

"Ken Sheridan" wrote:

I don’t know how to set up a “computed control on the form with a
ControlSource of:
=[End_Number] - [Form_Issued]

I assume that should be [Number_Issued] not [Form_Issued]. All you do is
add an unbound text box to the form from the toolbox, then enter
=[End_Number] - [Form_Issued] as its ControlSource property in its properties
sheet. I'd also suggest you change its Name property to something meaningful
like txtStartNumber.

Also, I would like to add the validation feature you mention below. Can you
outline the steps I would need to follow?

In the Change event procedure of the Number_Issued control put something
like the following code:

If IsNull(Me.[Number_Issued]) Then
MsgBox "Please select a form first.", vbExclamation, "Invalid
Operation"
Me.[Number_Issued].Undo
End If

Another issue just arose, Ken. When I typed 25 in the Number_Issued field, I
got a syntax error message: Private Sub Number_Issued_AfterUpdate(). How
can I fix this?

Sounds like there's a syntax error in the AfterUpdate event procedure's
code. It should be:

Dim intNumberIssued As Integer
Dim strCriteria as String

strCriteria = "[Form_Number] = """ & Me.[Form_Number] & """"

intNumberIssued =
Nz(Dmax("[End_Number]","[DocumentsIssued]",strCriteria),0)

Me.[End_Number] = intNumberIssued + Me.[Number_Issued]

Check that the table and field names in the code are all correct.

Ken Sheridan
Stafford, England