CODEWITHSUNDEEP
vbams-access
Deke
Deke

Reputation: 495

Copying records in Access VBA

I'm attempting to build a process where you click the EndofDay button which will then go into the subform look for any status of 10 (In Process) and then copy those records before auto completing them. The problem I'm having is getting my DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 ... command set to work properly. I get an error message saying "The action or command "Copy" isn't available now".

The main form is "frmTasks" and the subform is called "Tasks" and the table that "Tasks" uses is called "tblTasks".

UPDATE: I've found if I try to run my original code within the sub form Tasks by itself the code will copy without the "Copy is unavailable" message. If I try to call that code from the main form I get the copy is unavailable again. I'm not sure why it is saying the function copy is unavailable. Any clue as to why?

Private Sub EndofDay_Click()

If Me.Tasks.Form.Status = 10 Then
'Copy In Process Tasks
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70
'Complete Old In process Tasks
Else
MsgBox ("Nothing Done")
'do nothing
End If

End Sub

Updated Code:

Private Sub EndofDay_Click()
Dim rstSource   As DAO.Recordset
Dim rstInsert   As DAO.Recordset
Dim fld         As DAO.Field
Dim lngLoop     As Long
Dim lngCount    As Long

Set rstInsert = Me!Tasks.Form.RecordsetClone
Set rstSource = rstInsert.Clone

With rstSource
  lngCount = .RecordCount
  For lngLoop = 1 To lngCount
    If Nz(!Status.Value, 0) <> 10 Then
      ' Ignore record.
    Else
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              ElseIf .Name = "Start Date" Then
                ' Skip read-only field.
              ElseIf .Name = "Date Completed" Then
                ' Skip read-only field.
              ElseIf .Name = "Owner" Then
                ' Skip read-only field.
              ElseIf .Name = "Active" Then
                ' Skip read-only field.
              ElseIf .Name = "Status" Then
                ' Insert default value.
                rstInsert.Fields(.Name).Value = 0
              Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With
          Next
        .Update
      End With
      .Edit
        !Status.Value = 100
      .Update
    End If
    .MoveNext
  Next
  rstInsert.Close
  .Close
End With

Set rstInsert = Nothing
Set rstSource = Nothing
End Sub

Any help would be greatly appreciated.

Upvotes: 0

Views: 322

Answers (1)

Gustav
Gustav

Reputation: 55831

Could be something like this in your Click event of the EndOfDay button on the main form:

Public Sub CopyRecords()

    Dim rstSource   As DAO.Recordset
    Dim rstInsert   As DAO.Recordset
    Dim fld         As DAO.Field
    Dim lngLoop     As Long
    Dim lngCount    As Long

    Set rstInsert = Me!NameOfSubformControl.Form.RecordsetClone
    Set rstSource = rstInsert.Clone

    With rstSource
      lngCount = .RecordCount
      For lngLoop = 1 To lngCount
        If Nz(!Status.Value, 0) <> 10 Then
          ' Ignore record.
        Else
          With rstInsert
            .AddNew
              For Each fld In rstSource.Fields
                With fld
                  If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or GUID field.
                  ElseIf .Name = "SomeReadOnlyField" Then
                    ' Skip read-only field.
                  ElseIf .Name = "Status" Then
                    ' Insert default value.
                    rstInsert.Fields(.Name).Value = 0
                  Else
                    ' Copy field content.
                    rstInsert.Fields(.Name).Value = .Value
                  End If
                End With
              Next
            .Update
          End With
          .Edit
            !Status.Value = 100
          .Update
        End If
        .MoveNext
      Next
      rstInsert.Close
      .Close
    End With

    Set rstInsert = Nothing
    Set rstSource = Nothing

End Sub

Upvotes: 2

Related Questions