1012

動態報表查詢系統
1.編號查詢報表
2.姓名查詢報表
3.瀏覽資料報表
4.區間報表
5.日報表
6.月報表
7.季報表
                   sql                              VBA                            printSetup
主表單 ===========database=========報表種類===========列印

sql預存程序
CREATE DEFINER=`root`@`localhost` PROCEDURE `interdata`(IN `id1` VARCHAR(50), IN `id2` VARCHAR(50))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
select * from scoredata where 學號 between id1 and id2;
END


   Private Sub CommandButton1_Click()
    Dim id1 As String
    Dim id2 As String
 
    If TextBox1.Text <> "" And TextBox2.Text <> "" Then
        id1 = TextBox1.Text
        id2 = TextBox2.Text
     
        sql = "call interdata('" & id1 & "','" & id2 & "')"
        Call UserForm5.loaddata(sql)           '跨表單呼叫
    Else
        MsgBox "請輸入完整資料"
    End If
 
End Sub
=======================================

Private Sub CommandButton10_Click()
On Error Resume Next
    'Dim ws As Worksheet
    'ws.name = "report"
    'Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
    Set shreport = Worksheets("report")
     
    If shreport Is Nothing Then
        Sheets.Add After:=Sheets(Sheets.Count)   '加在最後面
        Sheets(Sheets.Count).name = "report"
    Else
        MsgBox "此報表已經存在"
        Application.DisplayAlerts = False
        Sheets("report").Delete
    End If
End Sub    
===================================
 Private Sub CommandButton11_Click()
On Error Resume Next
    Dim sbt As String
 
    sbt = CommandButton11.Caption
    If sbt = "報表二" Then
 
        Set shreport = Worksheets("report")
         
        If shreport Is Nothing Then
            Sheets.Add After:=Sheets(Sheets.Count)   '加在最後面
            Sheets(Sheets.Count).name = "report"
        End If
        CommandButton11.Caption = "關閉"
    Else
        MsgBox "此報表已經存在"
        Application.DisplayAlerts = False
        Sheets("report").Delete
        CommandButton11.Caption = "報表二"
    End If

End Sub