I guess I should start off by laying out my table design:

[u]Product</u>
*Product_ID
 Product_Name
 Product_Category
 Product_Description
 Product_Wholesale
 Product_Retail

[u]Product_Quantity</u>
*Product_ID
 Product_Quantity

[u]Product_Location</u>
*Product_ID
*Location_ID
 Location_Quantity

[u]Location</u>
*Location_ID
 Location_Name

[u]Product_Sales</u>
*Sales_Item_Number
 Product_ID
 Sales_Number

[u]Sales</u>
*Sales_Number
 Sales_Date
 Sales_Location

Now, I have a form called Inventory Update (that is where the option buttons and combo box reside).

Here's what I'm trying to do:

select a category (from the option buttons)

choose a product (from the combo box)

type in a quantity (in a text box)

type in a date (in a text box)

select whether inventory is being added or deleted/sold (option buttons)

update the inventory (command button)

From my limited understanding of VBA, it sounds to me like all these actions will take effect during the CLICK event of the Update Inventory command button.

I am, once again, at an utter loss as to how I should go about accomplishing this...

You guys really helped me out with my last problem and I can't thank you enough for that. This problem seems a little more involved but I'm sure its still quite elementary for most of the forum members.

Anubis,

You are right - you would use the Update Inventory
command button to trigger the updates.

I did had a few questions for you first:

 - Should the value in Product.Product_Quantity be
   equal to the sum of all column values in
   Product_Location.Location_Quantity? If this is
   the case, one wouldn't normally store what would
   be the equivalent of a calculated field in the
   first table - you would get the total inventory
   for a product by summing the Location_Quantity
   values for the matching product in the Product_
   Location table.
 - I noticed that you have the user enter a date
   which appears to be stored in both the Product_Sales
   table and Sales table. Is the user expected to
   enter a sales/order type of number as part of this
   process as well? It seems that for situations where
   the quantity would be depleted, you would want to
   update both the product tables and sales tables.
 - Is the category selection only being used to
   filter the entries that will be displayed to
   the user in the product combo box - as far
   as the database update, the category would not
   be required for the actual update.
 - It appears that inventory quantities are stored
   at a location level in your Product_Location
   table - would you need the user to specify which
   location the inventory quantity is being added to or
   taken away?
 - Would you like the have the code using .AddNew or
   methods against a DAO or ADO recordset, or would you
   prefer to have a SQL statement built with a string
   and then executed?
 - And as part of the last question, if you want to
   do .AddNew or .Edit, would you prefer ADO or DAO?

Please let me know.

Thanks,

Warren
:D

That is correct. Product.Product_Quantity = the sum of Product_Location.Location_Quantity

Nope, Sales_Date only appears in the Sales table.
Sales.Sales_Number and Product_Sales.Sales_Item_Number are AUTONUMBER. I was trying to create uniqueness of an item (Sales_Item_Number) on a sales order (Sales_Number).

The product table is intended to be a list of offered products. It would not be altered by a sale. The only time the product table would be altered is when a product was introduced or discontinued from the product line.
The tables that would be updated from a sale include: Product_Location and possibly Product_Quantity (though, if I removed quantity from Product_Quantity.Product_Quantity I'd have to include another table that looked something like this:

[u]Sales_Quantity</u>
*Product_ID
Sales_Quantity

I think I may add this table because the client wants to keep track of historical sales data.)

That is correct.

Yes.

I don't know if this has any relevance but the option buttons are setup to use a SQL string executed through a select case.
I will be honest with you...I don't know what DAO and ADO mean, let alone my preference of one over the other...

If anyone has any input on this little dilemma I'd be overjoyed to hear it.

Will all the code go into the CLick event of the command button or is code required at each step?

It would go in the OnClick event of the button and I would build a where string according to your criteria which you set through the option buttons, combo boxes and text boxes, then add this to a sqlstring (which can vary dependant on if you are adding, deleting or updating as sold inventory) which is specific for the type of Case which you are performing... adding, deleting or updating to Sold.

HTH,

Beth M

*smacks his forehead* oops, I meant the click event...
I edited above

Does anyone have an example of what the code might look like?

I know Beth said to build a where string according to my criteria which I set through the option buttons, combo boxes and text boxes, then add this to a sqlstring...but (sorry to keep repeating myself) I have very little background in VB and even less in VBA.

I just can't seem to envision how this code is supposed to look.

Are there any good online resources for specific VBA code examples?

This should give you a starting place:

Build your criteria by checking for not isnull value in your controls and build the criteria string as you go like in the following example:

Dim Target As String
Dim Value as string
Dim SqlString As String
Dim db As Database

Set db = CurrentDb

If Not IsNull(me![TextField]) and me![TextField <> "" Then
Target = Target & ",[TextField]"
Value = Value & ",'" & me![TextField] & "'"
End If

If Not IsNull(Me![TextField2]) Then
Value = Value & ",'" & Me![TextField2] & "'"
    Target = Target & ",[TextField2]"
End If

'continue with additional fields

Value = Value & ")"
Target = Target & ")"

'Change sqlstring according to case
If optAdd then
SqlString = "Insert Into RecordSource " & Target & " Values " & Value
Elseif optUpdate then
sqlstring = "UPDATE DISTINCTROW Recordsource set " & TARGET & "= " & Value
End if

db.execute sqlstring

db.close
set db = nothing


Watch out for word wrapping!

Regards,

Beth M