Criteria To Return Records From Another Table

[DMT Dave]

Hi Guy's, any advice would be appreciated,



I am looping through the 1st record set to find values which is returning correctly (rs) from tblStock

There are x 2 records with the same MFG values are in another table called tblStorage

So my aim is to lookup and return the records from tblStorage (rs2) that match the loop from the 1st recordset (rs)

I think i know where I am wrong, i have added a strMFG within the 1st loop that returns 2 numbers correctly, I am then setting rs2 based on that strMFG (thinking it may return all records in the 2nd loop) based on the 1st loop

I have tried adding a 3rd recordset (rs3) and added a field called "Delete" in rs3 then set them to true

My eventual goal is

if any zero qty in rs where and sortno <99 then delete ( i have this ready to run commented out) see DoCmd.RunSQL DELETE Line

Then find matching MFG numbers in storage table and delete those too (this is where i need to set rs2 perhaps not based on strMFG ?)

I will add another DELETE SQL for tblStorage once i can return matching records

I can't think of the method to set rs2 to loop through storage table based on results within the loop of stock table ?

Dim rs As DAO.Recordset, rs2 As DAO.Recordset, rs3 As DAO.RecordsetDim iStartQrt As Integer, intSort As Integer
Dim strMSG As String, strMFG As String, strSQL As String, strMSG2 As String


iStartQty = "0"
    intSort = "99"
    
    strSQL = "SELECT tblStock.LiftType, tblStock.StartQty, tblStock.LiftNo, tblStock.SortNo " _
            & "From tblStock " _
            & "WHERE (((tblStock.StartQty)=0) AND ((tblStock.LiftNo) Is Not Null) AND ((tblStock.SortNo)<99));"
            
            
    iRecs = DCount("StartQty", "tblStock", "[StartQty] = " & iStartQty & " And [SortNo] < " & intSort)
    
    Select Case iRecs
        
    Case Is = 0
        
        MsgBox ("There Are No Records To Delete")
        
    Exit Sub
        
    Case Else
        
        Set rs = CurrentDb.OpenRecordset(strSQL)
        
        Do While Not rs.EOF
    
            strMSG = strMSG & rs.Fields("LiftType") & " - " & rs.Fields("LiftNo") & " - " & rs.Fields("StartQty") & vbNewLine
            
            strMFG = rs.Fields("LiftNo")
            
            Debug.Print strMFG
            Debug.Print strMSG
            
            Set rs3 = CurrentDb.OpenRecordset("Select * From tblStorage WHERE MFG = '" & strMFG & "'")
                            
            With rs3
                Do Until rs3.EOF
                .Edit
                !Delete = True
                .MoveNext
                Loop
                
            End With
                
        rs.MoveNext
        
        Loop
        
    If IsNull(DLookup("MFG", "tblStorage", "[MFG] = '" & strMFG & "'")) Then
        
        MsgBox ("THere Is No Record In Storage")
        
    Else
   
    Set rs2 = CurrentDb.OpenRecordset("Select * From tblStorage WHERE MFG = '" & strMFG & "'")
   
        MsgBox ("There Is: " & rs2.RecordCount & " Records In Storage")
        
        Do While Not rs2.EOF
            
            strMSG2 = strMSG2 & rs2.Fields("DelTo") & " - " & rs2.Fields("Town") & " - " & rs2.Fields("PostCode") & " - " & rs2.Fields("MFG") & " - " & _
                rs2.Fields("SL") & " - " & rs2.Fields("Town") & " - " & rs2.Fields("Qty") & vbNewLine
        rs2.MoveNext
        
        Loop
        
        Debug.Print strMSG2
        
        'DoCmd.RunSQL "DELETE * From tblStock " _
            & "WHERE StartQty = " & iStartQty & " And SortNo < " & intSort
        
        
    End If
    
   End Select

[xps35]

I think you are making it way too hard on yourself. If you want to delete records from two tables, all you need is two delete commands:

DELETE * FROM tblStorage
WHERE (((tblStorage.MFG) In (SELECT LiftNo FROM tblStock WHERE (((tblStock.StartQty)=0) AND ((tblStock.LiftNo) Is Not Null) AND ((tblStock.SortNo)<99)))));

DELETE * FROM tblStock
WHERE (((tblStock.StartQty)=0) AND ((tblStock.LiftNo) Is Not Null) AND ((tblStock.SortNo)<99));

[NoellaG]

Hi, first of all: why these loops and not just use delete queries?
Looking at the code I see you declare intSort as integer, but fill it with a string "99"? You declare iStartQrt as integer, but in the code the string iStartQty is used?

[DMT Dave]

Hi guy's thank for spotting mixed variables

Peter, i think i will start again and go down the route of no recordsets and change set warnings to false and just use delete statements like suggested

Kindest

[Minty]

Don't turn warnings off just use

Dim strSQL as String

strSQL = "DELETE * FROM MyTable"

CurrentDb.Execute  strSQL, DbSeeChanges