代碼是這樣的：
其中標注的是這行錯誤 ： Sheets("Sheet1").Label20.Caption = Format(No, "000000")
完整的代碼是這樣的：
Private Sub CommandButton1_Click()
SaveAndPrint
End Sub
Private Function SaveAndPrint()
Dim myObject As Object
Dim strNo, strCircle, strSql As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim bUpdate As Boolean
cn.Provider = "Microsoft.ACE.OLEDB.12.0"
cn.Open (Application.ActiveWorkbook.Path & "\Sample.accdb")
rs.ActiveConnection = cn
a = Format(Date, "yyyy/mm/dd", vbSunday, vbUseSystem)
b = Format(Time, "hh:mm", vbSunday, vbUseSystem)
strSql = "select * from Data"
rs.Open (strSql), , adOpenStatic, adLockPessimistic
strCircle = "0"
If (OptionButton7.Value = True) Then
strCircle = "1"
End If
If (OptionButton8.Value = True) Then
strCircle = "2"
End If
If (OptionButton9.Value = True) Then
strCircle = "3"
End If
If (OptionButton10.Value = True) Then
strCircle = "4"
End If
' If rs.RecordCount > 0 Then
' bUpdate = True
' Else
bUpdate = False
'End If
With Worksheets("Sheet1")
If (bUpdate = True) Then
strSql = "Update Data Set Num =" & "'" & "000002" & "'" & "," & "Name = " & "'" & TextBox1.Text & "'" & "," & "Phone = " & "'" & TextBox4.Text & "'" & "," & "StartTime = " & "'" & TextBox2.Text & ":" & TextBox3.Text & "'" & "," & "EndTime = " & "'" & TextBox5.Text & ":" & TextBox6.Text & "'" & "," & "Circle = " & "'" & TextBox4.Text & "'" & "," & "Drawer = " & "'" & TextBox14.Text & "'" & "," & "Coach = " & "'" & TextBox8.Text & "'" & "," & "DriveSchool = " & "'" & TextBox9.Text & "'" & "," & "DrawDate = " & "'" & TextBox12.Text & "/" & TextBox10.Text & "/" & TextBox11.Text & "'"
cn.Execute strSql
Else
rs.AddNew
rs!Num = Label20.Caption
rs!Name = TextBox1.Text
rs!Phone = TextBox4.Text
rs!StartTime = TextBox2.Text & ":" & TextBox3.Text
rs!EndTime = TextBox5.Text & ":" & TextBox6.Text
rs!Circle = strCircle
rs!Drawer = TextBox14.Text
rs!Coach = TextBox8.Text
rs!DriveSchool = TextBox9.Text
rs!DrawDate = TextBox12.Text & "/" & TextBox10.Text & "/" & TextBox11.Text
rs!Money = TextBox13
rs.Update
End If
End With
No = No + 1
Label20.Caption = Format(No, "000000")
rs.Close
Set cn = Nothing
Set rs = Nothing
With ActiveSheet.PageSetup
.PrintArea = "B4:I19" '//列印區域
.FitToPagesWide = 1 '//頁寬是一頁
.FitToPagesTall = 1 '//頁高是一頁
.PaperSize = xlPaperA4 '//紙張大小
' .Copies = 3
' .PrintZoomColumn = 2
' .PrintZoomRow = 3
' .Orientation = xlLandscape '//橫向列印
End With
Sheets("Sheet1").PrintOut
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox14.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""
TextBox11.Text = ""
TextBox12.Text = ""
TextBox13.Text = ""
End Function
Private Function statistics()
Dim myObject As Object
Dim strTmp, strDate, strSql As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim bUpdate As Boolean
cn.Provider = "Microsoft.ACE.OLEDB.12.0"
cn.Open (Application.ActiveWorkbook.Path & "\Sample.accdb")
rs.ActiveConnection = cn
strDate = Format(Date, "yyyy/mm/dd")
strSql = "SELECT sum(Money) as Acount, count(Money) as Amount,sum(DATEDIFF('n',StartTime,EndTime)) as TotalTime FROM Data where DrawDate = " & "#" & strDate & "#"
rs.Open (strSql), , adOpenStatic, adLockPessimistic
With Worksheets("Sheet1")
Cells(40, 2) = strDate
Cells(40, 3) = rs!Amount
Cells(40, 4) = rs!Acount
Cells(40, 5) = rs!TotalTime
End With
rs.Close
Set cn = Nothing
Set rs = Nothing
End Function
Private Sub CommandButton2_Click()
statistics
End Sub
請教下啊，非常感謝
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/230807.html
標籤：VBA