no value given for one or more required parameters (1 Viewer)

[dodyryda]

I was hoping someone may be able to help me.. I'm v.new to vba and I'm trying to create a error handler that reconnects the database when it detects the connection has dropped.. I have this working on my main switchboard but having a bit of trouble with a calendar control..

The code is below. It first goes to the function GetBusinessDay which exits correctly but then it continues to run at the line
Set rs = Me.Recordset.Clone even though I have manually dropped the connection. do I need to retest for a connection to force an error before this line.. if so how would I do it??..

what I'm getting at the mo is a pop up with no value given for one or more required parameters

Private Sub Calendar5_Click()

Dim rs As ADODB.Recordset
On Error GoTo ProcError

Me![JD] = Me!Calendar5.Value
Me![NBD] = GetBusinessDay([JD], 1, "23456", 1, "Holidays", "Holiday Dates")

'figure i need to test connection here as it continues to run below????????????????

Set rs = Me.Recordset.Clone
If rs.BOF And rs.EOF Then 'No Records in DB
  rs.AddNew
  rs![Job Date] = Me![JD]
  rs![NextBusinessDay] = Me![NBD]
  rs.Update
  Me.Requery
  rs.MoveLast
  Me.Bookmark = rs.Bookmark
Else
  rs.MoveFirst
  rs.Find "[Job Date] = #" & Format(Me![JD], "dd/mm/yyyy") & "#"
  If rs.BOF Or rs.EOF Then
    rs.AddNew
    rs![Job Date] = Me![JD]
    rs![NextBusinessDay] = Me![NBD]
    rs.Update
    Me.Requery
    rs.MoveLast
    Me.Bookmark = rs.Bookmark
    Else
    Me.Bookmark = rs.Bookmark
   End If
End If

    
'CLEAN UP MEMORY AT END
If Not rs Is Nothing Then
   rs.Close
   Set rs = Nothing
End If

ExitProc:
   Exit Sub
ProcError:
Select Case Err.Number
Case -2147467529
DoCmd.OpenForm "Microsoft Office Access"
Forms![Microsoft Office Access].message = "Connection Error, Please wait for reconnection"
If Form_Switchboard.i = 0 Then
Me.TimerInterval = 120000
Form_Switchboard.i = 1
End If
Resume ExitProc
Case 11
DoCmd.OpenForm "Microsoft Office Access"
Forms![Microsoft Office Access].message = "Connection Error, Please wait for reconnection"
If Form_Switchboard.i = 0 Then
Me.TimerInterval = 120000
Form_Switchboard.i = 1
End If
Resume ExitProc
Case 17
DoCmd.OpenForm "Microsoft Office Access"
Forms![Microsoft Office Access].message = "Connection Error, Please wait for reconnection"
If Form_Switchboard.i = 0 Then
Me.TimerInterval = 120000
Form_Switchboard.i = 1
End If
Resume ExitProc
Case 30006
DoCmd.OpenForm "Microsoft Office Access"
Forms![Microsoft Office Access].message = "Connection Error, Please wait for reconnection"
If Form_Switchboard.i = 0 Then
Me.TimerInterval = 120000
Form_Switchboard.i = 1
End If
Resume ExitProc
Case 0
DoCmd.OpenForm "Microsoft Office Access"
Forms![Microsoft Office Access].message = "Connection Failure, Please wait for reconnection"
If Form_Switchboard.i = 0 Then
Me.TimerInterval = 120000
Form_Switchboard.i = 1
End If
Resume ExitProc
Case Else
MsgBox "Error: " & Err.Number & ". " & Err.Description
Resume ExitProc
End Select

End Sub

 

[dodyryda]

I've tried testing the connection with the following code
but even though the network connection is disable the adstateOpen is still at 1. Is there another way to test?

If CurrentProject.Connection.State = adStateOpen Then