Checking If Extra Data Added

[DMT Dave]

Hi Guy's, i am trying to find a method of checking if data exists in tblEdit and it Doesn't Exist in tblRoutes



So i use the following to add next weeks planning

Importing the week

dtShipdate = Format(Me.cboWeek, "mm/dd/yyyy")



strSQL = "INSERT INTO tblRoutes ( DelTo, Town, PostCode ) " _
        & " SELECT tblEdit.DelTo, tblEdit.Town, tblEdit.PostCode " _
        & "From tblEdit " _
        & "GROUP BY tblEdit.ShipmentDate, tblEdit.DelTo, tblEdit.Town, tblEdit.PostCode " _
        & "HAVING (((tblEdit.ShipmentDate)= #" & dtShipdate & "#));"
        
        
    
    DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    
    Forms!frmPlanning!frmPlanningDS.Requery
    
    
    Set rs = CurrentDb.OpenRecordset("Select * From tblRoutes")
    With rs
    
    Do Until rs.EOF
        .Edit
        strPC = rs.Fields("PostCode")
        
        If Not IsNull(iDrop = DLast("DelNo", "tblAssign", "[PostCode] = '" & strPC & "'")) Then
            iDrop = DLast("DelNo", "tblAssign", "[PostCode] = '" & strPC & "'")
            Else
            iDrop = "0"
        End If
        
        iQty = DCount("PostCode", "tblEdit", "[PostCode] = '" & strPC & "' And [ShipmentDate] = #" & dtShipdate & "#")
        
        If Not IsNull(dtLastDate = DLast("DeliveryDate", "tblAssign", "[PostCode] = '" & strPC & "'")) Then 'Checking We have delivered to before
            dtLastDate = DMax("DeliveryDate", "tblAssign", "[PostCode] = '" & strPC & "'")
            Else
            dtLastDate = Format(Now(), "dd/mmm/yyyy")
        End If
        
        .Fields("DelDate") = dtLastDate
        .Fields("DayName") = Format(.Fields("DelDate"), "dddd")
        .Fields("DelNo") = iDrop
        .Fields("Qty") = iQty
        
        .Update
        .MoveNext
    Loop


End With


Forms!frmPlanning!frmPlanningDS.Requery

Now Looking for the method for:

After updating tblRoutes, there maybe addition items added in tblEdit

I now want to check tblEdit if Postcode Exists (someone else has added items) in tblEdit that doesn't exist in tblRoutes within the shipment week (dtShipDate)

it doesn't matter if there are additional for the same postcode (Abort or Exit Sub)

it does matter if the postcode (records added) for a different postcode

Hope this makes sense

[DMT Dave]

Just thinking, it may be easy to re-import week but, made changes to fields so would prefer to add extra than a new import

[DMT Dave]

Got this one

Dim strPC As String, strSQL As StringDim rs As DAO.Recordset
Dim dtShipDate As Date


dtShipDate = Format(Me.cboWeek, "mm/dd/yyyy")


strSQL = "SELECT tblEdit.DelTo, tblEdit.Town, tblEdit.PostCode, tblEdit.ShipmentDate " _
        & "FROM tblEdit LEFT JOIN tblRoutes ON tblEdit.[PostCode] = tblRoutes.[PostCode] " _
        & "WHERE (((tblRoutes.PostCode) Is Null) AND ((tblEdit.ShipmentDate)= #" & dtShipDate & "#));"