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