Results 1 to 14 of 14
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    Adding Single Records From Another Table

    Hi Guy's, can you help with this one please ?

    I have various amounts of records in tblEdit ie: i may have 6 records with the same postcode and i may only have 1 record for another postcode and 1 record for another and possibly 2 for another postcode



    I am trying to add only 1 record per postcode to another table

    So i may have 86 records in tblEdit and ie: 24 x DelTo so out of 86 records i would have 24 postcodes

    I am wanting to add 24 records to tblPlanning all with different clients and postcodes ?

    Hope this all makes sense

    Code:
    Dim rs As DAO.RecordsetDim rs2 As DAO.Recordset
    Dim ShipDate As Date
    
    
    ShipDate = Me.cboShipmentDateIndex2 'Weekly Shipment Date
    
    
    Set rs = CurrentDb.OpenRecordset("Select * From tblEdit WHERE ShipmentDate = #" & ShipDate & "#") ' GROUP BY tblEdit.PostCode")
    Set rs2 = CurrentDb.OpenRecordset("Select * From tblPlanning")
    With rs2
    rs.MoveFirst
    .AddNew
    !ShipmentDate = ShipDate
    !DelTo = rs.Fields("DelTo")
    !Town = rs.Fields("Town")
    !PostCode = rs.Fields("PostCode")
    .Update
    rs.MoveNext
    .Close
    End With
    
    
    Forms!frmMainMenu!frmIndex2DS.Requery
    
    
    Set rs = Nothing
    Set rs2 = Nothing

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    run an append query:

    sSql = "insert into table2 (field1,field2) values ('" & sVal1 & "','" & sVal2 & "')"
    docmd.run sSql

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    To add to ranman's suggestion you want to use a Select Distinct or a Group By select query to get your unique postcodes values from tblEdit and then use that in your append query.
    Cheers,

  4. #4
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Thank you guy's would you kindly add a string example ?

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please have a look at the attached and see if that is what you had in mind.
    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Thank you Gicu, i have tried running both queries and they come up with "operartion must use an updatebale query"

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    In my sample? I tried them again and they both work OK.
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi Vlad, thank you all worked ok thank you, i need to add to Qty field in tblPlanning where each record that goes from tblEdit to tblPlanning

    So the part that works perfectly is if there is 7 records in tblEdit for PostCode DN99 9DN, only 1 record crosses over which is great, in the Qty field in planing (Set to short text) i want to be able to count the records (7 records for example) and add to the Qty Field: Qty 7

    So currently it's tblEdit:
    Joe Bloggs, HomeTown, DN99 9DN
    Joe Bloggs, HomeTown, DN99 9DN
    Joe Bloggs, HomeTown, DN99 9DN
    Joe Bloggs, HomeTown, DN99 9DN
    Joe Bloggs, HomeTown, DN99 9DN
    Joe Bloggs, HomeTown, DN99 9DN
    Joe Bloggs, HomeTown, DN99 9DN

    tblPlanning:
    Joe Bloggs, HomeTown, DN99 9DN

    I want to Add:
    Joe Bloggs, HomeTown, DN99 9DN, Qty 7

    hope this makes sense ?

    Click image for larger version. 

Name:	Capture.JPG 
Views:	21 
Size:	53.6 KB 
ID:	41062

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here you are Dave, look at the new query qryAddGroupByWithQty.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Thank you so much Vlad, will test in the morning

    Kindest

  11. #11
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    All is good Vlad and works great, I will use as it is by running an append query but if i can change how it works to tidy it up so it automatically updates via recordset then it would tidy up the message "you are about to update records etc"

    So on my MainMenu i have a Tab control where i initially set each page to an index number
    as below, so when i select to go to page 1, can i loop this through a recordset ?

    This is the first part of my tbcMain (tab Control) and you can see where i have pasted the SQL from the query (only pasted it in to show where it would execute "Case 1" so will take that back out until later)

    Code:
    Dim i As Integer
    i = Me!tbcMain.Value
    Me.txtIndexNumber = i
    
    
    Select Case i
    Case 0
    
    
    Me.frmIndex1.Visible = True
    Me.frmIndex2.Visible = False
    Me.frmIndex3.Visible = False
    Me.frmIndex4.Visible = False
    Me.frmIndex5.Visible = False
    Me.frmIndex6.Visible = False
    Me.frmIndex7.Visible = False
    Me.frmIndex8.Visible = False
    Me.frmIndex9.Visible = False
    Me.frmIndex10.Visible = False
    Me.frmIndex11.Visible = False
    Case 1
    
    
    INSERT INTO tblPlanning ( Postcode, DelTo, Town, Qty )
    SELECT tblEdit.Postcode, tblEdit.DelTo, tblEdit.Town, "Qty " & Count([tblEdit]![PostCode]) AS Qty
    FROM tblEdit LEFT JOIN tblPlanning ON (tblEdit.Town = tblPlanning.Town) AND (tblEdit.DelTo = tblPlanning.DelTo) AND (tblEdit.Postcode = tblPlanning.Postcode)
    GROUP BY tblEdit.Postcode, tblEdit.DelTo, tblEdit.Town, tblEdit.ShipmentDate, tblPlanning.Postcode, tblPlanning.DelTo, tblPlanning.Town, tblEdit.Status
    HAVING (((tblEdit.ShipmentDate) = [Forms]![frmMainMenu]![frmIndex2]![cboShipmentDateIndex2]) And ((tblPlanning.PostCode) Is Null) And ((tblEdit.Status) = "Planning" Or (tblEdit.Status) = "On Hold" Or (tblEdit.Status) = "Friday" Or (tblEdit.Status) = "Monday"))
    ORDER BY tblEdit.DelTo;
    
    
    Me.frmIndex1.Visible = True
    etc etc....

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101

    Post

    Dave,

    Not sure what you mean by "updates via recorset". And not quite sure why do you need table tblPlanning at all when you can use a select query instead that is always accurate. You realize that your append query in its current form will add a new PostCode to tblPlanning but it will not update the quantities after the initial add. I don't know your db or business rules so maybe that is OK.

    In any case, to run that in VBA with no prompts you can use Docmd.SetWarnings False, Docmd.OpenQuery "qryYourAppend", DoCmd.SetWarnings True or you can use CurrentDb.Execute with dbFailOnError.
    Because your query has a parameter (cboShipmentDateIndex2) to use that with Execute you will need resolve that prior to running the execute command. Or you can Execute the SQL staement like this:

    Code:
    Select Case i
    Case 0
    
    
    
    
    Me.frmIndex1.Visible = True
    Me.frmIndex2.Visible = False
    Me.frmIndex3.Visible = False
    Me.frmIndex4.Visible = False
    Me.frmIndex5.Visible = False
    Me.frmIndex6.Visible = False
    Me.frmIndex7.Visible = False
    Me.frmIndex8.Visible = False
    Me.frmIndex9.Visible = False
    Me.frmIndex10.Visible = False
    Me.frmIndex11.Visible = False
    Case 1
    Dim strSQL as string
    strSQL= "INSERT INTO tblPlanning ( Postcode, DelTo, Town, Qty ) " & _
                 "SELECT tblEdit.Postcode, tblEdit.DelTo, tblEdit.Town, "Qty " & Count([tblEdit]![PostCode]) AS Qty " & _
                 "FROM tblEdit LEFT JOIN tblPlanning ON (tblEdit.Town = tblPlanning.Town) AND (tblEdit.DelTo = tblPlanning.DelTo) AND (tblEdit.Postcode = tblPlanning.Postcode) " & _
                 "GROUP BY tblEdit.Postcode, tblEdit.DelTo, tblEdit.Town, tblEdit.ShipmentDate, tblPlanning.Postcode, tblPlanning.DelTo, tblPlanning.Town, tblEdit.Status " & _
                 "HAVING tblEdit.ShipmentDate = #" & [Forms]![frmMainMenu]![frmIndex2]![cboShipmentDateIndex2] & "# And tblPlanning.PostCode Is Null And tblEdit.Status = "Planning" " & _
                 "Or tblEdit.Status = "On Hold" Or tblEdit.Status = "Friday" Or tblEdit.Status = "Monday" ORDER BY tblEdit.DelTo;"
    
    
    CurrentDb.Execute strSQL, dbFailOnError
    Me.frmIndex1.Visible = True
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi Vlad, thank you for your reply, I guess if I can continue to run the append query by DoCmd.OpenQuery if I can add set warnings to false, is this from your post 12 the way to do it ?

    Docmd.SetWarnings False, Docmd.OpenQuery "qryYourAppend", DoCmd.SetWarnings True

    If so, I have a lack of understanding why SetWarnings is false before running the query then true after running the query ?

    I will try it though because at the moment i run the query via AfterUpdate on me.cboShipmentDateIndex2 and would change the event to Case 1 on my main menu form

    Even though the Append Query is based on that shipment date, I can instruct the cboShipmentDate to be the same date as the page before (Case 0) that would work in conjuction

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Dave,
    You "silence" the warnings with SetWarnings False, run the action query (that otherwise would raise the "You are about to append/update/delete .....") then you reset the warnings so any future warnings/errors will raise the proper messages.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 04-06-2018, 02:29 PM
  2. Adding multiple records to a table using a single button
    By pjordan@drcog.org in forum Queries
    Replies: 8
    Last Post: 04-14-2017, 03:05 PM
  3. Replies: 11
    Last Post: 02-02-2017, 10:31 AM
  4. Replies: 3
    Last Post: 06-25-2013, 10:57 AM
  5. Replies: 5
    Last Post: 12-04-2011, 10:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums