UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> 'movenext' Doesn't Process Records In Table Order, Access 2016    
 
   
MisterChips
post Jun 19 2017, 03:23 AM
Post#1



Posts: 6
Joined: 29-January 17



Hi there
I have code that uses a small table of 250 records which is linked to another database.
When I review the records in the table they are in Surname order, as I need them.
(This is true for both the remote table and also the linked table in the database I am running the code in).

When I run the following code, the records seem to be selected at random from the table.
Normally, this isn't an issue, except for when there is a transport issue with Outlook, and only some get sent.

EmailTable = "t_email_data_" & Combo247 'Client '#################################
Set rsEmail = db.OpenRecordset(EmailTable) '# COMPILE EMAIL FROM ABOVE INFO #
rsEmail.MoveLast '#################################
rsEmail.MoveFirst
Do While Not rsEmail.EOF

Code which uses MS Outlook to send out Emails

rsEmail.MoveNext


Is there something I can do to get the code to take the table records in sequence?

Many thanks
Go to the top of the page
 
BuzyG
post Jun 19 2017, 03:44 AM
Post#2



Posts: 281
Joined: 20-September 12
From: Cornwall UK


I would create a query based on your table. It's good practice to use a query and not the base table and it gives for more flexibility. Copy all the fields into the query. Then set a sort to organise the data in the desired order. Then open the query and use that.

Hope that Helps and Welcome.

--------------------
Live to Surf
Go to the top of the page
 
GroverParkGeorge
post Jun 19 2017, 07:29 AM
Post#3


UA Admin
Posts: 29,758
Joined: 20-June 02
From: Newcastle, WA


Tables have NO intrinsic order. The fact that records may appear to be in one sort order or another when you look directly at the table is irrelevant to how they may be processed in an operation such as the one you want to perform.

To work with records in any specific sequence, you must write a query that imposes that specific sequence on the records by applying an "ORDER BY" clause which specifies how those records should be ordered.


--------------------
Go to the top of the page
 
DanielPineault
post Jun 19 2017, 08:42 AM
Post#4


UtterAccess VIP
Posts: 4,949
Joined: 30-June 11



The others have explained the issue, below is one way of implementing it

CODE
Set rsEmail = db.OpenRecordset(EmailTable)

becomes
CODE
Set rsEmail = db.OpenRecordset(SELECT * FROM EmailTable ORDER BY [Surname];)


In the above, I use SELECT * when in reality it is preferable to only include those fields you are actually working with, but not having your complete code or knowing the details of your table I could not further optimize the above, but you should take a moment to further refine it.

Also, why are you performing a .MoveLast followed by a .MoveFirst? The only reason to do so is to retrieve the RecordCount, is that what you are doing? Otherwise, remove it.

--------------------
Daniel Pineault (2010-2016 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this".
* A user interface is like a joke, if you have to explain it, it's not that good!
Go to the top of the page
 
MisterChips
post Jun 26 2017, 12:23 PM
Post#5



Posts: 6
Joined: 29-January 17



Many thanks for your advice.
I got back from holiday ... used a query to sort as required.
Excellent results.
Many thanks
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    28th June 2017 - 09:09 PM