Delete record from one table if it does not exist in another 1

[StevenFromSouth]

I am having problems debugging this code.

Private Sub Form_Load()

Dim delsql As String
Dim UnMatchedQry As String
Dim rst As DAO.Recordset

UnMatchedQry = "SELECT Purchases.PID" & _
                " FROM Purchases LEFT JOIN PurchaseDetail ON Purchases.[PID] = PurchaseDetail.[PID] " & _
                " WHERE (((PurchaseDetail.PID) Is Null));"

Set rst = CurrentDb.OpenRecordset(umq)

delsql = "DELETE FROM Purchases" & _
            " WHERE PID =" & rst!PID & ""

If rst.RecordCount = 0 Then
        'Cancel = True
Else
    MsgBox "Cleaning up Tables....", vbInformation, "Initalizing DB"
    CurrentDb.Execute (delsql )
End If
End Sub

If the record exist in one table and not in the other it work fine but if the the record don't exist in either table I'm getting Error 3021 No Current Record. I want to just continue without prompts If there are no Unmatched Records.

 

[Andrzejek]

If you get more than one 'unmatched' PID, your code will delete only first one.
If your UnMatchedQry select returns no records, you get your error.

You may either do:

Set rst = CurrentDb.OpenRecordset([red]UnMatchedQry[/red])

If rst.RecordCount = 0 Then
        'Cancel = True
Else[blue]
    delsql = "DELETE FROM Purchases WHERE PID = " & rst!PID [/blue]
    MsgBox "Cleaning up Tables....", vbInformation, "Initalizing DB"
    CurrentDb.Execute (delsql)
End If

or...

delsql = " DELETE FROM Purchases " & _
         " WHERE PID [red]NOT IN[/red] (SELECT PID " & _
         " FROM PurchaseDetail)"
CurrentDb.Execute (delsql)

or... (and I would suggest to do this
Prevent this situation in the first place: create Primary Key / Foreign Key relation between the two tables.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[StevenFromSouth]

or... (and I would suggest to do this
Prevent this situation in the first place: create Primary Key / Foreign Key relation between the two tables.

Yeah I know. I have a Main form with a subform on it, what happens is that sometimes when I move from the main from to the subform ms access crashes and the ID is left in the Purchase Table and not in the PurchaseDetail table.

 

[Andrzejek]

Were any other solutions helpful

Living high-life in Scarborough...?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[StevenFromSouth]

delsql = " DELETE FROM Purchases " & _
         " WHERE PID NOT IN (SELECT PID " & _
         " FROM PurchaseDetail)"
CurrentDb.Execute (delsql)

This takes very long to execute sometimes even freezing Access. The other one works fine but as you said it only deletes one. How to delete all the unmatched records instead of just the first one?

 

[Andrzejek]

How many records do you have in [tt]Purchases[/tt] and in [tt]PurchaseDetail[/tt] tables?
And how many of those records do you (usually) have to Delete?
Unless you have hundreds of thousands of records, this Delete should take less than a moment.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[StevenFromSouth]

I only have to delete the one record that is created in the Purchases table when Access crashes. Using the "NOT IN" method takes a while just to delete that one record.

 

[Andrzejek]

No answers to any of my previous questions... :-(
Another way to fix the issue with your 'Access crashing' - and creating the record that you have to delete - would be to: Insert a record in Purchases and in PurchaseDetail tables (at the same time) when the first record is entered into PurchaseDetail table. This way you will never have a Purchases records without PurchaseDetail record(s).
Would that work?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[StevenFromSouth]

No answers to any of my previous questions... sad

I did answer. I said just the 1 record in the Purchases table but not in the PurchaseDetail table. It is generated when I open the Purchase form for Data Entry and Access crashes. The ID was not passed from the Main form to subform which is the PurchaseDetail recordset.

 

[Andrzejek]

No, I asked how many records do you have in these 2 tables. Not: how many records do you want to Delete.
The reason is, if you have millions of records in the 2 'un-related' tables (no PK-FK relations) and you want to use the Delete statement mentioned before, that's why it takes forever to do so. It should take no time at all if your data base is designed properly.

And it looks like your main issue is "Access crashes", that's no good. I would investigate this problem first.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

Another way to approach it is to 'collect' all needed IDs and then Delete them:

Private Sub Form_Load()

Dim delsql As String
Dim UnMatchedQry As String
Dim rst As DAO.Recordset
Dim strDelIDs As String

UnMatchedQry = " SELECT PID " & _
               " FROM Purchases " & _
               " WHERE PID NOT IN (Select PID From PurchaseDetail)"

Set rst = CurrentDb.OpenRecordset(UnMatchedQry)

If rst.RecordCount = 0 Then
        'Cancel = True
Else
    MsgBox "Cleaning up Tables....", vbInformation, "Initalizing DB"
    With rst
        Do Dhile NOT .EOF
            If Len(strDelIDs) = 0 Then
                strDelIDs = !PID
            Else
                strDelIDs = strDelIDs & ", " & !PID
            End If
            .MoveNext
        Loop
    End With
    
    delsql = "DELETE FROM Purchases" & _
            " WHERE PID IN (" & strDelIDs & ")"

    CurrentDb.Execute (delsql )
End If

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[StevenFromSouth]

I'll try it out and see how it goes.

Yes the Access crashing is something that I need to address and been trying to do for a couple of weeks now. On a combobox in the subform I am using the Find As You Type code provided by MajP Link Sometimes when the combobox gets focus Access freezes as crash.

 

[Andrzejek]

Access freezes as crash" - I would hope you have an error handler to detect the error (any error) and take care of it and not allow Access to crash in the "Find As You Type" code in combobox ...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[StevenFromSouth]

The code works. I just changed this

UnMatchedQry = " SELECT PID " & _
               " FROM Purchases " & _
               " WHERE PID NOT IN (Select PID From PurchaseDetail)"

to this

UnMatchedQry = "SELECT Purchases.PID" & _
                " FROM Purchases LEFT JOIN PurchaseDetail ON Purchases.[PID] = PurchaseDetail.[PID] " & _
                " WHERE (((PurchaseDetail.PID) Is Null));"

The "NOT IN" took too long to execute.

I would hope you have an error handler to detect the error (any error) and take care of it and not allow Access to crash in the "Find As You Type" code in combobox

As soon as the combobox gets focus Access crashes. I've tried debugging but nothing comes up. Where exactly can I put the error handler? On the second or third try it does not crash though.

 

[Andrzejek]

What's the name of your combobox?
What events do you have for it: _Click(), _GetFocus(), _Change() ?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

You can always set your VBA editor to Break on All Errors to see where the issue is.
Tools - Options... - General (tab) - Error Trapping (frame)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson