form4.frm

超市管理~~~ 付款~审核~收货~退货~~~~ AND SO ON

 clickpage '这里就是读取某一条记录的过程,并获取是否审核等状态
 XPButton3.Caption = "上一订单(" & intPage - 1 & ")"
 XPButton4.Caption = "下一订单(" & intPageCount - intPage & ")"
  If intPage < intPageCount Then
    XPButton4.Enabled = True
 End If
 If intPage <= 1 Then
  XPButton3.Enabled = False
 Else
  XPButton3.Enabled = True
 End If
 End Sub

Private Sub Form_Unload(Cancel As Integer)
'Cancel = True
'Unload Me
End Sub

Private Sub clickpage() '这里就是读取某一条数据的记录到窗体中
 XPButton2.Enabled = False
  xtext1.Text = cg1.Fields(0)
  xtext2.Text = cg1.Fields(1)
  xtext5(0).Text = cg1.Fields(2)
  xcombox1.Text = cg1.Fields(3)
  xtext5(1).Text = cg1.Fields(4)
  xcombox2.Text = cg1.Fields(5)
  xtext3.Text = cg1.Fields(6)
  xcombox3.Text = cg1.Fields(7)
  xtext4.Text = cg1.Fields(8)
  xtext5(2).Text = cg1.Fields(9)
  xtext8.Text = cg1.Fields(10)
  If cg1.Fields(11) = "已审核" Then
  Image1.Picture = LoadPicture(App.Path & "\images\shenhe-1.jpg")
  XPButton6.Caption = "反审核"
  Else
  Image1.Picture = LoadPicture(App.Path & "\images\shenhe-2.jpg")
  XPButton6.Caption = "审核"
  End If
  '获取某一单号的明细资料,并填入grid表格中
  Set cg2 = cnn.Execute("select * from 申购单明细 where 批号='" & xtext2.Text & "'")
  Grid1.Rows = 1
  i = 0
  Do While Not cg2.EOF
  i = i + 1
  Grid1.Rows = Grid1.Rows + 1
  For j = 0 To 7
   Grid1.Cell(i, j + 1).Text = cg2.Fields(j)
  Next
  cg2.MoveNext
  Loop
  '----在读取模式中某些文本内为不可写模式
xtext1.Enabled = False
xtext2.Enabled = False
xtext5(0).Enabled = False
xcombox1.Enabled = False
xtext5(1).Enabled = False
xcombox2.Enabled = False
xtext3.Enabled = False
xcombox3.Enabled = False
xtext4.Enabled = False
xtext5(2).Enabled = False
xtext8.Enabled = False
Grid1.ReadOnly = True
End Sub
Private Sub zdcsh()
'以下就是订单的初始化,自动生成新的单号以及将某些单元格作清空等处理
    Dim dh As Integer
    Dim strdh As String
    Dim bdh As Boolean
    bdh = False
    dh = 1
    Do While bdh = False
    strdh = dh
    Do While Len(strdh) < 6
    strdh = "0" & strdh
    Loop
    xtext1.Text = Date & "-" & strdh
   Set cg4 = cnn.Execute("select * from 申购单 where 采购单号='" & xtext1.Text & "'")
     If cg4.EOF = False Then
     dh = dh + 1
     Else
     bdh = True
     End If
    cg4.Close
    Loop
xtext2.Text = xtext1.Text
For dh = 0 To 2
xtext5(dh).Text = Date
Next
xtext8.Text = loginname
xcombox1.Text = ""
xcombox2.Text = ""
xcombox3.Text = ""
xtext3.Text = ""
xtext4.Text = ""
xtext1.Enabled = False
xtext2.Enabled = False
xtext5(0).Enabled = True
xcombox1.Enabled = True
xtext5(1).Enabled = True
xcombox2.Enabled = True
xtext3.Enabled = True
xcombox3.Enabled = True
xtext4.Enabled = True
xtext5(2).Enabled = False
xtext8.Enabled = False
Grid1.ReadOnly = False
End Sub

Private Sub Grid1_RowColChange(ByVal Row As Long, ByVal Col As Long)
If Row = 0 Then '但鼠标定位行是第0行时,退出按钮操作
Exit Sub
End If
If Grid1.Cell(Row, 1).Text <> "" Then '按商品类型获取对应的下属商品名称资料
 Set cg3 = cnn.Execute("select distinct(商品名称) from 商品 where 商品类型='" & Grid1.Cell(Row, 1).Text & "'")
 Grid1.ComboBox(2).Clear
 Do While Not cg3.EOF
  Grid1.ComboBox(2).AddItem cg3.Fields(0)
  cg3.MoveNext
 Loop
End If
If Grid1.Cell(Row, 1).Text <> "" And Grid1.Cell(Row, 2).Text <> "" Then '按商品类型和商品名称获取对应的下属商品型号资料
 Set cg3 = cnn.Execute("select distinct(商品型号) from 商品 where 商品类型='" & Grid1.Cell(Row, 1).Text & "' and 商品名称='" & Grid1.Cell(Row, 2).Text & "'")
 Grid1.ComboBox(3).Clear
 Do While Not cg3.EOF
  Grid1.ComboBox(3).AddItem cg3.Fields(0)
  cg3.MoveNext
 Loop
End If
If Grid1.Cell(Row, 1).Text <> "" And Grid1.Cell(Row, 2).Text <> "" And Grid1.Cell(Row, 4).Text = "" And Col = 4 Then '获取该产品的合理采购数量
  If Grid1.Cell(Row, 3).Text <> "" Then
   sql = "select 每批采购数量 from 商品 where 商品类型='" & Grid1.Cell(Row, 1).Text & "' and 商品名称='" & Grid1.Cell(Row, 2).Text & "'and 商品型号='" & Grid1.Cell(Row, 3).Text & "'"
  Else
   sql = "select 每批采购数量 from 商品 where 商品类型='" & Grid1.Cell(Row, 1).Text & "' and 商品名称='" & Grid1.Cell(Row, 2).Text & "'"
  End If
 Set cg3 = cnn.Execute(sql)
  If cg3.EOF = False Then
   Grid1.Cell(Row, 4).Text = cg3.Fields(0)
  End If
End If
If Grid1.Cell(Row, 4).Text <> "" And Grid1.Cell(Row, 6).Text <> "" Then '计算该产品的金额
 Grid1.Cell(Row, 7).Text = Val(Grid1.Cell(Row, 4).Text) * Val(Grid1.Cell(Row, 6).Text)
End If
End Sub

Private Sub Timer1_Timer() '通过选择的供货商获取对应的联系人姓名
If xcombox1.Text <> "" Then
  Set cg3 = cnn.Execute("select 联系人 from 供货商 where 供货商='" & xcombox1.Text & "'")
  If cg3.EOF = True Then
  Exit Sub
  End If
  xcombox3.Clear
  xcombox3.AddItem cg3.Fields(0)
End If
End Sub

Private Sub xpbutton1_Click()
Grid1.Rows = Grid1.Rows + 1 '这里是增加明细表中的行数
End Sub

Private Sub XPButton2_Click() '提交操作
On Error GoTo finish: '防止异常错误导致系统退出
If Grid1.Cell(1, 1).Text = "" Then '明细表中没有内容时退出操作
MsgBox "明细表内无内容，确定后输入..."
Exit Sub
End If
'---因为有时并不是面对一个人执行程序,所以申购单在提交之前还是要检测是否有重复的
Set cg1 = cnn.Execute("select * from 申购单 where 采购单号='" & xtext1.Text & "'")
If cg1.EOF = False Then
MsgBox "此订单己存在！请重新生成！"
Call Form_Load
Grid1.Rows = 1
Exit Sub
End If
'---以下是将生成一个SQL所需要使用的语句,把这些内容放到SQL字符串中,在后面在执行
sql = "insert into 申购单 values('" & xtext1.Text & "','" & xtext2.Text & "','" & xtext5(0).Text & "','" & xcombox1.Text & "','" & _
  xtext5(1).Text & " ','" & xcombox2.Text & "','" & xtext3.Text & "','" & xcombox3.Text & "','" & xtext4.Text & "','" & _
  xtext5(2).Text & " ','" & xtext8.Text & "','未审核')"
Set cg1 = cnn.Execute(sql) '这里执行了这个SQL语句
For i = 1 To Grid1.Rows - 1 '这里循环从第一行到最后一行
  If Grid1.Cell(i, 1).Text = "" Then
  MsgBox "提交成功，等待审核..."
  Call Form_Load
  Grid1.Rows = 1
  Exit Sub
  End If
  '生成一个SQL的插入语句,将表格中的明细内容存入到数据库中
  sql = "insert into 申购单明细 values('" & Grid1.Cell(i, 1).Text & "','" & Grid1.Cell(i, 2).Text & "','" & Grid1.Cell(i, 3).Text & "',"
  If Grid1.Cell(i, 4).Text <> "" Then
  sql = sql & Grid1.Cell(i, 4).Text & ",'" & Grid1.Cell(i, 5).Text & "',"
  Else
  sql = sql & "0,'" & Grid1.Cell(i, 5).Text & "'," '这里的选择语句,因为数据型是不允许为空载入数据库的,在这里要做下处理
  End If
  For j = 6 To Grid1.Cols - 2
   If Grid1.Cell(i, j).Text <> "" Then
    sql = sql & Grid1.Cell(i, j).Text & ","
   Else
    sql = sql & "0,"
   End If
  Next
  sql = sql & "'" & Grid1.Cell(i, 8).Text & "','" & xtext2.Text & "')"
  Set cg3 = cnn.Execute(sql) '执行SQL语句
  Next
MsgBox "提交成功，等待审核..."
Call Form_Load
Grid1.Rows = 1
Exit Sub
finish:
MsgBox Err.Description
End Sub

Private Sub XPButton3_Click() '以下实现了数据的上下进行翻页
If intPage > 1 Then '当前页>1的时候才可以执行
intPage = intPage - 1 '先把当前页-1
cg1.AbsolutePage = intPage
 clickpage
 cg1.MoveNext  '读取下一记录
 XPButton3.Caption = "上一订单(" & intPage - 1 & ")"
 XPButton4.Caption = "下一订单(" & intPageCount - intPage & ")"
   If intPage < intPageCount Then
    XPButton4.Enabled = True
 End If
 If intPage <= 1 Then
  XPButton3.Enabled = False
 End If
End If
End Sub

Private Sub XPButton4_Click()
If intPage < intPageCount Then
intPage = intPage + 1
cg1.AbsolutePage = intPage
  clickpage
 cg1.MoveNext  '读取下一记录
  XPButton3.Caption = "上一订单(" & intPage - 1 & ")"
 XPButton4.Caption = "下一订单(" & intPageCount - intPage & ")"
  If intPage >= intPageCount Then
    XPButton4.Enabled = False
 End If
 If intPage > 1 Then
  XPButton3.Enabled = True
  End If
End If
End Sub

Private Sub XPButton5_Click()
If system(0) = False Then
MsgBox "无此权限!"
Exit Sub
End If
zdcsh '执行订单初始化
Grid1.Rows = 1
Grid1.Rows = 5
XPButton2.Enabled = True
End Sub

Private Sub XPButton6_Click()
If system(1) = False Then
MsgBox "无审核权限!"
Exit Sub
End If
If cg1.State = adStateOpen Then
 cg1.Close
End If
cg1.Open "select * from 申购单 where 采购单号='" & xtext1.Text & "'", cnn, adOpenKeyset, adLockOptimistic, adCmdText
If XPButton6.Caption = "审核" Then
If cg1.EOF = False Then
cg1.Fields(11) = "已审核"
cg1.Update
Call Form_Load
MsgBox "审核完成！"
Image1.Picture = LoadPicture(App.Path & "\images\shenhe-1.jpg")
Else
MsgBox "无此订单，审核无效！"
Call Form_Load
End If
Else
If cg1.EOF = False Then
cg1.Fields(11) = "未审核"
cg1.Update
Call Form_Load
MsgBox "反审核完成！"
Image1.Picture = LoadPicture(App.Path & "\images\shenhe-2.jpg")
Else
MsgBox "无此订单，审核无效！"
Call Form_Load
End If
End If
End Sub

Private Sub XPButton7_Click()
If system(2) = False Then
MsgBox "无此权限!"
Exit Sub
End If
Set cg3 = cnn.Execute("select 采购单号 from 采购订单 where 采购单号='" & xtext1.Text & "'")
If cg3.EOF = False Then
MsgBox "此前订单已经生成！", vbInformation, "提示"
Exit Sub
End If
Set cg3 = cnn.Execute("select 采购单号,状态 from 申购单 where 采购单号='" & xtext1.Text & "'")
If cg3.EOF = True Then
MsgBox "没有此单，请检查!", vbInformation, "提示"
Exit Sub
End If
If cg3.Fields(1) = "未审核" Then
MsgBox "此单未审核，无法生成订单!", vbInformation, "提示"
Exit Sub
Else
Set cg4 = cnn.Execute("insert into 采购订单 values('" & cg3.Fields(0) & "','未审核')")
Unload Me
Form5.Show 1
End If
End Sub