access database runtime error 3340 on one machine but not on another

Question

I have a form that closes purchase orders using a checkbox. The form works normally on my computer but not on another. The message says the query is corrupt.

I don't understand why this works perfectly on my machine but not another. I should also say that this form worked on the other computer until last week. Here is the entire code for the form if this helps

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

Possible duplicate of [Getting Error 3340 Query ' ' is corrupt while executing queries DoCmd.RunSQL](https://stackoverflow.com/questions/58832269/getting-error-3340-query-is-corrupt-while-executing-queries-docmd-runsql) — Andre, Nov 20 '19 at 22:16

score 0 · Accepted Answer · answered Nov 20 '19 at 19:09

0

There is a Microsoft update the causes this. This is a related article from Microsoft.

answered Nov 20 '19 at 19:09

SunKnight0

3,331
1
10
8

Thank you so much, I had my co-worker follow the instructions and download the update. All is working fine now. – Rats Nov 20 '19 at 19:37

access database runtime error 3340 on one machine but not on another

1 Answers1