帮忙看看这段新建记录代码有没有问题

yori2007

不知道为什么，新建记录，新建以C开头的记录可以成功录入，而以V开头的不能录入。

Public coTBuyInvoice As New Collection

Public Sub NewTBuyInvoice()
On Error GoTo NewTBuyInvoice_Err
Dim mId As String
mId = InputBox("请输入发票号", "提示")
If mId & "" = "" Then
    Exit Sub
End If
If DCount("InvoiceId", "tbTBuyInvoice", "InvoiceId='" & mId & "'") <> 0 Then
    MsgBox "此发票号已经存在,请重新输入!", vbExclamation, "提示"
    Exit Sub
End If

'新建发票
Dim rs As New ADODB.Recordset
rs.Open "tbTBuyInvoice", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
        With rs
            .AddNew
            ![InvoiceId] = mId
            ![BuildDate] = Date
            ![BuildMan] = DLookup("EmployeeId", "tbEmployee", "UserName='" & mUserName & "'")
            .Update
        End With
        rs.Close
        Set rs = Nothing
        DoCmd.Requery
'打开新记录编辑
        Dim fm As Form
        Set fm = New Form_fmTBuyInvoice  ‘打开新窗口
        fm.FilterOn = True
        fm.Filter = "Me.InvoiceId='" & mId & "'"   ‘过滤
        fm.AllowAdditions = False  ‘表头不可编辑
        fm.Caption = "编辑发票" & mId
        fm.Visible = True
        coTBuyInvoice.Add Item:=fm, Key:=CStr(fm.Hwnd)
        Set fm = Nothing
NewTBuyInvoice_Exit:
    Exit Sub
NewTBuyInvoice_Err:
    MsgBox Err.Description
    Resume NewTBuyInvoice_Exit
End Sub

andymark

搞不明白新建记录为啥需要Filter

yori2007

在新建的InvoiceId下建记录啊，关键问题是不知道coTBuyInvoice.Add Item:=fm, Key:=CStr(fm.Hwnd)
，这句话，有没有问题

yori2007

求关注啊

yori2007

求关注

layaman_999

If DCount("InvoiceId", "tbTBuyInvoice", "InvoiceId='" & mId & "'") <> 0 ThenDCount会降低效率,建议使用dlookup来检查
，其他问题你都没说，如果运行正常，就算了吧，等基础好了再来检查，毕竟每个人都编写习惯都不一样

yori2007

新增记录，以V开头的数据会弹出空白窗口，以C开头的没问题

layaman_999

        fm.FilterOn = True
        fm.Filter = "Me.InvoiceId='" & mId & "'"   ‘过滤
调换一下顺序看看
fm.Filter = "Me.InvoiceId='" & mId & "'"   ‘过滤
fm.FilterOn = True
==========
我觉得你的代码效率不高，可能这样要好点：
On Error GoTo NewTBuyInvoice_Err
Dim mId As String
mId = InputBox("请输入发票号", "提示")
If mId = "" Then Exit Sub

If Nz(DLookup("InvoiceId", "tbTBuyInvoice", "InvoiceId='" & mId & "'"), "") = mId Then
    MsgBox "此发票号已经存在,请重新输入!", vbExclamation, "提示"
    Exit Sub
End If

'新建发票
'====================
Dim StrSql As String '不清楚你的字段类型，'UserName'为文本, #BuildDate#为日期
StrSql = "insert into tbTBuyInvoice(InvoiceId,BuildDate,BuildMan) "
StrSql = StrSql & " select '" & mId & "', #" & BuildDate & "#, '" & DLookup("EmployeeId", "tbEmployee", "UserName='" & mUserName & "'") & "'"

CurrentProject.Connection.Execute (StrSql)



'打开新记录编辑
        Dim fm As Form
        Set fm = New Form_fmTBuyInvoice  '打开新窗口
        fm.RecordSource = "select * from tbTBuyInvoice where InvoiceId='" & mId & "'"
        fm.AllowAdditions = False  '表头不可编辑
        fm.Caption = "编辑发票" & mId
        fm.Visible = True
        coTBuyInvoice.Add Item:=fm, Key:=CStr(fm.Hwnd)
        Set fm = Nothing
NewTBuyInvoice_Exit:
    Exit Sub
NewTBuyInvoice_Err:
    MsgBox Err.Description
    Resume NewTBuyInvoice_Exit

layaman_999

       fm.FilterOn = True
        fm.Filter = "Me.InvoiceId='" & mId & "'"   ‘过滤
调换一下顺序看看
fm.Filter = "Me.InvoiceId='" & mId & "'"   ‘过滤
fm.FilterOn = True
==========
我觉得你的代码效率不高，可能这样要好点：
On Error GoTo NewTBuyInvoice_Err
Dim mId As String
mId = InputBox("请输入发票号", "提示")
If mId = "" Then Exit Sub

If Nz(DLookup("InvoiceId", "tbTBuyInvoice", "InvoiceId='" & mId & "'"), "") = mId Then
    MsgBox "此发票号已经存在,请重新输入!", vbExclamation, "提示"
    Exit Sub
End If

'新建发票
'====================
Dim StrSql As String '不清楚你的字段类型，'UserName'为文本, #BuildDate#为日期
StrSql = "insert into tbTBuyInvoice(InvoiceId,BuildDate,BuildMan) "
StrSql = StrSql & " select '" & mId & "', #" & BuildDate & "#, '" & DLookup("EmployeeId", "tbEmployee", "UserName='" & mUserName & "'") & "'"

CurrentProject.Connection.Execute (StrSql)



'打开新记录编辑
        Dim fm As Form
        Set fm = New Form_fmTBuyInvoice  '打开新窗口
        fm.RecordSource = "select * from tbTBuyInvoice where InvoiceId='" & mId & "'"
        fm.AllowAdditions = False  '表头不可编辑
        fm.Caption = "编辑发票" & mId
        fm.Visible = True
        coTBuyInvoice.Add Item:=fm, Key:=CStr(fm.Hwnd)
        Set fm = Nothing
NewTBuyInvoice_Exit:
    Exit Sub
NewTBuyInvoice_Err:
    MsgBox Err.Description
    Resume NewTBuyInvoice_Exit

layaman_999

回复需要审核？