Unbound Form Loads Intermittently via ADO Recordset

[markgrizzle]

Hi:
My Access 2003 mdb form's combo uses the following AfterUpdate event to load it's subform with SQL Server 2000 data...

Me.SubFormName.Form.RecordSource = vbNullString

sql = "SELECT FieldName FROM TableName Where TableNameID = " & me.MainFormCombo_TableNameID

rs.Open sql,cnn,adOpenDynamic,adLockOptimistic,adCmdText

If Not rs.EOF Then
Set Me.SubFormName.Form.Recordset = rs
Me.SubFormName.Visible = True
End If

rs.Close

This works except for records created using...

DoCmd.DoMenuItem acFormBar, acFile, acSaveRecord

Those records display #Name? in each of SubFormName's controls.

I've confirmed that the records are being loaded in the table, and the sql statement returns the expected results when viewed in the query design window.

Has anyone seen this behavior before?

Thanks in advance,
Mark

 

[RoyVidar]

I don't know much about saving on forms bound to ADO recordsets, but I do know there's a thing or two written about methods of saving bound recordsets. Have a look at Allen Brownes article here Losing data when you close a form. Though this link deals with a bug with reagards to closing the form, you might perhaps find something there (hint dirty) that might help.

Else I don't know - could it be some kind of timing issue - I mean - that the save isn't finished when the retrieval is performed?

Roy-Vidar

 

[markgrizzle]

Thanks Roy:
Turns out the solution was to move the ado form loading code to a standalone function, and call it after creatign the new record.

*whew*

Thanks againg for taking the time to respond.
Mark

 

[markgrizzle]

One further comment, set subform.recordset = rs fails with error 3021 if there aren't any rows in the recordset. This prevented all the preceding subforms in the function from loading...

So now I've loaded all those forms with ado, and the last subform's bound via linked tables.