Limit record entries based on number in field

[lamore48]

Hi All,
I have a simple database with tool data inputs and outputs for sharpening, I have the initial forms working accepting the data. One of the fields in the database shows how many of each box there are in a set for example: Box number 46001-1, 46001-2, 46001-3, 46001-4. So in the series there are 4 sets of boxes for 46001. I have a [box in] table and I have a [box out] table, also in the table is a field which shows how many boxes are in each series, so in this case the total box field there is the number 4. My question is can I limit data entry based on total box field. In other words I can't have more than 4 boxes of 46001 in the [box in] table at one time? And how would I code to take out entries of the [box in] table as they were entered into the [box out] table?




Thanks in advance,
Lenny

[aytee111]

Put the boxes in their own table - box master table holding 46001 and box detail table holding 1 2 3, etc. Then for form entry the box number will be a combobox based on this table. For the box transactions, have it on one table - date in and date out, rather than separating the two bits of information.

[June7]

Do you need to retain historical sharpening records? If not and you only care about where the box is now, just have an In_Out field in the BoxDetails table.

[lamore48]

Historical records

[June7]

Then follow aytee's suggestion.

This sounds like checking a book out of library. Might look at the Microsoft Lending Library database template.

[Micron]

My question is can I limit data entry based on total box field. In other words I can't have more than 4 boxes of 46001 in the [box in] table at one time?

You have this number before the data entry starts? Then in the BeforeUpdate for the form, check the DCount of this field and cancel the update if the expression return value matches the count value:
DCount("BoxFieldName", "TheTable", "BoxFieldName = " & YourControlNameOnForm)
Downside is that user would have to move off the record to trigger the event. You could perform the validation no matter which control the user enters if you either put the same code on each control BeforeUpdate, or just a line to call a function that you put the expression in. The downside of that would likely mean that you could not edit existing data, so it would require a work around.