Setting a form's recordset via VBA is also setting its recordsource

Question

I have an Access 2010 database that's linked to a SQL Azure backend (yes, I know this isn't ideal, and it's being slowly phased out). On the backend, I have a stored procedure that I want to use to populate a read-only subform each time a new record is loaded. I'm attempting to do this by generating a recordset in VBA, then setting the subform's RecordSet property. It actually works, but with a nasty side-effect.

When I set the RecordSet property, it also seems to be setting the RecordSource property of the subform. That RecordSource is something Access can't parse, because it's meant to be a call to the backend. If I try using a DAO passthrough query to generate the recordset, the RecordSource looks like:

EXEC dbo.GetDuplicateAddressesByManufacturer N'...', N'...', N'...'

If I try using an ADO command to generate the recordset, it looks like:

{ call dbo.GetDuplicateAddressesByManufacturer ?, ?, ? }

As soon as I try to move to the next record, Access throws an error because it tries to load a new record for the subform first, and it can't open what it sees as the subform's RecordSource. If I'm trying the DAO route, it tells me "Invalid SQL Statement", and if I'm using ADO, it tells me "Data provider could not be initialized."

Anyone have an idea how I can get around this? Isn't there a way to set the RecordSet property without also setting the RecordSource? I could swear I've done that before, but maybe I've just never noticed that the RecordSource gets set too.

Failing that, is there a way I can interject some code before the Form_Current event to remove the subform's RecordSource? The code I'm using to set the RecordSet each time works great -- the problem is the error that's raised before my code works. Once I dismiss the error message, everything works fine, but obviously I don't want users to get an error message each time they change records.

If all else fails, I guess I could always use the query to populate a local temp table, but it seems like a lot of overhead to do every single time someone moves to a new record.

Albert D. Kallal Albert D. Kallal · Accepted Answer · 2014-10-18T21:18:11

Why bother with a stored procedure? Just link the sub form to the table, and setup the link master/child settings. You only pull down the required records.

If the sub form is a complex query with multiple tables, then you certainly want the data joins etc. to occur server side and AGAIN simply create a view and again set the sub form source to that view (and again the link master/child settings will do all the dirty work for you).

And there no reason why you cannot create a pass-through query and SIMPLE assign that to the forms recordSource.

It does not matter what “junk” you place inside of the query, and that includes RAW T-SQL.

And while you can load up the DAO reocrdset with this pass-thought, you really don’t need to. I suppose for some happy reason you are doing this, and at least if you must, then recordSoruce becomes the name of the pass-though and NOT your raw T-SQL anyway.

However, really, just dump all that recordset junk, and just go:

Me.MySubForm.Form.RecordSource = "my pass though query".

Thus above is only one line.

You doing all these hand stands to increase performance then at the end of the day why does your main form allow navigation? You should build a search screen, display the results, let user pick a row and THEN launch the main form to edit/display the ONE record along with the sub form data.

When they close, they are back to searching for the next customer etc. This approach also thus solves your messy navigation issues. It also why the web and most software works this way (it reduces bandwidth issues).

However, if you must have navigation, and for some reason CANNOT use a view and cannot let Access use the link master/child settings to do your dirty work?

Then in the forms on-current event you can modify the pass-though and simply re-assign it to the sub form.

Eg:

With CurrentDb.QueryDefs("qPass")
   .SQL = "select * from FaxBook3 where id = 3"
End With
'
Me.RecordSource = "qpass"

Now how in the above I am using RAW T-SQL in the pass-though query, and then simply assign the pass-though to the forms recordsource (in your case you assign to the sub form.

 Me.MySubForm.Form.RecordSource = above

And there NO reason why the above .SQL cannot be your stored procedure

.SQL = "Exec your-storedProcedure " & strVbaStringParmater

And again assign the form (or sub form) recordSource.

So you REALLY do not need to create some reocrdset in code and it not yield you any performance increase, but will cause you to write more code and have problems as you outlined in your post.

Setting a form's recordset via VBA is also setting its recordsource

1 Answers