frmaddmaintain.frm

数据库课程设计

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "Msflxgrd.ocx"
Begin VB.Form frmAddMaintain 
   Caption         =   "设备维修"
   ClientHeight    =   5970
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   6600
   LinkTopic       =   "Form1"
   ScaleHeight     =   5970
   ScaleWidth      =   6600
   StartUpPosition =   3  'Windows Default
   Begin VB.CommandButton cmdCancel 
      Caption         =   "返回(&C)"
      Height          =   375
      Left            =   3480
      TabIndex        =   21
      Top             =   5400
      Width           =   1215
   End
   Begin VB.CommandButton cmdSave 
      Caption         =   "保存(&S)"
      Height          =   375
      Left            =   1800
      TabIndex        =   20
      Top             =   5400
      Width           =   1215
   End
   Begin VB.Frame Frame2 
      Caption         =   "配件使用信息"
      Height          =   2295
      Left            =   120
      TabIndex        =   1
      Top             =   2880
      Width           =   6375
      Begin MSFlexGridLib.MSFlexGrid fgAccs 
         Height          =   1095
         Left            =   120
         TabIndex        =   19
         Top             =   1080
         Width           =   6135
         _ExtentX        =   10821
         _ExtentY        =   1931
         _Version        =   393216
      End
      Begin VB.CommandButton cmdAddAcc 
         Caption         =   "添加(&A)"
         Height          =   375
         Left            =   4920
         TabIndex        =   18
         Top             =   480
         Width           =   1215
      End
      Begin VB.TextBox txtAccQuantity 
         Height          =   285
         Left            =   3600
         TabIndex        =   17
         Text            =   " "
         Top             =   360
         Width           =   855
      End
      Begin VB.TextBox txtAccName 
         Enabled         =   0   'False
         Height          =   285
         Left            =   1200
         TabIndex        =   15
         Text            =   " "
         Top             =   720
         Width           =   3255
      End
      Begin VB.ComboBox cboAccNo 
         Height          =   315
         Left            =   1200
         TabIndex        =   13
         Text            =   " "
         Top             =   360
         Width           =   1575
      End
      Begin VB.Label Label8 
         Caption         =   "数量："
         Height          =   255
         Left            =   3000
         TabIndex        =   16
         Top             =   360
         Width           =   615
      End
      Begin VB.Label Label7 
         Caption         =   "配件名称："
         Height          =   255
         Left            =   360
         TabIndex        =   14
         Top             =   720
         Width           =   975
      End
      Begin VB.Label Label6 
         Caption         =   "配件编号："
         Height          =   255
         Left            =   360
         TabIndex        =   12
         Top             =   360
         Width           =   1095
      End
   End
   Begin VB.Frame Frame1 
      Caption         =   "设备维修信息"
      Height          =   2655
      Left            =   120
      TabIndex        =   0
      Top             =   120
      Width           =   6375
      Begin VB.TextBox txtInfo 
         Height          =   495
         Index           =   2
         Left            =   1320
         TabIndex        =   11
         Text            =   " "
         Top             =   1920
         Width           =   4815
      End
      Begin VB.TextBox txtInfo 
         Height          =   735
         Index           =   1
         Left            =   1320
         TabIndex        =   9
         Text            =   " "
         Top             =   1080
         Width           =   4815
      End
      Begin VB.TextBox txtInfo 
         Height          =   285
         Index           =   0
         Left            =   1320
         TabIndex        =   7
         Text            =   " "
         Top             =   720
         Width           =   1455
      End
      Begin VB.TextBox txtDeviceName 
         Enabled         =   0   'False
         Height          =   285
         Left            =   4080
         TabIndex        =   5
         Text            =   " "
         Top             =   360
         Width           =   2055
      End
      Begin VB.ComboBox cboDeviceNo 
         Height          =   315
         Left            =   1320
         TabIndex        =   3
         Text            =   " "
         Top             =   360
         Width           =   1455
      End
      Begin VB.Label Label5 
         Caption         =   "备注信息："
         Height          =   255
         Left            =   360
         TabIndex        =   10
         Top             =   1800
         Width           =   975
      End
      Begin VB.Label Label4 
         Caption         =   "维修内容："
         Height          =   255
         Left            =   360
         TabIndex        =   8
         Top             =   1080
         Width           =   975
      End
      Begin VB.Label Label3 
         Caption         =   "维修日期："
         Height          =   255
         Left            =   360
         TabIndex        =   6
         Top             =   720
         Width           =   975
      End
      Begin VB.Label Label2 
         Caption         =   "设备名称："
         Height          =   255
         Left            =   3120
         TabIndex        =   4
         Top             =   360
         Width           =   975
      End
      Begin VB.Label Label1 
         Caption         =   "设备编号："
         Height          =   255
         Left            =   360
         TabIndex        =   2
         Top             =   360
         Width           =   975
      End
   End
End
Attribute VB_Name = "frmAddMaintain"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Public sqlStr As String
Public msgText As String

'保存当前配件的库存量
Public currentQuantity As Integer


Private Sub cboAccNo_Click()
getAccInfo
End Sub

Private Sub cboDeviceNo_Click()
getDeviceName cboDeviceNo.Text

End Sub

Private Sub cmdAddAcc_Click()
If Val(txtAccQuantity.Text) > currentQuantity Then
   MsgBox "库存不足，请购买！！"
   Exit Sub
End If
addAccToTable
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdSave_Click()
saveMaintainInfo
saveAccUsageInfo
initDeviceNo
initDeviceTextBox
initAccTextBox
initAccNo
initAccTable
txtInfo(0).Text = Format(Date, "yyyy-mm-dd")

End Sub

Private Sub Form_Load()

'窗体居中显示
Me.Top = (Screen.Height - Me.Height) \ 2
Me.Left = (Screen.Width - Me.Width) \ 2

initDeviceNo
initDeviceTextBox
initAccTextBox
initAccNo
initAccTable
txtInfo(0).Text = Format(Date, "yyyy-mm-dd")
End Sub

Sub initDeviceNo()
'方法的作用：将设备表中的所有设备编号显示到列表框中

Dim rstDeviceNo As ADODB.Recordset

    '从数据库中读取所有设备编号并添加到组合列表框中
    sqlStr = "select deviceNo from devices"
    Set rstDeviceNo = ExecuteSQL(sqlStr, msgText)
    cboDeviceNo.Clear
    
    If Not rstDeviceNo.EOF Then
        
            Do While Not rstDeviceNo.EOF
                cboDeviceNo.AddItem Trim(rstDeviceNo.Fields(0))
                rstDeviceNo.MoveNext
            Loop
            cboDeviceNo.ListIndex = 0
        
    Else
        MsgBox "没有找到相关信息，请添加！", vbOKOnly + vbExclamation, "警告"
        
        Exit Sub
    End If
    rstDeviceNo.Close

End Sub

Sub getDeviceName(deviceNo As String)
Dim rstDeviceNo As ADODB.Recordset

    '从数据库中查找设备名称并显示出来
    sqlStr = "select deviceName from devices" _
           & " where deviceNo='" & deviceNo & "'"
    Set rstDeviceNo = ExecuteSQL(sqlStr, msgText)
    
    If Not rstDeviceNo.EOF Then
        txtDeviceName.Text = Trim(rstDeviceNo.Fields(0))
    Else
        MsgBox "没有找到相关信息，请添加！", vbOKOnly + vbExclamation, "警告"
        
        Exit Sub
    End If
    rstDeviceNo.Close

End Sub

Sub initDeviceTextBox()
Dim i As Integer
For i = 0 To 2
    txtInfo(i) = ""
Next i

End Sub

Sub initAccTextBox()
    txtAccQuantity = ""
    txtAccName = ""
End Sub

Sub initAccNo()
'方法的作用：将配件表中的所有配件编号显示到列表框中

Dim rstAccNo As ADODB.Recordset

    '从数据库中读取所有设备编号并添加到组合列表框中
    sqlStr = "select accNo from accessories"
    Set rstAccNo = ExecuteSQL(sqlStr, msgText)
    cboAccNo.Clear
    
    If Not rstAccNo.EOF Then
        
            Do While Not rstAccNo.EOF
                cboAccNo.AddItem Trim(rstAccNo.Fields(0))
                rstAccNo.MoveNext
            Loop
            cboAccNo.ListIndex = 0
        
    Else
        MsgBox "没有找到相关信息，请添加！", vbOKOnly + vbExclamation, "警告"
        
        Exit Sub
    End If
    rstAccNo.Close

End Sub

Sub getAccInfo()
'获取配件的名称和库存量信息
Dim rstAcc As ADODB.Recordset

    '从数据库中查找配件的名称和库存量
    sqlStr = "select accName,accStock from accessories" _
           & " where accNo='" & cboAccNo.Text & "'"
    Set rstAcc = ExecuteSQL(sqlStr, msgText)
    
    If Not rstAcc.EOF Then
        txtAccName.Text = Trim(rstAcc.Fields("accName"))
        currentQuantity = rstAcc.Fields("accStock")
    Else
        MsgBox "没有找到相关信息，请添加！", vbOKOnly + vbExclamation, "警告"
        
        Exit Sub
    End If
    rstAcc.Close

End Sub

Sub initAccTable()
    Dim i As Integer
    
    fgAccs.Rows = 1
    
    fgAccs.Cols = 4
    
    '设定行高
    For i = 0 To fgAccs.Rows - 1
       fgAccs.RowHeight(i) = 280
    Next i
    
    '设定列的属性
    fgAccs.Row = 0
    
    For i = 0 To fgAccs.Cols - 1
       fgAccs.Col = i  '指定当前列为第i列
       fgAccs.FixedAlignment(i) = 4  '每列内容居中显示
       Select Case i
           Case 0
              fgAccs.ColWidth(i) = 1000  '设定列宽
              fgAccs.Text = "序号"
           Case 1
              fgAccs.ColWidth(i) = 1500  '设定列宽
              fgAccs.Text = "配件编号"
           Case 2
              fgAccs.ColWidth(i) = 2500  '设定列宽
              fgAccs.Text = "配件名称"
           Case 3
              fgAccs.ColWidth(i) = 1000  '设定列宽
              fgAccs.Text = "数量"
        End Select
           
    Next i
End Sub

Sub addAccToTable()
'将使用的配件信息添加到表格中
Dim j As Integer

    If txtAccQuantity.Text = "" Then
        MsgBox "请填写配件数量！", vbExclamation, "提示"
        Exit Sub
    End If
   
   fgAccs.Rows = fgAccs.Rows + 1
   
   fgAccs.RowHeight(fgAccs.Rows - 1) = 280
   
   '设定列的属性
   fgAccs.Row = fgAccs.Rows - 1
   

   For j = 0 To fgAccs.Cols - 1
      
   fgAccs.Col = j '设置当前为列为第j列
   fgAccs.CellAlignment = 4  '每列内容居中显示
   
       
   Select Case j
       Case 0
             fgAccs.Text = fgAccs.Rows - 1
       Case 1
             fgAccs.Text = cboAccNo.Text
       Case 2
             fgAccs.Text = txtAccName.Text
       Case 3
             fgAccs.Text = txtAccQuantity.Text
       End Select
     Next j
   txtAccQuantity.Text = ""
        
End Sub

Sub saveMaintainInfo()
'保存设备维修的基本信息
 Dim rstMaintain As ADODB.Recordset
 Dim i As Integer
  
 For i = 0 To 1
    If txtInfo(i).Text = "" Then
    
       MsgBox "请将信息填写完整", vbOKOnly + vbExclamation, "警告"
 
       Exit Sub
    
    End If
 Next i

 '添加新记录
 sqlStr = "select * from maintainence"
 Set rstMaintain = ExecuteSQL(sqlStr, msgText)
 rstMaintain.AddNew
 rstMaintain.Fields("contents") = txtInfo(1).Text
 rstMaintain.Fields("deviceNo") = cboDeviceNo.Text
 rstMaintain.Fields("maintainDate") = txtInfo(0).Text
 rstMaintain.Fields("memo") = txtInfo(2).Text
 rstMaintain.Update
 rstMaintain.Close
     
 MsgBox "维修信息添加完成！", vbOKOnly + vbExclamation, "警告"
 
End Sub

Sub saveAccUsageInfo()
'保存配件使用信息
 Dim rstAccUsage As ADODB.Recordset
 
 Dim acc_no As String
 Dim acc_quantity As String
 
 Dim i As Integer
 Dim j As Integer
 
 If fgAccs.Rows < 2 Then
    Exit Sub
 End If
 
 
 sqlStr = "select * from acc_usage"
 Set rstAccUsage = ExecuteSQL(sqlStr, msgText)

 For i = 1 To fgAccs.Rows - 1
    fgAccs.Row = i
    rstAccUsage.AddNew
    For j = 1 To fgAccs.Cols - 1
        fgAccs.Col = j
        '在配件使用表中添加新记录
        Select Case j
            Case 1
                rstAccUsage.Fields("accNo") = fgAccs.Text
                acc_no = fgAccs.Text
            Case 3
                rstAccUsage.Fields("numOfUsed") = fgAccs.Text
                acc_quantity = fgAccs.Text
        End Select
        modifyAccInfo acc_no, acc_quantity
    Next j
    rstAccUsage.Fields("useDate") = Format(Date, "yyyy-mm-dd")
    rstAccUsage.Update
 Next i
 rstAccUsage.Close

End Sub

Sub modifyAccInfo(no As String, quantity As String)
   Dim conn As ADODB.Connection
   
   '组合得到完成数据修改的SQL语句
   sqlStr = "update accessories set accStock=accStock-" & quantity _
          & " where accNo='" & no & "'"
   On Error GoTo exitSub
   Set conn = New ADODB.Connection
   conn.Open connStr
   
   '执行SQL语句
   conn.Execute sqlStr
exitSub:
   conn.Close

End Sub