Specified row could not be located when adding child record (Adodc)

[MikeCox]

Hi. I have a situation where users typically add a new parent record, and then associated child records immediately afterward. I've found that if I don't refresh the main (parent) recordset, I get the above error when they try to add a child record. So I created a public sub in the main form to which I've delegeted the refresh operation, and the add-record form calls this sub after validating the new record info. Here's an example of my code:

'in frmMain:
'Adodc1.CommandType = adCmdText
'Adodc1.RecordSource = "SELECT * FROM tblSiteInfo ORDER BY CLLICode

Public Sub RefreshRecordset(SiteID as String)
Adodc1.Refresh
'Refresh sets recordset position to first record, so find the record they just added for user's convenience:
Adodc1.Recordset.Find "SiteID = '" & SiteID & "' ", , adSearchForward, 1
End Sub

'in frmAddRecord:
'after validation:
frmMain.txtFields(0).Text = SiteID
frmMain.Adodc1.Recordset.Update
frmMain.RefreshRecordset SiteID

The frustrating thing about this is after adding a new record, the Adodc doesn't find the new SiteID, like it was never added. But if I refresh the recordset again, it's there. And when I add breakpoints and run in debug mode, it works every time! (that seems to happen to me a lot) Does anyone see what I may be doing wrong? Thanks!

 

[MichaelRed]

Not reviewing the code. The description of the symptoms suggests a timing problem. e.g. The process] is correct, however you need a delay between some parts of it.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MikeCox]

That was my impression, too, since running the code in debug break-mode is solid as a rock, but each line of code should be "modal" to the rest of the program (emphasis on should). How else could a command take a trip to a slow server without choking on it every time? I dunno, maybe instead of refreshing the recordset, I'll try closing and opening it again.

 

[MikeCox]

I found the fix. I just had to run a repair/compact database from Access. Works great now every time!