Textbox to Filter Listbox

[Kwoolridge]

I'm trying to create a database to track when items are sent out of my facility and when they're active in the system. I know I could easily create an excel spreadsheet to track this information but I prefer Access's reporting capabilities. 

I have a table with Order#, UPC, Item#, ShipDate, Tracking#, and ActiveDate as the columns. I created a form to enter all this information with the exception of ActiveDate. I'm trying to figure out a way to enter the ActiveDate without having to go into the spreadsheet itself and enter the information. 

I thought the best way to update this column would be to create a second form that I could enter the UPC or the Item# in a Textbox and use that to filter a Listbox with the corresponding Order# (since some items get shipped out more than once and would therefore have a different Order#). 

I tried googling a solution to this and the solution I found was to enter the following VBA code in the On Change property of the Textbox: Me.List1.RowSource="Select[Table1].[Textbox] From Table1 WHERE Table1.Textbox like Forms!Form2.Textbox.Text&'*'" However, I'm not super familiar with VBA coding and I couldn't get this to work for me. 
Is there a better or easier way to accomplish this task? 

[#2]

@Kwoolridge 

When you upgrade from Excel to Access, you must store the data differently as well. Tables in Access are not like "tables" in Excel. One of the key differences is that each Access table stores one kind of data and ONLY that one kind of data. Spreadsheets force you to put everything in one place with multiple columns for this and that.

So, you will almost never have a one table Access relational database application, and that is true here as well.

You need a table of products. I assume that is what you mean by "items sent out", they are products you distribute. 

Your products table contains fields for each of the relevant attributes of a product (and no other attributes). I can see at least two such fields: Item Number and Item Description or Item Name. Depending on how you assign "Item Number", that may or may not be the Primary Key for this table.

If you don't know what I mean by Primary Key, stop now. Invest some time in studying relational database design before continuing. In particular, you need to know the Rules of Normalization and the use of Primary and Foreign Keys. These are fundamental principles that have to be followed in order to create a usable relational database application. Long before you get to forms, you must create the appropriate table design.

You will also need a table of Orders, in which you record information about orders your organization receives. It will include a Primary Key for Orders, the Order Number and the Customer Number, along with the Order Date.

If one order can include one or more products, you also need an Order Detail table. This table includes, again, a Primary Key, Order Number, Product Number, quantity and Price.

The next table is the Shipping table, in which you record information about each shipment. Again, a Primary Key, the Order Number, Ship Date and Tracking Number, at least. Probably you will want to include the Shipper Number. 

It's not clear how any of this relates to the "active in the system" requirement, unfortunately, so I can't be sure where to track that. What does that mean in business terms? Is it part of an order? Part of shipment of an order, or something else?

Once we know how to design the tables for a Relational Database Application, though, we can address how you go about recording that "ActiveDate", whatever it means in this particular situation.

You might want to download and look at one of the templates which are built around similar kinds of business operations. I'm thinking primarily of Northwind, which is a venerable application from Microsoft.

[#3]

@George Hepworth,
Thank you so much for your detailed and concise answer.
I understand Primary keys insomuch as they are a unique value that is used to create relationships between tables. When I initially started fooling around with Access database I began by breaking down databases that my company was already using, none of which have ever used Primary keys. So, while I understand their functionality as a theory, I'm not entirely sure how to utilize them in practice.

I understand what you're saying as far as creating different tables and that will definitely be something I change with the database I had started. I guess my confusion with utilizing a primary key is with how that connects to other tables. Forgetting about the dates, let's say:

Item# = 123ABC
UPC = 123456789
Order# = 987654
Tracking# = 4561239
With Table1 containing both the Order# and the Tracking# and Table2 containing the Item# and UPC. I could use the Order# as the primary key, but how does that relate to Table2? I will have multiple items on a single order but your primary key cannot repeat. I could also use either the Item# or UPC as the Primary Key, but we have instances where an item will be ordered and sent out on more than one occasion, and again, I can't have a repeating primary key. 

[#4]

@Kwoolridge 

A field in a table which is identified as the Primary Key uniquely identifies each record in that table.

If, for example, you are looking at the "Items" table, and if you have designated "ItemNumber" as the Primary Key, then 123ABC can appear once and only once in the Item table.

On the other hand, when you want to ORDER an item, that primary key becomes the Foreign Key in the OrderDetail table. One or more orders can include Item 123ABC.   One item is included in many orders. Primary Key 123ABC from the Item table relates to several instance of Foreign Key 123ABC in all of those order details.

The reason I refer to an Order table as well as an Order Detail table is that each order, I assume, can include one or more items. If that is not the case, if each order is for one and only one item, then the ItemNumber can be in the Order table. However, it is far more common to have multiple items in an order.

The OrderDetail table has two foreign keys. One of those is the OrderNumber, as discussed above,the other is the ItemNumber, as discussed above. This is a many-too-many relationship. One item can be in many orders. One order can contain many items.

You will not repeat Primary Keys in any table, as you note. However the same VALUE (e.g. 123ABC) which is the Primary Key in the item table appears as Foreign Keys multiple times in an OrderDetail table. 

Again, I would recommend using the Northwind Sample database as a sort of template, or study guide.