×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
I am having problems debugging this code.

CODE --> VBA

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.

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

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:

CODE

Set rst = CurrentDb.OpenRecordset(UnMatchedQry)

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

or...

CODE

delsql = " DELETE FROM Purchases " & _
         " WHERE PID NOT IN (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

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

(OP)

Quote (Andrzejek)

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.

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

Were any other solutions helpful ponder

Living high-life in Scarborough...?
smile

---- Andy

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

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

(OP)

CODE --> vba

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?

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

How many records do you have in Purchases and in PurchaseDetail 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

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

(OP)
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.

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

No answers to any of my previous questions... sad
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

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

(OP)

Quote (andrzejek)

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.

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

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

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

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

CODE

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

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

(OP)
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.

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

"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 ... ponder

---- Andy

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

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

(OP)
The code works. I just changed this

CODE --> vba

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

to this

CODE --> vba

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.

Quote:

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.

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

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

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close