会计凭证的设计和录入 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

此主题相关图片如下：双击行弹出的转账凭证.png

红色标注代码明明设了安日期生成单号辅助列、字号双重限制，可弹出的窗口没了这两加载条件，把单号相同的不同字号都加载进来，不明！收、付款凭证的双击行弹窗加载数据没问题，唯独转账凭证出了问题，不明。

ElseIf e.Row("字号") = "转字" Then

btName = e.Table.Name '将Table名称保存在全局标量btName中

e.Cancel = True '禁止进入编辑状态

Forms("转账凭证").Open()

Forms("转账凭证").Controls("rq").text = e.Row("日期")

Forms("转账凭证").Controls("dh").text = e.Row("凭证号")

Forms("转账凭证").Controls("dh1").text = e.Row("安日期生成单号辅助列")

Forms("转账凭证").Controls("CheckBox1").Checked = e.Row("已审核")

Forms("转账凭证").Controls("shr").text = e.Row("审核人")

Forms("转账凭证").Controls("TextBox1").text = e.Row("附件张数")

With Tables("转账凭证_Table1")

LockBaseMainForm() '锁定主界面

.StopRedraw()

For i1 As Integer = .Rows.count - 1 To 0 Step -1

.Rows(i1).Delete

Dim Filter As String = "安日期生成单号辅助列 = '" & e.Row("安日期生成单号辅助列") & "' And [字号] ='" & e.Row("字号") & "' And [凭证号] = '" & e.Row("凭证号") & "'"

DataTables("转账凭证_Table1").LoadFilter = Filter '设置加载条件

DataTables("转账凭证_Table1").Load() '加载数据

If e.Row.IsNull("外币金额") And e.Row.IsNull("数量") Then

Forms("转账凭证").Controls("ComboBox2").text = Forms("转账凭证").controls("combobox2").items(0)

Forms("转账凭证").Controls("ComboBox1").text = Forms("转账凭证").controls("combobox1").items(0)

.SetColVisibleWidth("摘要|300|会计科目|100|明细科目|190|借方金额|80|贷方金额|80")

ElseIf e.Row.IsNull("外币金额") And e.Row("数量") IsNot Nothing Then

Forms("转账凭证").Controls("ComboBox2").text = Forms("转账凭证").controls("combobox2").items(0)

Forms("转账凭证").Controls("ComboBox1").text = Forms("转账凭证").controls("combobox1").items(1)

.SetColVisibleWidth("摘要|240|会计科目|100|明细科目|180|数量|60|单价|50|借方金额|60|贷方金额|60")

ElseIf e.Row("外币金额") IsNot Nothing And e.Row.IsNull("数量") Then

Forms("转账凭证").Controls("ComboBox2").text = Forms("转账凭证").controls("combobox2").items(1)

Forms("转账凭证").Controls("ComboBox1").text = Forms("转账凭证").controls("combobox1").items(0)

.SetColVisibleWidth("摘要|200|会计科目|100|明细科目|150|币种|60|汇率|60|外币金额|60|借方金额|60|贷方金额|60")

.Cols("外币金额").GrandTotal = True

ElseIf e.Row("外币金额") IsNot Nothing And e.Row("数量") IsNot Nothing Then

Forms("转账凭证").Controls("ComboBox2").text = Forms("转账凭证").controls("combobox2").items(1)

Forms("转账凭证").Controls("ComboBox1").text = Forms("转账凭证").controls("combobox1").items(1)

.SetColVisibleWidth("摘要|130|会计科目|100|明细科目|100|数量|60|单价|60|币种|60|汇率|60|外币金额|60|借方金额|60|贷方金额|60")

.Cols("外币金额").GrandTotal = True

End If

.Cols("会计科目").DropForm = "科目"

.Cols("借方金额").GrandTotal = True

.Cols("贷方金额").GrandTotal = True

.GrandTotal = True

'设合计二字居中方法一

'For Each cl As Col In Tables("月租收费_Table1").Cols '设所有列的数据居中

'cl.TextAlign = TextAlignEnum.Center

'Next

'设合计二字居中方法二

Dim r As Row

For i As Integer = 0 To .Rows.Count(True) - 1 'Count加上参数True

r = .Rows(i,True) 'Rows也需要加上参数True

If r.Level Then '如果是合计行

.Cols("摘要").TextAlign = TextAlignEnum.Center

End If

.ResumeRedraw()

UnLockBaseMainForm() '解锁主界面

End With

Tables("转账凭证_Table1").DataTable.LoadFilter = "凭证号='" & e.Row("凭证号") & "'"

Tables("转账凭证_Table1").DataTable.Load

If DataTables("转账凭证_Table1").DataRows.Count < 10 Then

DataTables("转账凭证_Table1").AddNew(10 - DataTables("转账凭证_Table1").DataRows.Count)

End If

[此贴子已经被作者于2021/5/9 7:48:03编辑过]

解决了，删除下面的

Tables("转账凭证_Table1").DataTable.LoadFilter = "凭证号='" & e.Row("凭证号") & "'"

Tables("转账凭证_Table1").DataTable.Load

下载信息 [文件大小：下载次数： ]
点击浏览该文件:收款凭证0612.foxdb

只要收款凭证保存后修改日期，再保存后财务数据源表就会重复录入一行借方数据。

如何设置它不重复添加

允许修改日期，凭证号都可以随之变，但财务数据源中的原单[安日期生成单号辅助列] = '" & e.Form.Controls("dh1").text不为空，不允许添加借方行，只能替代原数据。

问题是一直在这问题上绕转始终没解决，你看你在69楼给的答案，我改为现在这样，删除已经保存的借方行，重新添加一新，保存后，原来的行并没删除，新添的修改后的行。

Select Case e.StripItem.Name

Case "保存" '保存代

If e.Form.Controls("rq").text = "" Then

messagebox.show("请选择日期")

Return

ElseIf Tables("收款凭证_Table1").Compute("Count([贷方总账科目])") = 0 Then

Return

Else

Tables("收款凭证_Table1").FinishEditing '如果单元格处于编辑状态,需要结束编辑,让编辑内容写入单元格

For Each dt As DataRow In Tables("收款凭证_Table1").DataTable.Select("[贷方总账科目] Is not null")

Dim Parts() As String = dt("明细科目").Split("-")

dt("日期") = e.Form.Controls("rq").text

dt("凭证号") = e.Form.Controls("dh").text

dt("安日期生成单号辅助列") = e.Form.Controls("dh1").text

dt("附件张数") = e.Form.Controls("TextBox1").text

dt("录入员") = e.Form.Controls("lry").text

dt("会计科目") = dt("贷方总账科目") & "-" & dt("明细科目")

If dt("明细科目") = "" Then

dt("会计科目辅助列") = dt("会计科目").Split(" ")(1)

Else

dt("会计科目辅助列") = dt("会计科目").Split(" ")(1) & "-" & dt("明细科目")

End If

dt("一级科目") = dt("贷方总账科目")

dt("字号") = (e.Form.Controls("TextBox2").text).SubString(0,2)

dt("借或贷") = "贷"

dt("贷方数量") = dt("数量")

dt("贷方金额") = dt("金额")

dt("操作人姓名") = User.Name & " " & Date.Now

If e.Form.Controls("ComboBox2").text = e.form.controls("combobox2").items(1) And e.Form.Controls("ComboBox1").text = e.form.controls("combobox1").items(0) _

OrElse e.Form.Controls("ComboBox2").text = e.form.controls("combobox2").items(1) And e.Form.Controls("ComboBox1").text = e.form.controls("combobox1").items(1) Then

dt("外币贷方金额") = dt("外币金额")

End If

If Parts.length > 0 Then

dt("二级科目") = Parts(0)

If Parts.length > 1 Then

dt("三级科目") = Parts(1)

If Parts.length > 2 Then

dt("四级科目") = Parts(2)

If Parts.length > 3 Then

dt("五级科目") = Parts(3)

End If

dt.Save

End If

DataTables("财务数据源").load

Dim r As DataRow = DataTables("财务数据源").SQLFind("[字号] = '" & (e.Form.Controls("TextBox2").text).SubString(0,2) & "# And [安日期生成单号辅助列] = '" & e.Form.Controls("dh1").text & "' And [借或贷] = '借'")

If r Is Nothing Then

r = DataTables("财务数据源").addnew

r("日期") = e.Form.Controls("rq").text

r("凭证号") = e.Form.Controls("dh").text

r("借或贷") = "借"

r("安日期生成单号辅助列") = e.Form.Controls("dh1").text

r("附件张数") = e.Form.Controls("TextBox1").text

r("录入员") = e.Form.Controls("lry").text

r("字号") = (e.Form.Controls("TextBox2").text).SubString(0,2)

r("操作人姓名") = User.Name & " " & Date.Now

r("会计科目") = e.Form.Controls("ComboBox3").Value

r("会计科目辅助列") = (e.Form.Controls("ComboBox3").Value).Split(" ")(1)

If e.Form.Controls("ComboBox2").text = e.form.controls("combobox2").items(1) And e.Form.Controls("ComboBox1").text = e.form.controls("combobox1").items(0) _

OrElse e.Form.Controls("ComboBox2").text = e.form.controls("combobox2").items(1) And e.Form.Controls("ComboBox1").text = e.form.controls("combobox1").items(1) Then

r("外币借方金额") = r("外币金额")

End If

Dim Parts0() As String = r("会计科目").Split("-")

If Parts0.length > 0 Then

r("借方总账科目") = Parts0(0)

r("一级科目") = Parts0(0)

If Parts0.length > 1 Then

r("二级科目") = Parts0(1)

If Parts0.length > 2 Then

r("二级科目") = Parts0(2)

End If

r("借方金额") = Tables("收款凭证_Table1").Compute("Sum(金额)")

r("金额") = Tables("收款凭证_Table1").Compute("Sum(金额)")

r.save

Dim drs As List(Of DataRow) = DataTables("财务数据源").SQLSelect("[字号] = '" & (e.Form.Controls("TextBox2").text).SubString(0,2) & "' And [日期] = #" & e.Form.Controls("rq").Value & "# And [安日期生成单号辅助列] = '" & e.Form.Controls("dh1").text & "' And [借或贷] = '贷'")

Dim zheyao As String = ""

For c As Integer = 0 To drs.count - 1

zheyao = zheyao & drs(c)("摘要") & ","

r("摘要") = zheyao.TrimEnd(",")

Else

DataTables("财务数据源").SQLDeleteFor("[字号] = '" & (e.Form.Controls("TextBox2").text).SubString(0,2) & "# And [安日期生成单号辅助列] = '" & (e.Form.Controls("dh1").text).OldValue & "' And [借或贷] = '借'")

r = DataTables("财务数据源").addnew

r("日期") = e.Form.Controls("rq").text

r("凭证号") = e.Form.Controls("dh").text

r("借或贷") = "借"

r("安日期生成单号辅助列") = e.Form.Controls("dh1").text

r("附件张数") = e.Form.Controls("TextBox1").text

r("录入员") = e.Form.Controls("lry").text

r("字号") = (e.Form.Controls("TextBox2").text).SubString(0,2)

r("操作人姓名") = User.Name & " " & Date.Now

r("会计科目") = e.Form.Controls("ComboBox3").Value

r("会计科目辅助列") = (e.Form.Controls("ComboBox3").Value).Split(" ")(1)

If e.Form.Controls("ComboBox2").text = e.form.controls("combobox2").items(1) And e.Form.Controls("ComboBox1").text = e.form.controls("combobox1").items(0) _

OrElse e.Form.Controls("ComboBox2").text = e.form.controls("combobox2").items(1) And e.Form.Controls("ComboBox1").text = e.form.controls("combobox1").items(1) Then

r("外币借方金额") = r("外币金额")

End If

Dim Parts0() As String = r("会计科目").Split("-")

If Parts0.length > 0 Then

r("借方总账科目") = Parts0(0)

r("一级科目") = Parts0(0)

If Parts0.length > 1 Then

r("二级科目") = Parts0(1)

If Parts0.length > 2 Then

r("二级科目") = Parts0(2)

End If

r("借方金额") = Tables("收款凭证_Table1").Compute("Sum(金额)")

r("金额") = Tables("收款凭证_Table1").Compute("Sum(金额)")

r.save

Dim zheyao As String = ""

For c As Integer = 0 To drs.count - 1

zheyao = zheyao & drs(c)("摘要") & ","

r("摘要") = zheyao.TrimEnd(",")

End If

r.save

DataTables("财务数据源").LoadOrder = "字号,日期,凭证号,[借或贷] DESC"

DataTables("财务数据源").load

DataTables("财务数据源").DataCols("日期").RaiseDataColChanged()

DataTables("财务数据源").DataCols("凭证号").RaiseDataColChanged()

For i As Integer = DataTables("财务数据源").DataRows.count - 1 To 0 Step -1

Dim tr As DataRow = DataTables("财务数据源").DataRows(i)

If tr.Isnull("日期") Then

tr.delete

End If

End Select

删除已存在的行删除行，修改日期依然添加行。

借方数不在窗口的表中，无论怎么操作，修改日期，都会添加一行借方数，原来的借方数依然存在。

[此贴子已经被作者于2021/6/12 12:03:35编辑过]

这是添加时定义的行

这是删除行代码

DataTables("财务数据源").SQLDeleteFor("[字号] = '" & (e.Form.Controls("TextBox2").text).SubString(0,2) & "# And [安日期生成单号辅助列] = '" & e.Form.Controls("dh1").Value & "' And [借或贷] = '借'")

能生成了，双击财务数据源表中的行，能打开对应单据，且e.Form.Controls("dh1").Value没变，说明这些数据都没问题，怎么就不能删除原来的行，不明！

[此贴子已经被作者于2021/6/12 14:43:53编辑过]

主题：会计凭证的设计和录入