Problem reading correct values from table into variables

[aworsley]

When I run the following code, instead of reading values from the last record, the variables are being assigned field values from a much earlier record (records are sorted by date). It seems to randomly select the record to read from! This used to work correctly. I have verified that CurrentRecord is indeed the last record in the table. I added the Pause in case it needed more time to read the last record.
Here is code...
DoCmd.GoToRecord , , acLast 'go to end of table
MsgBox (CurrentRecord)
Pause (4) 'wait 4 seconds
mcurCaBal = [CashBalance] ' load field values into variables
mcurChBal = [CheckBalance]
mcurOBal = [OfficeBalance]
mcurBBal = [BankBalance]
mcurGTot = [GrandTotal]

 

[Gasman]

MsgBox or debug.print the ID of the record after your goto.

 

[theDBguy]

Hi. Welcome to AWF!

Contrary to popular belief, tables are not really sorted in any particular way. That's why we use queries to do the sorting. Which field is supposed to be sorted? You might be able to use DMax() on that field.

 

[DHookom]

I would place a breakpoint in the code so the values can be seen by hovering the pointer over your code. Also, use Me.[CheckBalance]

 

[GPGeorge]

When I run the following code, instead of reading values from the last record, the variables are being assigned field values from a much earlier record (records are sorted by date). It seems to randomly select the record to read from! This used to work correctly. I have verified that CurrentRecord is indeed the last record in the table. I added the Pause in case it needed more time to read the last record.
Here is code...
DoCmd.GoToRecord , , acLast 'go to end of table
MsgBox (CurrentRecord)
Pause (4) 'wait 4 seconds
mcurCaBal = [CashBalance] ' load field values into variables
mcurChBal = [CheckBalance]
mcurOBal = [OfficeBalance]
mcurBBal = [BankBalance]
mcurGTot = [GrandTotal]

One should never rely on "first" or "last" in an unordered recordset, as theDBGuy already mentioned.

"Last" is a relative position, not an absolute position.

Think of it this way. Each time a horse runs a race, it could be:

• first across the finish line for that race,
• or it could be the last across the finish line for that race,
• or it could be any any position across the finish line for that race

That order of finish will change for any given race, with the probability that a given horse will tend to finish toward the first, or toward the last, depending on certain other factors.

The point is, bettors cannot confidently place their bets on that horse every single time it runs.

The same is true when dealing with unsorted recordsets in a relational database application. You simply can't expect the "last" record to be the one you are looking for.

Only if you set things up so the outcome goes your way by applying a sort order can you predict the outcome.

I suppose you could liken that to a trainer rigging races so their horse always finishes first (or last), but in horse racing, that's cheating. In Access, that is being explicit about what you want.

 

[aworsley]

Hi. Welcome to AWF!

Contrary to popular belief, tables are not really sorted in any particular way. That's why we use queries to do the sorting. Which field is supposed to be sorted? You might be able to use DMax() on that field.

Thanks for the quick response. I found the problem. The code was on a form. I noticed that the SortBy property of that form was blank. Setting SortBy to "Date" (the sorted field in the table) seems to have solved it. Since there was no such issue two weeks ago, wonder what could have erased the SortBy property value. Could a Windows update do this?

 

[The_Doc_Man]

wonder what could have erased the SortBy property value. Could a Windows update do this?

While we (correctly) heap all sorts of scorn on Windows Updates and their undesirable side-effects, Windows knows NOTHING about your app beyond (a) the name of the file and (b) that it is an Access file. That's IT. Therefore, I'm going to say "highly unlikely" to your question.

More likely is that you happened to save the form when you intended to save just the record, and for some reason the SortBy property was clear at that time.

As to table order, your solution of the "Sort By" does tend give you better control over the meaning of "acLast" when you have a meaningful sort key available. As near as we can tell, and this is more guess than guarantee: When you update a record, no matter where it was in the set of records for that table, that "most recently updated" record will become the record selected by that GoToRecord...acLast if you don't have an explicit sort operation. I.e. acLast will by default be the last record you modified in some way. But of course an ORDER BY in a query or something in the SortBy property overrides that behavior.

 

[Mark_]

Thanks for the quick response. I found the problem. The code was on a form. I noticed that the SortBy property of that form was blank. Setting SortBy to "Date" (the sorted field in the table) seems to have solved it. Since there was no such issue two weeks ago, wonder what could have erased the SortBy property value. Could a Windows update do this?

Are you saving calculated fields in your tables? [CashBalance] looks like the type of value you would want to calculate rather than try and "Save". Reason is the value can become incorrect quickly if you have more than one user or process dealing with your table that could be changing this value. Hate for you to be reporting you've money in the bank when your overdrawn, simply because two people were entering transactions at the same time and one overwrote the other's update.

 

[theDBguy]

Thanks for the quick response. I found the problem. The code was on a form. I noticed that the SortBy property of that form was blank. Setting SortBy to "Date" (the sorted field in the table) seems to have solved it. Since there was no such issue two weeks ago, wonder what could have erased the SortBy property value. Could a Windows update do this?

Glad to hear you got it "sorted" out.

Hope you picked up on what we were telling you. Applying a sort order to a recordset is the "only way" to ensure what you're looking for is what you will get.

On that note, with the solution you ended up using, you might want to update the comment in your code from this:

DoCmd.GoToRecord , , acLast 'go to end of table

into this - to make it more specific.

DoCmd.GoToRecord , , acLast 'go to end of Form

Good luck with your project.