Recordset problem

[zdog]

Hi Guys,
This things been driving me nuts.
What i'm trying to do is copy certain fields from one table into another.

Heres part of the code I'm using...

Dim rst as dao.recordset
Dim rst1 as dao.recordset

Set rst = CurrentDb.OpenRecordset("Entry_Log")
Set rst1 = CurrentDb.OpenRecordset("Premium Freight Log")

rst1.MoveLast
rst.AddNew

rst.Fields(0).Value = rst1.Fields(0).Value
rst.Fields(1).Value = Now()
rst.Fields(2).Value = rst1.Fields(13).Value
rst.Update

The problem is with the rst1.MoveLast line.
The last entry in the table has Field 0 value 558 (autonumber field)
however when I step through the above code it displays rst1.field(0).value = 338

What am I doing wrong over here. Your help would be much appreciated.

Thanks guys

 

[RoyVidar]

The concept of "first", "last"... isn't really a relational concept. If you wish a certain order when you open a recordset, try using an order by clause:

Set rst1 = CurrentDb.OpenRecordset("select * from [Premium Freight Log] order by somefield")

 

[RoyVidar]

...oh - but if you can perform this action by executing a query instead of using recordsets, that would be preferrable

 

[zdog]

Thanks for the response Roy. I did end up using a query, worked perfectly.

Just out of curiosity, what is the movefirst/last command supposed to do???

 

[modest]

MoveLast forces the entire recordset to load. When it is complete, it points to the last record of the recordset

MoveFirst points to the first record of the recordset


Generally to make sure all your records load you want to call the .MoveLast and then make sure you start at the beginning with .MoveFirst



Edit:
You should add some error checking in your code, in case no records are returned by the query. For instance:

Set rst = ..
rst.MoveLast
rst.MoveFirst
If Not (rst.BOF and rst.EOF) Then
   <insert your code here>
End If

BOF means beginning of file (in this case file is the recordset). EOF means end of file (again file = recordset). If BOF and EOF are the same then there are no records returned... so the operations inside the IF statement are only performed when records are returned.

 

[RoyVidar]

In relational theory, both "rows" and "columns" of the tables are supposed to be unordered. Ordering, is supposed to be achieved throug queries. Order by clause for rows and the order of the field list in the select clause for columns.

Access is a database management system based on relational principles, so this is also true for Access. MoveLast and MoveFirst, is as modest explaines, methods to move to the first or last record in the recordset based on the current sort order of the recordset. If you have given a sort order (order by clause), then you will probably get results in the correct order. If you haven't given any, then... To make a slight comparision, you've got two interesting Domain Aggragate functions, DLast and DFirst, which intuitively should give the first and last match from a table. Sometimes it does, perhaps based on the index, but look what it says in the help files on DFirst "Use the DFirst function to return a random record from a particular field in a table or query" and a bit later, which relates a bit more to this discussion "Note If you want to return the first record of a set of records (a domain), you should create a query sorted as ascending or descending and set the TopValues property to 1"

So to get some order - use a query with an order by clause - just wonder why an order by clause won't work for kids...

 

[modest]

So to get some order - use a query with an order by clause - just wonder why an order by clause won't work for kids...

Roy, I think he is using a query and probably the orderby clause as you suggested. I think he was only asking what .movelast and .movefirst do because he was curious and wanted to expand his knowledge of Access.

 

[RoyVidar]

Moderating again, modest?

Please, again, do not do that! It's OK to correct any direct failures/errors, but leave the evaluation of whether a reply is relevant or not, to the original poster.

I answered based on the original question, where .movelast seems to be used to get the last record of the recordset. That is the purpose of .movelast, and how it does work. But what Access perceives as the last record of the recordset may or may not be what one think it is, based on whether or not one have applied an order by clause when opening the recordset. In the original question, no order by clause was applied.

You give some relevant information on the movelast and movefirst methods, but, in my eyes, do not relate it to the original question (why do I get autonumber 338 when my last inserted autonumber (read "last record inserted") is 558).

But in stead of using something like this,

"yo modest, I don't think you answered the question, see, in the initial question, the original poster has issued a .movelast, to move to the end of the recordset, so to me, it seems they are aware af that funcionality. The problem seems to be that there's no order by clause used when opening the recordset..."

Perhaps also said something along the lines of, "no - no, you generally do not want to fully populate the recordset, unless you depend on a fully populated recordset for some reason. If you're just looping through the recordset for some edits, then issuing a .movelast/.movefirst is a complete waste of recourses. Only fully populate if it is absolutely necessary."

attacking your reply, I simply answered what I think/thought was asked, and refrained from commenting on other replies.

 

[modest]

haha, okay roy.. take it however you want. i still love you.


just know, it's my job to understand people and get people what they want. in this post it looks like all he wanted was a way to easily sort his data, which you helped him with.

then the only question he had was what did .movelast/.movefirst do? this is probably because he looked at someone else's code and used it, thinking it did something different (probably in terms of moving data around or sorting the information). when he found out that it didn't do what he expected it to, he was probably curious as to what they actually do... and just wanted to learn more vba. so why not ask the question when we were on the subject?

again, this is just MY interpretation of what was going on. and i take no offense to what you have said roy, as i know you're a good guy. and please don't take any offense to what i've said.

cheers