MoveLast has no effect

[newboy18]

Please help. I am trying to copy data from one table to the END of another, although the copy works it seem to go where ever it wants.

Set db = CurrentDb()
Set rec1 = db.OpenRecordset("Table1&quot
rec1.MoveFirst
pcpCodes = rec1(0)
mess1 = pcpCodes
rec1.MoveNext
Set rec2 = db.OpenRecordset("PRODUCTS&quot
rec2.MoveLast
Do While pcpCodes > 0 ' Copies data from Table1 to PRODUCTS
pcpCodes = pcpCodes - 1
rec2.AddNew
For cells = 0 To rCount
rec2(cells) = rec1(cells)
Next
rec2.Update
rec1.MoveNext
Loop
rec1.Close: rec2.Close

 

[SgtJarrow]

What I gather from your post is that the data is being appended but not to the end, as you think you want.

If that is the case, there is a flaw in your logic....Access is a relational database program, and relational database can be thought of as not having any specific order. To Access records in order 1,2,3,4,5 are the same as in the order 3,2,5,1,4. It can find the data just fine.

What you should really be concerned with is not the appending. You expressed it works fine. Now you need to concentrate on your sort order in your display.

Find the query that you form or report is based on, find the field that should be in a specific order, and in design mode, look for the property called Sort. Set it to ascending (or descending) and your records should now be in the correct order for display. If we knew what it was we were doing, it would not be called research, would it? - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[newboy18]

Thanks for the advise, the light is starting to shine.
I will add an AutoNumber field at the start, then after I have copied the data I will just need to sort the table.
Please could tell me what VBA commands to use.

 

[SgtJarrow]

You shouldn't need any VBA commands.....

Go to the table and add the autonumber field. It will automatically create numbers for existing records and every record you add after that. Then, in you query, add the autonumber field as the first field, and sort it ascending. You form or report should now be in the correct order... If we knew what it was we were doing, it would not be called research, would it? - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[newboy18]

I see what you mean but, this is a simple database, data is just added to a table and then reports are run, there are no queries, I did not even need a AutoNumber field. All I wanted to do was to either, manualy add data to the end of the table or automaticaly import data to the end of the table.
I guess there must be a way of either inputting the data sequentialy (would not need AutoNumbering) or sorting the table.

 

[SgtJarrow]

Well, the addition of a query is very simple.....Merely go to the query tab, click the new button, use the query wizard, select your table, select all fields including the autonumber field. Then set the autonumber field to ascending as suggested above. Then, open your report in design view, find the report property of recordsource and change this to the query....everythign should now be ordered as you would like.

If you can zip it up (less than 1 MB) and want to, feel free to send it to me at the address below and I will help you out....

Good luck If we knew what it was we were doing, it would not be called research, would it? - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[newboy18]

Thanks but the DB is 17Mb. I found that all I needed to do was select Indexed for the AutoNumber field and that did the trick.
Once again thank for your help.