Any ideas on how to confirm data entry of an item post entry?

[Dale C Gray]

Access 97 is being used. Sorry for the vague subject.

The database is a frontend/backend if that makes a difference. There are 3
tables, Registry, BarcodeItem, Timestamp. I have users that will be
registering prebarcoded items into a form/subform. The items and paperwork
will be sent to a central location and received by scanning the barcode of
the item into a "receipt" form that timestamps the received date/time of the
item.

What if the item has a barcode but has not been registered i.e. data entry
was skipped. I wanted to capture those, I can only think of doing a lookup
against each item...which I am guessing would be time consuming. Users
could be scanning 100 items at a time. The relationship of the registry
table and the barcode items is one to many. Do I capture this by the
relationship of the tables i.e. barcode items to timestamp as one to one??

I hope that was as clear as mud Thanks for any thoughts...

 

[Allen Browne]

Dale, I'm not clear on the relationships here, but you have probably set up
a one-to-many relationship with Referential Integrity enforced (Tools |
Relationships).

If so, Access won't allow you to create an entry into the related table if
the value does not exist in the primary table (the "one" side of the
relationship). It will automatically block the entry. Use the Error event of
the form if you wish to capture and replace the standard error message, e.g.
DataErr 3201 indicates a related record is required.

If you want to, you can use the AfterUpdate event of the control that
accepts the BarCode info to notify the user that the barcode doesn't exist.
DLookup() should be fast enough for this. You can write something double the
speed if you really need to, but I doubt this would be necessary: 200
milliseconds is not very different to 100.

 

[Richard]

I think I understand your system. What about this?
Data entry - barcode is entered into a table
When you go to add the time stamp, search the data entry
table to find that value. If it is found, move it to
another table and add the timestamp there (enter it in
another table and delete the orignal record).

This will keep your searh table small. If the value you
are looking for doesn't show up, you found one that
slipped through data entry.

Hope that helps,
Richard

 

[Dale C Gray]

Thanks guys, I've decided to go with the "Dlookup" option set to the before
update event on the barcode item...I'll have to see how this holds up in the
field...I have no way of testing prior to launch. Scary!