Search data from one table to append to another table (2 Viewers)

[Melbrett68]

Been a long time since I have Access coded. I have one table (named Inventory) that contains 100 inventory items for a small shop - 3 basic fields: Part Number, UPC, Description. I want to build a form that let's me either enter the part number or scan a bar code to search for one inventory item and them update a different table (named PartsUsed) with these three fields (I am also adding a date field, but defaulting to today's date and I know how to do that). I'm getting stumped on how to do this.

 

[XPS35]

It's not clear to me what you want. In the title, you mention "append" and in the question you mention "update."
I'm also wondering why you would want that. Generally, storing the same data twice is a bad idea.

 

[GPGeorge]

From the description of the end goal, it appears there is a faulty table design here.

It would not be proper design to have to store the same values from three different fields in a second table. I think I can guess what the tables actually look like based on this description, although this is subject to verification.

The proper design is to designate the relationship between two tables using a Primary Key in the first table and a Foreign Key in the second table.

So, your inventory table should have 4 fields (at a minimum).

InventoryItemID -- Long Integer. Primary Key. The unique value which identifies each inventory item. Most commonly, we use the AutoNumber for this.
PartNumber -- Short Text. Your company's designation as numbers and letters.
UPC -- Long Integer. The digits making up the Universal Product Code of the part.
Description -- Short Text (up to 255 characters) or Long Text (if your descriptions are more than 255 characters long).

---Corrected--
The PartsUsed table needs 3 fields (at a minimum).
PartsUsedID -- Long Integer. PrimaryKey. Identifies each use of an inventory item in a part. Most commonly, we use the AutoNumber for this.
InventoryItemID -- Long Integer. Foreign Key. Identities the InventoryItem used for this part.
PartBuiltDate -- Date Time. The date on which that inventory item was used for that part.

To use this in a data entry form, you would either use a subform, if more than one inventory item is included in each part, or you could use a combo box for the inventory item.

Tell us more about the actual usage, for more specific suggestions.

 

[Melbrett68]

The actual usage is for a small repair shop. I believe I have the 2 tables set up correctly, it's the implementation of the form for my very non-tech savvy mechanic. I have about 100 inventory items that this shop uses - some for repairs, but others for shop supplies (think WD40 used to repair a squeak). I have an existing table (Table Name = Inventory) of these 100 items with these 3 fields: Product# (primary key), UPC, Description. I want to track materials used by the shop, but not truly billed to the customer. I would like the employee to be able to scan the UPC on WD40 can OR enter the Product# (such as 1202) based off the table INVENTORY, see that they selected the correct item and then append to different table named SHOP_ITEMS_USED that will have a new record created/appended for when this WD40 is taken out for shop use (Product#, Description and Today's Date). I don't want them to have to use a combo box to scroll through 100 lines to select one item. There is a method to my madness, trust me. Let me know if I'm totally not explaining this correctly. I am thinking I need a subform of the table INVENTORY maybe? And then unbound search box on the main form to search/bring up the correct item in the subform, and then button to append to the new table SHOP_ITEMS_USED. Or can I make this simpler with using a combo or list box with the unbound search criteria and then somehow append the Product# and Description from that field into the table SHOP_ITEMS_USED

 

[The_Doc_Man]

From what I can gather you are building a work-order that records what you used in a given repair job so that you can charge for it properly, plus have the record of what you did if that is ever needed in the future. Not an unreasonable use if I guessed correctly. I'm not 100% certain of the table you called SHOP_ITEMS_USED because I could see this in another way, as a list of items to order for maintaining inventory levels, the OTHER possible use of that _ITEMS_USED table. Can you explain your intended use of that table better? It will help us to understand the 10,000 foot overview of your project.

The wild-card in this mix is if you ever have to call out something not in the Inventory table, you just killed your ability to use a relationship between the PK (Prime Key) of Inventory and the FK (Foreign Key) of the work order. But as you point out, you might not be explaining this completely and therefore, my guess becomes questionable. So... does it ever happen that you have to call for something NOT in inventory, and in that case is it even tracked? If so, how? Do you add it to inventory?

As to scrolling through a 100-unit inventory table, look up something called "Find as you type" that lets you type in the first few letters of a name to reduce the size of the drop-down list. So if you wanted WD40 or Washers, you would type "W" in the box and those items not beginning with "W" would drop out of the list. That feature may also be looked up on the forum as "FAYT".

 

[GPGeorge]

The actual usage is for a small repair shop. I believe I have the 2 tables set up correctly, it's the implementation of the form for my very non-tech savvy mechanic. I have about 100 inventory items that this shop uses - some for repairs, but others for shop supplies (think WD40 used to repair a squeak). I have an existing table (Table Name = Inventory) of these 100 items with these 3 fields: Product# (primary key), UPC, Description. I want to track materials used by the shop, but not truly billed to the customer. I would like the employee to be able to scan the UPC on WD40 can OR enter the Product# (such as 1202) based off the table INVENTORY, see that they selected the correct item and then append to different table named SHOP_ITEMS_USED that will have a new record created/appended for when this WD40 is taken out for shop use (Product#, Description and Today's Date). I don't want them to have to use a combo box to scroll through 100 lines to select one item. There is a method to my madness, trust me. Let me know if I'm totally not explaining this correctly. I am thinking I need a subform of the table INVENTORY maybe? And then unbound search box on the main form to search/bring up the correct item in the subform, and then button to append to the new table SHOP_ITEMS_USED. Or can I make this simpler with using a combo or list box with the unbound search criteria and then somehow append the Product# and Description from that field into the table SHOP_ITEMS_USED

Good luck with your project.

 

[mike60smart]

The actual usage is for a small repair shop. I believe I have the 2 tables set up correctly, it's the implementation of the form for my very non-tech savvy mechanic. I have about 100 inventory items that this shop uses - some for repairs, but others for shop supplies (think WD40 used to repair a squeak). I have an existing table (Table Name = Inventory) of these 100 items with these 3 fields: Product# (primary key), UPC, Description. I want to track materials used by the shop, but not truly billed to the customer. I would like the employee to be able to scan the UPC on WD40 can OR enter the Product# (such as 1202) based off the table INVENTORY, see that they selected the correct item and then append to different table named SHOP_ITEMS_USED that will have a new record created/appended for when this WD40 is taken out for shop use (Product#, Description and Today's Date). I don't want them to have to use a combo box to scroll through 100 lines to select one item. There is a method to my madness, trust me. Let me know if I'm totally not explaining this correctly. I am thinking I need a subform of the table INVENTORY maybe? And then unbound search box on the main form to search/bring up the correct item in the subform, and then button to append to the new table SHOP_ITEMS_USED. Or can I make this simpler with using a combo or list box with the unbound search criteria and then somehow append the Product# and Description from that field into the table SHOP_ITEMS_USED

Are you able yo upload a copy of the database?