ADO recordsets and Access2000 forms 1

[markgrizzle]

Hi:
I'm using an ado recordset as my unbound form's recordsource. This is done in the the form's open event. However, when the form is closed and reopened, it's recordsource has been set to a sql statement, which fails since the table the sql references isn't linked in the db.

On a related note, can I also use an ado recordset for the form's subforms? And if so, how do I keep the subform's data synced to the main form?

Thanks
ps - I'm using the code in an access article (

http://support.microsoft.com/default.aspx?scid=kb;en-us;281998)

mentioned by Roy Vidar in his response to a similar post.

 

[sin1965]

are u developing an access project and sql server?

 

[markgrizzle]

The backend is sql server, but the frontend's an mdb.

 

[sin1965]

what version of access r u using?
Access 2000 cannot bind to ado recordsets. I haven't used access 2002 or 2003.

 

[RoyVidar]

sin1965,
1 - markgrizzle states they are using Access 2000 (subject)
2 - check out the link markgrizzle posted (which also contains a link to a Access 2000 specific page). You can bind forms in Access 2000 to ADO recordsets, you just won't get them updateable if they're based on jet - and as markgrizzle points out, they are using SQL-server, so usual binding to updateable recordsets are all covered within the link.

markgrizzle, I don't think I quite understand your setup. You're talking about unbound forms, ADO recordsets and a forms recordsource.

To bind a form to an ADO recordset, you don't use the recordsource property at all, you assign the recordset to the forms recordset property.

Or are you working unbound - just assigning values to the controls?

In any case, if you put anything within the recordsource property of the form, for safety, you can for instance assign vbNullString to it both in the on open and the on unload event of the form, though, I can't see any reason the property should be changed, and it should be sufficient to just remove the sql string there once and for all in design view.

I have no experience with the usage of binding ADO recordset for subforms too, but I suspect the link child/master fields wont work, and that you'll probably need to filter the subform recordset per each on current of the main form.

Roy-Vidar

 

[sin1965]

sorry, i just glanced at the main post

 

[markgrizzle]

Thanks Roy/Sin,
To recap, I was trying to use an unbound form's open event to create an ado recordset, and update the form's recordset property.

The recordsource property wasn't set. I saved and closed the form. The next time the form loaded, I got the message that it couldn't find the table referenced in the sql statement, and the recordsource property had been set to a sql statement.

While composing this response, I tried it again, the form's recordsource property is still being updated, but now the form load error message isn't occuring.

I do also use a combo box on the form to choose a new current record, but it's change event code is the same as the form.open code. I suspect the problem may be related to this.

 

[markgrizzle]

FYI:
I just added me.recordsource = vbnullstring to the form's open event, and it solved the problem.

As always Roy, thanks again.
Mark