1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

MS Access 2007 - select items shopping cart-style, then update table?

Discussion in 'Business Applications' started by Gram123, Apr 24, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    I have a (currently) simple Access database with Search form, to which I've added a multi-field filter, based on a nice bit of code I found online.

    The next stage is to reproduce this form with added functionality, for issuing and receiving goods into stock.
    I'd like for the user to be able to select multiple stock items, see them added to something in a "shopping cart"-style and then click a button to issue the selected items (subtract from stock) or receipt them (add to stock) and, print the details.

    The way I'm imagining it, I'd add Issue Qty and Receipt Qty fields to the existing Stock search form. Anything that had been given an Issue Qty or Receipt Qty would populate a query. The query would simultaneously feed a subform, displaying the slected items & quantities for the user to see, and then buttons for Issue and Receive (or a single button, maybe) would allow the user to confirm their changes, updating the stock quantity accordingly, printing a report showing what they'd issued or receipted and emptying the query for next use.

    I'm not sure if this is the best way to go about it. Perhaps rather than populating a query, it could or should be done with a sort of temporary recordset.
    There might be difficulties in undoing, too. I'd need to have something in place to allow the user to either delete items from the "cart" subform or set the Issue / Receipt Qty on the main form to zero. In either case, it'd be necessary to ensure the stock wasn't affected when the button was clicked.

    Any advice, or links to similar existing dbs?

    Cheers.
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    If you look at most on line companies their Basket is in actual fact a form, or sub form in Continuous forms mode.
    so you could work directly with a form and only make it "Visible" when "goto basket" was clicked.
     
  3. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Hi OBP,
    It may be viable to have a "basket" subform appear when a "go to basket" button was clicked.

    I had envisaged it working as a subform visible at the bottom of the screen that would update each time a value was entered into the issued or receipted qty box for that particular Part on the main form. The idea being that the users could easily keep track of what they've already added to their "basket" and make changes (i.e. delete a part from the basked or change qtys) right there rather than in a seperate window. There'll ultimately be thousands of different Parts in stock and a user might require a variety of Parts in any one transaction.

    But thinking about it, I might end up with difficulties with a continuous form and a continuous subform (I guess I could make a new form and add both the stock and the transactions as continuous subforms).
    I don't think any one transaction is likely to include tons of parts, users will probably only issue 3 or 4 Parts at a time.
    Hmm.


    One thing that I'll definitely require is the stock to be changed by whatever the user enters in the issues and receipts.
    i.e. The quantity of the part in stock ([Actual]) to be updated by the number of parts ([Quantity]) issued ([TransactionType] = Issue) or receipted ([TransactionType] = Receipt).
    I'll also need to do something to flag whether such a transaction takes a stock part below it's minimum ([Min]) or above it's maximum ([Max]).
    And the plan is to date/time stamp each transaction (I guess, when another button is clicked, that confirms the user wants the transaction to take place, similar to a "Buy" button), so we can run a report showing the last 24 hrs (or whatever period's) transactions.

    Maybe you could assist with the method / code ?


    Cheers,

    Gram
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    If you take a look at the attached database, especially the VBA Code for the "New Purchase" and "Order line" forms you will see the code for updating the quantities in the tables based on the form's input.
    Note the Public Variables in Module1 which store the value in the Qty and Price fields when the cursor enters them, which are required if someone changes their choice either during or after the initial entry.
     

    Attached Files:

  5. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Eurff... :eek:

    Ok, looking at your db, the immediate difference is that I won't need to deal with any costs and values. My db is purely stock quantities coming in and going out.

    Bundling my way through the code to try and make something work...
    I set the form's OnCurrent event, and the Quantity (issued/receipted) field's AfterUpdate and OnEnter as follows:


    Option Compare Database

    Private Sub Form_Current()
    oldactual = 0
    End Sub

    Private Sub Quantity_AfterUpdate()
    On Error GoTo Eventerror
    Dim rs As Object, SQL As String
    SQL = "SELECT Stock.* " & _
    "FROM Stock " & _
    "WHERE StockID = " & Me.[StockID]
    Set rs = CurrentDb.OpenRecordset(SQL)

    If Me.TransactionType = "Issue" Then
    With rs
    .Edit
    ![Actual] = ![Actual] - (Me.Quantity - oldactual)
    .Update
    .Bookmark = .LastModified
    End With
    End If

    If Me.TransactionType = "Receipt" Then
    With rs
    .Edit
    ![Actual] = ![Actual] + (Me.Quantity - oldactual)
    .Update
    .Bookmark = .LastModified
    End With
    End If

    rs.Close
    Set rs = Nothing

    Exit Sub
    Eventerror:
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description


    End Sub


    Private Sub Quantity_Enter()
    If Not IsNull(Me.Actual) Then oldactual = Me.Actual
    End Sub



    This threw out an error #2465 "Microsoft Office Access can't find the field '|' referred to in your expression."
    I realised the StockID field was not present, so added it to the query behind the form and as a hidden control on the form.

    Next error = error #2424 "The expression you entered has a field, control, or property name that Access can't find."

    I'm wondering if a) I need to Dim "oldactual" ; b) whether entering the TransactionType values in the code as "Issue" and "Receipt" is the right way to go. They don't currently have TransactionTypeIDs as I just added them as values in the table's lookup field rather than creating another table for them; and c) whether a) or b) could be the cause of the error...
     
  6. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Actually, something is working... error message ceased appearing.
    I'm now able to enter a value in the Quantity field, and as long as the TransactionType field has either Issue or Receipt in it, after closing and reopening the form, the Actual figure changes accordingly.

    I'll try putting the code onto a button click instead of the Quantity field's AfterUpdate, and I'll need to follow that by emptying the values from the Quantity and TrasactionType fields, as it currently leaves any entered values in there.
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    As I said when I posted the database, you need the Module called Module1, which declares the oldqty as a Public Variable which can be used anywhere in the database. This is the
    Public oldvalue As Currency, oldqty As Integer
    You also have to ensure that the Field names you use are are accurate for your form.
    Also that you set the public variable in the fields "On Enter" event.
     
  8. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Yep, sorry, you did say. I've added the Module1 now, changed it to oldactual, as per my code, and removed the oldvalue part as I don't need it.

    I added a button to the Heder section, which does some interesting things...
    Currently, if I put a value in Quanity, I then have to enter either Issue or Receipt into the TransactionType or if it already contains an old undeleted value, just click in that field prior to clicking the button. Multiple clicks in fields makes multiple issues or receipts when clicking the button once, so I definitely need to clear those fields out after the transaction to avoid user errors.
    Also, I'll need the button to not just affect 1 record at a time, but all records with a TransactionType and Quantity value.

    Then comes the adding of the date/time stamp.

    Apologies that the thread is becoming a partial sounding-board / self-reminder...
     
  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    That is why the code was in the After Update event, to work with all the records you will probably need a Recordsetclone and handle the oldactual with the code rather than the On Enter event.
    I will also make it very difficult to handle subsequent changes to the Actual.
     
  10. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Is there a better way to do this?!

    In short, I need the user to be able to see what he's intending on issuing / receipting before it happens, so he can change quantities / abandon the transaction / correct errors ("oops, I accidentally marked it Issue when I meant Receipt"). Once he's confirmed it (whether via a single button in the header, or by a per record button, or some other method) the Actual value in the stock table is then changed for the relevant parts, a transaction log query is updated with the date/time of the transaction, and all instances of the TransactionType and Quantity fields on the form are blanked ready for the next transaction.
     
  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    The sort of subform I used in the database I provided will do what you want, it just needs the addition of a "Delete from Basket" command Button for each record.
    The command button would also need to correct the "Stock" by adding back in the Actual that was in the record.
     
  12. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    I'm lost, I'm afraid.
    I can't seem to translate the structure of your database into mine. I clearly need to add a subform for the transactions and then link it to the stock before any of the vba will work as required.
    Might just be Monday morning madness, but I'm not understanding. As far as I can see, your Purchase New form doesn't link to the Order Line form in the way I'll need mine to work.

    ...
     
  13. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    Have you created your subform?
     
  14. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    I'd started off by making a single continuous form, but obviously needed the transactions to be seperate from the stock data (part info and Actual quantity in stock).
    So I created a new version of the form containing 2 subforms (both continuous, but datasheet would be equally viable, if not better, from a design pov) - 1 for the stock and 1 for the transactions. Not sure this is right. Well, it's definitely not set up correctly, as the stock subform isn't showing the full scrollable list of parts.
    I've set the stock subform as continuous so I can include my fiter (nowhere to put the filter boxes on a datasheet...).

    So in short, yes, I've added the subform(s), but I don't know if I can get it to work as required in the way I currently have it set up.
     
  15. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Ok, I had the main form bound to a query, as per the original version of the form - I've now corrected this.
    It's now unbound, and the Stock successfully shows all records. I've added a linking field on the main form, so I can apply a master-child relationship from the Stock subform to the Transactions subform. This is working fine.
    However, I need the Transaction subform to repeat for every item of stock. Presently, it's just visible next to the first line of stock.
    Do I need to place the Transactions subform onto the Stock subform in order for this to work?
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1096989

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice