Deleting records from just one side of a join

[patjones]

Hi everyone,

I've been wrestling with this for a couple of days. I'm going to present a simple but equivalent scenario to the one I'm actually dealing with.

Suppose I have two tables: tblEmployees(ERN, last_name, first_name) and tblArchivedData(ERN, earned_date, earned_amount). The archived data table may have (and in practice will have) many dozens of records per employee (same ERN for many records). The employees table will contain just one record for each employee.

Next, suppose I have a bound sub-form in datasheet view, where my object is to display ERN, last_name, first_name, earned_date and earned_amount. I want to display all those records from the archive table.

If I set the datasheet recordsource to be the archive table, I get the ERN, earned_date and earned_amount just fine; but I also want to display the employee's name in the datasheet...even if it's appearing multiple times. When I do this by building a simple query involving a join between tblEmployees and tblArchivedData, and set the datasheet recordsource to that query, it works nicely.

The problem is that if I then cut a record from the datasheet, with the intention of deleting that record from tblArchivedData...it also cuts that employee's record from tblEmployees. I don't want this; tblEmployees is essentially functioning as a look-up table here, and I don't want anything deleted from it.

I sort of circumvented the problem by using DLookup to populate the names in the datasheet. But it's performance prohibitive, and you also lose other simple functionality like the ability to sort the datasheet based on last name.

Any ideas? Thanks.

Pat

[NeoPa]

As far as simply deleting a record from the subform goes, you're sort of stuck with deleting whatever's in the record source I'm afraid.

That said, you may be able to get around it by handling the BeforeDelConfirm event and coding it such that the records otherwise to be deleted are done via specific SQL, and then Cancel the event. Sound like a plan?

[ADezii]

I duplicated your conditions exactly as you have stated, and have not encountered the problem which you have described. Here are the details:

1. tblEmployees and tblArchivedData are involved in a 1 to MANY Relationship in the following manner:
   

   tblEmployees.[ERN](1) ==> tblArchivedData.[ERN](MANY)

2. Cascade Updates and Deletes are enforced on the Join between these Tables.
3. The RecordSource for the Sub-Form is a Query whose SQL is:
   

   SELECT tblArchivedData.ERN, tblEmployees.last_name, tblEmployees.first_name, tblArchivedData.earned_date, tblArchivedData.earned_amount
   FROM tblEmployees INNER JOIN tblArchivedData ON tblEmployees.ERN = tblArchivedData.ERN;

4. Link Child Fields: [ERN]
5. Link Master Fields: [ERN]
6. When I DELETE a Record in the Sub-Form, only that Record is DELETED, and has no effect on the Related Record on the Main Form.

[patjones]

For some reason I keep forgetting about the "Before..." events. It does sound workable. I'll try it this afternoon and let you know how it works out. Thanks.

[patjones]

@ADezii:

There is no related record in the main form. The main form has two date boxes that I type a date range into, which is used to narrow the sub-form recordsource query. But there is no parent-child relationship between the main and sub forms per se.

@NeoPa:

I tried both 'Before Del Confirm' and 'On Delete' with message boxes just to make sure they would fire. 'On Delete' fires, but 'Before Del Confirm' does not. This is the case regardless of whether I delete the record by right-clicking and using Cut, or use Delete in the Access window menu.

[NeoPa]

Sounds ideal Pat. Use the Form_Delete event instead.

@ADezii
Your response deals with a typical use of main and subforms, but if you read the OP carefully you'll see that isn't claimed in this case. It appears all the data is shown exclusively on the subform in this case.

[patjones]

Right, this isn't a typical form/sub-form usage. The end user has a certain combination of needs (including deletion and editing capabilities) that requires this arrangement. Otherwise I would use a list box. Thanks.

I'll try Form_Delete.

[NeoPa]

@Pat
This is a side-issue I know, but if the main form is not bound is there really any need for a subform? The managing controls could possibly be held in the Header section of the form and that would make referencing elements from with the code that much more straightforward.

Just a thought. You obviously know the project better than I do, but I know that's a design which developers often overlook.

[patjones]

I changed my code to use the join method rather than the DLookup method in order to continue this discussion.

The subform's 'On Delete' fires as I mentioned previously, but even when I execute a SQL DELETE specifying just the tblArchivedData part of the record, it still deletes the tblEmployees part of the record also. I think this is because when the user initiates the process, Access deletes the record regardless of what code happens to be associated with the event.

The main form 'On Delete' event does not fire.

I have to say that I completely understand the logic of using the 'Before Del Confirm' event. I could execute my SQL to delete just the one side of the join, then say Cancel = True in order to cancel whatever Access would have done with the delete. But that event isn't firing. Truly maddening!

[NeoPa]

Are you setting Cancel = True in your Form_Delete() event procedure when you delete the records with the specific SQL?

Also, can you confirm whether or not you are staying with the main/sub approach rather than the simpler main as suggested - so we know exactly where we are?

[patjones]

I'm sticking with the main/sub approach.

I thought perhaps that the reason the 'Before...' event wasn't firing is that I did not have the Confirm Record Changes box checked in Access options. The help page says the event won't run if that's the case, so I turned that option on but it still didn't make a difference.

BUT, this works:

Private Sub Form_Delete(Cancel As Integer)
 
CurrentDb.Execute "DELETE FROM dbo_tblArchivedData WHERE dbo_tblArchivedData.ERN = " & Me.txtERN.Value
Cancel = True
 
End Sub

...and the tblEmployees side of my record is preserved. The only problem is that the entire record remains visible in the subform. Doing a Me.Requery gives me Error 3246: Operation not supported in transactions.

[NeoPa]

Pat:
I'm sticking with the main/sub approach.

8-) We're on the same page as to what your design is at least.

Pat:
BUT, this works:

You say that like it weren't what I suggested :-S

Pat:
Doing a Me.Requery gives me Error 3246: Operation not supported in transactions.

Now there you have a real problem. I came across this one the other day on here and frankly I haven't thought of a way around it yet (if there even is one).

If you want to attach a copy so that I can play around with your database and experiment then I'd be happy to look at it for you. The usual suggestions apply for attaching databases (Attach Database (or other work)).

[NeoPa]

I had some further thoughts on this overnight, and it seems that the form itself, understandably, is locking the record(s) it's preparing to delete.

Getting around this will be interesting. Worst case scenario I can think of now is to cancel the Delete then use the Timer to run the SQL very shortly afterwards. I'd need to play for a while and experiment before coming to a conclusion as to the best way to handle though.

[ADezii]

Doing a Me.Requery gives me Error 3246: Operation not supported in transactions.

Wouldn't dynamically changing the RecordSource of the Sub-Form do the trick, after the Deletion?

[NeoPa]

One of the possible ideas to check if/when I get hold of a copy of the database. I'm not convinced the SQL delete even works yet in the current circumstances. All determinable with the database to hand though.

[patjones]

OK, just got in the office...

I was just a little surprised that 'On Delete' worked because it's basically what I was trying to do before. And now suddenly, it works. I'm not complaining, I'm just expressing befuddlement.

This is an Access 2007 database linked to SQL Server tables. Before I go through the trouble of converting it 2003 for you and making the tables local, I want to try a couple of things:

First, ADezii's suggestion of reassigning the recordsource. I think this will work although I feel that it's redundant because it gets assigned when the subform is first populated.

Second, per your Timer suggestion, NeoPa, I want to see if this is just a timing issue. There is an internal transaction taking place and perhaps it simply needs to finish before I can Requery.

Stay tuned. Thanks guys.

[NeoPa]

That's absolutely fine Pat. Do any testing you want first. There's only point in my doing it if you can't. You finding the solution is a far better result at the end of the day ;-)

My thinking ref the Timer approach is that while within the code that processes the Delete requested, the record is likely to be locked. Once that's completed fully, I'm hoping it's possible for code to run with full access to the dataset.

PS. I never read what you said as complaining. Simply as indicating you maybe hadn't understood that it was one of the things I'd suggested earlier in the thread. I've noticed things are easily missed in threads, and sometimes long discussions follow where both sides are communicating at cross-purposes, just to realise eventually it was a simple matter of one party missing something that had already been said much earlier. It's the nature of the game I suppose, but I do try to ensure any such misunderstandings are avoided where possible.

[patjones]

I finally found someone on another board who had precisely the same problem. Allen Browne suggested to him that he set Allow Deletions on the form to No, and put the required deletion code behind a command button. I can't argue with that. I'll post my final code when I have it. Thanks.

[NeoPa]

8-) That's certainly a solution. I'd prefer one that's more intuitive personally, but if Allen Browne (A great Access MVP) suggests that then it certainly confirms finding an intuitive solution isn't straightforward at least. Obviously my preference doesn't (nor should it) count for twopence in this thread.

[patjones]

I agree. I try to develop streamlined forms, with a minimal number of controls. Unfortunately, this project is overdue and I have to go the route of using the button. The code behind the button is:

Private Sub cmdDeleteRecord_Click()
 
On Error GoTo Err_cmdDeleteRecord_Click
 
Dim lngERN As Long: lngERN = Nz(Me.frmReportedData.Form!txtERN, 0)
 
    If lngERN = 0 Then
        MsgBox "Please ensure that the preview window is populated and select a record for deletion.", vbOKOnly + vbExclamation, "Current Record Deletion": Exit Sub
    Else
        If MsgBox("Delete the record for " & Me.frmReportedData.Form!officer_name & "?", vbOKCancel + vbQuestion, "Current Record Deletion") = vbOK Then
            CurrentDb.Execute "DELETE FROM dbo_tblArchivedData WHERE dbo_tblArchivedData.ERN = " & lngERN
            Me.frmReportedData.Form.Requery
        End If
    End If
 
Exit_cmdDeleteRecord_Click:
    Exit Sub
 
Err_cmdDeleteRecord_Click:
    MsgBox err.Number & " " & err.Description, vbExclamation + vbOKOnly, "Current Record Deletion"
    Resume Exit_cmdDeleteRecord_Click
 
End Sub

Thanks again guys for the help!

Pat

[NeoPa]

No worries Pat :-)

It's always more rewarding to help those who help others (Not that other members don't in other ways - but I'm not aware of that).