G
Guest
Hi all.
I have a database that is used to keep track of our stock in locations which
we enter using a barcode scanner. 1 table lists valid product codes, the 2nd
lists the product codes booked in along with the location and quantity. There
are another 2 tables that log entries when items are scanned either in or out
for us to monitor and compare against items loaded onto vehicles and items
produced that day. The software we use that enters data into the database
operates as follows:
A barcode of the product is scanned, this then sends the data to verify if
the product in is the valid product table. If not, we get a validation error.
If it is valid, the user then scans the location barcode and then enters how
many items. If there are no products in the location, then a new entry is
created in the location table. If the product already exists in the location,
then the quantity is added to the existing quantity. The software that allows
the scanners to talk to Access uses SQL commands, here is an example of the
booking in operation: (the numbers are used to link the data from the scanner
to the software)
insert into StockIn (Product, Qty)
values (':012:', :014
:AND:
update ProdLocations
set Quantity = Quantity + :014:
where ProdCode = ':012:'
and Location = ':013:'
:ERR:
insert into ProdLocations (ProdCode, Location, Quantity)
values (':012:', ':013:', :014
I am now looking to create a form that will do the same, so that if the
scanners don't work the users can still keep the stock updated. Will the same
commands above work if the references are changed? I guess it is more
complicated than I think!
Any ideas would be greatly appreciated?
Lee
I have a database that is used to keep track of our stock in locations which
we enter using a barcode scanner. 1 table lists valid product codes, the 2nd
lists the product codes booked in along with the location and quantity. There
are another 2 tables that log entries when items are scanned either in or out
for us to monitor and compare against items loaded onto vehicles and items
produced that day. The software we use that enters data into the database
operates as follows:
A barcode of the product is scanned, this then sends the data to verify if
the product in is the valid product table. If not, we get a validation error.
If it is valid, the user then scans the location barcode and then enters how
many items. If there are no products in the location, then a new entry is
created in the location table. If the product already exists in the location,
then the quantity is added to the existing quantity. The software that allows
the scanners to talk to Access uses SQL commands, here is an example of the
booking in operation: (the numbers are used to link the data from the scanner
to the software)
insert into StockIn (Product, Qty)
values (':012:', :014
:AND:
update ProdLocations
set Quantity = Quantity + :014:
where ProdCode = ':012:'
and Location = ':013:'
:ERR:
insert into ProdLocations (ProdCode, Location, Quantity)
values (':012:', ':013:', :014
I am now looking to create a form that will do the same, so that if the
scanners don't work the users can still keep the stock updated. Will the same
commands above work if the references are changed? I guess it is more
complicated than I think!
Any ideas would be greatly appreciated?
Lee