Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 17th, 2003, 03:32 PM
Authorized User
 
Join Date: Nov 2003
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default Alright, here's a tricky one...

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
 
Old November 17th, 2003, 03:39 PM
Authorized User
 
Join Date: Nov 2003
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)
 
Old November 17th, 2003, 03:42 PM
Authorized User
 
Join Date: Nov 2003
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old November 18th, 2003, 12:34 PM
Authorized User
 
Join Date: Oct 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 18th, 2003, 04:04 PM
Authorized User
 
Join Date: Nov 2003
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:If this is the case, one wouldn't normally store what would be the equivalent of a calculated field in the first table
That is correct. Product.Product_Quantity = the sum of Product_Location.Location_Quantity

Quote:
quote:I noticed that you have the user enter a date which appears to be stored in both the Product_Sales table and Sales table.
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).

Quote:
quote:It seems that for situations where the quantity would be depleted, you would want to update both the product tables and sales tables.
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.)

Quote:
quote:the category would not be required for the actual update.
That is correct.

Quote:
quote:would you need the user to specify which location the inventory quantity is being added to or taken away?
Yes.

Quote:
quote: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?
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...
 
Old November 19th, 2003, 12:29 PM
Authorized User
 
Join Date: Nov 2003
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?
 
Old November 19th, 2003, 12:41 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 19th, 2003, 12:45 PM
Authorized User
 
Join Date: Nov 2003
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

*smacks his forehead* oops, I meant the click event...
I edited above
 
Old November 19th, 2003, 02:44 PM
Authorized User
 
Join Date: Nov 2003
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?
 
Old November 19th, 2003, 03:34 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Tricky Queries balesh.mind SQL Server 2005 5 April 12th, 2008 10:29 PM
A tricky question mike_remember ASP.NET 1.0 and 1.1 Professional 3 October 27th, 2006 08:17 AM
Easy but tricky for me... :( Varg_88 Classic ASP Basics 3 January 13th, 2005 11:21 AM
Tricky SQL fastcorvette Access 2 October 10th, 2003 10:28 AM
Very tricky challenge 12th_Man SQL Language 3 June 20th, 2003 06:45 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.