Help Debugging VBA Code

xljunkie

Board Regular
Joined
May 20, 2011
Messages
92
This month, our database isn't running a process the same as it has in the past. Specifically when it gets to rsV.NoMatch, in a past model the code has continued through the rsV.AddNew procedure, but this month, it will always skip this section and go to the Else statement after.

When I look at the data in qryAppendForecasts, the data looks the same in both months. tblVolumes starts out blank in both cases. Any ideas?


Code:
    Set rsV = db.OpenRecordset("tblVolumes")
    rsV.Index = "ImportIndex"
    
    Set rs = db.OpenRecordset("qryAppendForecasts")
    If Not rs.EOF Then
        rs.MoveFirst
        Do
            rsV.MoveFirst
            rsV.Seek "=", rs!COC, rs!Plant, rs!mf5an8
            If rsV.NoMatch Then
                rsV.AddNew
                    rsV!PlantNum = rs!Plant
                    rsV!ShipTo = rs!mf5an8
                    rsV!COC = rs!COC
                    fld = Format(rs!drq, "mmm") & "Est"
                    rsV.Fields(fld) = rs!Vol
                    rsV!SalesRep = rs!mf5Oss
                    rsV!CMI = rs!CMI
                rsV.Update
            Else
                rsV.Edit
                    fld = Format(rs!drq, "mmm") & "Est"
                    rsV.Fields(fld) = rs!Vol
                rsV.Update
            End If
            rs.MoveNext
        Loop While Not rs.EOF
    End If
    rs.Close
    rsV.Close
    
    Set rs = Nothing
    Set rsV = Nothing
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,900
I dont see why your are doing this in code.
Cant you run a single update query?
 

xljunkie

Board Regular
Joined
May 20, 2011
Messages
92
I was able to resolve this issue by taking tblVolumes from the old working model and copying over the tblVolumes in the newer model. I'm not entirely sure why it wasn't working as the field settings seemed to be the same in both tables, but there must have been some small differences in the structure of the table.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,203
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top