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
Issa S.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertConsultingCommented:
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.
Dale FyeOwner, Dev-Soln LLCCommented:
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

Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Issa S.Author Commented:
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.
Issa S.Author Commented:
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.
Issa S.Author Commented:
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?


According to relational theory, tables and queries are UNORDERED sets.  It makes absolutely no difference what the physical order is of the records on disk.  Query engines build recordsets at their convenience and they are multi threaded so multiple subsets can be being build simultaneously.  The ONLY way to ensure a recordset is in a predictable order is to SORT it on a UNIQUE ID or set of columns.  Any sort on a non-unique set of columns will result in potentially different results.  So, if all you are sorting on is date and date is not unique, then each time you run the query, the rows for any given date could be returned in a different order.  That is simply the way relational databases work.  If you have a problem with the order, you must know something you are not telling the query.  Try adding additional columns to the Order By clause.
Dale FyeOwner, Dev-Soln LLCCommented:

My sql doesn't do any updates, it merely compute running balance based upon the values in your table.  If you need to update the Balance column, then you are going to need to do it via code, or take the results of my query, export it to a temp table, and then join that temp table to your root table and perform an update query.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Issa S.Author Commented:
Many  Thanks.
I did it all via queries and it is working well with your guidance.
Dale FyeOwner, Dev-Soln LLCCommented:
glad I could help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.