recordset update method question

[devo96]

I have a question about the Recordset Update method in Access 2010 VBA using the DAO.Recordset. If I add a new record using the .AddNew method, where is the record inserted? At the end of the query regardless of the sort declared in the .OpenRecordset method? And if i add a new record that does not meet the criteria in the .OpenRecordset method, it will be added to the table, but is it added to the recordset? Example is below...

Is the new record added to the recordset? If so, where?

Dim db as database
Dim rs as DAO.Recordset

set db = CurrentDB
set rs = db.OpenRecordset("SELECT * FROM [a query] WHERE [theDate] < #" & _
    dateVariable & "# AND [Change] = 0 ORDER BY [theDate]")

'add a new record
rs.AddNew
rs![theDate] = dateVariable        'the same date as the statement above
rs![Change] = 1                    'in the SQL statement above...Change=0
rs.Update

 

[Galaxiom]

It is added to the end of the recordset regardless of the criteria. If you want it in a particular place then either Sort the recordset or open it again.

Surely this would have been easily tested?

 

[devo96]

thank you for your response...
i am doing some testing, but the database i'm troubleshooting is doing some funny things....i'm just trying to figure out exactly what the .update method does (like re-execute the query from .OpenRecordset) to determine if this part of the module is the trouble or if it's elsewhere...it's an inherited project, and i'm trying to figure out exactly how the whole thing works.

i'll post more if it starts to seem impassible....

thanks again,
devo

 

[devo96]

after some more searching, it seems that rs.Requery would be helpful...that would allow for a record to be added by using the current recordset, but not necessarily adding the record to the recordset....
i apologize for such a simple question...it just wasn't making sense.