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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
2,171
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,505
Messages
5,832,117
Members
430,111
Latest member
Francis Xavier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top