Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    Unanswered: Creating an ADD button in a shopping basket style database

    Hi there,

    I have two combo boxes. First one is category and the second one Products.
    When you select a category this limits the choice of products depending on the category. That bit works fine. What I would like to do now is have an ADD button which takes the selection from the product combo and store it in a new table/field to create an order list.

    It's basically an order form and I'm trying to allow the user to add products which i'll eventually add the up the price qty etc..
    Many thanks
    Marcusmacman

  2. #2
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    nearly there...?

    Created a macro and used the setValue command to look at the combo and use the data to populate the main field.

    I say nearly there, I need a way of updating the macro or field or something as it just keeps over writing the previous data

  3. #3
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    not quite there at all really...

    I'm basically copying data from 1 field in a table to another field in another table.
    The subform I have set up receives the data ok but I need to increment to position so it copies into the next new record slot. Can this be done with a macro or do I need to start learning VB?

    basically I'm trying to create a shopping list..sounds easy!!

    thanks
    Marcusmacman

    P.S 10hours staring at a computer has sent my eyes funny

  4. #4
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    You could create an append query and use a macro to run it. Another way would be to use a recordset and VBA/SQL to append the record.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Inserting a new row into a table is simple. In your case you could use a little piece of VBA code.

    Let's suppose that you have a Form with 2 comboboxes: Combo_Products and Combo_Categories, a textbox: Text_Quantity, a command button: Command_Add and a subform Child_Purchases that shows the contents of the Tbl_Purchases table.

    In the Click event of the Command_Add button, place this code:
    Code:
    Private Sub Command_Add_Click()
    
        Dim dbs As DAO.Database
        Dim strSQL As String
        
        If Not IsNull(Me.Combo_Products.Value) And Not IsNull(Me.Combo_Categories.Value) And Val(Nz(Me.Text_Quantity, 0)) > 0 Then
            strSQL = "INSERT INTO Tbl_Purchases ( Product, Category, Quantity ) VALUES ( '" & _
                      Me.Combo_Products.Value & "', '" & _
                      Me.Combo_Categories.Value & "', " & _
                      Me.Text_Quantity.Value & " );"
            Set dbs = CurrentDb
            dbs.Execute strSQL, dbFailOnError
            Set dbs = Nothing
            Me.Child_Purchases.Requery
        End If
                
    End Sub
    The line "If Not IsNull..." checks that you have something selected in both combos and a number ( greater than zero) in the Text_Quantity textbox.
    If so, the code creates a dynamic INSERT query and executes it.
    Finally the Child_Purchases subform (which shows the contents of the Tbl_Purchases table) is refreshed (Me.Child_Purchases.Requery) to immediately display the new row in the list.

    In a functional application there would be other values that would be added to several other columns of the table Tbl_Purchases (Customer Id, date of purchase, etc.) but you have the general idea here.
    Have a nice day!

  6. #6
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    thanks Sinndho, I appreciate the effort on the VB code. I'll try to put it in place next week. I did a bit of a temporary work around taking the sample Northwind.mdb as a guide to create my orders. I finally got it to work today.

    I think I need to invest a bit of time understanding VB, it seems to hold the answer to many of my problems.
    cheers
    marcusmacman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •