search by barcode

[seanyeap]

Hi all,I would be very appreciative if someone could help me. Basically,I want to search information after all items have been randomly scanned by barcode scanner. For example,each of my staff has 4 items namely:- A:shirt1, B-pant1, C-shirt2, D-pant2. All items have their own individual barcode numbers. At the time of collecting these items for washing, how do I ensure that NOT all items are out so that they have one set to wear? I am sincerely hope that someone could help me & guide me thru. FYI,I am quite new in Access but I believe I can do it with your assistance & guidance. Thank you in advance.

Regard,
Sean

 

[WayneRyan]

Sean,

If they have only 4-items (2-shirts, 2-pants). Then this is simple;
you can never accept two items in a category. You just need
to add a category to their inventory.

If you exceed the 4-item count (and/or add categories) then you
have a much more complex problem and will need tables for
people, categories and items. Then you will have to do nested
queries to select number of items not within other queries.

Wayne

 

[seanyeap]

Dear WayneRyan,
Thanks for your prompt feedback. The thing is that I have 100 staffs. Each of them has 2 different shirts & 2 different pants. And all these items have different barcodes & they are scanned randomly. How many tables should I create? Thanks.

Regard,
Sean

 

[WayneRyan]

Sean,

If you had:

tblPeople:
PersonID - Autonumber
LastName
FirstName
OtherStuff

tblInventory:
InventoryID - BarCode
PersonID - relates to tblPeople
ItemCategory (Shirt/Pants)
ItemStatus (In/Out)

Then when you receive a set of items:

Your main form shows info about each person.
Your subform shows their inventory and the status.

You can have command buttons for Incoming and
Outgoing items.

In the OnClick of the Incoming button you
can put:

If Nz(DCount("[ItemCategory]", _
             "tblInventory", _
             "[PersonID] = " & Me.PersonID & " And " & _
             "[ItemCategory] = '" & Me.ItemCategory & "' And " & _
             "[ItemStatus] = 'Out'"), 0) Then
   MsgBox("Your customer isn't wearing any " & Me.ItemCategory)
   Me.Cancel = True
End If

That's just a rough idea, sorry about the Message Box joke.

hth,
Wayne

 

[seanyeap]

Dear WayneRyan,
You r so helpful, I really owe u so much, let me try it.

Regard,
Sean