CODEWITHSUNDEEP
ms-accessvbams-access-2010
Jim S
Jim S

Reputation: 1

VBA code works in Access97 but not 2010

The 1st record in the open order table matches a record in the Bookings table but the NO MATCH = True is happening and therefore it goes down thru the code anf tries to insert a new record. This is true for several records in the file and it tries to add the record even though there is a match. If I set the NO MATCH = False then it does the else. I imported these table from Access 97 to 2010 where it is working correctly. Any help would be appreciated.

Additional note: While in Debug, If I hover the mouse over the .Seek "=", TempCust, TempPart fields, it shows the 1st record in the table and that data is in the Bookings table. Not understanding why it is not matching?

Sub Get_Current_Info()

    DoCmd.SetWarnings False

    Dim rstOpenOrd, rstBookings As Recordset
    Dim TempCust, TempPart, TempQty, TempDollars As Variant

    Set rstOpenOrd = CurrentDb.OpenRecordset("Open Orders", dbOpenTable)
    Set rstBookings = CurrentDb.OpenRecordset("Bookings", dbOpenTable)

    'Get the open orders
    Do While Not rstOpenOrd.EOF

        With rstOpenOrd
            TempCust = !ODCSNO
            TempPart = !ODITNO
            TempQty = !ODQTOR
            TempDollars = !OrdDollars
        End With

        With rstBookings
            .Index = "PrimaryKey"
            .Seek "=", TempCust, TempPart

            If rstBookings.NoMatch = True Then
                With rstBookings
                    .AddNew
                    !cusno = TempCust
                    !PrdNo = TempPart
                    !Qty_booked = TempQty
                    !Dol_booked = TempDollars
                    !Yest_qty_booked = 0
                    !Yest_dol_booked = 0
                    !Shipped_qty = 0
                    !Shipped_dol = 0
                    .Update
                End With
            Else
                With rstBookings
                    .Edit
                    !Qty_booked = !Qty_booked + TempQty
                    !Dol_booked = !Dol_booked + TempDollars
                    .Update
                End With
            End If
        End With

        rstOpenOrd.MoveNext

    Loop
End Sub

Upvotes: 0

Views: 62

Answers (2)

Gustav
Gustav

Reputation: 56026

You must dim your variables or they are just Variant/Object:

Dim rstOpenOrd As DAO.Recordset
Dim rstBookings As DAO.Recordset

Upvotes: 0

HansUp
HansUp

Reputation: 97131

This line suppresses information, including many types of error information ...

DoCmd.SetWarnings False

I don't see why you would want it at all in this procedure. But, at least during troubleshooting, make sure SetWarnings is on ...

'DoCmd.SetWarnings False
DoCmd.SetWarnings True

The point is that you need every possible tidbit of information you can get while troubleshooting. Don't suppress any of it.

The code would not do what you expect if the Bookings table does not include an index named PrimaryKey, or if that index does not include cusno and PrdNo (in that order) as its first 2 keys.

But that is just speculation. You need to test with SetWarnings on and see whether Access gives you useful details.

Upvotes: 1

Related Questions