在UserForm4代码修改一下
Private Sub CommandButton1_Click() Dim r As Integer Set Itm = UserForm2.TreeView1.SelectedItem Set cnn = CreateObject("ADODB.connection") Set rst = CreateObject("ADODB.recordset") cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\db1.mdb"
Nameofaccounts = Me.TextBox4.Value
If Me.TextBox3.Text <> Mid(Itm.Key, 5) Then sql = "SELECT 科目编码 From zykmb where 科目编码= '" & Me.TextBox3.Text & "'" '判断科目编码=是否已经存在 rst.Open sql, cnn, 3, 1 If rst.RecordCount > 0 Then MsgBox "你的科目编码已存在,请重新输入" Me.TextBox3.SetFocus Exit Sub End If rst.Close End If
If Me.TextBox3.Text = "" Or Len(Me.TextBox3.Text) <> Me.TextBox1 Then MsgBox "请输入科目编码或者你的编码长度不够" Me.TextBox3.SetFocus ElseIf Left(Trim(Me.TextBox3.Text), 1) <> Left(Trim(Me.TextBox5.Text), 1) Then MsgBox "请注意编码规律" ElseIf Me.TextBox4.Text = "" Then MsgBox "请输入科目名称" Me.TextBox4.SetFocus ElseIf Me.OptionButton1 = False And Me.OptionButton2 = False Then MsgBox "请输入科目方向" Me.TextBox6.SetFocus Else
If Len(Mid(Trim(Itm.Key), 5)) > 4 Then '如果大于4,则表明有明细科目,要兼顾总帐科目和明细科目 ,Name of accounts If Left(Trim(Me.TextBox3.Text), 4) <> Left(Trim(Me.TextBox5.Text), 4) Then MsgBox "明细科目编号前4位与总账科目前4位不同": Exit Sub End If sql = "SELECT 总账科目 From zykmb where 科目编码= '" & Left(Trim(Me.TextBox5.Text), 4) & "'" rst.Open sql, cnn, 3, 1 If rst.RecordCount > 0 Then Nameofaccounts = rst.fields(0) End If rst.Close End If
sql = " update zykmb set " sql = sql & "科目编码= '" & Me.TextBox3.Text & "'," sql = sql & "助记码= '" & Me.TextBox6.Text & "'," sql = sql & "总账科目= '" & Nameofaccounts & "'," sql = sql & "明细科目= '" & IIf(Len(Me.TextBox3.Text) > 4, Me.TextBox4.Text, Empty) & "'," sql = sql & "方向= '" & IIf(Me.OptionButton1 = True, "借", "贷") & "'," sql = sql & "期初金额 = " & IIf(IsNumeric(Me.TextBox7.Text), Me.TextBox7.Text, 0) & " " sql = sql & " where 科目编码 = '" & Mid(Trim(Itm.Key), 5) & "'" cnn.Execute sql cnn.Close Set cnn = Nothing Call Tree Me.Hide End If End Sub |