ms-access - 在一台机器上访问数据库运行时错误 3340 但在另一台机器上却没有

问题描述

我有一个使用复选框关闭采购订单的表单。该表格在我的电脑上正常工作，但在另一台电脑上却不行。消息说查询已损坏。

我不明白为什么这在我的机器上完美运行，但在另一台机器上却不行。我还应该说，直到上周，此表格都在另一台计算机上工作。如果有帮助，这是表单的完整代码

Private Sub Complete_BeforeUpdate(Cancel As Integer)
    Dim WeekNum, YearNum, count As Double
        WeekNum = DLookup("[WeekNum]", "[PurchaseOrders]", "[PONum] = '" & PONum & "'")
        YearNum = DLookup("[YearNum]", "[PurchaseOrders]", "[PONum] = '" & PONum & "'")
    Dim queryTemp As DAO.Recordset
    Set queryTemp = CurrentDb.OpenRecordset("SELECT [UsedPartNum],[Quantity]*" & Qty & " AS Used " _
                                        & "FROM PartsUsed " _
                                        & "WHERE [FinPartNum] = '" & PartNum & "'")

If Complete = True Then
    If MsgBox("You are indicating this order is complete, are you sure the complete order is in shipping ready to go?") = vbYes Then

        MsgBox "You just confirmed that order is done. All inventory moves have been made and this box CANNOT be unchecked or inventory moves will be duplicated."

    End If
    'parts used moved to out
    If Not (queryTemp.EOF And queryTemp.BOF) Then
        queryTemp.MoveFirst 'Unnecessary in this case, but still a good habit
        Do Until queryTemp.EOF = True
            If Not IsNull(DLookup("[Out]", "[Inventory]", "[PartNum] = '" & queryTemp!UsedPartNum & "' AND [YearNum] = " & YearNum & " AND [WeekNum] = " & WeekNum & "")) Then
                'add new to existing quantity going out
                count = DLookup("[Out]", "[Inventory]", "[PartNum] = '" & queryTemp!UsedPartNum & "' AND [YearNum] = " & YearNum & " AND [WeekNum] = " & WeekNum & "")
                CurrentDb.Execute "UPDATE [Inventory] " _
                                & "SET [Out] = " & count + queryTemp!Used & " " _
                                & "WHERE [PartNum] = '" & queryTemp!UsedPartNum & "' AND [YearNum] = " & YearNum & " AND [WeekNum] = " & WeekNum & ""
            ElseIf Not IsNull(DLookup("[In]", "[Inventory]", "[PartNum] = '" & queryTemp!UsedPartNum & "' AND [YearNum] = " & YearNum & " AND [WeekNum] = " & WeekNum & "")) Then
                'add new to existing record
                CurrentDb.Execute "UPDATE [Inventory] " _
                                & "SET [Out] = " & queryTemp!Used & " " _
                                & "WHERE [PartNum] = '" & queryTemp!UsedPartNum & "' AND [YearNum] = " & YearNum & " AND [WeekNum] = " & WeekNum & ""
            Else
                'create new record
                CurrentDb.Execute "INSERT INTO [Inventory] ([PartNum],[YearNum],[WeekNum],[Out],[In]) " _
                                & "VALUES ('" & queryTemp!UsedPartNum & "'," & YearNum & "," & WeekNum & "," & queryTemp!Used & ",0)"
            End If

            queryTemp.MoveNext
        Loop
    End If

    'created parts move in
    If Not IsNull(DLookup("[In]", "[Inventory]", "[PartNum] = '" & PartNum & "' AND [YearNum] = " & YearNum & " AND [WeekNum] = " & WeekNum & "")) Then
        'add new to existing quantity going in
        count = DLookup("[In]", "[Inventory]", "[PartNum] = '" & PartNum & "' AND [YearNum] = " & YearNum & " AND [WeekNum] = " & WeekNum & "")
        CurrentDb.Execute "UPDATE [Inventory] " _
                        & "SET [In] = " & count + Qty & " " _
                        & "WHERE [PartNum] = '" & PartNum & "' AND [YearNum] = " & YearNum & " AND [WeekNum] = " & WeekNum & ""
    ElseIf Not IsNull(DLookup("[Out]", "[Inventory]", "[PartNum] = '" & PartNum & "' AND [YearNum] = " & YearNum & " AND [WeekNum] = " & WeekNum & "")) Then
        'add new to existing record
        CurrentDb.Execute "UPDATE [Inventory] " _
                        & "SET [In] = " & Qty & " " _
                        & "WHERE [PartNum] = '" & PartNum & "' AND [YearNum] = " & YearNum & " AND [WeekNum] = " & WeekNum & ""
    Else
        'create new record
        CurrentDb.Execute "INSERT INTO [Inventory] ([PartNum],[YearNum],[WeekNum],[In],[Out]) " _
                        & "VALUES ('" & PartNum & "'," & YearNum & "," & WeekNum & "," & Qty & ",0)"
    End If

Else
    'Parts used come back in
    If Not (queryTemp.EOF And queryTemp.BOF) Then
        queryTemp.MoveFirst 'Unnecessary in this case, but still a good habit
        Do Until queryTemp.EOF = True
            count = DLookup("[Out]", "[Inventory]", "[PartNum] = '" & queryTemp!UsedPartNum & "' AND [YearNum] = " & YearNum & " AND [WeekNum] = " & WeekNum & "")
            CurrentDb.Execute "UPDATE [Inventory] " _
                            & "SET [Out] = " & count - queryTemp!Used & " " _
                            & "WHERE [PartNum] = '" & queryTemp!UsedPartNum & "' AND [YearNum] = " & YearNum & " AND [WeekNum] = " & WeekNum & ""
            queryTemp.MoveNext
        Loop
    End If

    'created parts come bank out
    count = DLookup("[IN]", "[Inventory]", "[PartNum] = '" & PartNum & "' AND [YearNum] = " & YearNum & " AND [WeekNum] = " & WeekNum & "")
    CurrentDb.Execute "UPDATE [Inventory] " _
                    & "SET [In] = " & count - Qty & " " _
                    & "WHERE [PartNum] = '" & PartNum & "' AND [YearNum] = " & YearNum & " AND [WeekNum] = " & WeekNum & ""
End If
queryTemp.Close
End Sub

标签： ms-access

ms-access - 在一台机器上访问数据库运行时错误 3340 但在另一台机器上却没有

问题描述

解决方案

推荐阅读