Code only working if stepped through.

G

Grunners

Registered User.
Local time
Today, 00:37
Joined
Jun 25, 2002
Messages
59
Hi all,

Strange one this. I've a simple module that does some calculations based on a record and when a field hits a certain value a date is added to the record and a new record created.

If I simply run the code it loops through and once finished there's an extra record in the table. If I set a break point and step through the code it works perfectly.

It's as if the code execution is going too fast!

Any ideas much appreciated...
 
My idea is to post the code... might help us a bit. Sounds sarcastic, but true ;-)
 
It's not good looking code I'm afraid.

SORTED!

I am an idiot, please accept my apologies for time spent.
 
Last edited:
Grunners said:
SORTED!

I am an idiot, please accept my apologies for time spent.
Click to expand...

Can you tell me what you did, i think im also an idiot ;) since im having the same problem. Stepping thru the program seems to work but executing it from the form doesnt work
thanks
 
Continued...

Basically I had a recordset and a loop as mentioned in the first post.

It turned out that when I ran the code straight thru the recordset was populating with x number of records. However if I set a breakpoint it would still populate with the same number of records, but in the order I actually wanted.

The problem seemed to be because there were two records with the same date. It would order them by date but put the record i didn't want first. As the record had just been created with an 'addnew' the only thing that could be in it was the date field. In the end I've just altered the code to add one day on to the newly created record and then it would always appear first in the list.

Still, the problem remains. If I remove the ammended code and try running it I get incorrect results. If I set a breakpoint and step thru the exact same code it works perfectly. This was probably why it took so long to find the problem - the debugger said it was fine even when it wasn't.

If anyone can shoot me down in flames please feel free!:D
 
Again...

Unless you post your code I have no idea how on earth you expect anyone to help.
 
Sorry RM, I was replying to the previous post by Mcirvine. It turned out to be a recordset that I wanted the first record from. This was ordered by date and was giving me the second record as there were two records with the same date - the 'second' being a complete record and the first being only partially populated. And it's fixed now in any case.

Sorry for the misunderstanding.
 
Grunners said:
Sorry RM, I was replying to the previous post by Mcirvine.
Sorry for the misunderstanding.
Click to expand...

LOL No problem, I was addressing Mcirvine too. You have fixed your problem, so you are OK. If Mcirvine wants to know whats wrong with his code, he's either going to have to post it, or find a psychic VBA debugger...
 
I'm the psychic vba debugger - the answer is 42. Now, what was the question? :D

I wouldn't expect the sort order to change simply because you are stepping through the code - all things being equal, the same recordset should be returned either way. Maybe you need to specify a sort order in the recordset.
 
reclusivemonkey said:
LOL No problem, I was addressing Mcirvine too. You have fixed your problem, so you are OK. If Mcirvine wants to know whats wrong with his code, he's either going to have to post it, or find a psychic VBA debugger...
Click to expand...

I posted my problem in another thread before i saw this one ;). When i read this one it sounded like the same problem i have, when running the code from my command button it is not doing what i want, if i step thru it with the debugger i get sometimes the records inserted that i need and other times just not all the records. I hope someone can help me. I'm not a vba programmer so any hints on how i can improve my work would be appreciated, and a solution for my problem too :).

http://www.access-programmers.co.uk/forums/showthread.php?t=98790

****below a smaller program i wrote with simular problem.
Code: 
Private Sub Command11_Click()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim rs2 As ADODB.Recordset
    Dim rs3 As ADODB.Recordset

    Dim sumTegen As Double
    Dim sumBoek As Double
    Dim roundError As Double
    Dim maxScen As Integer 'value of last scenario id
    Dim sqlstmt As String
    Dim firstTime As Integer
    
    
' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=C:\CAS\cost_accounting.mdb;"
     
'open a recordset
    Set rs = New ADODB.Recordset
    Set rs2 = New ADODB.Recordset
    Set rs3 = New ADODB.Recordset

    DoCmd.SetWarnings (No)
    maxScen = DLookup("[Max Scen]", "Max Scen Id")
    i = 1
    firstTime = 1
    sqlstmt = "UPDATE [CURR_SCEN_ID] SET[currScenID] = " & [i]
    DoCmd.RunSQL sqlstmt
    With rs
          .Open "SELECT Sum(WORK_LEDGER.SUM) as Expr1, WORK_LEDGER.REK_NR, WORK_LEDGER.CC_NR, WORK_LEDGER.REVERSE_ENTRY, WORK_LEDGER.EURO_CODE, WORK_LEDGER.COST_TYPE FROM (CURR_SCEN_ID INNER JOIN SCENARIO ON CURR_SCEN_ID.CurrScenID = SCENARIO.ScenID)" & _
          "INNER JOIN WORK_LEDGER ON SCENARIO.CC_NR = WORK_LEDGER.CC_NR GROUP BY WORK_LEDGER.REK_NR, WORK_LEDGER.CC_NR, WORK_LEDGER.REVERSE_ENTRY, WORK_LEDGER.EURO_CODE, WORK_LEDGER.COST_TYPE HAVING (((WORK_LEDGER.REVERSE_ENTRY)=-1));", cn, , , adCmdText
    With rs2
          .Open "SELECT Sum(WORK_LEDGER.SUM) as Expr1, WORK_LEDGER.REK_NR, SCENARIO.CC_NR, WORK_LEDGER.EURO_CODE FROM (CURR_SCEN_ID INNER JOIN SCENARIO ON CURR_SCEN_ID.CurrScenID = SCENARIO.ScenID)" & _
          "INNER JOIN WORK_LEDGER ON SCENARIO.CC_NR = WORK_LEDGER.PREV_CC WHERE (((WORK_LEDGER.REVERSE_ENTRY)=0)) GROUP BY WORK_LEDGER.REK_NR, SCENARIO.CC_NR, WORK_LEDGER.EURO_CODE;", cn, , , adCmdText

Do While i <= maxScen
    
    Do While rs.EOF = False And rs2.EOF = False
        sumTegen = rs.Fields(0)
        
        sumBoek = rs2.Fields(0)
        roundError = Round((sumTegen + sumBoek), 2)

        If firstTime = 1 Then
            rs3.Open "WORK_LEDGER", cn, adOpenKeyset, adLockOptimistic, adCmdTable
            firstTime = 9
        End If

        If roundError <> 0 Then
        With rs3
         .AddNew
         .Fields("REK_NR") = rs.Fields(1)
         .Fields("CC_NR") = "900006"
         .Fields("SUM") = -roundError
         .Fields("EURO_CODE") = rs.Fields(4)
         .Fields("PREV_CC") = rs.Fields(2)
         .Fields("COST_TYPE") = rs.Fields(5)
         .Fields("REVERSE_ENTRY") = 0
         .Update
        End With
        End If
        rs.MoveNext
        rs2.MoveNext
    Loop
    i = i + 1
    sqlstmt = "UPDATE [CURR_SCEN_ID] SET[currScenID] = " & [i]
    DoCmd.RunSQL sqlstmt
    rs.Requery
    rs2.Requery
Loop
    rs.Close
    Set rs = Nothing
    rs2.Close
    Set rs2 = Nothing
    rs3.Close
    Set rs3 = Nothing
    cn.Close
    Set cn = Nothing
    End With
    End With
End Sub
 
Last edited:
problem solved using DAO now instead of ADO. seems that the odbc driver was giving me ttroubles
 
You must log in or register to reply here.

Similar threads

C
Help with looping through records
Replies
11
Views
645
cricketbird
C
D
Solved Determine What Code Is Still Running?
2
Replies
26
Views
2,065
NauticalGent
NauticalGent
R
VBA not working for saved query parameters
2
Replies
24
Views
760
RonPaii
RonPaii
J
Match the primary Key in the Main form to the foreign key in the Subform
Replies
9
Views
343
MajP
MajP
E
Solved Add Record VBA Not working
Replies
18
Views
1,185
E9-Tech
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom