VBA Help in a While Looping not giving consistent results.

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

Select allOpen 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
ORDER BY B.TransID

Select allOpen in new window

HTH
Dale

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.

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.

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?

Thanks

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

glad I could help.