機房收費系統總結——窗體程式碼框架

ZeroWM發表於2013-12-01

一、判斷

1.不需要資料庫

1).是否為空字元

2).是否為數字

3).是否前後相同


通用格式:

If Not  Testtxt(txtUserID.Text) Then 
       MsgBox "請輸入使用者名稱!", vbOKOnly +vbExclamation, "警告"
        txtUserID.SetFocus
        Exit Sub
    End If


Testtxt處可以改寫成IsNumeric(是否為數字的判斷),或者條件也可以寫成 
txtIdentifyPWD.Text  <>txtPWD.Text,大家觸類旁通就好。

2.需要資料庫

1).卡號是否相同

2).卡號是否存在

3).是否有記錄的判斷


二、SQL語句增、刪、改、查


增:

例1:

txtSQL = "select * from User_Info whereLevel='" & Trim(ComboHead.Text) & "'"
set mrc=ExecuteSQL(txtSQL,MsgText)
mrc.addnew

例2:    

 txtSQL = "insert into checkday_Info values('" & ReMainCash & "','" &ReChargeCash & "','" &  ConsumeCash &   "','"& Cancel Cash & "','" & AllCash & "','" &Mydate & "')"
              Set mrc = ExecuteSQL(txtSQL, MsgText)

刪:

例1:     

 txtSQL = "delete from User_Info where UserID='" & Trim(DelUser) & "'"
 Set mrc = ExecuteSQL(DelSQL, MsgText)

改:

例1:

 txtSQL = "select * from User_Info whereLevel='" & Trim(ComboHead.Text) & "'"
 set mrc=ExecuteSQL(txtSQL,MsgText)
 mrc.addnew

例2:   

txtSQL="update student_Info set  cash="& Balance &" where cardno='"& Trim(cardno) &"'"
set mrc=ExecuteSQL(txtSQL,MsgText)

查:

    通過兩個DTPicker控制元件 構成的時間段查詢:

     txtSQL= "select * from CancelCard_Info where Date between '" &CStr(DTPicker1.Value) & "' and'" & CStr(DTPicker2.Value)& "'"


   計算一個資料庫表中記錄數量的查詢:  

 txtSQL= "select Count(*) from OnLine_Info"

 

   對一個表中的某一項求和的查詢:

 txtSQL= "select sum(addmoney) from Recharge_Info where UserID='" &Trim(cmbOperatorName) & "'and    status='未結賬'"


三、顯示方式

1.text

text的caption屬性

2.flexgrid


系統中多采用迴圈的方式顯示flexgrid表格中的內容。
 
With myflexgrid
        .Font = "微軟雅黑"
        .Font.Size = "16"
        .Rows = 1
        .CellAlignment = 4
        .TextMatrix(0, 0) = "卡號"
        .TextMatrix(0, 1) = "充值金額"
        .TextMatrix(0, 2) = "充值日期"
        .TextMatrix(0, 3) = "充值時間"
        .TextMatrix(0, 4) = "充值教師"
        .TextMatrix(0, 5) = "結賬狀態"
    

    While mrc.EOF = False
          .Rows = .Rows + 1
          .CellAlignment = 4
          .TextMatrix(.Rows - 1, 0) = mrc.Fields(2)
          .TextMatrix(.Rows - 1, 1) = mrc.Fields(3)
           .TextMatrix(.Rows - 1, 2) = mrc.Fields(4)
          .TextMatrix(.Rows - 1, 3) = mrc.Fields(5)
          .TextMatrix(.Rows - 1, 4) = mrc.Fields(6)
          .TextMatrix(.Rows - 1, 5) = mrc.Fields(7)
          mrc.MoveNext
    Wend

3.輸出到excel表格

Dim i As Integer
        Dim j As Integer
        '如果有錯誤就統一處理,提示沒有資料匯出  
        On Error Resume Next 
        If myflexGrid.TextMatrix(1, 0) = "" Then
                MsgBox "沒有資料匯出", vbInformation, "提示"
                Exit Sub
        End If
        
        Dim excelApp As Excel.Application
        Set excelApp = New Excel.Application
        Set excelApp = CreateObject("Excel.application")'建立excel物件
        Dim exbooks As Excel.Workbook
        Dim exsheet As Excel.Worksheet
        Set exbook = excelApp.Workbooks.Add'新增新工作簿
        
        excelApp.SheetsInNewWorkbook = 1
        excelApp.Visible = True
        
        With excelApp.ActiveSheet'建立excel工作簿中的行和列
            For i = 1 To myflexGrid.Rows
                For j = 1 To myflexGrid.Cols
                   .Cells(i, j).Value = "" & Format$(myflexGrid.TextMatrix(i - 1, j - 1))
                Next j
            Next i
            
        End With
        
        Set exsheet = Nothing
        Set exbook = Nothing
        Set excelApp = Nothing     


4.list

 List1.AddItem "充值教師:" & MyUserID

5.列印預覽

Report.PrintPreview (True)

6.報表



相關文章