vrental_engine.cls

中小型图书会员制租赁管理系统,采用ACCESS数据库。

                                tmpArray(counter - 8 + loop2) = adoRecordset![出版年份]
                        Case 7:
                                tmpArray(counter - 8 + loop2) = adoRecordset![是否可租]
                        Case 8:
                                tmpArray(counter - 8 + loop2) = adoRecordset![剩余库存量]
                    End Select
                Next loop2
            adoRecordset.MoveNext
         Loop
       '------ Start Backward display
      ' For loop1 = 1 To counter
      ' Debug.Print Str(loop1) & ": " & tmpArray(loop1)
       'Next
       loop1 = 0
        adoRecordset.MoveFirst
        countdown = counter
        For loop1 = 1 To counter / 9
             TDM = DoEvents()
             FlexMovies.AddItem ""
                                FlexMovies.TextMatrix(loop1, 8) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMovies.TextMatrix(loop1, 7) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMovies.TextMatrix(loop1, 6) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMovies.TextMatrix(loop1, 5) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMovies.TextMatrix(loop1, 4) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMovies.TextMatrix(loop1, 3) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMovies.TextMatrix(loop1, 2) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMovies.TextMatrix(loop1, 1) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMovies.TextMatrix(loop1, 0) = str(loop1)
                                countdown = countdown - 1
      Next loop1

'---END--- Backward Proceesing (Sort)  完成降序排列
 End Select
adoRecordset.Close
adoConnection.Close
Set adoRecordset = Nothing
Set adoConnection = Nothing
End Sub
Sub Search_Members(FlexMembers As MSFlexGrid, SearchString As String, SearchFields As String, SearchMode As Boolean, SortByFields As String, SortMode As Boolean)
    Dim TDM As Variant
    Dim loop1, loop2 As Long
    Dim mySQL As String
    Dim adoConnection As ADODB.Connection
    Dim adoRecordset As ADODB.Recordset
    Dim connectString As String
    Set adoConnection = New ADODB.Connection
    Set adoRecordset = New ADODB.Recordset
    connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DataBase\MembersDB.mdb;Persist Security Info=False;Jet OLEDB:Database Password=AdmiN"
    adoConnection.Open connectString
   
With FlexMembers
    .ColWidth(0) = 600
    .ColWidth(1) = 1000 ' ID Number
    .ColWidth(2) = 1150 ' Date Entered
    .ColWidth(3) = 1000 ' 会员等级
    .ColWidth(4) = 450 ' 姓氏
    .ColWidth(5) = 700 ' First Name
    .ColWidth(6) = 700 ' Middle Name
    .ColWidth(7) = 1190 ' 生日
    .ColWidth(8) = 600 ' Age
    .ColWidth(9) = 500 ' 性别
    .ColWidth(10) = 1100 ' Civil Status
    .ColWidth(11) = 1450 ' 职业
    .ColWidth(12) = 2100 ' 家庭住址
    .ColWidth(13) = 2400 ' Office/School Address
    .ColWidth(14) = 3500 ' Comments
    .ColAlignment(0) = 5
    .ColAlignment(1) = 5
    .ColAlignment(2) = 5
    .ColAlignment(3) = 5
    .ColAlignment(4) = 5
    .ColAlignment(5) = 5
    .ColAlignment(6) = 5
    .ColAlignment(7) = 5
    .ColAlignment(8) = 5
    .ColAlignment(9) = 5
    .ColAlignment(10) = 5
    .ColAlignment(11) = 5
    .ColAlignment(12) = 2
    .ColAlignment(13) = 5
    .ColAlignment(14) = 5
    
    .TextMatrix(0, 0) = "No."
    .TextMatrix(0, 1) = "ID Number"
    .TextMatrix(0, 2) = "登记时间"
    .TextMatrix(0, 3) = "会员等级"
    .TextMatrix(0, 4) = "姓氏"
    .TextMatrix(0, 5) = "名字1"
    .TextMatrix(0, 6) = "名字2"
    .TextMatrix(0, 7) = "生日"
    .TextMatrix(0, 8) = "年龄"
    .TextMatrix(0, 9) = "性别"
    .TextMatrix(0, 10) = "公民身份"
    .TextMatrix(0, 11) = "职业"
    .TextMatrix(0, 12) = "家庭住址"
    .TextMatrix(0, 13) = "办公/学校地址"
    .TextMatrix(0, 14) = "读者个性留言"
End With
 
'' ---------Start SQL
  If SearchString = "[所有名字]" Or SearchString = "*" Or SearchString = "" Then
     mySQL = "SELECT * from [MembersInfo] ORDER by [" & SortByFields & "]"
  Else
     If SearchMode = True Then
        mySQL = "SELECT * from [MembersInfo] WHERE [" & SearchFields & "] LIKE '%" & SearchString & "%' ORDER by [" & SortByFields & "]"
     Else
        mySQL = "SELECT * from [MembersInfo] WHERE [" & SearchFields & "] = " & """" & SearchString & """" & " ORDER by [" & SortByFields & "]"
     End If
  End If
adoRecordset.Open mySQL, adoConnection, adOpenStatic, adLockOptimistic, adCmdText
'' --------End SQL

If adoRecordset.BOF = True And adoRecordset.EOF = True Then
           MsgBox "没有找到匹配记录", vbInformation, "注意！"
           Exit Sub
End If
Select Case SortMode
Case True:
  '---------- Forward Processing ------------------------------------------
        FlexMembers.Rows = 1
        loop1 = 0
        adoRecordset.MoveFirst
        Do Until adoRecordset.EOF
             TDM = DoEvents()
             loop1 = loop1 + 1
             FlexMembers.AddItem ""
                For loop2 = 0 To 14
                    Select Case loop2
                        Case 0:
                                FlexMembers.TextMatrix(loop1, loop2) = str(loop1)
                        Case 1:
                                FlexMembers.TextMatrix(loop1, loop2) = adoRecordset![ID NUMBER]
                        Case 2:
                                FlexMembers.TextMatrix(loop1, loop2) = adoRecordset![登记时间]
                        Case 3:
                                FlexMembers.TextMatrix(loop1, loop2) = adoRecordset![会员等级]
                        Case 4:
                                FlexMembers.TextMatrix(loop1, loop2) = adoRecordset![姓氏]
                        Case 5:
                                FlexMembers.TextMatrix(loop1, loop2) = adoRecordset![名字1]
                        Case 6:
                                FlexMembers.TextMatrix(loop1, loop2) = adoRecordset![名字2]
                        Case 7:
                                FlexMembers.TextMatrix(loop1, loop2) = adoRecordset![生日]
                        Case 8:
                                FlexMembers.TextMatrix(loop1, loop2) = GetAge(adoRecordset![生日])
                        Case 9:
                                FlexMembers.TextMatrix(loop1, loop2) = adoRecordset![性别]
                        Case 10:
                                FlexMembers.TextMatrix(loop1, loop2) = adoRecordset![Civil Status]
                        Case 11:
                                FlexMembers.TextMatrix(loop1, loop2) = adoRecordset![职业]
                        Case 12:
                                FlexMembers.TextMatrix(loop1, loop2) = adoRecordset![家庭住址]
                        Case 13:
                                FlexMembers.TextMatrix(loop1, loop2) = adoRecordset![办公/学校地址]
                        Case 14:
                                FlexMembers.TextMatrix(loop1, loop2) = adoRecordset![读者个性留言]
                    End Select
                Next loop2
            adoRecordset.MoveNext
         Loop
         'FlexMembers.Rows = FlexMembers.Rows - 1
  '-----------------------------------------------
Case False:
'---START--- Backward Proceesing (Sort)
Dim tmpArray() As Variant
Dim counter, countdown As Long
counter = 0
loop1 = 0
        adoRecordset.MoveFirst
        FlexMembers.Rows = 1
        Do Until adoRecordset.EOF
             TDM = DoEvents()
             loop1 = loop1 + 1
             counter = counter + 15
             ReDim Preserve tmpArray(counter)
             'FlexMembers.AddItem ""
                For loop2 = 0 To 14
                    Select Case loop2
                        Case 0:
                                tmpArray(counter - 14 + loop2) = str(loop1)
                        Case 1:
                                tmpArray(counter - 14 + loop2) = adoRecordset![ID NUMBER]
                        Case 2:
                                tmpArray(counter - 14 + loop2) = adoRecordset![登记时间]
                        Case 3:
                                tmpArray(counter - 14 + loop2) = adoRecordset![会员等级]
                        Case 4:
                                tmpArray(counter - 14 + loop2) = adoRecordset![姓氏]
                        Case 5:
                                tmpArray(counter - 14 + loop2) = adoRecordset![名字1]
                        Case 6:
                                tmpArray(counter - 14 + loop2) = adoRecordset![名字2]
                        Case 7:
                                tmpArray(counter - 14 + loop2) = adoRecordset![生日]
                        Case 8:
                                tmpArray(counter - 14 + loop2) = GetAge(adoRecordset![生日])
                        Case 9:
                                tmpArray(counter - 14 + loop2) = adoRecordset![性别]
                        Case 10:
                                tmpArray(counter - 14 + loop2) = adoRecordset![Civil Status]
                        Case 11:
                                tmpArray(counter - 14 + loop2) = adoRecordset![职业]
                        Case 12:
                                tmpArray(counter - 14 + loop2) = adoRecordset![家庭住址]
                        'Case 13:
                                'tmpArray(counter - 14 + loop2) = adoRecordset![家庭住址]
                        Case 13:
                                tmpArray(counter - 14 + loop2) = adoRecordset![办公/学校地址]
                        Case 14:
                                tmpArray(counter - 14 + loop2) = adoRecordset![读者个性留言]
                    End Select
               Next loop2
            adoRecordset.MoveNext
         Loop
       '------ Start Backward display
       For loop1 = 1 To counter
      '' Debug.Print Str(loop1) & ": " & tmpArray(loop1)
       Next
       loop1 = 0
        adoRecordset.MoveFirst
        countdown = counter
        For loop1 = 1 To counter / 15
             TDM = DoEvents()
             FlexMembers.AddItem ""
                                FlexMembers.TextMatrix(loop1, 14) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMembers.TextMatrix(loop1, 13) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMembers.TextMatrix(loop1, 12) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMembers.TextMatrix(loop1, 11) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMembers.TextMatrix(loop1, 10) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMembers.TextMatrix(loop1, 9) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMembers.TextMatrix(loop1, 8) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMembers.TextMatrix(loop1, 7) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMembers.TextMatrix(loop1, 6) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMembers.TextMatrix(loop1, 5) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMembers.TextMatrix(loop1, 4) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMembers.TextMatrix(loop1, 3) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMembers.TextMatrix(loop1, 2) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMembers.TextMatrix(loop1, 1) = tmpArray(countdown)
                                countdown = countdown - 1
                                FlexMembers.TextMatrix(loop1, 0) = str(loop1)
                                countdown = countdown - 1
      Next loop1
'---END--- Backward Proceesing (Sort)
 End Select
adoRecordset.Close
adoConnection.Close
Set adoRecordset = Nothing
Set adoConnection = Nothing
End Sub
Function Transaction_GetDateDue(ItemCode As String, RefDate As String) As String
    On Error GoTo ErrHandler
    Dim adoConnection As ADODB.Connection
    Dim adoRecordset As ADODB.Recordset
    Dim connectString As String
    Dim RentalPeriod As Integer
    Dim DateDue As Long
    Set adoConnection = New ADODB.Connection
    Set adoRecordset = New ADODB.Recordset

    connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DataBase\CD_Tapes.mdb;Persist Security Info=False;Jet OLEDB:Database password=AdmiN"

    adoConnection.Open connectString
    
    adoRecordset.Open "SELECT RentalPeriod from [CD Tapes Table] WHERE [Item Code] = '" & ItemCode & "'", adoConnection, adOpenStatic, adLockOptimistic, _
           adCmdText
    
    RentalPeriod = ad