Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    228

    Unanswered: How to Delete All Data From a Record Except for One Field

    How would I "blank" all data in a record except for one field?
    There are many fields so I don't want to manually delete each field except for the one I want to keep.

  2. #2
    Join Date
    Oct 2003
    Location
    US
    Posts
    343

    Re: How to Delete All Data From a Record Except for One Field

    Try using this function:

    Public Function ClearTable(tblName As String, StartLoc As Integer, EndLoc As Integer) As Boolean
    'Clears a Table from a given column to a given column
    'Column numbers start from 0

    Dim rs As ADODB.Recordset
    Dim i As Integer

    On Error GoTo Local_Err
    Set rs = New ADODB.Recordset
    ' rs.CursorLocation = adUseClient
    rs.Open tblName, CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic, adCmdTableDirect

    Do While Not rs.EOF
    For i = StartLoc To EndLoc
    rs(i) = Null
    rs.Update
    Next i
    rs.MoveNext
    Loop

    End_of_Func:
    ClearTable = True

    Exit_Err:
    Exit Function

    Local_Err:
    Select Case Err.Number
    Case Else
    MsgBox Err.Number & ": " & Err.DESCRIPTION, vbCritical, "Function:ClearTable"
    Resume Exit_Err
    End Select

    End Function



    Originally posted by ottomatic
    How would I "blank" all data in a record except for one field?
    There are many fields so I don't want to manually delete each field except for the one I want to keep.

  3. #3
    Join Date
    Sep 2003
    Posts
    228
    Sorry I'm a nube...
    Where would I put a WHERE clause in there to do this "clear" for only one record? It appears this function clears columns from the entire talbe.

  4. #4
    Join Date
    Sep 2003
    Posts
    228
    Here is the code that exists now...
    I want to modify it so that instead of deleting the reocrd it just clears out all the fields but one:

    'This function will delete the record number that is provided to it from the PCAD Data Table
    Dim MyDb As Database
    Dim RecSet As Recordset
    Dim criteria As String



    Set MyDb = CurrentDb()
    Set RecSet = MyDb.OpenRecordset("PCAD Data Table", dbOpenDynaset)
    criteria = "[PCAD Number] = " & RecNum & ""
    With RecSet
    .FindFirst (criteria)
    If Not .NoMatch Then
    .Delete
    End If
    .Close
    End With

  5. #5
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    Its easy, in the code that I put earlier, instead of opening a whole table opena SQL statement. so in the code where I wrote:

    rs.open tblname ....

    replace it with rsopen strSQL...

    for strSQL you can construct your SQL statement with the where clause.

    Make sure you change the recordset parameters accordingly (the parameters that I defined while opening the recrodset.)

    good luck


    Originally posted by ottomatic
    Here is the code that exists now...
    I want to modify it so that instead of deleting the reocrd it just clears out all the fields but one:

    'This function will delete the record number that is provided to it from the PCAD Data Table
    Dim MyDb As Database
    Dim RecSet As Recordset
    Dim criteria As String



    Set MyDb = CurrentDb()
    Set RecSet = MyDb.OpenRecordset("PCAD Data Table", dbOpenDynaset)
    criteria = "[PCAD Number] = " & RecNum & ""
    With RecSet
    .FindFirst (criteria)
    If Not .NoMatch Then
    .Delete
    End If
    .Close
    End With

  6. #6
    Join Date
    Sep 2003
    Posts
    228
    I thought the following should work but I get a 3020 runtime error, update or cancel update without addnew or edit...


    Dim MyDb As Database
    Dim RecSet As Recordset
    Dim criteria As String
    Dim i As Integer
    Dim startloc As Integer
    Dim endloc As Integer

    Set MyDb = CurrentDb()
    Set RecSet = MyDb.OpenRecordset("PCAD Data Table", dbOpenDynaset)
    criteria = "[PCAD Number] = " & [Form_PCAD Data Form].PCAD_Number & ""
    With RecSet
    .FindFirst (criteria)
    If Not .NoMatch Then
    '.Delete
    startloc = 1
    endloc = RecSet.Fields.Count - 5
    For i = startloc To endloc
    If Not IsNull(RecSet(i)) Then
    RecSet(i) = Null
    .Update
    End If
    Next i
    End If
    .Close
    End With

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •