Restricting number of records in a table

[Guest]

I have two tables and two forms

1 Batches
2 Items

Items are related to a batch number (number is autonumbering for batch
number and sample number). There needs to be a maximum of 17 items to a batch.
I wish to have a check box / command button which when clicked will create a
field within the table BATCHES with the number of samples relating to the
particular batch number

ie if there are 10 items in a batch then I need this counting and inputing
the number into the appropriate field in the batch.
I thought this may be achieved with an update query which would select the
data based upon the batch number selected count the number of records and put
this information into the field in batches.
The point(s) I am unsure of are
1 Is this the best way of achieving this and if it is
2 How to perform the count part of the query

Many thanks

Richard

 

[Guest]

Richard:

Don't create a column in the Batches table for the number of items per
batch. The number can be computed on the fly, so its unnecessary to store
it, but more importantly, to do so runs the risk of introducing
inconsistencies into the data.

To show the number for the current batch on the batches form add an unbound
text box with a ControlSource along these lines:

=DCount("*", "Items", "BatchID = " & [BatchID])

This assumes that BatchID is the name of the primary key of Batches and of
the corresponding foreign key column in Items, and that the columns are of a
number data type, not text. If the keys are text data type wrap the value in
quotes like so:

=DCount("*", "Items", "BatchID = """ & [BatchID] & """")

You can also use the DCount function in the same way in a report or in a
query, though in the latter you could also use a subquery, e.g.

SELECT *,
(SELECT COUNT(*)
FROM Items
WHERE Items.BatchID = Batches.BatchID) AS ItemCount
FROM Batches;

You can prevent more than 17 items per batch being created with some
validation code in the Items form. You don't say how Items records are being
created, but if the batch is being selected manually, most likely via a combo
box bound to the BatchID column, then you can put the code in that control's
BeforeUpdate event procedure, e.g.

Const conMESSAGE = "17 items have already been entered for this batch."

If DCount("*", "Items", "BatchID = " & Me.BatchID) = 17 Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

If the BatchID value is being entered automatically when a new record is
created, e.g. by being passed from the Batches form via code which opens the
Items form, then you could either do the validation in the Batches form, so
it only opens the Items form if less than 17 items records already exist for
the batch, e.g.

Const conMESSAGE = "17 items have already been entered for this batch."

If DCount("*", "Items", "BatchID = " & Me.BatchID) = 17 Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Else
DoCmd.OpenForm "Items", DataMode:=acFormAdd
Forms("Items").BatchID = Me.BatchID
End If

or you could use the same code as suggested for the BatchID control on the
Items form above in the form's BeforeUpdate event procedure. Doing the
validation before data is entered in the items form is obviously a better way
to go, however.

Note that validating the number of items per batch at form level does not
prevent more than 17 rows per batch being entered directly into the table, so
you should ensure that users can only enter data via the form.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks

The formula is giving me in the unbound text box the message "#error". I
have tried both versions of the formula.
Both fields in the tables are Long Integers, with format as General Number
Both have the same name of Batch Number in both tables and in the BATCHES
table the Batch Number is the primary key, it is also Autonumbering.
Any Thoughts

Richard

Richard:

Don't create a column in the Batches table for the number of items per
batch. The number can be computed on the fly, so its unnecessary to store
it, but more importantly, to do so runs the risk of introducing
inconsistencies into the data.

To show the number for the current batch on the batches form add an unbound
text box with a ControlSource along these lines:

=DCount("*", "Items", "BatchID = " & [BatchID])

This assumes that BatchID is the name of the primary key of Batches and of
the corresponding foreign key column in Items, and that the columns are of a
number data type, not text. If the keys are text data type wrap the value in
quotes like so:

=DCount("*", "Items", "BatchID = """ & [BatchID] & """")

You can also use the DCount function in the same way in a report or in a
query, though in the latter you could also use a subquery, e.g.

SELECT *,
(SELECT COUNT(*)
FROM Items
WHERE Items.BatchID = Batches.BatchID) AS ItemCount
FROM Batches;

You can prevent more than 17 items per batch being created with some
validation code in the Items form. You don't say how Items records are being
created, but if the batch is being selected manually, most likely via a combo
box bound to the BatchID column, then you can put the code in that control's
BeforeUpdate event procedure, e.g.

Const conMESSAGE = "17 items have already been entered for this batch."

If DCount("*", "Items", "BatchID = " & Me.BatchID) = 17 Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

If the BatchID value is being entered automatically when a new record is
created, e.g. by being passed from the Batches form via code which opens the
Items form, then you could either do the validation in the Batches form, so
it only opens the Items form if less than 17 items records already exist for
the batch, e.g.

Const conMESSAGE = "17 items have already been entered for this batch."

If DCount("*", "Items", "BatchID = " & Me.BatchID) = 17 Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Else
DoCmd.OpenForm "Items", DataMode:=acFormAdd
Forms("Items").BatchID = Me.BatchID
End If

or you could use the same code as suggested for the BatchID control on the
Items form above in the form's BeforeUpdate event procedure. Doing the
validation before data is entered in the items form is obviously a better way
to go, however.

Note that validating the number of items per batch at form level does not
prevent more than 17 rows per batch being entered directly into the table, so
you should ensure that users can only enter data via the form.

Ken Sheridan
Stafford, England

I have two tables and two forms

1 Batches
2 Items

Items are related to a batch number (number is autonumbering for batch
number and sample number). There needs to be a maximum of 17 items to a batch.
I wish to have a check box / command button which when clicked will create a
field within the table BATCHES with the number of samples relating to the
particular batch number

ie if there are 10 items in a batch then I need this counting and inputing
the number into the appropriate field in the batch.
I thought this may be achieved with an update query which would select the
data based upon the batch number selected count the number of records and put
this information into the field in batches.
The point(s) I am unsure of are
1 Is this the best way of achieving this and if it is
2 How to perform the count part of the query

Many thanks

Richard

 

[Guest]

Also having trouble with code, it will only allow me to enter 17 items period
into the table, regardless of batches. I have checked the spelling of the
text and have included the code below. Items is actually called Sample
Details and the BatchID is Batch Number(with a space between the words)
Private Sub Batch_Number_BeforeUpdate(Cancel As Integer)

Const conMESSAGE = "17 items have already been entered for this batch."

If DCount("*", "Sample Details", "Batch_Number = " & Me.Batch_Number) = 17
Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

End Sub


More Help please (I am guessing it has got to be something in the field
setup or format???????)

Thanks

Richard

Thanks

The formula is giving me in the unbound text box the message "#error". I
have tried both versions of the formula.
Both fields in the tables are Long Integers, with format as General Number
Both have the same name of Batch Number in both tables and in the BATCHES
table the Batch Number is the primary key, it is also Autonumbering.
Any Thoughts

Richard

Richard:

Don't create a column in the Batches table for the number of items per
batch. The number can be computed on the fly, so its unnecessary to store
it, but more importantly, to do so runs the risk of introducing
inconsistencies into the data.

To show the number for the current batch on the batches form add an unbound
text box with a ControlSource along these lines:

=DCount("*", "Items", "BatchID = " & [BatchID])

This assumes that BatchID is the name of the primary key of Batches and of
the corresponding foreign key column in Items, and that the columns are of a
number data type, not text. If the keys are text data type wrap the value in
quotes like so:

=DCount("*", "Items", "BatchID = """ & [BatchID] & """")

You can also use the DCount function in the same way in a report or in a
query, though in the latter you could also use a subquery, e.g.

SELECT *,
(SELECT COUNT(*)
FROM Items
WHERE Items.BatchID = Batches.BatchID) AS ItemCount
FROM Batches;

You can prevent more than 17 items per batch being created with some
validation code in the Items form. You don't say how Items records are being
created, but if the batch is being selected manually, most likely via a combo
box bound to the BatchID column, then you can put the code in that control's
BeforeUpdate event procedure, e.g.

Const conMESSAGE = "17 items have already been entered for this batch."

If DCount("*", "Items", "BatchID = " & Me.BatchID) = 17 Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

If the BatchID value is being entered automatically when a new record is
created, e.g. by being passed from the Batches form via code which opens the
Items form, then you could either do the validation in the Batches form, so
it only opens the Items form if less than 17 items records already exist for
the batch, e.g.

Const conMESSAGE = "17 items have already been entered for this batch."

If DCount("*", "Items", "BatchID = " & Me.BatchID) = 17 Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Else
DoCmd.OpenForm "Items", DataMode:=acFormAdd
Forms("Items").BatchID = Me.BatchID
End If

or you could use the same code as suggested for the BatchID control on the
Items form above in the form's BeforeUpdate event procedure. Doing the
validation before data is entered in the items form is obviously a better way
to go, however.

Note that validating the number of items per batch at form level does not
prevent more than 17 rows per batch being entered directly into the table, so
you should ensure that users can only enter data via the form.

Ken Sheridan
Stafford, England

I have two tables and two forms

1 Batches
2 Items

Items are related to a batch number (number is autonumbering for batch
number and sample number). There needs to be a maximum of 17 items to a batch.
I wish to have a check box / command button which when clicked will create a
field within the table BATCHES with the number of samples relating to the
particular batch number

ie if there are 10 items in a batch then I need this counting and inputing
the number into the appropriate field in the batch.
I thought this may be achieved with an update query which would select the
data based upon the batch number selected count the number of records and put
this information into the field in batches.
The point(s) I am unsure of are
1 Is this the best way of achieving this and if it is
2 How to perform the count part of the query

Many thanks

Richard

 

[Guest]

Richard:

Check that the names of the table and fields in the expression (not formula,
that's a spreadsheet concept) used as the unbound text box's ControlSource
property are correct and that any object names with spaces or other special
characters are wrapped in brackets [like this]. If in doubt use the
brackets. It might also be prudent to cater for Null e.g.

=DCount("*", "[Sample Details]", "[Batch Number] = " & Nz([BatchNumber],0))

As regards the code the same applies. Also don't be misled by the fact that
the procedure name includes an underscore character into thinking that the
object name in the code also should have one. Procedure names cannot include
a space, so an underscore is automatically substituted if the relevant
control name includes one. Again, catering for Null might be prudent in case
a user deletes the value from the control:

Private Sub Batch_Number_BeforeUpdate(Cancel As Integer)

Const conMESSAGE = "17 items have already been entered for this batch."

If Not IsNull(Me.[Batch Number]) Then
If DCount("*", "[Sample Details]", "[Batch Number] = " & Me.[Batch
Number]) = 17 Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If
End If

End Sub

Ken Sheridan
Stafford, England

Also having trouble with code, it will only allow me to enter 17 items period
into the table, regardless of batches. I have checked the spelling of the
text and have included the code below. Items is actually called Sample
Details and the BatchID is Batch Number(with a space between the words)
Private Sub Batch_Number_BeforeUpdate(Cancel As Integer)

Const conMESSAGE = "17 items have already been entered for this batch."

If DCount("*", "Sample Details", "Batch_Number = " & Me.Batch_Number) = 17
Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

End Sub


More Help please (I am guessing it has got to be something in the field
setup or format???????)

Thanks

Richard

Thanks

The formula is giving me in the unbound text box the message "#error". I
have tried both versions of the formula.
Both fields in the tables are Long Integers, with format as General Number
Both have the same name of Batch Number in both tables and in the BATCHES
table the Batch Number is the primary key, it is also Autonumbering.
Any Thoughts

Richard

Richard:

Don't create a column in the Batches table for the number of items per
batch. The number can be computed on the fly, so its unnecessary to store
it, but more importantly, to do so runs the risk of introducing
inconsistencies into the data.

To show the number for the current batch on the batches form add an unbound
text box with a ControlSource along these lines:

=DCount("*", "Items", "BatchID = " & [BatchID])

This assumes that BatchID is the name of the primary key of Batches and of
the corresponding foreign key column in Items, and that the columns are of a
number data type, not text. If the keys are text data type wrap the value in
quotes like so:

=DCount("*", "Items", "BatchID = """ & [BatchID] & """")

You can also use the DCount function in the same way in a report or in a
query, though in the latter you could also use a subquery, e.g.

SELECT *,
(SELECT COUNT(*)
FROM Items
WHERE Items.BatchID = Batches.BatchID) AS ItemCount
FROM Batches;

You can prevent more than 17 items per batch being created with some
validation code in the Items form. You don't say how Items records are being
created, but if the batch is being selected manually, most likely via a combo
box bound to the BatchID column, then you can put the code in that control's
BeforeUpdate event procedure, e.g.

Const conMESSAGE = "17 items have already been entered for this batch."

If DCount("*", "Items", "BatchID = " & Me.BatchID) = 17 Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

If the BatchID value is being entered automatically when a new record is
created, e.g. by being passed from the Batches form via code which opens the
Items form, then you could either do the validation in the Batches form, so
it only opens the Items form if less than 17 items records already exist for
the batch, e.g.

Const conMESSAGE = "17 items have already been entered for this batch."

If DCount("*", "Items", "BatchID = " & Me.BatchID) = 17 Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Else
DoCmd.OpenForm "Items", DataMode:=acFormAdd
Forms("Items").BatchID = Me.BatchID
End If

or you could use the same code as suggested for the BatchID control on the
Items form above in the form's BeforeUpdate event procedure. Doing the
validation before data is entered in the items form is obviously a better way
to go, however.

Note that validating the number of items per batch at form level does not
prevent more than 17 rows per batch being entered directly into the table, so
you should ensure that users can only enter data via the form.

Ken Sheridan
Stafford, England

:

I have two tables and two forms

1 Batches
2 Items

Items are related to a batch number (number is autonumbering for batch
number and sample number). There needs to be a maximum of 17 items to a batch.
I wish to have a check box / command button which when clicked will create a
field within the table BATCHES with the number of samples relating to the
particular batch number

ie if there are 10 items in a batch then I need this counting and inputing
the number into the appropriate field in the batch.
I thought this may be achieved with an update query which would select the
data based upon the batch number selected count the number of records and put
this information into the field in batches.
The point(s) I am unsure of are
1 Is this the best way of achieving this and if it is
2 How to perform the count part of the query

Many thanks

Richard

 

[Guest]

Wonderful, it was all about the brackets

Thanks

Richard

Richard:

Check that the names of the table and fields in the expression (not formula,
that's a spreadsheet concept) used as the unbound text box's ControlSource
property are correct and that any object names with spaces or other special
characters are wrapped in brackets [like this]. If in doubt use the
brackets. It might also be prudent to cater for Null e.g.

=DCount("*", "[Sample Details]", "[Batch Number] = " & Nz([BatchNumber],0))

As regards the code the same applies. Also don't be misled by the fact that
the procedure name includes an underscore character into thinking that the
object name in the code also should have one. Procedure names cannot include
a space, so an underscore is automatically substituted if the relevant
control name includes one. Again, catering for Null might be prudent in case
a user deletes the value from the control:

Private Sub Batch_Number_BeforeUpdate(Cancel As Integer)

Const conMESSAGE = "17 items have already been entered for this batch."

If Not IsNull(Me.[Batch Number]) Then
If DCount("*", "[Sample Details]", "[Batch Number] = " & Me.[Batch
Number]) = 17 Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If
End If

End Sub

Ken Sheridan
Stafford, England

Also having trouble with code, it will only allow me to enter 17 items period
into the table, regardless of batches. I have checked the spelling of the
text and have included the code below. Items is actually called Sample
Details and the BatchID is Batch Number(with a space between the words)
Private Sub Batch_Number_BeforeUpdate(Cancel As Integer)

Const conMESSAGE = "17 items have already been entered for this batch."

If DCount("*", "Sample Details", "Batch_Number = " & Me.Batch_Number) = 17
Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

End Sub


More Help please (I am guessing it has got to be something in the field
setup or format???????)

Thanks

Richard

Thanks

The formula is giving me in the unbound text box the message "#error". I
have tried both versions of the formula.
Both fields in the tables are Long Integers, with format as General Number
Both have the same name of Batch Number in both tables and in the BATCHES
table the Batch Number is the primary key, it is also Autonumbering.
Any Thoughts

Richard

:

Richard:

Don't create a column in the Batches table for the number of items per
batch. The number can be computed on the fly, so its unnecessary to store
it, but more importantly, to do so runs the risk of introducing
inconsistencies into the data.

To show the number for the current batch on the batches form add an unbound
text box with a ControlSource along these lines:

=DCount("*", "Items", "BatchID = " & [BatchID])

This assumes that BatchID is the name of the primary key of Batches and of
the corresponding foreign key column in Items, and that the columns are of a
number data type, not text. If the keys are text data type wrap the value in
quotes like so:

=DCount("*", "Items", "BatchID = """ & [BatchID] & """")

You can also use the DCount function in the same way in a report or in a
query, though in the latter you could also use a subquery, e.g.

SELECT *,
(SELECT COUNT(*)
FROM Items
WHERE Items.BatchID = Batches.BatchID) AS ItemCount
FROM Batches;

You can prevent more than 17 items per batch being created with some
validation code in the Items form. You don't say how Items records are being
created, but if the batch is being selected manually, most likely via a combo
box bound to the BatchID column, then you can put the code in that control's
BeforeUpdate event procedure, e.g.

Const conMESSAGE = "17 items have already been entered for this batch."

If DCount("*", "Items", "BatchID = " & Me.BatchID) = 17 Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

If the BatchID value is being entered automatically when a new record is
created, e.g. by being passed from the Batches form via code which opens the
Items form, then you could either do the validation in the Batches form, so
it only opens the Items form if less than 17 items records already exist for
the batch, e.g.

Const conMESSAGE = "17 items have already been entered for this batch."

If DCount("*", "Items", "BatchID = " & Me.BatchID) = 17 Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Else
DoCmd.OpenForm "Items", DataMode:=acFormAdd
Forms("Items").BatchID = Me.BatchID
End If

or you could use the same code as suggested for the BatchID control on the
Items form above in the form's BeforeUpdate event procedure. Doing the
validation before data is entered in the items form is obviously a better way
to go, however.

Note that validating the number of items per batch at form level does not
prevent more than 17 rows per batch being entered directly into the table, so
you should ensure that users can only enter data via the form.

Ken Sheridan
Stafford, England

:

I have two tables and two forms

1 Batches
2 Items

Items are related to a batch number (number is autonumbering for batch
number and sample number). There needs to be a maximum of 17 items to a batch.
I wish to have a check box / command button which when clicked will create a
field within the table BATCHES with the number of samples relating to the
particular batch number

ie if there are 10 items in a batch then I need this counting and inputing
the number into the appropriate field in the batch.
I thought this may be achieved with an update query which would select the
data based upon the batch number selected count the number of records and put
this information into the field in batches.
The point(s) I am unsure of are
1 Is this the best way of achieving this and if it is
2 How to perform the count part of the query

Many thanks

Richard