form1.frm

vb+sql 用于公交点钞结算和报表结合的相关软件

    End If
End Sub

Private Sub Command4_Click()

End Sub

Private Sub cmdAdd_Click()
    Dim cmd As New ADODB.Command
    With cmd
        .ActiveConnection = cnn
        .CommandText = "AMC_INSERT_DEPT_INFO"
        .CommandType = adCmdStoredProc
        .CommandTimeout = 0
        .Parameters.Append .CreateParameter("@DEPT_NO", adDouble, adParamInput, 4, CDbl(Trim(txtDeptNo)))
        .Parameters.Append .CreateParameter("@Name", adVarChar, adParamInput, 40, Trim(txtDeptName))
        .Parameters.Append .CreateParameter("@Address", adVarChar, adParamInput, 60, Trim(txtAddress))
        .Parameters.Append .CreateParameter("@TEL", adVarChar, adParamInput, 20, Trim(txtTel))
        .Execute
    End With
    Call DeptDisplay
End Sub

Private Sub cmdDel_Click()
    If MsgBox("确认要删除 {" + mshflexgrdCompany.TextMatrix(mshflexgrdCompany.Row, 1) + "}" + mshflexgrdCompany.TextMatrix(mshflexgrdCompany.Row, 2) + "吗？", vbYesNo + vbInformation, "提示...") = vbYes Then
        Dim cmd As New ADODB.Command
        With cmd
            .ActiveConnection = cnn
            .CommandText = "AMC_DELETE_DEPT_INFO"
            .CommandType = adCmdStoredProc
            .CommandTimeout = 0
            .Parameters.Append .CreateParameter("@DEPT_NO", adDouble, adParamInput, 4, CDbl(Trim(txtDeptNo)))
            .Execute
        End With
        Call DeptDisplay
    End If
    
End Sub

Private Sub Command1_Click()
    
End Sub

Private Sub cmdExit_Click()
    Unload Me
End Sub

Private Sub cmdHlp_Click()
    SendKeys "{F1}"
End Sub

Private Sub cmdReWrite_Click()
    If MsgBox("确认要修改 {" + mshflexgrdCompany.TextMatrix(mshflexgrdCompany.Row, 1) + "}" + mshflexgrdCompany.TextMatrix(mshflexgrdCompany.Row, 2) + "吗？", vbYesNo + vbInformation, "提示...") = vbYes Then
        Dim cmd As New ADODB.Command
        With cmd
            .ActiveConnection = cnn
            .CommandText = "AMC_UPDATE_DEPT_INFO"
            .CommandType = adCmdStoredProc
            .CommandTimeout = 0
            .Parameters.Append .CreateParameter("@DEPT_NO", adDouble, adParamInput, 4, CDbl(Trim(txtDeptNo)))
            .Parameters.Append .CreateParameter("@Name", adVarChar, adParamInput, 40, Trim(txtDeptName))
            .Parameters.Append .CreateParameter("@Address", adVarChar, adParamInput, 60, Trim(txtAddress))
            .Parameters.Append .CreateParameter("@TEL", adVarChar, adParamInput, 20, Trim(txtTel))
            .Execute
        End With
        Call DeptDisplay
    End If
End Sub

Private Sub Command2_Click()

End Sub

Private Sub Form_Load()
    blnOkFlag = False
    Call DeptDisplay
    Call LineDisplay
    Call BusDisplay
    Call OpratorDisplay
    cmbSex.ListIndex = 0
    cmbCompanyLine.ListIndex = 0
    cmbCompanyWorker.ListIndex = 0
    Left = 0
    Top = 0
    Caption = Left(sstabBk.Caption, 4)
End Sub

Private Sub mshflexgrdBus_Click()
    Dim rs As New ADODB.Recordset
    rs.Open "Select * from zy_bus_info where bus_no='" + mshflexgrdBus.TextMatrix(mshflexgrdBus.Row, 1) + "'", cnn, adOpenStatic, adLockOptimistic
    If Not rs.EOF Then
        txtBusNo = rs.Fields("bus_no")
        txtLineNobus = rs.Fields("line_no")
'        txtModelNo = IIf(IsNull(rs.Fields("model_no")), "", rs.Fields("model_no"))
        txtBusID = IIf(IsNull(rs.Fields("bus_id")), "", rs.Fields("bus_id"))
        txtbusFrame = IIf(IsNull(rs.Fields("frame_no")), "", rs.Fields("frame_no"))
        dtpBuyDate.Value = IIf(IsNull(rs.Fields("buy_date")), Now, rs.Fields("buy_date"))
        '= getDeptName(mshflexgrdBus.TextMatrix(mshflexgrdBus.Row, 3))
    End If
End Sub

Private Sub mshflexgrdBus_EnterCell()
    Dim rs As New ADODB.Recordset
    rs.Open "Select * from zy_bus_info where bus_no='" + mshflexgrdBus.TextMatrix(mshflexgrdBus.Row, 1) + "'", cnn, adOpenStatic, adLockOptimistic
    If Not rs.EOF Then
        txtBusNo = rs.Fields("bus_no")
        txtLineNobus = rs.Fields("line_no")
'        txtModelNo = IIf(IsNull(rs.Fields("model_no")), "", rs.Fields("model_no"))
        txtBusID = IIf(IsNull(rs.Fields("bus_id")), "", rs.Fields("bus_id"))
        txtbusFrame = IIf(IsNull(rs.Fields("frame_no")), "", rs.Fields("frame_no"))
        dtpBuyDate.Value = IIf(IsNull(rs.Fields("buy_date")), Now, rs.Fields("buy_date"))
        '= getDeptName(mshflexgrdBus.TextMatrix(mshflexgrdBus.Row, 3))
    End If
End Sub

Private Sub mshflexgrdCompany_Click()
    Dim rs As New ADODB.Recordset
    
    rs.Open "Select * from zy_dept_info where dept_no='" + mshflexgrdCompany.TextMatrix(mshflexgrdCompany.Row, 1) + "'", cnn, adOpenStatic, adLockOptimistic
    If Not rs.EOF Then
        txtDeptNo = rs.Fields("dept_no")
        txtDeptName = rs.Fields("name")
        txtAddress = IIf(IsNull(rs.Fields("AddRess")), "", rs.Fields("AddRess"))
        txtTel = IIf(IsNull(rs.Fields("Tel")), "", rs.Fields("Tel"))
    End If
End Sub

Private Sub mshflexgrdCompany_EnterCell()
    Dim rs As New ADODB.Recordset
    
    rs.Open "Select * from zy_dept_info where dept_no='" + mshflexgrdCompany.TextMatrix(mshflexgrdCompany.Row, 1) + "'", cnn, adOpenStatic, adLockOptimistic
    If Not rs.EOF Then
        txtDeptNo = rs.Fields("dept_no")
        txtDeptName = rs.Fields("name")
        txtAddress = IIf(IsNull(rs.Fields("AddRess")), "", rs.Fields("AddRess"))
        txtTel = IIf(IsNull(rs.Fields("Tel")), "", rs.Fields("Tel"))
    End If
End Sub

Private Sub mshflexgrdLine_Click()
    Dim rs As New ADODB.Recordset
    On Error Resume Next
    rs.Open "Select * from zy_line_info where line_no='" + mshflexgrdLine.TextMatrix(mshflexgrdLine.Row, 1) + "'", cnn, adOpenStatic, adLockOptimistic
    If Not rs.EOF Then
        txtLineNo = rs.Fields("line_no")
        txtlineName = rs.Fields("line_name")
        txtSStation = IIf(IsNull(rs.Fields("S_Station")), "", rs.Fields("s_Station"))
        txtEStation = IIf(IsNull(rs.Fields("e_Station")), "", rs.Fields("e_Station"))
        cmbCompanyLine = getDeptName(mshflexgrdLine.TextMatrix(mshflexgrdLine.Row, 3))
    End If
End Sub

Private Sub mshflexgrdLine_EnterCell()
    Dim rs As New ADODB.Recordset
    
    rs.Open "Select * from zy_line_info where line_no='" + mshflexgrdLine.TextMatrix(mshflexgrdLine.Row, 1) + "'", cnn, adOpenStatic, adLockOptimistic
    If Not rs.EOF Then
        txtLineNo = rs.Fields("line_no")
        txtlineName = rs.Fields("line_name")
        txtSStation = IIf(IsNull(rs.Fields("S_Station")), "", rs.Fields("s_Station"))
        txtEStation = IIf(IsNull(rs.Fields("e_Station")), "", rs.Fields("e_Station"))
        cmbCompanyLine = getDeptName(mshflexgrdLine.TextMatrix(mshflexgrdLine.Row, 3))
    End If
End Sub

Private Sub mshflexgrdOperator_Click()
    Dim rs As New ADODB.Recordset
    rs.Open "Select * from zy_worker_info where op_no='" + mshflexgrdOperator.TextMatrix(mshflexgrdOperator.Row, 1) + "'", cnn, adOpenStatic, adLockOptimistic
    If Not rs.EOF Then
        txtOpNo = rs.Fields("op_no")
        txtOpName = rs.Fields("name")
        txtOpTel = IIf(IsNull(rs.Fields("tel")), "", rs.Fields("tel"))
        txtOpAddress = IIf(IsNull(rs.Fields("Address")), "", rs.Fields("Address"))
        cmbSex = rs.Fields("sex")
        cmbCompanyWorker = getDeptName(mshflexgrdOperator.TextMatrix(mshflexgrdOperator.Row, 3))
    End If
End Sub

Private Sub mshflexgrdOperator_EnterCell()
    Dim rs As New ADODB.Recordset
    rs.Open "Select * from zy_worker_info where op_no='" + mshflexgrdOperator.TextMatrix(mshflexgrdOperator.Row, 1) + "'", cnn, adOpenStatic, adLockOptimistic
    If Not rs.EOF Then
        txtOpNo = rs.Fields("op_no")
        txtOpName = rs.Fields("name")
        txtOpTel = IIf(IsNull(rs.Fields("tel")), "", rs.Fields("tel"))
        txtOpAddress = IIf(IsNull(rs.Fields("Address")), "", rs.Fields("Address"))
        cmbSex = rs.Fields("sex")
        cmbCompanyWorker = getDeptName(mshflexgrdOperator.TextMatrix(mshflexgrdOperator.Row, 3))
    End If
End Sub


Private Sub sstabBk_Click(PreviousTab As Integer)
    Select Case sstabBk.Tab
        Case 0
            Caption = "公司维护管理"
        Case 1
            Caption = "线路维护管理"
        Case 2
            Caption = "车辆维护管理"
        Case 3
            Caption = "员工维护管理"
            
    End Select
End Sub


Private Sub txtDeptNo_KeyPress(KeyAscii As Integer)
    KeyAscii = clsDaivd.ValiText(KeyAscii, "0123456789", True)
End Sub

Private Sub txtDeptNo_LostFocus()
    txtDeptName = SearchExist(Trim(txtDeptNo))
    If txtDeptName <> "" Then
        MsgBox "此公司号已存在，请检查重输!", vbExclamation, "提示..."
        Exit Sub
    Else
        blnOkFlag = True
    End If
End Sub

Private Function SearchExist(OpNo As String) As String
    Dim rs As New ADODB.Recordset
    If OpNo <> "" Then
        rs.Open "select name from zy_dept_info where dept_no='" + Trim(OpNo) + "'", cnn, adOpenStatic, adLockOptimistic
        If rs.EOF Then
        Else
            SearchExist = rs.Fields(0).Value
        End If
    End If
End Function

Private Sub DeptDisplay()
    Dim rs As New ADODB.Recordset
    
    rs.Open "select dept_no ,name  ,Address , tel from zy_dept_info where is_trans=1 order by dept_no", cnn, adOpenStatic, adLockOptimistic
    If rs.EOF Then
    Else
        Set mshflexgrdCompany.DataSource = rs
        Do While Not rs.EOF
            cmbCompanyLine.AddItem rs.Fields("name")
            cmbCompanyWorker.AddItem rs.Fields("name")
            rs.MoveNext
        Loop
    End If
    mshflexgrdCompany.FormatString = "  |^   公司编号   |^   公司名称     |^       公司地址       |^     电    话     "
End Sub

Private Sub LineDisplay()
    Dim rs As New ADODB.Recordset
    rs.Open "select line_no 线路编号,line_name 线路名称, dept_no 公司编号,s_station 起始站, e_station 终点站 from ZY_line_INFO order by line_no", cnn, adOpenStatic, adLockOptimistic
    If rs.EOF Then
    Else
        Set mshflexgrdLine.DataSource = rs
    End If
    mshflexgrdLine.FormatString = "  |^   线路编号   |^   线路名称     |^       公司编号       |^     起始站    |^    终点站   "
End Sub

Private Sub BusDisplay()
    Dim rs As New ADODB.Recordset
    rs.Open "select bus_no ,line_no, engine_no,bus_id,model_no,frame_no,buy_date from zy_bus_info order by line_no", cnn, adOpenStatic, adLockOptimistic
    If rs.EOF Then
    Else
        Set mshflexgrdBus.DataSource = rs
    End If
    mshflexgrdBus.FormatString = "  |^   车辆编号   |^   线路编号     |^       引擎号       |^     牌照号    |^    车型号   |^    车架号    |^    购买日期    "
End Sub

Private Sub OpratorDisplay()
    Dim rs As New ADODB.Recordset
    rs.Open "select op_no ,name , dept_no ,sex, tel,address from ZY_worker_INFO order by op_no", cnn, adOpenStatic, adLockOptimistic
    If rs.EOF Then
    Else
        Set mshflexgrdOperator.DataSource = rs
    End If
    mshflexgrdOperator.FormatString = "  |^   员工编号   |^   员工姓名     |^       公司编号       |^     性    别    |^    电    话   |^    住    址    "
End Sub

Private Function getDeptName(lineno As String) As String
    Dim rs As New ADODB.Recordset
    On Error Resume Next
    rs.Open "select name from zy_dept_info where dept_no='" + lineno + "'", cnn, adOpenStatic, adLockOptimistic
    If rs.EOF Then
        
    Else
        getDeptName = rs.Fields(0).Value
    End If
End Function