[RESOLVED] Updating stocks and Comprehensive VBA tutorials

Hi to all!

I have a main form in ms access (OrderEntryForm) and a subform(OrderEntrySubform). These forms are similar to northwinds orders form.
My question is how do i update the quantity on hand field in the OrderEntrySubform after I inputted the payment in the txtAmount in the OrderEntryForm. The OrderEntrySubform is bound to a query (qryOrderDetails) similar to northwind's order details extended..
The code below illustrates when the cashier inputs payment in the txtAmount the and press enter, the change due is computed...

Here is the scenario....

Product Code: a-111
Name: Jergens
Stocks: 200
Quantity Ordered: 10

Product Code: a-112
Name: Graham Crackers
Stocks: 300
Quantity Ordered: 15

Product Code: a-113
Name: Cream-O
Stocks: 100
Quantity Ordered: 5




Total Due: 490
(txtAmount) --> Payment: 500
Change Due: 10.00

When the txtAmount_LostFocus() is called, the change due is computed and the stocks for Items Jergens, Graham Crackers, and Cream-O will be updated into 190, 285, and 95 respectively...Hope you got my point...

VB Code:

' Main form: OrderEntryForm textbox (txtAmount)
 
        Private Sub txtAmount_LostFocus()
           'these four lines will compute for the change due
           total = txtTotal.Value
           amt_paid = txtAmount.Value
           change = amt_paid - total
           txtChange.Value = change
       
           'code for updating the quantity on hand
           ???????????
 
        End Sub

If you can provide me with comprehensive tutorials on VBA for ms access, it would be a great help also...

Hoping for any response....

:wave: :wave: :wave:

Quote:

---

Originally Posted by KGComputers

If you can provide me with comprehensive tutorials on VBA for ms access, it would be a great help also...

---

Here are some things that I hope you will find useful.

http://mis.commerce.ubc.ca/courses/c...htm#whatfilter

http://cisnet.baruch.cuny.edu/holowc...accessall.html

http://www.functionx.com/access/

Hi Hack!!!

Thanks for posting the links....I already have a copy of those links...Functionx.com is one of my references for all my projects...

What I'm trying to emphasize is this....

I have this code in visual basic 6.0 using ADO and Listview control, and the Item Table...This code will update the stocks of the products(Item table)...(new stock = current stocks - quantity ordered) in the Item table...For every product registered in the listview, the quantityonhand will be updated based from customer purchases.

VB Code:

For i = 1 To lvwSales.ListItems.Count
                item_name = CStr(Trim(lvwSales.ListItems(i).Text))
                q_order = Val(lvwSales.ListItems(i).SubItems(4))
                
                rstItem.Open "Select * from Item", cnn, adOpenDynamic, adLockPessimistic
                Do While rstItem.EOF <> True
                    If rstItem.Fields("Item_Code") = item_name Then
                        cnn.BeginTrans
                        curr_stocks = Val(rstItem.Fields("QuantityOnHand"))
                        rstItem.Fields("QuantityOnHand") = curr_stocks - q_order
                        cnn.CommitTrans
                    End If
                    rstItem.MoveNext
                Loop
                rstItem.Close
                
                curr_stocks = 0
                q_order = 0
                        
            Next i

My question is how can I convert this vb6.0 code into an MS Access VBA code? Is ADO in vb 6.0 very similar to MSAccess VBA? How can I update the stocks in the subform like what i did using vb 6.0 listview control? I only have little experience using VBA...

This client of mine wants sales orders be manipulated directly through a database (ms access) w/o using front-end vb 6.0 applications..

Hoping for a response...... :) :) :) :)

VBA is a subset of VB, so I'm guessing that much of your code would transfer with relative ease.

I believe that Access does have a ListView component. If I incorrect in this, then I know it does have a multicolumn listbox (one of the very few things I find envious about. Why couldn't that have been included with VB as well?)

The ListView control is not part of the default control in the Access Toolbox. You can add one as an additional control but if you distribute the database you will need to distribute the comctl2.ocx also and register it.

Quote:

---

Originally Posted by RobDog888

The ListView control is not part of the default control in the Access Toolbox. You can add one as an additional control but if you distribute the database you will need to distribute the comctl2.ocx also and register it.

---

But, Access does have a multicolumn listbox control, right?

Yes, basically its default toolbox is almost the same as vb6's default toolbox.

Good day gurus.....

thanks for the spark...Yes indeed, there is a listview control in the MS Access database.I will try that later on...

My point is the products which the customer ordered is entered using a subform which is also bound to a query.The query is bound to a query (qryOrderDetail) and this query is bound to the Item table..The subform does not apply listview..What Im trying to emphasize is without using listview, just the plain subform in a datasheet view similar to Northwinds database Orders subform, how can I update the stocks of the products using VBA code? In listview we can use its listitems() and subitems() to access the elements right? In an ordinary subform in datasheet view, how can we access the elements like quantityordered and etc....hope you get my point....The code will be added in the txtAmount_LostFocus() which is my first post....

Waiting for your responses...... :thumb: :) :) :)

Hi RobDog8888 and Hack...

Is this the listview control your talking about( Microsoft Listview Control 6.0) ? I'm using access 2000.The active x component (comctl2.ocx), if I plan to distribute a database using listview, how will I do that? Say, my client has also MS Office 2000 in his PC...

Yes, listbox in MS Access is also quite nice which should also be included in the VB later on...

Hoping for any response on my previous post.....



:) :) :)

I just solved this...


I just substituted the connection with this

VB Code:

Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
 
Then I used the traditional code of ADO to update the stocks...