Dim i As Integer
Public Sub xxjfshowtitle()
MSF1.Clear
Dim i As Integer
With MSF1
.Cols = 6
.TextMatrix(0, 1) = "学期"
.TextMatrix(0, 2) = "本次交费(元)"
.TextMatrix(0, 3) = "本次欠费(元)"
.TextMatrix(0, 4) = "日期"
.TextMatrix(0, 5) = "操作员"
.ColWidth(0) = 200
.ColWidth(1) = 2200
.ColWidth(2) = 1500
.ColWidth(3) = 1500
.ColWidth(4) = 1500
.ColWidth(5) = 1200
.FixedRows = 1
For i = 1 To 5
.ColAlignment(i) = 6
Next i
.FillStyle = flexFillSingle
.Col = 0
.Row = 0
.RowSel = 1
.ColSel = .Cols - 1
.CellAlignment = 4
.Row = 1
End With
End Sub
Public Sub xxxuefeidata()
Dim stucode As String
Dim mrc As ADODB.Recordset
Dim mrc1 As ADODB.Recordset
'Trim(frmjf2.MSF1.TextMatrix(frmjf2.MSF1.Row, 1))stucode = "1234"txtsql = "select jf.学号,xj.姓名,xj.班级,jf.学期,jf.交费,jf.欠费,jf.日期,jf.操作员 from jf inner join xj on jf.学号=xj.学号 where jf.学号='" & stucode & "' order by jf.日期"
Set mrc = ExecuteSQL(txtsql)
txtsql = "select 学号,sum(交费),sum(欠费) from jf where 学号='" & stucode & "' group by 学号 order by 学号"
Set mrc1 = ExecuteSQL(txtsql)
Dim j As Integer
Dim i As Integer
If mrc.EOF = True Then
MSF1.Clear
Exit Sub
End If
mrc.MoveFirst
With MSF1
.Rows = 3
.Row = 1
Do While Not mrc.EOF
.Rows = .Rows + 1
For i = 0 To 2
Text1(i) = mrc.Fields(i)
Next i
.TextMatrix(.Row, 1) = mrc.Fields(3)
.TextMatrix(.Row, 2) = "¥" & Format(mrc.Fields(4), "0.00")
If Val(mrc.Fields(5)) >= 0 Then
.TextMatrix(.Row, 3) = "¥" & Format(mrc.Fields(5), "0.00")
Else
.TextMatrix(.Row, 3) = "-¥" & Format(-Val(mrc.Fields(5)), "0.00")
End If
.TextMatrix(.Row, 4) = mrc.Fields(6)
.TextMatrix(.Row, 5) = mrc.Fields(7)
.Row = .Row + 1
mrc.MoveNext
Loop
' .MergeCells = flexMergeFree
' .MergeRow(6) = True
'.MergeCol(1) = True
'.MergeCol(2) = True
' .MergeCol(3) = True
' .MergeCol(4) = True
.Row = .Row + 1
.Col = 1
MSF1.CellForeColor = vbRed
.TextMatrix(.Row, 1) = "累计交费:"
.Col = 2
MSF1.CellForeColor = vbRed
.TextMatrix(.Row, 2) = "¥" & Format(mrc1.Fields(1), "0.00") & " 元"
.Col = 4
MSF1.CellForeColor = vbRed
.TextMatrix(.Row, 4) = "累计欠费:"
.Col = 5
MSF1.CellForeColor = vbRed
.TextMatrix(.Row, 5) = "¥" & Format(mrc1.Fields(2), "0.00") & " 元"
End With
End SubPrivate Sub Form_Activate()
xxjfshowtitle
xxxuefeidata
End SubPrivate Sub MSF1_Click()
Text2.Visible = False
MSF1.SetFocus
End SubPrivate Sub MSF1_DblClick()
With MSF1
Dim c As Integer
Dim r As Integer
r = .Row
c = .Col
If c = 1 Or r = .Rows - 1 Or r = .Rows - 2 Then
Dim ss As String
ss = MsgBox("该项不能修改!", vbExclamation + vbOKOnly, " 警告")
Exit Sub
End If
qxstr = Executeqx(3)
If qxstr = "readonly" Then
ss = MsgBox("对不起,你是只读用户不能修改记录,请与管理员联系!", vbInformation + vbOKOnly, " 警告")
Exit Sub
End If
Text2.Top = .Top + .RowPos(r) + 15
Text2.Left = .Left + .ColPos(c) + 25
Text2.Width = .ColWidth(c)
Text2.Height = .RowHeight(r) - 15
Text2.Text = .Text
Text2.SelStart = 0
Text2.SelLength = Len(Text2.Text)
Text2.Visible = True
Text2.SetFocusEnd With
End SubPrivate Sub Text2_KeyPress(KeyAscii As Integer)
Dim shao As Integer
Dim zs As String
If KeyAscii <> 8 And KeyAscii <> 13 Then
shao = InStr(Trim(Text1(Index).Text), ".")
If shao <> 0 Then
zs = Right(Trim(Text1(Index).Text), Len(Trim(Text1(Index).Text)) - shao)
If Len(zs) > 1 Then KeyAscii = 0
End If
End If
Select Case MSF1.Col
Case 2
If KeyAscii = 13 Or KeyAscii = 46 Or KeyAscii > 47 And KeyAscii < 58 Or KeyAscii = 8 Then
Else
KeyAscii = 0
Exit Sub
End If
Case 3
If KeyAscii = 13 Or KeyAscii = 46 Or KeyAscii > 47 And KeyAscii < 58 Or KeyAscii = 8 Then
Else
KeyAscii = 0
Exit Sub
End If
Case 4
If KeyAscii = 13 Or KeyAscii > 47 And KeyAscii < 58 Or KeyAscii = 8 Or KeyAscii = 47 Or KeyAscii = 45 Then
Else
KeyAscii = 0
Exit Sub
End If
End Select
If KeyAscii = 13 Then
Dim str As String
Dim mrc As ADODB.Recordset
Select Case MSF1.Col
Case 2
If InStr(Text2.Text, "¥") Then
str = "update jf set 交费=" & Right(Trim(Text2.Text), Len(Trim(Text2.Text)) - 1) & " where 学号='" & Trim(Text1(0).Text) & "' and 学期='" & Trim(Me.MSF1.TextMatrix(MSF1.Row, 1)) & "'"
Else
str = "update jf set 交费=" & Text2.Text & " where 学号='" & Trim(Text1(0).Text) & "' and 学期='" & Trim(Me.MSF1.TextMatrix(MSF1.Row, 1)) & "'"
End If
Set mrc = ExecuteSQL(str)
Case 3
If InStr(Text2.Text, "¥") Then
str = "update jf set 欠费=" & Right(Trim(Text2.Text), Len(Trim(Text2.Text)) - 1) & " where 学号='" & Trim(Text1(0).Text) & "' and 学期='" & Trim(Me.MSF1.TextMatrix(MSF1.Row, 1)) & "'"
Else
str = "update jf set 欠费=" & Text2.Text & " where 学号='" & Trim(Text1(0).Text) & "' and 学期='" & Trim(Me.MSF1.TextMatrix(MSF1.Row, 1)) & "'"
End If
Set mrc = ExecuteSQL(str)
Case 4
If Not IsDate(Text2.Text) Then '判断是否日期格式
ss = MsgBox("应输入日期 mm-dd-yy", vbInformation + vbOKOnly, "警告")
Text2.SetFocus
Text2.SelStart = 0
Text2.SelLength = Len(Text2.Text)
Exit Sub
End If
str = "update jf set 日期=#" & Trim(Text2.Text) & "# where 学号='" & Trim(Text1(0).Text) & "' and 学期='" & Trim(Me.MSF1.TextMatrix(MSF1.Row, 1)) & "'"
Set mrc = ExecuteSQL(str)
Case 5
str = "update jf set 操作员='" & Trim(Text2.Text) & "' where 学号='" & Trim(Text1(0).Text) & "' and 学期='" & Trim(Me.MSF1.TextMatrix(MSF1.Row, 1)) & "'"
Set mrc = ExecuteSQL(str)
End Select
Text2.Visible = False
xxxuefeidata
End IfEnd Sub
Public Sub xxjfshowtitle()
MSF1.Clear
Dim i As Integer
With MSF1
.Cols = 6
.TextMatrix(0, 1) = "学期"
.TextMatrix(0, 2) = "本次交费(元)"
.TextMatrix(0, 3) = "本次欠费(元)"
.TextMatrix(0, 4) = "日期"
.TextMatrix(0, 5) = "操作员"
.ColWidth(0) = 200
.ColWidth(1) = 2200
.ColWidth(2) = 1500
.ColWidth(3) = 1500
.ColWidth(4) = 1500
.ColWidth(5) = 1200
.FixedRows = 1
For i = 1 To 5
.ColAlignment(i) = 6
Next i
.FillStyle = flexFillSingle
.Col = 0
.Row = 0
.RowSel = 1
.ColSel = .Cols - 1
.CellAlignment = 4
.Row = 1
End With
End Sub
Public Sub xxxuefeidata()
Dim stucode As String
Dim mrc As ADODB.Recordset
Dim mrc1 As ADODB.Recordset
'Trim(frmjf2.MSF1.TextMatrix(frmjf2.MSF1.Row, 1))stucode = "1234"txtsql = "select jf.学号,xj.姓名,xj.班级,jf.学期,jf.交费,jf.欠费,jf.日期,jf.操作员 from jf inner join xj on jf.学号=xj.学号 where jf.学号='" & stucode & "' order by jf.日期"
Set mrc = ExecuteSQL(txtsql)
txtsql = "select 学号,sum(交费),sum(欠费) from jf where 学号='" & stucode & "' group by 学号 order by 学号"
Set mrc1 = ExecuteSQL(txtsql)
Dim j As Integer
Dim i As Integer
If mrc.EOF = True Then
MSF1.Clear
Exit Sub
End If
mrc.MoveFirst
With MSF1
.Rows = 3
.Row = 1
Do While Not mrc.EOF
.Rows = .Rows + 1
For i = 0 To 2
Text1(i) = mrc.Fields(i)
Next i
.TextMatrix(.Row, 1) = mrc.Fields(3)
.TextMatrix(.Row, 2) = "¥" & Format(mrc.Fields(4), "0.00")
If Val(mrc.Fields(5)) >= 0 Then
.TextMatrix(.Row, 3) = "¥" & Format(mrc.Fields(5), "0.00")
Else
.TextMatrix(.Row, 3) = "-¥" & Format(-Val(mrc.Fields(5)), "0.00")
End If
.TextMatrix(.Row, 4) = mrc.Fields(6)
.TextMatrix(.Row, 5) = mrc.Fields(7)
.Row = .Row + 1
mrc.MoveNext
Loop
' .MergeCells = flexMergeFree
' .MergeRow(6) = True
'.MergeCol(1) = True
'.MergeCol(2) = True
' .MergeCol(3) = True
' .MergeCol(4) = True
.Row = .Row + 1
.Col = 1
MSF1.CellForeColor = vbRed
.TextMatrix(.Row, 1) = "累计交费:"
.Col = 2
MSF1.CellForeColor = vbRed
.TextMatrix(.Row, 2) = "¥" & Format(mrc1.Fields(1), "0.00") & " 元"
.Col = 4
MSF1.CellForeColor = vbRed
.TextMatrix(.Row, 4) = "累计欠费:"
.Col = 5
MSF1.CellForeColor = vbRed
.TextMatrix(.Row, 5) = "¥" & Format(mrc1.Fields(2), "0.00") & " 元"
End With
End SubPrivate Sub Form_Activate()
xxjfshowtitle
xxxuefeidata
End SubPrivate Sub MSF1_Click()
Text2.Visible = False
MSF1.SetFocus
End SubPrivate Sub MSF1_DblClick()
With MSF1
Dim c As Integer
Dim r As Integer
r = .Row
c = .Col
If c = 1 Or r = .Rows - 1 Or r = .Rows - 2 Then
Dim ss As String
ss = MsgBox("该项不能修改!", vbExclamation + vbOKOnly, " 警告")
Exit Sub
End If
qxstr = Executeqx(3)
If qxstr = "readonly" Then
ss = MsgBox("对不起,你是只读用户不能修改记录,请与管理员联系!", vbInformation + vbOKOnly, " 警告")
Exit Sub
End If
Text2.Top = .Top + .RowPos(r) + 15
Text2.Left = .Left + .ColPos(c) + 25
Text2.Width = .ColWidth(c)
Text2.Height = .RowHeight(r) - 15
Text2.Text = .Text
Text2.SelStart = 0
Text2.SelLength = Len(Text2.Text)
Text2.Visible = True
Text2.SetFocusEnd With
End SubPrivate Sub Text2_KeyPress(KeyAscii As Integer)
Dim shao As Integer
Dim zs As String
If KeyAscii <> 8 And KeyAscii <> 13 Then
shao = InStr(Trim(Text1(Index).Text), ".")
If shao <> 0 Then
zs = Right(Trim(Text1(Index).Text), Len(Trim(Text1(Index).Text)) - shao)
If Len(zs) > 1 Then KeyAscii = 0
End If
End If
Select Case MSF1.Col
Case 2
If KeyAscii = 13 Or KeyAscii = 46 Or KeyAscii > 47 And KeyAscii < 58 Or KeyAscii = 8 Then
Else
KeyAscii = 0
Exit Sub
End If
Case 3
If KeyAscii = 13 Or KeyAscii = 46 Or KeyAscii > 47 And KeyAscii < 58 Or KeyAscii = 8 Then
Else
KeyAscii = 0
Exit Sub
End If
Case 4
If KeyAscii = 13 Or KeyAscii > 47 And KeyAscii < 58 Or KeyAscii = 8 Or KeyAscii = 47 Or KeyAscii = 45 Then
Else
KeyAscii = 0
Exit Sub
End If
End Select
If KeyAscii = 13 Then
Dim str As String
Dim mrc As ADODB.Recordset
Select Case MSF1.Col
Case 2
If InStr(Text2.Text, "¥") Then
str = "update jf set 交费=" & Right(Trim(Text2.Text), Len(Trim(Text2.Text)) - 1) & " where 学号='" & Trim(Text1(0).Text) & "' and 学期='" & Trim(Me.MSF1.TextMatrix(MSF1.Row, 1)) & "'"
Else
str = "update jf set 交费=" & Text2.Text & " where 学号='" & Trim(Text1(0).Text) & "' and 学期='" & Trim(Me.MSF1.TextMatrix(MSF1.Row, 1)) & "'"
End If
Set mrc = ExecuteSQL(str)
Case 3
If InStr(Text2.Text, "¥") Then
str = "update jf set 欠费=" & Right(Trim(Text2.Text), Len(Trim(Text2.Text)) - 1) & " where 学号='" & Trim(Text1(0).Text) & "' and 学期='" & Trim(Me.MSF1.TextMatrix(MSF1.Row, 1)) & "'"
Else
str = "update jf set 欠费=" & Text2.Text & " where 学号='" & Trim(Text1(0).Text) & "' and 学期='" & Trim(Me.MSF1.TextMatrix(MSF1.Row, 1)) & "'"
End If
Set mrc = ExecuteSQL(str)
Case 4
If Not IsDate(Text2.Text) Then '判断是否日期格式
ss = MsgBox("应输入日期 mm-dd-yy", vbInformation + vbOKOnly, "警告")
Text2.SetFocus
Text2.SelStart = 0
Text2.SelLength = Len(Text2.Text)
Exit Sub
End If
str = "update jf set 日期=#" & Trim(Text2.Text) & "# where 学号='" & Trim(Text1(0).Text) & "' and 学期='" & Trim(Me.MSF1.TextMatrix(MSF1.Row, 1)) & "'"
Set mrc = ExecuteSQL(str)
Case 5
str = "update jf set 操作员='" & Trim(Text2.Text) & "' where 学号='" & Trim(Text1(0).Text) & "' and 学期='" & Trim(Me.MSF1.TextMatrix(MSF1.Row, 1)) & "'"
Set mrc = ExecuteSQL(str)
End Select
Text2.Visible = False
xxxuefeidata
End IfEnd Sub
Public classmodi As Boolean
Public xuefeimodi As Boolean
Public txtsql As String
Public find As Boolean
Public classfind As Boolean
Public xuefeifind As Boolean
Public username As String
Public qxstr As StringPublic Function ExecuteSQL(ByVal sql As String) As ADODB.Recordset
Dim mycon As ADODB.Connection
Dim rst As ADODB.Recordset
Set mycon = New ADODB.Connection
mycon.ConnectionString = connstring
mycon.Open
Dim stokens() As String
On Error GoTo exectuesql_error
stokens = Split(sql)
If InStr("INSER,DELETE,UPDATE", UCase(stokens(0))) Then
mycon.Execute sql
Else
Set rst = New ADODB.Recordset
rst.Open Trim(sql), mycon, adOpenKeyset, adLockOptimistic
Set ExecuteSQL = rst
End If
exectuesql_exit:
Set rst = Nothing
Set mycon = Nothing
Exit Function
exectuesql_error:
Resume exectuesql_exit
End Function
Public Function connstring() As String
connstring = "provider=Microsoft.Jet.OLEDB.4.0;Data source =" & App.Path & "/xs.mdb"End FunctionPublic Function Executeqx(ByVal txt As Integer) As String
Dim sql As String
Dim mycon As ADODB.Connection
Dim rst As ADODB.Recordset
Set mycon = New ADODB.Connection
mycon.ConnectionString = connstring
mycon.Open
Set rst = New ADODB.Recordset
sql = "select admin from use where username='" & username & "'"
rst.Open Trim(sql), mycon, adOpenKeyset, adLockOptimistic
If rst.EOF = True Then
MsgBox "非法用户!", vbExclamation + vbOKOnly, "警告"
Executeqx = "nothing"
Exit Function
End If
If rst.Fields(0) = "y" Then
Executeqx = "admin"
Exit Function
End If
rst.Close
sql = "select readonly from use where username='" & username & "'"
rst.Open Trim(sql), mycon, adOpenKeyset, adLockOptimistic
If rst.Fields(0) = "y" Then
Executeqx = "readonly"
Exit Function
End If
Select Case txt
Case 1
sql = "select qx1 from use where username='" & username & "'"
Case 2
sql = "select qx2 from use where username='" & username & "'"
Case 3
sql = "select qx3 from use where username='" & username & "'"
Case 4
sql = "select qx4 from use where username='" & username & "'"
End Select
On Error GoTo exectuesql_error Set rst = New ADODB.Recordset
rst.Open Trim(sql), mycon, adOpenKeyset, adLockOptimistic
If rst.Fields(0) = "y" Then
Executeqx = "true"
Else
Executeqx = "false"
End If
exectuesql_exit:
Set rst = Nothing
Set mycon = Nothing
Exit Function
exectuesql_error:
Resume exectuesql_exit
End Function