Should I use an AutoNumber for a PK or use a FieldName as a PK?

[Technyc2003]

I created a rental database in which I have multiples of the same equipment but barcoded with a different number. I know most inventory type databases uses 1 number for the same item. Like i.e. cans of Ajax: 50 cans with the same product ID.

In my case what I did is that I have a Product ID for the equipment but each equipment has a unique numbered barcode label attached to it. Kind of like when you have a drill same model number but different serial numbers.

The issue I have is that in my purchase order form when a customer walks in I want to scan the barcode label and put in how long they want to use it and generate a bill from there.

Part of my table looks like this


OrderProducts
------------------
ProductID (PK)
ProductName
ProductDescription

Next, I have a table called tblInventory which holds the following fields:

tblInventory
---------------
ProductCode (PK)
ProductID
Barcode
SerialNumber


The Form looks like this on the screen:

Combo Box: ProductName (also used as a search feature)

Field (ProductName): AJAX
Field (Product Description)

Subform:

Barcode Number
Serial Number


I'm having issues with my OrdersDetails form where I have the ProductID as combo but it won't allow me to choose more than 1 barcode item it keeps going back to the default record.

I tried using the Barcode number as choice but that didn't work.

Just need some clarification as to what the best option would be to accomplish this.

----------------------------------------------------------
I was told on another forum that I should have my tables structured in the following format:
----------------------------------------------------------

Should be something like:

tblProducts
------------
ProdID Autonumber PK
ProdAbbr Text
ProdName Text
ProdDesc Text

tblInventory
------------
InventoryID Autonumber PK
Barcode Text (Alternate Unique Index on this field)
SerialNumber Text
DateInService Date/Time
DateOutOfService Date/Time

tblRented
-----------
RentedID Autonumber PK
CustID FK to tblCustomers
InventoryID FK to tblInventory
DateRented Date/Time
DateReturned Date/Time
RentalDuration Integer

------------------------

Attached also is the actual database. When you open it up go ahead and click orders or if you want Papa John Pizza is a sample customer and I believe it's order #4 on that main screen. If you click it the Order Details Form will open up and you will see Product, Start Date, End Date, No Days Rented, Rate, Discount and the final total. What I'm trying to do is in that product table is scan the equipment's Barcode and the fill in the rest. If you notice that when you click the Combo box down and select i.e. Barcode #123456789 it will drop down to all the rests of the products. That I don't want to happen.

If you click on the Products button you will see 1 product name, several barcode numbers and different serials numbers for that item.

I think you'll see what I'm trying to accomplish.

Thank you ALL SOOOOOO much for your help.

 

[lameid]

Try enetering a control source for your combobox (make it bound).

 

[Technyc2003]

I tried with ProductID but that doesn't let me choose different barcode items even though it's the same product but different code #. I tried Barcode# as a control source and that didn't work. Were you able to see the database and the issue?