Cmd Button Does not work on first click

[TitaniaTiO2]

I have a command button on a form that does not work on the first click. If
I pause and click a second time it works perfectly. What am I missing?

Thanks for the help
Titania

Code behind button:
Private Sub cmdClose_Click()

On Error GoTo ErrorHandler

Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim i As Integer
Dim WasAdded As Boolean
Dim stDocName As String
Dim stLinkCriteria As String

Set ctl = Forms!frmAssignSopsToEmployee.lstboxSOPSToChooseFrom

WasAdded = False
' open a recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblTraining")

'loop thru the items in the list box
For i = 0 To ctl.ListCount - 1
If ctl.Selected(i) Then
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblTraining")
rs.AddNew
rs!PersonID = Me.cmbEmployeeName
rs!DocumentNumber = ctl.Column(0, i)
rs.Update

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblHistory")
rs.AddNew
rs!DocumentNumber = ctl.Column(0, i)
rs.Update

ctl.Selected(i) = False 'clears the selection
WasAdded = True

End If


Next i

If WasAdded Then



Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click
End If




stDocName = "frmSOPHistory"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Here:
On Error Resume Next
rs.Close
Set rs = Nothing
Exit Sub

ErrorHandler:
MsgBox Err.Number & vbCrLf & Err.Description
Resume Exit_Here

End Sub

 

[Piet Linden]

I have a command button on a form that does not work on the first click.  If
I pause and click a second time it works perfectly.  What am I missing?

Thanks for the help
Titania

Code behind button:
Private Sub cmdClose_Click()

  On Error GoTo ErrorHandler

  Dim rs As DAO.Recordset
  Dim ctl As Control
  Dim varItem As Variant
  Dim strSQL As String
  Dim i As Integer
  Dim WasAdded As Boolean
  Dim stDocName As String
  Dim stLinkCriteria As String

 Set ctl = Forms!frmAssignSopsToEmployee.lstboxSOPSToChooseFrom

  WasAdded = False
  ' open a recordset
  Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblTraining")

  'loop thru the items in the list box
  For i = 0 To ctl.ListCount - 1
    If ctl.Selected(i) Then
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblTraining")
      rs.AddNew
      rs!PersonID = Me.cmbEmployeeName
      rs!DocumentNumber = ctl.Column(0, i)
      rs.Update

      Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblHistory")
      rs.AddNew
      rs!DocumentNumber = ctl.Column(0, i)
      rs.Update

      ctl.Selected(i) = False  'clears the selection
      WasAdded = True

    End If

  Next i

  If WasAdded Then

Exit_cmdClose_Click:
 Exit Sub

Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
  End If

    stDocName = "frmSOPHistory"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Here:
  On Error Resume Next
  rs.Close
  Set rs = Nothing
  Exit Sub

ErrorHandler:
  MsgBox Err.Number & vbCrLf & Err.Description
  Resume Exit_Here

End Sub

Why are you opening an already open recordset? Why are you not
closing the first recordset and then opening the second? there's no
rs.Close for the first recordset. If you're just appending records,
the way to do it is

Set rs=CurrentDB.OpenRecordset("TABLENAME",dbOpenTable,dbAppendOnly)

 

[TitaniaTiO2]

Thanks. I have made the update, but the command button is still not working
on the first click.

Any suggestions?

Thanks

Titania