Link to home
Create AccountLog in
Avatar of Issa S.
Issa S.

asked on

VBA Help in a While Looping not giving consistent results.

Hi All,

Q:   VBA Help in a While Looping not giving consistent results.

I have made a VBA Sub to loop in a file and update a field in all records according to a defined formula.
It is to pick up the First value from the first record and loop to the rest of the file.

It picks up the First Value sometimes correctly and sometimes from (God Knows), especially on re-run!!!
I think, as I was reading some posts, that it is picking from somethings hanging in the memory!!
Attached is the source code for it.

Help Please
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Wow, wow, wow ........

Can you upload the database instead ?
And precise explanantion of what it is supposed to do will help.

My first tought is you try to do too many things at once.
1. remove dead code.
2. No gratuitous comments

You didn't post the query but queries and tables are unordered sets.  If order is important, then you MUST use a query and the query MUST include an Order By clause that orders the recordset on a unique identifier.  If the order by clause is not unique, then records within the matching values will be returned in different orders at the convenience of the the query engine.
Ok, so it is obvious that what you are attempting to do is get the balance after each transaction.  Is there a datetime field in  your table which would allow you to identify the sequence of the transactions, or maybe an ever increasing ID field?  If so, your query that selects the records should sort by that column, as well as the Account# or ClientID (I'm assuming ClientID).

The way I generally do this is to create a query.  The first step is to identify each record and the next record in the sequence, for a particular person.  Since you rarely need to compute these things for multiple clients at a time, I tend to do my filtering for the client in the subquery, to minimize the number of records considered in the join.  I would start out with something like:
SELECT T.ClientID, T.TransID, Min(N.TransID) as NextTransID
FROM (SELECT ClientID, TransID FROM yourTable WHERE ClientID = 23) as T
LEFT JOIN (SELECT ClientID, TransID FROM yourTable  WHERE ClientID = 23) as N
ON T.ClientID = N.ClientID
AND N.TransID > T.TransID
GROUP BY T.ClientID, T.TransID

Open in new window

This query would be saved or could be embedded in the SQL for the following query.  I'll assume it would be named "yourQuery" for the purposes of this next query.  Then you can use this is a joining table to join your table to itself.  I'm terrible with the parenthesis Access adds to queries, but it would look something like:
SELECT B.ClientID, B.BegBal, N.Debit, N.Credit, NZ(B.BegBal, 0) + NZ(N.Debit, 0) + NZ(N.Credit, 0) as NewBal
FROM yourTable as B
INNER JOIN yourQuery as Q ON B.ClientID = Q.ClientID and B.TransID = Q.TransID
INNER JOIN yourTable as N on Q.ClientID = N.ClientID AND Q.NextTransID = N.TransID

Open in new window

Avatar of Issa S.
Issa S.


Dear  Dale,,
Thanks for your effort and Patience with me.

- I  am glad that you understood what am after. I do have a date field in my table but the query that prepares it is ordering it in ascending order on this date field.
- I am trying to understand and do your above suggestions.
- I need as you said "Ok, so it is obvious that what you are attempting to do is get the balance after each transaction."  

My Objective is:
      1-1  Start  with               BegBal = 0     before entering into the looping.
      1-2  For te first record      New.Bal =  BegBal +  N.debit - N.Credit  
                                                      (since N.Debit and N.Credit = 0 on this record,  then  NewBal will result = itself),
                                  Set        BegBal = NewBal
     1-3 For the second record onwards:     NewBal  =  BegBal + N.debit - N.Credit
                                  Set        BegBal = NewBal
                                                      (i.e.Same formulas in 1-2).
- My table is prepared by a collection of queries and it is assembled based on date sequence, so I thought there is no need make another query on it to order by date.       . . .   so shall I do this and introduce the new query as the record set or the SQL will do the job  ??

- The table is cleared on every entry and appended with new record set, so its ID  is an ever going one.
The first record is picked up by one query and inserted as the first record.
The rest on the records are collected and appended after this one.
The results of the table are fine and then I am introducing it to the loop .

Problem is : If I run it for 2-6 times (without changing anything in its selection criteria Form, (Client, start and end dates),
it runs well but then after few runs, it starts collecting the first record with values that are of no relation to the correct data!!!

My main problem in the table is basically confined to the first record starts well and then starts changing its value and back  again to the correct value in a random manner  when I run it few times .   So it is not something to rely on!!!.

I was reading about this and found something saying that I am not closing my record set properly after my loop.
Can you confirm if  my closing in not proper in my sub  ??
If the order of the records is not reliable, there is one of two problems.
1. You don't have an Order By clause on the query
2. The Order By is not on a unique value or set of values.  If you look at your checking account, you will see that the checks are not processed in check number order.  They are processed (and so affect the balance) by the order in which they are processed so the bank assignes some unique identifier to each check/deposit based on the sequence in which it is processed.
Avatar of Issa S.


Dear Pat / Dear Dale,,,

I was relying on the fact that the INSERT Query, Which has the Order By on a date Field, is doing the Ordering correctly.

In fact, it was showing this correctly visually. However, it seems that the fractional part of the date was (probably) not correct.

So, when I made a new query for the resultant table and sorted it again on the same Date field, records within the same dates changed their position. Introducing this to as the Record Set to the loop made it to work correctly and like a rocket... lol.

Thanks a Lot for your Advice and help.

Now I wish to do this again (for the sake of learning) via SQLs and avoid used interim temporary tables.

Any clue on this pls.
a- Can I stack my SQLs into one Macro or an Event Private Sub ?   or
b- Manage the first SQL as input to the subsequent one  ?   or
c- Something else ?

Thanks a lot.
Avatar of Issa S.


Dear Dale,,,
I am little bit confused about your suggested SQL up here.

- What makes the second SQL update the current  record for  BankBal = BankBal (of the previous record) + D - C   and so on?

Avatar of PatHartman
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Issa S.


Many  Thanks.
I did it all via queries and it is working well with your guidance.
glad I could help.