Book items into & out of stock

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
Hi,


The SQL syntax is not working with Jet, and :012:, :013:, and :014: seems
to be place holders for parameters that VBA would need to get renamed as
valid name. It seems you need an interface between the scanner and VBA,
unless you decide to just display, and refresh, the table(s) actually filled
in by the scanner. A database allows multiple users, so, in addition to the
scanner, which is a kind of 'user', another human user should also be able
to append rows to the table, at the same time, or modify existing rows.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top