📄 form11.frm
字号:
VERSION 5.00
Begin VB.Form Form11
BorderStyle = 1 'Fixed Single
Caption = "Form11"
ClientHeight = 5400
ClientLeft = 45
ClientTop = 435
ClientWidth = 6225
LinkTopic = "Form11"
MaxButton = 0 'False
MinButton = 0 'False
ScaleHeight = 5400
ScaleWidth = 6225
StartUpPosition = 3 '窗口缺省
Begin VB.TextBox Text1
BeginProperty Font
Name = "宋体"
Size = 12
Charset = 134
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
Height = 420
Left = 2880
TabIndex = 3
Text = "Text1"
Top = 1920
Width = 1615
End
Begin VB.TextBox Text2
BeginProperty Font
Name = "宋体"
Size = 12
Charset = 134
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
Height = 420
Left = 2880
TabIndex = 2
Text = "Text2"
Top = 2580
Width = 1615
End
Begin VB.CommandButton Command1
Caption = "确认"
BeginProperty Font
Name = "宋体"
Size = 12
Charset = 134
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
Height = 540
Left = 2400
TabIndex = 1
Top = 3360
Width = 1455
End
Begin VB.Label Label3
AutoSize = -1 'True
Caption = "年 份："
BeginProperty Font
Name = "宋体"
Size = 12
Charset = 134
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
Height = 240
Left = 1920
TabIndex = 5
Top = 2040
Width = 960
End
Begin VB.Label Label2
AutoSize = -1 'True
Caption = "月 份："
BeginProperty Font
Name = "宋体"
Size = 12
Charset = 134
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
Height = 240
Left = 1920
TabIndex = 4
Top = 2640
Width = 960
End
Begin VB.Label Label1
AutoSize = -1 'True
Caption = "结转操作画面"
BeginProperty Font
Name = "宋体"
Size = 15.75
Charset = 134
Weight = 400
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
Height = 315
Left = 2280
TabIndex = 0
Top = 960
Width = 1890
End
End
Attribute VB_Name = "Form11"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub Command1_Click()
Dim cnn1 As ADODB.Connection
Dim cmd1 As ADODB.Command
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim rs3 As ADODB.Recordset
Dim rs4 As ADODB.Recordset
Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\jujumao\My Documents\粤丰饲料\粤丰饲料经营情况.mdb;"
Set cmd1 = New ADODB.Command
SQL = "select max(年份) ,max(月份) from 月结转表"
With cmd1
.ActiveConnection = cnn1
.CommandText = SQL
.CommandType = adCmdText
End With
Set rs1 = cmd1.Execute
rs1.MoveFirst
'While Not rs1.EOF
' If Text1.Text < rs1.Fields(0) Or Text2.Text < rs1.Fields(1) Then
' MsgBox ("只能结转最大月份")
' j = 0
' GoSub m_exit
' End If
' rs1.MoveNext
'Wend
SQL = "delete from 月结转表 where 年份=" & Text1.Text & "and 月份= " & Text2.Text
With cmd1
.ActiveConnection = cnn1
.CommandText = SQL
.CommandType = adCmdText
.Execute
End With
SQL = "select * from 品种类型表"
With cmd1
.ActiveConnection = cnn1
.CommandText = SQL
.CommandType = adCmdText
End With
Set rs1 = cmd1.Execute
rs1.MoveFirst
While Not rs1.EOF
SQL = "select count(*),sum(购入斤数),sum(购入金额),sum(运费),sum(卸车费) from 品种购入表 " & _
"where 品种=" & "'" & rs1.Fields(0) & "'" & "and 年份 =" & Text1.Text & _
"and 月份 =" & Text2.Text
With cmd1
.ActiveConnection = cnn1
.CommandText = SQL
.CommandType = adCmdText
End With
Set rs2 = cmd1.Execute
SQL = "select count(*),sum(售出斤数),sum(售出金额),sum(欠款金额) from 品种售出表 " & _
"where 品种=" & "'" & rs1.Fields(0) & " '" & "and 年份 =" & Text1.Text & _
"and 月份 =" & Text2.Text
With cmd1
.ActiveConnection = cnn1
.CommandText = SQL
.CommandType = adCmdText
End With
Set rs3 = cmd1.Execute
rs2.MoveFirst
If rs2.Fields(0) = 0 Then
t_buy_weight = 0
t_buy_money = 0
t_transportation_charge = 0
t_unload_charge = 0
Else
t_buy_weight = rs2.Fields(1)
t_buy_money = rs2.Fields(2)
t_transportation_charge = rs2.Fields(3)
t_unload_charge = rs2.Fields(4)
End If
rs3.MoveFirst
If rs3.Fields(0) = 0 Then
t_sale_weight = 0
t_sale_agv_price = 0
t_sale_money = 0
t_owe_money = 0
Else
t_sale_weight = rs3.Fields(1)
If rs3.Fields(1) = 0 Then
t_sale_price = 0
Else
t_sale_agv_price = rs3.Fields(2) / rs3.Fields(1)
End If
t_sale_money = rs3.Fields(2)
t_owe_money = rs3.Fields(3)
End If
t_month = Text2.Text - 1
t_year = Text1.Text
If t_month = 0 Then
t_month = 12
t_year = Text1.Text - 1
End If
SQL = "select 结存斤数,结存金额 from 月结转表 where 品种 = " & "'" & rs1.Fields(0) & "'" & _
"and 年份= " & t_year & " and 月份 = " & t_month
With cmd1
.ActiveConnection = cnn1
.CommandText = SQL
.CommandType = adCmdText
End With
Set rs4 = cmd1.Execute
If rs4.EOF Then
last_remain_weight = 0
last_remain_money = 0
Else
rs4.MoveFirst
last_remain_weight = rs4.Fields(0)
last_remain_money = rs4.Fields(1)
End If
'MsgBox (last_remain_weight)
If last_remain_weight + t_buy_weight = 0 Then
t_buy_agv_price = 0
Else
t_buy_agv_price = (last_remain_money + t_buy_money) / (last_remain_weight + t_buy_weight)
End If
t_remain_weight = last_remain_weight + t_buy_weight - t_sale_weight
t_remain_money = t_remain_weight * t_buy_agv_price
t_profit = t_sale_money - (t_buy_money + last_remain_money) + t_remain_money
SQL = " insert into 月结转表 values (" & "'" & rs1.Fields(0) & "'" & "," & _
Text1.Text & "," & Text2.Text & "," & last_remain_weight & "," & last_remain_money & "," & t_buy_weight & "," & t_buy_agv_price & _
"," & t_buy_money & "," & t_transportation_charge & "," & t_unload_charge & _
"," & t_sale_weight & "," & t_sale_agv_price & "," & t_sale_money & "," & _
t_owe_money & "," & t_remain_weight & "," & t_remain_money & "," & _
t_profit & ")"
With cmd1
.ActiveConnection = cnn1
.CommandText = SQL
.CommandType = adCmdText
.Execute
End With
rs1.MoveNext
Wend
MsgBox ("结转成功!")
j = 1
m_exit:
Text1.SetFocus
cnn1.Close
Set cnn1 = Nothing
If j = 1 Then
Unload Form11
End If
End Sub
Private Sub Form_Load()
Text1.Text = Year(Date)
Text2.Text = Month(Date)
End Sub
Private Sub Form_Unload(Cancel As Integer)
Form1.Show
End Sub
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -