I'm trying to make the following code to count the number or rows containing 21:00 - 22:00 in my access table, when the user writes a date in the text box txtDate.
However, when doing so I'm getting syntax error messages. What may I be dong wrong here?
Private Sub txtDate_Change()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Application.ScreenUpdating = False
Application.EnableEvents = False
If Me.txtDate.Value <> "" Then
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
"Data Source=D:\V\C.accdb;"
Set rst = New ADODB.Recordset
strSQL = "SELECT COUNT[Name]FROM tblA WHERE [Name] = '21:00 - 22:00';"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
Me.txtOne.Value.CopyFromRecordset rst
'End If
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Private Sub txtDate_Change()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Application.ScreenUpdating = False
Application.EnableEvents = False
If Me.txtDate.Value <> "" Then
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
"Data Source=D:\V\C.accdb;"
Set rst = New ADODB.Recordset
strSQL = "SELECT COUNT([Name])FROM tblA WHERE [Name] = '21:00 - 22:00';"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
Me.txtOne.Value = rst!
End If
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
The code now counts the total rows for the 21:00 - 22:00 mentioned in the code. As you've been repeatedly saying how could I change the line so that the code would count the text for the date mentioned in the txtDate?
Private Sub txtDate_Change()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Application.ScreenUpdating = False
Application.EnableEvents = False
If Me.txtDate.Value <> "" Then
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
"Data Source=D:\V\C.accdb;"
Set rst = New ADODB.Recordset
strSQL = "SELECT COUNT([Name]) FROM tblA WHERE [Name] = '21:00 - 22:00';"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
Me.txtOne.Value = rst(0)
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub