Limiting Inserts into a table

[Josh Mikow]

I am currently working on a program that will allow us to scan barcodes
for our company.

I have a table that stores the barcode value and the date/time scanned.
My problem is that by using a Primary Key on the table, it limits the
table to having one row for each barcode and date/time combo.

Our end goal is that we can have a way to limit the number of times a
barcode can be scanned into the table each day. Right now, by using a
PK with the barcode value and date, we can only insert one row per day.
We will typically need to do this a few times a day, but I'm not sure
how to limit this.

Any ideas would be great and helpful.

Thanks,

Josh Mikow

 

[Josh Mikow]

I'm also looking for a way to limit the number of times that a report
can be printed each day. I have a table similar to the one for scans
that tracks when barcodes are printed.

Thanks,

Josh

 

[arthurjr07]

Insert another Field in your BarcodeTable.
Example
BarcodeNo Date RecordNo
111111111 04/24/06 1
111111111 04/24/06 2
111111111 04/24/06 3
111111111 04/24/06 4
111111111 04/24/06 5

Before you saved a new record, query
first the last RecordNo.
rs.Open "Select RecordNo Fron BarcodeTable where BarcodeNo =? and Date
= now"
if rs.recordcount = 0 then
Insert the record with recordNo = 1
else
if rs!recordNo = limit then
Msgbox "Exceed the Maximum Limit"
Else
Insert the record with recordNo = rs!RecordNo + 1
end if
End if

Hope u understand what i am trying to say.

 

[Josh Mikow]

Arthur,

Thanks for the response. Would this need to be done in a Macro or a
Query along with my insert statement?

I understand the logic, just not sure where to execute it.

Thanks,

Josh

 

[arthurjr07]

It should be done in VBA.

 

[Josh Mikow]

Arthur,

I'm trying to do this in the On Before Insert event of my form.

Can you give me a quick example of how to create the connection to the
table? I can't seem to get it working.

Also, how would I cancel the insert if the limit has been reached?

Thanks for your help.

Josh

 

[arthurjr07]

Please check your email. I have sent you a sample MDB.

 

[Josh Mikow]

Arthur,

Thanks for that sample. It clearified what I needed to do more than I
understood before.

Thanks,

Josh