Calculating numbers

[Etta]

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?

 

[Ken Sheridan]

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]

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

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?

 

[Etta]

Ken,

I tried to follow your instructions very carefully but my results were
inaccurate. 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 you 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.

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).

Am I missing a step? When I issue forms for the first time, how will Access
know that and figure out 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 are not
appearing in the Form? They do appear in the Table.

Thanks, again Ken, for your help.

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

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! I will follow your advice.

 

[Etta]

Ken,

I can't see the reply I just submitted ten minutes ago, so please excuse me
if my questions here are redundant.

In a nutshell, I followed your advice, from creating the DocumentsIssued
table (with the fields you list) and then a FormsIssued form. In the
DocumentsIssued table, I set FormsNumber to lookup numbers from another table
(Forms#_Title). While this works in the table, it does not work in the forms.

Also, my numbers are not working. The StartNumber is 00000, the EndNumber 0
(even though I set the fields exactly the same) and the NumberIssued is 000
(as I set it). When issuing the first form, how is Access to know to set the
Start Number at 00001? As a test, I entered 125 in the NumberIssued field
and came up with the following error message: 'Runtime error 2465' Microsoft
can't find the field referred to in your expression.'

Can you also tell me why the Form#s are not appearing in the form?

Thanks so much for your help.

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

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?