Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Auto fill Orders from a 'Kit' name (Access 2003/SP

    How can I have a 'Kit' auto fill my Order Details table with all of the contents of the kit? For example, I have a box of cardboard displays. One box full has 40 displays in it. The decals for the display are a separate item and I would like it to be automatically filled in with 40 decals. Another size of display has 2 inserts that have to be included, thus 40 displays needs 80 inserts. How can I automatically fill that data in after I enter those products?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto fill Orders from a 'Kit' name (Access 2003/SP

    Are the contents fixed, or should the user be able to modify them after they have been filled in automatically?

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Auto fill Orders from a 'Kit' name (Access 200

    They are fixed.

    I'm not sure how to do this. Currently, I only have a few items like this that I 'think' could all be done just in code, but down the road I will have quite a few 'kit's' that I think I will need to have in a table and have it filled in somehow through primary keys I think.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto fill Orders from a 'Kit' name (Access 200

    What exactly do you want to add to the order details table:

    1) one record with Decals as product and 40 as quantity, or

    2) forty individual records for Decals?

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Auto fill Orders from a 'Kit' name (Access 200

    1) one record with Decals as product and 40 as quantity

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto fill Orders from a 'Kit' name (Access 200

    I would create a table tblKits describing the contents of the kits:

    <table border=1><td align=center>KitID</td><td align=center>Product</td><td align=center>[/b]Quantity[/b]</td><td align=right>1</td><td>Decal</td><td align=right>40</td><td align=right>2</td><td>Insert</td><td align=right>80</td><td align=right>3</td><td>Thingummy</td><td align=right>20</td><td align=right>3</td><td>Widget</td><td align=right>10</td></table>
    If your products have unique IDs, you should use those instead of the product names.
    You can then use code to append the appropriate record(s) to the order details table when you select a kit:

    Dim strSQL As String
    strSQL = "INSERT INTO [Order Details] (OrderID, Product, Quantity) SELECT " & Me.OrderID & " AS OrderID, Product, Quantity FROM tblKits WHERE KitID = " & Me.KitID
    DoCmd.RunSQL strSQL, True

    This assumes that you have a form based on the orders table with an OrderID to identify the order, and a control (for example a combo box) to select a kit.

  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Auto fill Orders from a 'Kit' name (Access 200

    OK, a couple more questions. (Sorry it took so long to get back, I had sick children and couldn't be at work)

    Should this tblKits be related to my tblProducts table? On your example, you have the KitID as 1,2,3,4. Does each item that makes up the kit have a separate ID, or does the whole kit have the same ID with different Product names?

    If I understand your post correctly, I also need a combo box (maybe on the parent form) that I would enter the Kit ID?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto fill Orders from a 'Kit' name (Access 200

    If you look carefully at the example table I posted, you'll see that the third and fourth record both have KitID = 3, but with different Product names (or ProductIDs). A KitID identifies a kit which can have several products. You may want to define a separate table tblKits, with fields KitID (AutoNumber) and KitName (Text). You can use this as row source for a combo box from which the user can select a kit.

  9. #9
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Auto fill Orders from a 'Kit' name (Access 200

    I know the original post on this was over a month ago, but I finally got my auto fill to work like I want it to. I thought I'd post it here in case it can help someone else.

    Private Sub cmdInsertassmt_Click()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String, strSQLInsert As String


    strSQL = "Select ProductID, Quantity FROM tblAssortments WHERE AssortmentID = '" & Me.cboAssortments & "'"

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)

    If rst.EOF = False Or rst.BOF = False Then 'This line just checks to make sure there is at least one record in the recordset
    rst.MoveFirst 'move to the first record. If there is only one record in the assortment, it will simply use the first one, otherwise it will move through the next records and 'use them also.
    While rst.EOF = False
    strSQLInsert = "INSERT INTO [tblTractWorkerOrderDetails] (OrderID, ProductID, Quantity) Select " & Me!OrderID & ", " & rst!ProductID & ", " & rst!Quantity & ""
    'MsgBox strSQLInsert 'use this to check your SQL statement before executing it
    CurrentDb.Execute strSQLInsert, dbFailOnError
    rst.MoveNext
    Wend
    Me.sbfTractWorkerOrderDetails.Requery
    End If


    Set dbs = Nothing
    Set rst = Nothing

    Me.sbfTractWorkerOrderDetails.SetFocus
    DoCmd.GoToRecord , , acNewRec

    End Sub

Posting Permissions

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