Deleting records in a subform triggered by after update event 1

[khwaja]

I am using an after update event to clear few fields in the main form as well as the records in the sub form. It works well only to the point where fields in the main form are cleared and and only the first record in the sub form (continuous form). I guess I need to use code to delete all records in the sub form relevant to the main record instrad ofclearing them. Not sure what code to use. Will appreciate some help. I am using following code:

Private Sub Department_AfterUpdate()
Me!Position.Requery
Me!Position = Me.Position.Column(0, 0)
Me!frmRelationships.Form!RPosition.Requery
Me!frmRelationships.Form!RPosition = Null
Me!frmDReports.Form!DReports.Requery
Me!frmDReports.Form!DReports = Null
End Sub

Cheers

AK

Note: Using Access 97 - still.

 

[RickSpr]

Yes, you need to use code to delete the subform records. But you can do it with a single statement, more or less. It would be something like this:
DoCmd.RunSQL &quot;DELETE * FROM <subform table> &quot; _
& &quot;WHERE <subform child link field> = &quot; _
& &quot;'&quot; & <subform master link field> & &quot;'&quot;
Omit the apostrophe strings in the last line, if the link field is numeric.
After deleting the records you should requery the subform control.

Warning: If the user clicks the Undo button after you have deleted the subform records, the changes made in the main form will be backed out, but the records will not be restored in the subform. Access can not restore the records because they were deleted by VBA code.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[khwaja]

Thanks Rick. What would be the syntax when referring to child/master link field. I interpreted it as under:

DoCmd.RunSQL &quot;DELETE * FROM frmRelationships &quot; _
& &quot;WHERE JobID = &quot; _
& &quot;'&quot; & JobID
But this comes with an error 3075 which is syntax error. I guess I need to add main and sub for identifiers? Changing this to following did not help.

DoCmd.RunSQL &quot;DELETE * FROM frmRelationships &quot; _
& &quot;WHERE frmRelationships.Form!JobID = &quot; _
& &quot;'&quot; & frmMain!JobID
Can you please help?

Cheers

AK

Note: Using Access 97 - still.

 

[khwaja]

Rick, I figured out! I made a mistake of not showing the table in the code. After fixing this, it works fine. Thank you for your help. My revised syntax is as under forbenefit of others.

DoCmd.RunSQL &quot;DELETE * FROM tblRelationships &quot; _
& &quot;WHERE JobID = JobID&quot;

Cheers

AK

Note: Using Access 97 - still.

 

[RickSpr]

Eeeek!!! No!!!!

Khwaja, I'm so sorry I couldn't get back to you sooner. I hope you haven't lost any data.

&quot;JobID = JobID&quot; is always True. This will delete everything in the table!

It should be
DoCmd.RunSQL &quot;DELETE * FROM tblRelationships &quot; _
& &quot;WHERE JobID = &quot; & JobID

Your 3075 syntax error occurred because you only deleted the last apostrophe. I had to delete the apostrophe strings if the link field is numeric.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[khwaja]

Thanks a lot Rick. I changed it but surprisingly it was working fine using the code you have suggested to amend. One question though, after deleting rows, I could still see the #Deleted written in fields and it does not disappear until I move to a new record or press shift F9. There are three fields in the sub form which are deleted. Two are combo boxes which I requery using the following code immidiately after the delete code and third is a free text field.

Me!frmRelationships.Form!RPosition.Requery

It would be much tidier if whole thing initialises in some way. I have turned the warning off prior to the code so that gets rid of warning.

Cheers

AK

Note: Using Access 97 - still.

 

[RickSpr]

You should requery your subform:
Me!frmRelationships.Form.Requery

If you do that, I don't think you'll need to requery the combo boxes.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[khwaja]

Many thanks. It worked.

Cheers

AK

Note: Using Access 97 - still.

 

[khwaja]

Rick

Any idea, instead of clearing individual fields, what is the best way to clear values from all fields in a record after being triggered by on change event? I am assuming that auto numbers will remain unafected. I need this so as to ensure that user re enters info when a new department is selected.

Cheers

AK

Note: Using Access 97 - still.

 

[RickSpr]

You shouldn't do this in the Change event, because the user may have hit a wrong key by mistake. Wait until the AfterUpdate event. That way they can Undo a mistake without losing all the field data.

There's no function to clear all the fields (except AutoNumbers) in a record, but you could take a shortcut by either:
1. looping through the form's Controls collection, and setting each editable control to Null, or
2. looping through the form recordset's Fields collection, and setting each non-AutoNumber field to Null.

Here's code to do the latter (works in Access 97 or later):

    Dim rst As DAO.Recordset, fld As DAO.Field

    If Not Me.NewRecord Then
        Set rst = Me.RecordsetClone
        rst.Bookmark = Me.Bookmark
        For Each fld In rst.Fields
            If (fld.Attributes And dbAutoIncrField And dbSystemField) = 0 Then
                fld = Null
            End If
        Next fld
        Set rst = Nothing
    End If

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[khwaja]

Thanks Rick. I tried. there is a runtime error 3020 referring to Update or CanceUpdate without AddNew or Edit. Following syntax is highlighted.

fld = Null

Cheers

AK

Note: Using Access 97 - still.

 

[RickSpr]

My mistake. Revised code follows:

    Dim rst As DAO.Recordset, fld As DAO.Field

    If Not Me.NewRecord Then
        Set rst = Me.RecordsetClone
        rst.Bookmark = Me.Bookmark
        rst.Edit
        For Each fld In rst.Fields
            If (fld.Attributes And dbAutoIncrField And dbSystemField) = 0 Then
                fld = Null
            End If
        Next fld
        rst.Update
        Set rst = Nothing
    End If

Note: Depending on your form's Record Locking property, you may get an error on the rst.Edit line. If you do, we'll have to switch to looping through the Controls collection, which will be a little more complicated.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[khwaja]

Thanks Rick.There is no error on rst.edit line but there is another runtime error 3164, Field can't be updated.



Cheers

AK

Note: Using Access 97 - still.

 

[RickSpr]

Rats! Guess we'll have to do the control code then.

For <Department>, substitute the name of the control that causes the fields to be cleared.
For <RecordID>, substitute the name of the control that contains your AutoNumber field.

Private Sub <Department>_AfterUpdate()
    Dim ctl As Control

    On Error GoTo ErrorHandler
    If Not Me.NewRecord Then
        For Each ctl In Me.Controls
            If ctl.Name <> &quot;<Department>&quot; _
            And ctl.Name <> &quot;<RecordID>&quot; _
            And (ctl.ControlType = acTextBox _
              Or ctl.ControlType = acListBox _
              Or ctl.ControlType = acComboBox _
              Or ctl.ControlType = acOptionGroup) Then
                ctl.Value = Null
            End If
        Next ctl
    End If
ErrorExit:
    Exit Sub
ErrorHandler:
    MsgBox &quot;Runtime error (&quot; & Err.Number & &quot;) clearing '&quot; & ctl.Name & &quot;':&quot; _
        & vbCrLf & Err.Description
    Resume ErrorExit
End Sub

If this gives you an error, tell me what kind of control it fails on (its name is given in the message) and, if you know it, why it can't be cleared.

This logic only attempts to clear text boxes, list boxes, combo boxes, and option groups. Are there any other kinds of controls we might need to clear? (Controls inside options groups don't count.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[khwaja]

Thanks Rick. When I sibstituted my department and autonumber field, there was a runtime error (2448) clearing 'JobId' : You cannot assign value to this object. But then I realised that I had removed the quotes around the JobID field and there was no error after I added these quotes back. But the main problem is that whole record disappears/deleted as soon as I move to next record.

Cheers

AK

Note: Using Access 97 - still.