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

    Update query and tLookup (Access 2003 SP2)

    I have this code that works fine. Private Sub cboProductID_AfterUpdate()
    Dim strSQLInsert As String

    If Nz(Me.txtQuantity, "") = "" Then Me.txtQuantity = Nz(Me.Parent!txtDefaultQty, "1")

    strSQLInsert = "INSERT INTO tblInternationalOrderDetails (OrderID, ProductID, Quantity)" & _
    "Select " & OrderID & ", ' " & ProductID & " ', ' " & Quantity & " '"
    'MsgBox strSQLInsert 'use this to check your SQL statement before executing it

    End Sub


    Sometimes I have several different 'Assortments' or 'Kits' that go to one customer and there can be one product in several assortments. This code will put the product in 3 times if it is in 3 assortments. I would like it to put it in 1 time and have the quantity set at 3 instead of 3 of the same product and Qty=1 each time.

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

    Re: Update query and tLookup (Access 2003 SP2)

    The SQL string is incomplete and it isn't used for anything except displaying it, so I suspect that you haven't given us the full picture.

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

    Re: Update query and tLookup (Access 2003 SP2)

    Well, you have way more expertise than I do, but when I run it, it sure inserts new records.

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

    Re: Update query and tLookup (Access 2003 SP2)

    There must be something missing. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

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

    Re: Update query and tLookup (Access 2003 SP2)

    <P ID="edit" class=small>(Edited by 79schultz on 20-Mar-08 11:19. )</P>Here it is. I have to leave work now and probably won't be able to respond to any questions or solutions 'til next week.

    Just a quick update before I leave. The following code is where I actually need help on. The previous code I was working with was not the right one.

    Private Sub cmdInsertassmt_Click()

    Dim dbs As DAO.Database
    Dim rstForm As DAO.Recordset
    Dim rstData As DAO.Recordset
    Dim strSQL As String

    Set dbs = CurrentDb

    ' We need to look at the forms records, so get a reference to
    ' its recordset
    Set rstForm = Me!frmInternationalAssortments.Form.RecordsetClone

    ' Loop through the forms records.
    rstForm.MoveFirst
    Do Until rstForm.EOF
    ' Fetch data from tblAssortments based on the current form record.
    strSQL = "Select ProductID, Quantity FROM tblAssortments WHERE AssortmentID = '" & rstForm![AssortmentID] & "'"
    Set rstData = dbs.OpenRecordset(strSQL)

    Do Until rstData.EOF
    ' and now append record using data in rstData
    strSQL = "INSERT INTO [tblInternationalOrderDetails] " & _
    "(OrderID, ProductID, Quantity) SELECT " & Me!OrderID & ", " & rstData!ProductID & ", " & rstData!Quantity * Me.txtDefaultQty & ""
    CurrentDb.Execute strSQL, dbFailOnError
    rstData.MoveNext
    Loop
    rstData.Close
    Set rstData = Nothing

    ' Move to next form record
    rstForm.MoveNext
    Loop

    Set rstForm = Nothing
    Set dbs = Nothing

    Me!sbfInternationalOrderDetails.Requery
    Me!sbfInternationalOrderDetails.SetFocus
    DoCmd.GoToRecord , , acNewRec
    Me!sbfInternationalTotals.Requery
    End Sub
    Attached Files Attached Files

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

    Re: Update query and tLookup (Access 2003 SP2)

    The code that you originally posted doesn't do anything at all, as I suspected. It only assigns a value to a string but it doesn't do anything with that string.
    However, there is other code that does insert records into the tblInternationalOrderDetails table: the On Click event of the cmdInsertassmt button on the main form.
    This code loops through all records of the frmInternationalAssortments subform, and then loops through the products for the assortment and inserts a record for each.
    You'll have to create a totals query instead that groups the products and adds the quantities, and use that to append records.
    I'll see if I can come up with something.

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

    Re: Update query and tLookup (Access 2003 SP2)

    Create a query with the following SQL:

    INSERT INTO tblInternationalOrderDetails ( OrderID, ProductID, Quantity )
    SELECT tblInternationalAssortments.OrderID, tblAssortments.ProductID, Sum([tblAssortments].[Quantity]*[Forms]![frmInternationalOrders]![txtDefaultQty]) AS Quantity
    FROM tblInternationalAssortments INNER JOIN tblAssortments ON tblInternationalAssortments.AssortmentID = tblAssortments.AssortmentID
    GROUP BY tblInternationalAssortments.OrderID, tblAssortments.ProductID
    HAVING (((tblInternationalAssortments.OrderID)=[Forms]![frmInternationalOrders]![OrderID]));

    Save it as qryInsertAssortment.

    Change the code for cmdInsertassmt_Click to

    Private Sub cmdInsertassmt_Click()
    ' Run the append query while suppressing warnings
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryInsertAssortment"
    DoCmd.SetWarnings True
    ' Rest of the code
    Me!sbfInternationalOrderDetails.Requery
    Me!sbfInternationalOrderDetails.SetFocus
    DoCmd.GoToRecord , , acNewRec
    Me!sbfInternationalTotals.Requery
    End Sub

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

    Re: Update query and tLookup (Access 2003 SP2)

    That did it, HansV. Thanks a million!

Posting Permissions

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