addnew not adding in order

[al_puff]

I have a sub that is supposed to split a table (which is imported from excel) into two tables based on a value in the column "Award Categroy" (sic). This main table "list" is in alphabetical order, and the two new tables should be, too. However, sometimes, this sub does not add the entries to the new tables in order. For example, table "A" will start with last names beginning with H. Here is my code for the sub:

Private Sub split_Click()
On Error GoTo Err_split_Click

Dim dbs As Database

Dim Master As Recordset
Dim Accepted As Recordset
Dim Rejected As Recordset

Set dbs = CurrentDb()

Set Master = dbs.OpenRecordset("list")
Set Accepted = dbs.OpenRecordset("A")
Set Rejected = dbs.OpenRecordset("DandR")

clearA (Accepted.RecordCount) 'clear table "A" if not empty
clearDandR (Rejected.RecordCount)

If Not Master.EOF Then
Master.MoveFirst

Do Until Master.EOF

If (Master("SA#ACTION") = "A") Then

Accepted.AddNew
Accepted("SA#ACYR") = Master("SA#ACYR")
Accepted("LAST") = Master("LAST")
Accepted("FIRST") = Master("FIRST")
Accepted("Student Awards") = Master("Student Awards")
Accepted("Award Amount") = Master("Award Amount")
Accepted("SA#ACTION") = Master("SA#ACTION")
Accepted("Award Categroy") = Master("Award Categroy")
Accepted.Update

Else

Rejected.AddNew
Rejected("SA#ACYR") = Master("SA#ACYR")
Rejected("LAST") = Master("LAST")
Rejected("FIRST") = Master("FIRST")
Rejected("Student Awards") = Master("Student Awards")
Rejected("Award Amount") = Master("Award Amount")
Rejected("SA#ACTION") = Master("SA#ACTION")
Rejected("Award Categroy") = Master("Award Categroy")
Rejected.Update

End If

Master.MoveNext

Loop

End If

MsgBox "Table Split Complete", vbInformation, "Finished"

Master.Close
Accepted.Close
Rejected.Close

Exit_split_Click:
Set dbs = Nothing
Set Master = Nothing
Set Accepted = Nothing
Set Rejected = Nothing
Exit Sub

Err_split_Click:
MsgBox Err.Description
Resume Exit_split_Click

End Sub

I have tried running it in debug, and it seems like the addnew/update methods are the problem. How can i make this always add the next new record below the previously added one (in the two split tables)? Also, is there any way i should clean up my code (i'm a n00bie). Thanks in advance

 

[KenHigg]

What are you concerned about the order they get appended in?

???
ken

 

[al_puff]

yes... I want the appending order to be alphabetical for the two new tables, just like the original master table

 

[KenHigg]

Build a new query based on the master list where the query sorts it. Then use that query as the source for master recordset


???
ken

 

[al_puff]

But the master list is already in alphabetical order. That's the problem. Shouldn't the add method add the records from the alphabetical master list in order in the two tables?

 

[KenHigg]

You are absolutely correct. The only problem I can see is that your master is not in alphabetical order. But, does it really matter what order they are in?

???
ken

 

[Brian1960]

Try Accepted.MoveLast before you add .. Mind you i don't know why you need this either.

 

[al_puff]

yes. I have a function which counts how many people have records in the two tables (one person can have multiple records). The way my function works, it must be in the same order that the master list was. I guess i'll just have to keep troubleshooting...

 

[al_puff]

tried accepted.moveLast...still has the same problem...and like you said, it shouldn't be necessary. The problem seems so random...sometimes, the tables will be in order, but other times, they won't. I ran it like 20 times in a row, and about 1 out of every 4 tries was wrong.

 

[KenHigg]

Hum...

Let us know if Brian's suggestion works...

ken

 

[al_puff]

I did a little more debugging...i can definately see that the addnew function is sometimes skipping to the middle of the table and inserting records there. How is that even possible?

 

[KenHigg]

I wonder if you did something like:

Accepted!LAST = Master!LAST

would work?
ken

 

[al_puff]

no, that won't work either. I think i'm just gonna give up. I made sure that the master list was sorted and i checked out all the addnew/update code, but it still has these anomalous results. oh well...

 

[WayneRyan]

al,

When you store data in a table, that's all you are doing is
storing it. If you just open the table, or base a recordset
on the table, there is no guarantee as to what order the
records will be presented in. You don't insert records into
a "particular position" in the table.

However, when you build a query/form/report you will surely
want to specify the order they are presented in.

I didn't closely read all of the posts in this thread, but
I'm thinking that you want your table to "look" like an
Excel spreadsheet with everything in order. It only has
to appear that way for forms/queries/reports.

Anyway, need more specifics here. Are AutoNumbers the
confusing issue here?

Wayne

 

[KenHigg]

If you work through your master recordset and did a msg box displaying each value as you go down this list, would they be in order?

???
ken