Deactivate A Microsoft Access Table Record
Then Reuse That Record To Start A New One

Start The Record Deactivation / Archiving Procedure

Click the Deactivate command button to start the deactivation process.

Confirm Or Cancel The Deactivation / Archiving Procedure

Next, the Confirmation message box opens so you can confirm or cancel the procedure. Click on "Yes" to confirm the deactivation/archive operation.

Record Deactivation Code

This code deactivates the current record for archiving.

Private Sub cmdDeactivate_Click()

Dim strSQL As String
Dim ctl As Control
Dim db As DAO.Database
Dim strMsgaction As String

With Me

  DoCmd.SetWarnings False

  strMsgaction = MsgBox("This action with make the Case INACTIVE." & vbCrLf & _
    "The Case can be made ACTIVE only by a DBA" & vbCrLf & _
    "Do you wish to proceed?", vbYesNoCancel)

  If strMsgaction = vbYes Then
    strSQL = "INSERT INTO USysInactive(CaseID, CaseNumber, CaseName) " & _
      "SELECT CaseID, CaseNumber, CaseName " & _
      "FROM tblCase WHERE tblCase.CaseID = " & .CaseID & ";"
    CurrentDb.Execute strSQL, dbFailOnError

    DoCmd.SetWarnings True

    Call FormatForm

  Else
    Exit Sub
  End If

End With

End Sub

Disabling Form Controls

The Deactivate Record button is dimmed to indicate it is now disabled. Controls on the form are disabled and are now have a gray background. background.

Form Formatting Code

This code changes the colors of the controls on the form and disables form controls.

Function FormatForm()

Dim ctl As Control
Dim strSQL As String
Dim intExceptionCount As Integer
Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("z_z_qselCase", dbOpenDynaset)

With Me
  'Use Dcount to determine if there is a matching record
  'in the "inactive" table (a lookup table)
  intExceptionCount = DCount("CaseID", "USysInactive", "USysInactive.CaseID =" & Nz(.CaseID, 0))

  'Loop through controls on the form and enable disable based active or inactive status
  'Text boxes and combo box controls will either be enabled or disabled
  For Each ctl In .Controls
    If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox _
        Or ctl.ControlType = acCheckBox Then

    'There is no matching record in the USysInactive table
    If intExceptionCount = 0 Then
      .AllowEdits = True
      .Controls(ctl.Name).Enabled = True
      lblInactive.Visible = False'Set the Deactivate command button to enabled
      .cmdDuplicate.Visible = False'Set the Duplicate record command button to not visible
      .cmdDuplicate.Enabled = False'Set the Duplicate record command button to disabled

        'Set form text box and combo box colors to indicate enabled state
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
            ctl.BackColor = 16777215
        End If

    ElseIf intExceptionCount > 0 Then     'There is a matching record exits
      .AllowEdits = False          'Make the record inactive by not allowing edits
      .Controls(ctl.Name).Enabled = False     'Make all the controls disabled
      .lblInactive.Visible = True     'The Inactive label is not visible
      .cmdDeactivate.Enabled = False     'The Deactivate button is now diabled
      .cboGoToCase.Visible = True     'The Go To combo box is visible
      .cboGoToCase.Enabled = True     'The Go To combo box is enabled
      .cmdDuplicate.Visible = True     'The Duplicate button is is visible
    .  cmdDuplicate.Enabled = True     'The Duplicate button is is enabled

        'Change the color for controls on the form to indicate disabled state
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
          .BackColor = 15921906

        End If
      End If
    End If

  Next

  End With

If Not rs Is Nothing Then  'Check to make sure the recordset is open
  rs.Close     'Close the recordset
  Set rs = Nothing    'Empty the recordset
End If

Set db = Nothing

End Function

The Duplicate Record Button

The Duplicate record button is initially not visible.

The Duplicate record button becomes visible and active when you confirm the Deactivate record process.

Starting The Duplication Procedure

When you click on the Duplicate record button the New Record dialog opens so you can start a new record.

Duplicate Record Code

This code creates the new record using data from the New Record form and from the original archived record.

Private Sub cmdDuplicate_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field

Dim ctl As Control
Dim intI As Integer
Dim lngID As Long
Dim intValCount As Integer
Dim avarField() As Variant
Dim strSQL As String

Dim qdef As QueryDef
Set db = CurrentDb
Set qdef = db.QueryDefs("z_z_qselCase") 'Change this to your record source!
DoCmd.OpenForm "frm_DB_SYS_NewRecord", , , , , acDialog 'Change this to your form name

  With Me

    lngID = DMax("CaseID", "tblCase") + 1'Find the the highest numbered CaseID in the Case table
    'If there is no Case Name or Number exit
    If GetPar("CASE_NAME") & "" = "" Or GetPar("CASE_NUMBER") & "" = "" Then Exit Sub

      intValCount = qdef.Fields.Count
      ReDim avarField(intValCount)
      'Loop through all records to make certain there are no values in text fields
      For Each fld In qdef.Fields
        If (fld.Name <> "CaseID") And (fld.Name <> "CurrentDate") And _
          (fld.Name <> "Contact1FullName") And (fld.Name <> "CaseName") And _
          (fld.Name <> "CaseNumber") Then
          avarField(intI) = fld.Name
          intI = intI + 1

        End If

      Next

      Set rst = CurrentDb.OpenRecordset("z_z_qselCase")'You will need to change this to your record source
      rst.AddNew'Start a new record
      For Each ctl In .Controls
        Select Case ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acCheckBox

        Case ctl.ControlType = acTextBox
          For intI = 0 To intValCount
          If ctl.Name = avarField(intI) Then
            rst.Fields(avarField(intI)) = ctl.Value

          ElseIf ctl.Name = "CaseName" Then
            rst.Fields("CaseName") = GetPar("CASE_NAME") & ""'Change this to your data name

          ElseIf ctl.Name = "CaseNumber" Then
            rst.Fields("CaseNumber") = GetPar("CASE_NUMBER") & ""'Change this to your data name

          End If

        Next

        Case ctl.ControlType = acComboBox
          For intI = 0 To intValCount

          If ctl.Name = "cbo" & avarField(intI) Then
            rst.Fields(avarField(intI)) = ctl.Value
          End If
        Next

        Case ctl.ControlType = acCheckBox
          For intI = 0 To intValCount

          If ctl.Name = "chk" & avarField(intI) Then
            rst.Fields(avarField(intI)) = ctl.Value
           End If

        Next

    End Select

    Next
    rst.UpDate
    .Requery

    If Not rst Is Nothing Then
      rst.Close
      Set rst = Nothing
    End If

    MsgBox "The new record has been saved"

    FilterOn = True
    Filter = ""
    FilterOn = False

    Filter = "[CaseID]=" & lngID
    FilterOn = True
    .Requery

  End With

End Sub

Managing Archived Records

Use the Active / Inactivate Manager to either deactivate (make inactive/archive) records or to reactivate (make active / remove from archive) records.