Mark,
You are so smart, just made a little bit change, the error was gone. Thank you very much.
Now, go back to my main problem that VBA code didn't work anymore.
I copied my code below, would you please look at it, and give me your help? I looked code for a week, still cannot find a way to work out. If you can help me, I am really apprecated.
----------------------------
The code as below:
Function ToNextRecord() As Boolean
Dim req As Boolean
Dim varNum As Variant
' first check for required fields
req = False
'declars and instantiante one connection object and recordset object
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim strConnectString As String
'strConnectStri\strConnectString = "Provider=MSDASQL.1;" & _
"Data Source=MydataSource; Initial Catalog=MyDatabase;" & _
"User ID=bb; PWD=xxxx"
Set cnn = New ADODB.Connection
cnn.ConnectionString = strConnectString
cnn.Open
Set rst = cnn.Execute( _
"Select * from dbo.tblMyTable " & _
"Where (dbo.tblMyTable.ServiceDate) = " & [Forms]![frmSession]![ServiceDate], _
Options:=adCmdText)
Do Until rst.EOF
' Finally check for overlap with existing request
' Try to grab ID - will be Null on unsaved row
varNum = Me.ID
If IsNull(varNum) Then varNum = 0 ' Set dummy value
If Not (IsNull(Me.CmbClient) Or IsNull(Me.ServiceDate) Or IsNull(Me.StartTime) Or IsNull(Me.EndTime)) Then
If Not IsNull((rst!ClientID = Me.CmbClient) And (rst!ServiceDate = Me.ServiceDate) And (rst!StartTime < Me.EndTime) And (rst!EndTime > Me.StartTime) And (rst!ID <> _
varNum) And (rst!Flag2 Is Null) And (Forms!frmSession!CmbFlag2 Is Null)) Then
MsgBox "Database already has a Service that overlaps the Service Time you have entered. Please correct the errors first!", vbCritical, "Service times is not correct"
'vbQuestion vbYesNo
'Cancel = True
StartTime.SetFocus
req = True
'Exit Sub
'End If
End If
Else
MsgBox "Please fill the required fields", vbCritical, "complete data entry form"
End If
rst.MoveNext
Loop
If req = False Then
ToNextRecord = True
Else
ToNextRecord = False
End If
'close and destroy the recordset object and the connecting object
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing
End Function
