Limiting Inserts into a table

  • Thread starter Thread starter Josh Mikow
  • Start date Start date
J

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

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

Thanks,

Josh
 
Back
Top