Calculating numbers, part 2

[Etta]

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

 

[Ken Sheridan]

Let's take it step by step:

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.

This suggests that the control is not formatted 00000

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

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

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]

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)

Let's take it step by step:

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.

This suggests that the control is not formatted 00000

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

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

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]

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?

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)

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

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

:

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]

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

 

[Etta]

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?

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

 

[Ken Sheridan]

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

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?

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

 

[Etta]

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.

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

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?

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

 

[Etta]

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.

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.

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

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?

:

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

 

[Ken Sheridan]

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_Number =""" & [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

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.

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.

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

:

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?

:

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

 

[Etta]

Thanks, Ken.

GREAT NEWS!!! I created a fresh form and following your advice.

The End Number is now working and the Start Number works for the second
package of forms issued. However, when the first package is issued
(00001-00025) the start number still shows up as 00000. I edited the control
so that the first number equals 1, but when I entered 25 as the quantity
issued, the end number was 00026, which makes sense considering the controls.
However, how can I get the first number issued to show as 00001?

Also, how do I get the date to automatically appear when I tab to that field?

I truly appreciate all your help, Ken.

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_Number =""" & [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

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.

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.

:

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

:

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?

:

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

 

[Etta]

Hi, Ken,

I need to add three fields from two other tables to the DocumentsIssued
form. These fields are Issued To (a drop down menu of all employees, which
is linked to an external table), Issued From (a drop down menu of members my
department) and Reconciled, which can be a checkbox.

I had this working in an older form (Forms Issued), so I copied those fields
and labels to DocumentsIssued. Evidently, that's not how things are done, as
I now get an error message "Control can't be edited; it's bound to unknown
field." How can I add these fields to DocumentsIssued form?

Thanks, Ken.

GREAT NEWS!!! I created a fresh form and following your advice.

The End Number is now working and the Start Number works for the second
package of forms issued. However, when the first package is issued
(00001-00025) the start number still shows up as 00000. I edited the control
so that the first number equals 1, but when I entered 25 as the quantity
issued, the end number was 00026, which makes sense considering the controls.
However, how can I get the first number issued to show as 00001?

Also, how do I get the date to automatically appear when I tab to that field?

I truly appreciate all your help, Ken.

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_Number =""" & [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

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.


:

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.

:

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

:

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?

:

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

 

[Ken Sheridan]

You don't say how you are approaching it, the original way or computing on
the basis of the date/time value. I'm assuming its still the original way.

If the start number is to be 1 for the first package issued the start number
for the second package would logically then be 26 (if 25 forms were issued in
the first record), not 25, otherwise there is an inconsistency in the bases
used between the first issue and subsequent ones. That's easily engineered
by adding 1 to the start number:

=[End_Number]-[Number_Issued] + 1

If, however, you want the first start number to be 1 but (accepting the
logical inconsistency) the second to be 25, and the start numbers thereafter
to be the same as the preceding end number, then you'd have to make it
conditional like so:

=IIf(IsNull(DLookup("[End_Number]", "["YourTableName]", "[Form_Number] =
""" & [Form_Number] & """")), 1, [End_Number]-[Number_Issued] + 1)

To get the date/time to automatically show the simplest thing is to set its
DefaultValue property to the Now() function. That will show it as soon as
you move the form to a new record. To show it only when focus is moved to
the control on the form put the following in its GotFocus event procedure:

If Not IsNull([YourDateTimeControl]) Then
[YourDateTimeControl] = Now()
End If

Ken Sheridan
Stafford, England

Thanks, Ken.

GREAT NEWS!!! I created a fresh form and following your advice.

The End Number is now working and the Start Number works for the second
package of forms issued. However, when the first package is issued
(00001-00025) the start number still shows up as 00000. I edited the control
so that the first number equals 1, but when I entered 25 as the quantity
issued, the end number was 00026, which makes sense considering the controls.
However, how can I get the first number issued to show as 00001?

Also, how do I get the date to automatically appear when I tab to that field?

I truly appreciate all your help, Ken.

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_Number =""" & [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

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.


:

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.

:

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

:

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?

:

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

 

[Ken Sheridan]

For the first two, Issued To and Issued By, you need foreign key columns in
the DocumentsIssued table. These would normally be numbers of long integer
data type referencing the primary keys of the Employees table (which can be
an autonumber). Do not use names as keys; they can be duplicated. The names
of the foreign key columns would be the ControlSource properties of two combo
boxes on the form with a RowSource property such as:

SELECT EmployeeID, FirstName & " " & LastName AS FullName FROM Employees
ORDER BY LastName, FirstName;

And with a BoundColumn property of 1, a ColumnCount property of 2 and a
ColumnWidths property of 0cm;8cm (the first dimension must be zero but the
size of the second isn't crucial so long as its at least as wide as the combo
box).

The Reconciled column would be a Boolean (Yes/No) column in the
DocumentsIssued table and would be the ControlSource property of a check box
on the form.

Ken Sheridan
Stafford, England

Hi, Ken,

I need to add three fields from two other tables to the DocumentsIssued
form. These fields are Issued To (a drop down menu of all employees, which
is linked to an external table), Issued From (a drop down menu of members my
department) and Reconciled, which can be a checkbox.

I had this working in an older form (Forms Issued), so I copied those fields
and labels to DocumentsIssued. Evidently, that's not how things are done, as
I now get an error message "Control can't be edited; it's bound to unknown
field." How can I add these fields to DocumentsIssued form?

Thanks, Ken.

GREAT NEWS!!! I created a fresh form and following your advice.

The End Number is now working and the Start Number works for the second
package of forms issued. However, when the first package is issued
(00001-00025) the start number still shows up as 00000. I edited the control
so that the first number equals 1, but when I entered 25 as the quantity
issued, the end number was 00026, which makes sense considering the controls.
However, how can I get the first number issued to show as 00001?

Also, how do I get the date to automatically appear when I tab to that field?

I truly appreciate all your help, Ken.

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_Number =""" & [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

:

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.


:

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.

:

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

:

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?

:

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

 

[Etta]

Hi, Ken,

I should have mentioned that I went with your suggestion of using the
date/time value with the following as the Control Source:
a=Nz(DSum("Number_Issued","DocumentsIssued","Form_Number =""" &
[Form_Number] & """ And DateTimeIssued < #" &
Format([DateTimeIssued],"mm/dd/yyyy hh:nn:ss") & "#"),0)"

How can this be modified to result in 00001 as the start number for the
first package and 00025 the end number for the first package of 25? The
second package of 25 would start with 00026 and end with 00050.

As ever, thanks for your help.

You don't say how you are approaching it, the original way or computing on
the basis of the date/time value. I'm assuming its still the original way.

If the start number is to be 1 for the first package issued the start number
for the second package would logically then be 26 (if 25 forms were issued in
the first record), not 25, otherwise there is an inconsistency in the bases
used between the first issue and subsequent ones. That's easily engineered
by adding 1 to the start number:

=[End_Number]-[Number_Issued] + 1

If, however, you want the first start number to be 1 but (accepting the
logical inconsistency) the second to be 25, and the start numbers thereafter
to be the same as the preceding end number, then you'd have to make it
conditional like so:

=IIf(IsNull(DLookup("[End_Number]", "["YourTableName]", "[Form_Number] =
""" & [Form_Number] & """")), 1, [End_Number]-[Number_Issued] + 1)

To get the date/time to automatically show the simplest thing is to set its
DefaultValue property to the Now() function. That will show it as soon as
you move the form to a new record. To show it only when focus is moved to
the control on the form put the following in its GotFocus event procedure:

If Not IsNull([YourDateTimeControl]) Then
[YourDateTimeControl] = Now()
End If

Ken Sheridan
Stafford, England

Thanks, Ken.

GREAT NEWS!!! I created a fresh form and following your advice.

The End Number is now working and the Start Number works for the second
package of forms issued. However, when the first package is issued
(00001-00025) the start number still shows up as 00000. I edited the control
so that the first number equals 1, but when I entered 25 as the quantity
issued, the end number was 00026, which makes sense considering the controls.
However, how can I get the first number issued to show as 00001?

Also, how do I get the date to automatically appear when I tab to that field?

I truly appreciate all your help, Ken.

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_Number =""" & [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

:

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.


:

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.

:

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

:

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?

:

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

 

[Etta]

Hi, Ken,

I added the two foreign key columns in DocumentsIssued Table and titled them
Issued To and Issued By, with number as data type for each. Is that correct?

The primary key for Issued To is Emp#, which is a unique number in the
Employee Table, and the primary key for Dept_Staff is an autonumber.

On the DocumentsIssued Form, I added the combo box for Issued To,with the
row source set as SELECT Employee.EmpNum, Employee.EmpLastName,
Employee.EmpFirstName FROM Employee ORDER BY [EmpLastName];
This results in the list of employees appearing when I tab to that field,
but when I attempt to select a name, I get the same old error message:
Control can't be edited; it is bound to unknown field "Issued To."

The same thing happens with the Issued By field, which has the row source of
SELECT QA_Staff.[QA_ID], QA_Staff.Name FROM QA_Staff ORDER BY [Name]; . That
is the error message "Control can't be edited; it is bound to unknown field
"QA_Staff," appears.

For the first two, Issued To and Issued By, you need foreign key columns in
the DocumentsIssued table. These would normally be numbers of long integer
data type referencing the primary keys of the Employees table (which can be
an autonumber). Do not use names as keys; they can be duplicated. The names
of the foreign key columns would be the ControlSource properties of two combo
boxes on the form with a RowSource property such as:

SELECT EmployeeID, FirstName & " " & LastName AS FullName FROM Employees
ORDER BY LastName, FirstName;

And with a BoundColumn property of 1, a ColumnCount property of 2 and a
ColumnWidths property of 0cm;8cm (the first dimension must be zero but the
size of the second isn't crucial so long as its at least as wide as the combo
box).

The Reconciled column would be a Boolean (Yes/No) column in the
DocumentsIssued table and would be the ControlSource property of a check box
on the form.

Ken Sheridan
Stafford, England

Hi, Ken,

I need to add three fields from two other tables to the DocumentsIssued
form. These fields are Issued To (a drop down menu of all employees, which
is linked to an external table), Issued From (a drop down menu of members my
department) and Reconciled, which can be a checkbox.

I had this working in an older form (Forms Issued), so I copied those fields
and labels to DocumentsIssued. Evidently, that's not how things are done, as
I now get an error message "Control can't be edited; it's bound to unknown
field." How can I add these fields to DocumentsIssued form?

Thanks, Ken.

GREAT NEWS!!! I created a fresh form and following your advice.

The End Number is now working and the Start Number works for the second
package of forms issued. However, when the first package is issued
(00001-00025) the start number still shows up as 00000. I edited the control
so that the first number equals 1, but when I entered 25 as the quantity
issued, the end number was 00026, which makes sense considering the controls.
However, how can I get the first number issued to show as 00001?

Also, how do I get the date to automatically appear when I tab to that field?

I truly appreciate all your help, Ken.

:

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_Number =""" & [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

:

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.


:

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.

:

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

:

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?

:

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

 

[Ken Sheridan]

The ControlSource properties of the two combo boxes should be the names of
the relevant foreign key columns, i.e. [Issued To] and [Issued By]. It
sounds like you have the first correct, so why you are getting an error I
don't know. It sounds like the second is using a table name, QA_Staff, as
its ControlSource, however?? Try deleting the controls from the form and
adding them again by dragging from the field list on the toolbar in form
design view. That will add text boxes to the form, so then right click it
and select Change To | Combo Box from the shortcut menu and give it the
properties as I described: BoundColumn 1, ColumnCount 2, ColumnWidths
0cm;8cm. For the RowSource you might prefer to concatenate the names into a
single column, e.g.

SELECT EmpNum, EmpFirstName & " " EmpLastName AS FullName FROM Employee
ORDER BY EmpLastName, EmpFirstName;

If not you'd need to change the ColumnCount and ColumnWidths properties to
accommodate the three columns.

I assume EmpNum is the correct column name; you refer to it as Emp# elsewhere.

You seem to be having some trouble understanding the underlying principles
involved here. You might like to spend some time examining how the sample
Northwind database which comes with Access works. Have a look at how the
underlying reality is modelled by the tables, each representing one entity
type, and how these are related by means of their primary and foreign key
columns. The Relationships window is the best place to see this expressed
visually. Also look at how forms are used to provide an interface with the
data, particularly the orders form and its order details subform. Examine
the RecordSource for the forms/subform and how the form/subform's controls
are bound to the columns in the underlying tables.

It might also be worth your while getting hold of a good general purpose
book on Access such as John L Viescas's 'Running Microsoft Access'. This
includes sample files on CD for you to work with.

Ken Sheridan
Stafford, England

Hi, Ken,

I added the two foreign key columns in DocumentsIssued Table and titled them
Issued To and Issued By, with number as data type for each. Is that correct?

The primary key for Issued To is Emp#, which is a unique number in the
Employee Table, and the primary key for Dept_Staff is an autonumber.

On the DocumentsIssued Form, I added the combo box for Issued To,with the
row source set as SELECT Employee.EmpNum, Employee.EmpLastName,
Employee.EmpFirstName FROM Employee ORDER BY [EmpLastName];
This results in the list of employees appearing when I tab to that field,
but when I attempt to select a name, I get the same old error message:
Control can't be edited; it is bound to unknown field "Issued To."

The same thing happens with the Issued By field, which has the row source of
SELECT QA_Staff.[QA_ID], QA_Staff.Name FROM QA_Staff ORDER BY [Name]; . That
is the error message "Control can't be edited; it is bound to unknown field
"QA_Staff," appears.

For the first two, Issued To and Issued By, you need foreign key columns in
the DocumentsIssued table. These would normally be numbers of long integer
data type referencing the primary keys of the Employees table (which can be
an autonumber). Do not use names as keys; they can be duplicated. The names
of the foreign key columns would be the ControlSource properties of two combo
boxes on the form with a RowSource property such as:

SELECT EmployeeID, FirstName & " " & LastName AS FullName FROM Employees
ORDER BY LastName, FirstName;

And with a BoundColumn property of 1, a ColumnCount property of 2 and a
ColumnWidths property of 0cm;8cm (the first dimension must be zero but the
size of the second isn't crucial so long as its at least as wide as the combo
box).

The Reconciled column would be a Boolean (Yes/No) column in the
DocumentsIssued table and would be the ControlSource property of a check box
on the form.

Ken Sheridan
Stafford, England

Hi, Ken,

I need to add three fields from two other tables to the DocumentsIssued
form. These fields are Issued To (a drop down menu of all employees, which
is linked to an external table), Issued From (a drop down menu of members my
department) and Reconciled, which can be a checkbox.

I had this working in an older form (Forms Issued), so I copied those fields
and labels to DocumentsIssued. Evidently, that's not how things are done, as
I now get an error message "Control can't be edited; it's bound to unknown
field." How can I add these fields to DocumentsIssued form?

:

Thanks, Ken.

GREAT NEWS!!! I created a fresh form and following your advice.

The End Number is now working and the Start Number works for the second
package of forms issued. However, when the first package is issued
(00001-00025) the start number still shows up as 00000. I edited the control
so that the first number equals 1, but when I entered 25 as the quantity
issued, the end number was 00026, which makes sense considering the controls.
However, how can I get the first number issued to show as 00001?

Also, how do I get the date to automatically appear when I tab to that field?

I truly appreciate all your help, Ken.

:

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_Number =""" & [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

:

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.


:

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.

:

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

:

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?

:

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

 

[Etta]

Ken, Someone responded to this post, but I am unable to find the response. I
have started a new thread, Caclulating numbers, part 3.

Hi, Ken,

I should have mentioned that I went with your suggestion of using the
date/time value with the following as the Control Source:
a=Nz(DSum("Number_Issued","DocumentsIssued","Form_Number =""" &
[Form_Number] & """ And DateTimeIssued < #" &
Format([DateTimeIssued],"mm/dd/yyyy hh:nn:ss") & "#"),0)"

How can this be modified to result in 00001 as the start number for the
first package and 00025 the end number for the first package of 25? The
second package of 25 would start with 00026 and end with 00050.

As ever, thanks for your help.

You don't say how you are approaching it, the original way or computing on
the basis of the date/time value. I'm assuming its still the original way.

If the start number is to be 1 for the first package issued the start number
for the second package would logically then be 26 (if 25 forms were issued in
the first record), not 25, otherwise there is an inconsistency in the bases
used between the first issue and subsequent ones. That's easily engineered
by adding 1 to the start number:

=[End_Number]-[Number_Issued] + 1

If, however, you want the first start number to be 1 but (accepting the
logical inconsistency) the second to be 25, and the start numbers thereafter
to be the same as the preceding end number, then you'd have to make it
conditional like so:

=IIf(IsNull(DLookup("[End_Number]", "["YourTableName]", "[Form_Number] =
""" & [Form_Number] & """")), 1, [End_Number]-[Number_Issued] + 1)

To get the date/time to automatically show the simplest thing is to set its
DefaultValue property to the Now() function. That will show it as soon as
you move the form to a new record. To show it only when focus is moved to
the control on the form put the following in its GotFocus event procedure:

If Not IsNull([YourDateTimeControl]) Then
[YourDateTimeControl] = Now()
End If

Ken Sheridan
Stafford, England

Thanks, Ken.

GREAT NEWS!!! I created a fresh form and following your advice.

The End Number is now working and the Start Number works for the second
package of forms issued. However, when the first package is issued
(00001-00025) the start number still shows up as 00000. I edited the control
so that the first number equals 1, but when I entered 25 as the quantity
issued, the end number was 00026, which makes sense considering the controls.
However, how can I get the first number issued to show as 00001?

Also, how do I get the date to automatically appear when I tab to that field?

I truly appreciate all your help, Ken.

:

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_Number =""" & [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

:

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.


:

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.

:

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

:

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?

:

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

 

[Etta]

Ken,

I finally got the DocumentsIssued form to work, except for the Start number
(00000 instead of 00001).

Thank you for your advice and patience.