clear all filds in a record, except the primary key

[markjkubicki]

I've added a command button to a form that allows the end user to delete all entries in that record (and it's related tables) EXCEPT the primary key field.


My intent is to replace the field entries with their default value (when one has been defined) or otherwise with 'null' (as they would be in a new record.)

The record consists of MANY fields of many various data types. The problem 'might' be that many, if not most of the fields do not have an assigned default value. (In some instances, there is a default declared at record entry on the form.)
In an earlier version of the code, I had simply saved the value of the primary key; deleted the record; created a new record and assigned the value of the old primary key to the new record. This seemed to be award and problematic (not certain it wasn't my lack of skill. NTL). So I thought I could deal with it in a more direct way: just wipe all of the entries from the existing record (code below.)
(Don't think it matters, but also know that there is also a constant onslaught of new fields and related tables periodically being added to the record / table.)

It is these null default values that are tossing errors. (I think)

Any thoughts, suggestions, ... will be greatly appreciated ins advance

Private Sub cmdClearEntries_Click()
    Dim strfieldname As String
    Dim varDefaultValue As Variant
    Dim strTable As String
    Dim strType As String
    On Error GoTo err_cmdClearEntries_Click

    strprompt = "This action will REMOVE ALL detail entries for this fixture type" _
        & vbCrLf _
        & vbCrLf & "DO YOU WANT TO PROCEED ?"
    gsMsgTitle = "CLEAR ENTRY"
    gsMsgResponse = MsgBox(strprompt, vbCritical + vbYesNo + vbDefaultButton2, gsMsgTitle)
    
    If gsMsgResponse = vbYes Then
        DoCmd.SetWarnings False
        
        strTable = "tbeFixtureTypeDetails"
        strType = Me.Type
        Set rs = CurrentDb.OpenRecordset(strTable)
        With rs
            For n = 0 To .Fields.Count - 1
                If .Fields(n).Name <> "type" Then
                    strfieldname = .Fields(n).Name
                    varDefaultValue = Nz(.Fields(n).DefaultValue, Null)
                    Debug.Print strfieldname & "  :  " & varDefaultValue
                    gsSQL = "update " & strTable & _
                        " Set " & strfieldname & " = " & varDefaultValue & _
                        " WHERE " & strTable & ".type = '" & strType & "';"
                    CurrentDb.Execute gsSQL, dbFailOnError
                End If
            Next 'n
            .Close
        
        End With
        Set rs = Nothing
    
        Dim rel As DAO.Relation
        DoCmd.SetWarnings False
        For Each rel In CurrentDb.Relations
            With rel
                If .Table = "tbeFixtureTypeDetails" Then
                    strSQL = "delete * from " & .Table & " where ([type] = '" & vType & "')"
                    DoCmd.RunSQL strSQL
                End If
            End With
            DoCmd.SetWarnings True
        Next
    Else
        Exit Sub
    End If

exit_cmdClearEntries_Click:
    DoCmd.SetWarnings True
    Exit Sub

err_cmdClearEntries_Click:
    DoCmd.SetWarnings True
    MsgBox Err.Description
    Resume exit_cmdClearEntries_Click
End Sub

[moke123]

Why keep the PKey?

It is these null default values that are tossing errors. (I think)

What kind of errors?

[June7]

Set DefaultValue property with empty string, not Null. However, that will not remove entries from fields. Set the field Value property to Null.

Using Nz() to provide Null as alternate value for Null is redundant.

[xps35]

I think you were on the right track by deleting and re-creating the record. It will work If you assign default values in the table design for each field that needs one. Also enable cascade delete on all the relations with related tables.

[markjkubicki]

in order of response:


- I keep thinking its a good idea to keep the primary key since the intention is to only delete the details of that record, not the record itself (ex: keep item #123, but delete all of the (customizing) details the end user is associating to it; ...start over.
the reality is that if I save the PK value, delete the record and start over... there is nothing stopping me

- the error is syntax error in update statement (the varDefaultValue returns blank(?) resulting in the error; assigning "" (in lieu of the null() returned) will not work as some of the fields are not text, they could be boolean, date, number...)

tried changing code to be:
( Set " & strfieldname & " = " & null....) previously: (Set " & strfieldname & " = " & varDefaultValue....) <--no luck

- I think your right about the delete and replace being a much more straight forward approach, but at this point I'm in a wormhole of fixation. And while in the end, i may go that way, in the process, there are always lessons to be learned

[Micron]

I'm with moke123. If there's no data, what good is the pk, which as we know, is not supposed to be meaningful data anyway? If there were related records, they're certainly are none after all the field data has been wiped out.

there is also a constant onslaught of new fields and related tables periodically being added to the record / table.

That seems quite strange, and maybe even an indication of a bigger problem.

[Welshgasman]

Null is a word, NOT a variable ?

Set " & strfieldname & " = NULL " & 

[Micron]

Null is a word, NOT a variable ?

Set " & strfieldname & " = NULL " & 

something + null = something
something & null = null
So if you wanted to convert "apples" to null you could use & but I wouldn't use that form. I'd SET something = null

[moke123]

something + null = something
something & null = null

Other way around, isn't it?

[Micron]

You are correct - I even tested first and this is what I had, but brain released gas and I wrote it wrong.
?9+null
Null
?9 & null
9

[June7]

Assigning "" to DefaultValue just empties the property, fields/controls will not populate with empty string. However, my misunderstanding - not setting DefaultValue but trying to pull data from DefaultValue property. This is unusual.

[orange]

Can you expand on why deleting the records involved (PK and all) might be problematic?

Seems the PK would be for identifying details --so no details, no need for the PK --but you know your situation better than readers.

[moke123]

In an earlier version of the code, I had simply saved the value of the primary key; deleted the record; created a new record and assigned the value of the old primary key to the new record.

This leads me to believe your not using an autonumber for your PKey.

The problem 'might' be that many, if not most of the fields do not have an assigned default value.

That should not be a problem although you would need to deal with them in your code under certain circumstances. Nz() is your friend.

[Welshgasman]

something + null = something
something & null = null
So if you wanted to convert "apples" to null you could use & but I wouldn't use that form. I'd SET something = null

This is what I meant, the concatenation is all wrong