DAO Recordset Entries in Table Do Not Stay Saved

[dasalight]

I am having a continuing problem with Recordsets. I use them to add new records (.AddNew/.Update) and if I step through the code, everything works fine. If I run the code normally, there a numerous times (inconsistently) where my table will not have any of the added records. It seems like I can "avoid" this problem by adding a messagebox before closing, but I don't want the user to have a prompt and have to hit "OK" every time I've added records from a recordset. I'd truly appreciate any thoughts, ideas, etc. that I can try as I think I've tried a bunch (adding doevents, sleeps, moving first/last before closing, etc.) all with mixed results. Here is an example of some code:

Set rsSimilarTable = dbCurrent.OpenRecordset("SELECT * FROM [tbl tblName]", dbOpenDynaset, dbSeeChanges)​

DoEvents​

....​

While Not (rs.EOF)​

With rsSimilarTable​

.AddNew​

!SalesOrderNumber = rs!SalesOrderNumber​

!CustomerNumber = rs!CustomerNumber​

!PurchaseOrder = rs!PurchaseOrder​

!OrderDate = rs!OrderDate​

!Value = rs![Value]​

.Update​

DoEvents​

End With​

rs.MoveNext​

Wend​

....​

rsSimilarTable.Close​

doevents​

set rsSimilarTable = Nothing​

....​

' Exit from function/procedure and looking to use the table​

Thanks for your help and input.

 

[dhookom]

I don't know the cause of the issue but I'm curious if insert statements would work. You would need to complete the strSQL and use the appropriate delimiters based on field data type.

strSQL = "INSERT INTO [tbl tblName] (SalesOrderNumber, CustomerNumber, " & _
    "PurchaseOrder, OrderDate, Value) " & _
    "VALUES (" & rs!SalesOrderNumber & ",'" & rs!CustomerNumber & "', " & _
    [COLOR=#4E9A06]' etc[/color]
    debug.Print strSQL  [COLOR=#4E9A06]'take a look at the statement[/color]
Currentdb.Execute strSQL, dbFailOnError

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016