Calculating numbers, part 2

**Etta** · June 23rd, 2007, 04:53 PM posted to microsoft.public.access.gettingstarted

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.

"Ken Sheridan" wrote:

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

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

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

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

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

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

And for txtEndNunumber:

=[txtStartNumber] + [Number_Issued]

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

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

Ken Sheridan
Stafford, England

"Etta" wrote:

Actually, the AfterUpdate event procedure attached to Number_Issued is:

Option Compare Database
Private Sub NumberIssued_AfterUpdate()

Dim intNumberIssued As Integer
Dim strCriteria As String

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

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

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

End Sub

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

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

"Etta" wrote:

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

Option Compare Database

Private Sub End_Number_AfterUpdate()

End Sub

End Sub

Private Sub Number_Issued_AfterUpdate()

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

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

End Sub

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

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

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

"Ken Sheridan" wrote:

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

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

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

Ken Sheridan
Stafford, England

"Etta" wrote:

Thanks again, Ken.

I still need your help.

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

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

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

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

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

"Ken Sheridan" wrote:

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

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

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

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

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

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

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

Dim intNumberIssued As Integer
Dim strCriteria as String

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

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

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

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

Ken Sheridan
Stafford, England

**Etta** · June 23rd, 2007, 05:48 PM posted to microsoft.public.access.gettingstarted

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?

"Etta" wrote:

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.

"Ken Sheridan" wrote:

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

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

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

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

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

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

And for txtEndNunumber:

=[txtStartNumber] + [Number_Issued]

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

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

Ken Sheridan
Stafford, England

"Etta" wrote:

Actually, the AfterUpdate event procedure attached to Number_Issued is:

Option Compare Database
Private Sub NumberIssued_AfterUpdate()

Dim intNumberIssued As Integer
Dim strCriteria As String

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

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

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

End Sub

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

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

"Etta" wrote:

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

Option Compare Database

Private Sub End_Number_AfterUpdate()

End Sub

End Sub

Private Sub Number_Issued_AfterUpdate()

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

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

End Sub

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

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

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

"Ken Sheridan" wrote:

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

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

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

Ken Sheridan
Stafford, England

"Etta" wrote:

Thanks again, Ken.

I still need your help.

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

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

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

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

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

"Ken Sheridan" wrote:

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

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

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

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

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

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

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

Dim intNumberIssued As Integer
Dim strCriteria as String

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

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

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

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

Ken Sheridan
Stafford, England

**Ken Sheridan** · June 23rd, 2007, 06:24 PM posted to microsoft.public.access.gettingstarted

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 procedu

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

Ken Sheridan
Stafford, England

"Etta" wrote:

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.

"Ken Sheridan" wrote:

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

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

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

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

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

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

And for txtEndNunumber:

=[txtStartNumber] + [Number_Issued]

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

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

Ken Sheridan
Stafford, England

"Etta" wrote:

Actually, the AfterUpdate event procedure attached to Number_Issued is:

Option Compare Database
Private Sub NumberIssued_AfterUpdate()

Dim intNumberIssued As Integer
Dim strCriteria As String

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

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

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

End Sub

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

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

"Etta" wrote:

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

Option Compare Database

Private Sub End_Number_AfterUpdate()

End Sub

End Sub

Private Sub Number_Issued_AfterUpdate()

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

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

End Sub

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

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

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

"Ken Sheridan" wrote:

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

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

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

Ken Sheridan
Stafford, England

"Etta" wrote:

Thanks again, Ken.

I still need your help.

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

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

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

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

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

"Ken Sheridan" wrote:

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

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

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

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

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

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

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

Dim intNumberIssued As Integer
Dim strCriteria as String

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

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

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

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

Ken Sheridan
Stafford, England

**Ken Sheridan** · June 24th, 2007, 05:13 PM posted to microsoft.public.access.gettingstarted

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

"Etta" wrote:

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?

"Etta" wrote:

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.

"Ken Sheridan" wrote:

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

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

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

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

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

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

And for txtEndNunumber:

=[txtStartNumber] + [Number_Issued]

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

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

Ken Sheridan
Stafford, England

"Etta" wrote:

Actually, the AfterUpdate event procedure attached to Number_Issued is:

Option Compare Database
Private Sub NumberIssued_AfterUpdate()

Dim intNumberIssued As Integer
Dim strCriteria As String

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

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

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

End Sub

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

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

"Etta" wrote:

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

Option Compare Database

Private Sub End_Number_AfterUpdate()

End Sub

End Sub

Private Sub Number_Issued_AfterUpdate()

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

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

End Sub

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

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

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

"Ken Sheridan" wrote:

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

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

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

Ken Sheridan
Stafford, England

"Etta" wrote:

Thanks again, Ken.

I still need your help.

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

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

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

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

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

"Ken Sheridan" wrote:

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

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

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

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

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

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

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

Dim intNumberIssued As Integer
Dim strCriteria as String

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

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

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

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

Ken Sheridan
Stafford, England

**Etta** · June 27th, 2007, 12:32 AM posted to microsoft.public.access.gettingstarted

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.

"Ken Sheridan" wrote:

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 procedu

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

Ken Sheridan
Stafford, England

"Etta" wrote:

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.

"Ken Sheridan" wrote:

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

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

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

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

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

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

And for txtEndNunumber:

=[txtStartNumber] + [Number_Issued]

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

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

Ken Sheridan
Stafford, England

"Etta" wrote:

Actually, the AfterUpdate event procedure attached to Number_Issued is:

Option Compare Database
Private Sub NumberIssued_AfterUpdate()

Dim intNumberIssued As Integer
Dim strCriteria As String

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

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

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

End Sub

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

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

"Etta" wrote:

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

Option Compare Database

Private Sub End_Number_AfterUpdate()

End Sub

End Sub

Private Sub Number_Issued_AfterUpdate()

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

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

End Sub

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

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

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

"Ken Sheridan" wrote:

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

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

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

Ken Sheridan
Stafford, England

"Etta" wrote:

Thanks again, Ken.

I still need your help.

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

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

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

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

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

"Ken Sheridan" wrote:

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

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

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

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

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

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

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

Dim intNumberIssued As Integer
Dim strCriteria as String

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

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

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

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

Ken Sheridan
Stafford, England

**Etta** · June 27th, 2007, 01:32 AM posted to microsoft.public.access.gettingstarted

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.

On the bright side, I did manage to get the Reconciled column to work just
fine!

"Ken Sheridan" wrote:

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

"Etta" wrote:

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?

"Etta" wrote:

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.

"Ken Sheridan" wrote:

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

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

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

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

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

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

And for txtEndNunumber:

=[txtStartNumber] + [Number_Issued]

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

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

Ken Sheridan
Stafford, England

"Etta" wrote:

Actually, the AfterUpdate event procedure attached to Number_Issued is:

Option Compare Database
Private Sub NumberIssued_AfterUpdate()

Dim intNumberIssued As Integer
Dim strCriteria As String

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

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

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

End Sub

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

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

"Etta" wrote:

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

Option Compare Database

Private Sub End_Number_AfterUpdate()

End Sub

End Sub

Private Sub Number_Issued_AfterUpdate()

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

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

End Sub

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

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

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

"Ken Sheridan" wrote:

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

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

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

Ken Sheridan
Stafford, England

"Etta" wrote:

Thanks again, Ken.

I still need your help.

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

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

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

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

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

"Ken Sheridan" wrote:

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

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

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

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

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

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

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

Dim intNumberIssued As Integer
Dim strCriteria as String

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

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

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

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

Ken Sheridan
Stafford, England

**Ken Sheridan** · June 27th, 2007, 05:14 PM posted to microsoft.public.access.gettingstarted

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

"Etta" wrote:

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.

On the bright side, I did manage to get the Reconciled column to work just
fine!

"Ken Sheridan" wrote:

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

"Etta" wrote:

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?

"Etta" wrote:

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.

"Ken Sheridan" wrote:

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

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

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

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

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

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

And for txtEndNunumber:

=[txtStartNumber] + [Number_Issued]

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

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

Ken Sheridan
Stafford, England

"Etta" wrote:

Actually, the AfterUpdate event procedure attached to Number_Issued is:

Option Compare Database
Private Sub NumberIssued_AfterUpdate()

Dim intNumberIssued As Integer
Dim strCriteria As String

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

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

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

End Sub

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

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

"Etta" wrote:

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

Option Compare Database

Private Sub End_Number_AfterUpdate()

End Sub

End Sub

Private Sub Number_Issued_AfterUpdate()

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

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

End Sub

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

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

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

"Ken Sheridan" wrote:

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

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

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

Ken Sheridan
Stafford, England

"Etta" wrote:

Thanks again, Ken.

I still need your help.

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

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

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

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

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

"Ken Sheridan" wrote:

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

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

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

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

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

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

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

Dim intNumberIssued As Integer
Dim strCriteria as String

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

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

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

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

Ken Sheridan
Stafford, England

**Etta** · June 30th, 2007, 12:08 AM posted to microsoft.public.access.gettingstarted

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

"Etta" wrote:

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.

"Ken Sheridan" wrote:

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 procedu

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

Ken Sheridan
Stafford, England

"Etta" wrote:

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.

"Ken Sheridan" wrote:

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

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

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

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

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

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

And for txtEndNunumber:

=[txtStartNumber] + [Number_Issued]

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

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

Ken Sheridan
Stafford, England

"Etta" wrote:

Actually, the AfterUpdate event procedure attached to Number_Issued is:

Option Compare Database
Private Sub NumberIssued_AfterUpdate()

Dim intNumberIssued As Integer
Dim strCriteria As String

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

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

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

End Sub

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

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

"Etta" wrote:

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

Option Compare Database

Private Sub End_Number_AfterUpdate()

End Sub

End Sub

Private Sub Number_Issued_AfterUpdate()

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

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

End Sub

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

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

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

"Ken Sheridan" wrote:

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

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

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

Ken Sheridan
Stafford, England

"Etta" wrote:

Thanks again, Ken.

I still need your help.

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

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

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

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

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

"Ken Sheridan" wrote:

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

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

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

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

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

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

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

Dim intNumberIssued As Integer
Dim strCriteria as String

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

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

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

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

Ken Sheridan
Stafford, England

**Etta** · June 30th, 2007, 01:54 AM posted to microsoft.public.access.gettingstarted

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.

"Ken Sheridan" wrote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode