Inventory Help

[Lost]

I have created a database for inventory for my work.

I have set up a table with product information and each product has
thousands of items. And each item has it own barcode on it.

I have set up a way for the database to know with a scan of a a bar code
what the item is and what number it is.

What i need to do is figure out a way for the item to be given a location
and be able to change that location fast because hundreds of items move
thorught differents areas at my work everyday.

What I want to is to be able to scan the item and tell it is going to be
there now.

 

[Nicholas Scarpinato]

I'm pretty sure I know what you're trying to do, but I need a better
description of the exact physical process before I can give you my best
answer. For now, I'll answer as best I can and we'll fill in the blanks as we
go.

Comments inline:

I have created a database for inventory for my work.

I have set up a table with product information and each product has
thousands of items. And each item has it own barcode on it.

I have set up a way for the database to know with a scan of a a bar code
what the item is and what number it is.

This is the easy part. Most barcode scanners read the barcode and translate
it to text much like if you were typing it in manually, so as long as you
have one of these scanners, just plug it in and you're good to go. As far as
using that information to search your database, you need a simple query that
uses that form control as it's criteria. But I'll get to that later on once I
know more about what you're trying to do.

What i need to do is figure out a way for the item to be given a location
and be able to change that location fast because hundreds of items move
thorught differents areas at my work everyday.

I'm assuming you have various checkpoints with a PC and a scanner at those
locations?

What I want to is to be able to scan the item and tell it is going to be
there now.

So you want an item that has reached a specific area, say "STOCK", to be
able to be scanned in and added to that location... and then later when that
item moves, you want to be able to scan it and change it again? I'm doing
much the same process right now for the returns department where I work, if
that's the case. Let me know more about what your process is, phsyically, and
I'll give you as much help as I can.

 

[Lost]

Right now I have two main tables.

One table that has the actual product.

And the other table is for all the different items with a structure of:
Item #
Product ID (relationship to the first table)
Current Location
Transfer Date

When the item comes into the building the item has a upc barcode on it and
then the staff puts on a second bar code for it unique item number.

So the staff scans in the second bar code to give it a unique item number
then scan in the upc number to let the database know what the item is.

I have it set up for the initial scan to tell it that the item is in the
storeroom room and it recive date is the current date.

So my main problem is to set up a form that when I scan in the itemid that
it will change it to a new location automaticaly.

Currently I have it set up with a search query in a form so when the item is
scanned it will pull it up and then i have to manualy change the current
location. Close it and reopen the form for the next bottle.

What I am hopeing to do if possible is to open a form call for example
transfer to storeroom #1 And scan the item number and then it will know that
item #100 is know it current location storeroom #1 and it was done on current
date. After the item barcode scan being able to scan the next item.

Thank You In Advance

 

[Nicholas Scarpinato]

In your search textbox, try to add in something along these lines in your
search code (this is air code so you might have to play around with it to get
it to work the way you need):

ItemID = Me![Scanbox] 'Get ItemID from barcode scan
If ItemID = Dlookup("[ItemIDField]", "ItemInfoTable", "[ItemIDField] = '" &
Me! _
Scanbox] & "'") Then 'Check if ItemID scanned matches any ItemID's already
in the table
ItemStatus = "Next Location" 'If ItemID has a match, set status to "Next
Location"
DoCmd.RunSQL "UPDATE [ItemInfoTable] SET [ItemStatusField] = '" & ItemStatus _
& "' WHERE [ItemID] = '" & Me![Scanbox] & "';" 'The actual SQL query to
change the status
End If

In your code, you can declare ItemStatus to be a global variant and use an
OnOpen event to set ItemStatus to match whatever form you're opening:

Private Sub Form_Open()
ItemStatus = "Form Status"
End Sub

Then when you open the form, the status will correspond to the form you're
working in.