Assign Recordset to Subform Help

[Brandon Johnson]

Im having problems with tieing the recordset to the subform that i have
presented on my form. I get a runtime 91 error when it tries to set the
subform to promoteSub recordset. The code is as follows. does anyone
know how i could fix that.

Dim promoteSub As DAO.Recordset

Set promoteSub = CurrentDb.OpenRecordset("SELECT * FROM
qryPromotedItems WHERE PromoteGroup = " & Chr$(34) & cboPromote &
Chr$(34) & " ORDER BY PromoteGroup ASC;", dbOpenDynaset)
Me.frmPromote.Form.Recordset = promoteSub

promoteSub.Close

 

[Marshall Barton]

Im having problems with tieing the recordset to the subform that i have
presented on my form. I get a runtime 91 error when it tries to set the
subform to promoteSub recordset. The code is as follows. does anyone
know how i could fix that.

Dim promoteSub As DAO.Recordset

Set promoteSub = CurrentDb.OpenRecordset("SELECT * FROM
qryPromotedItems WHERE PromoteGroup = " & Chr$(34) & cboPromote &
Chr$(34) & " ORDER BY PromoteGroup ASC;", dbOpenDynaset)
Me.frmPromote.Form.Recordset = promoteSub

promoteSub.Close

I would not do this the way you're trying to do it, but I
think you have to SET the recordset property. I also don't
think you should close the recordset, setting promoteSub to
Nothing would be appropriate though.

Is there a reason why you are not assigning the form's
RecordSource to the SQL statement?

 

[Brandon Johnson]

Im going to be having about 25 people accessing this same table so i
figured it would be beneficial to assign a recordset as a temporary
holding container so it will load the results into the subform and
immediatly close the connection rather tahn having a stable connection
with the table forever. If that makes any sense. now you suggest that i
dont do it this way? If i could, could i get your input on what your
thinking on this particular idea. I thought about it and i just dont
want to have any conflicts going on.

 

[Brandon Johnson]

Keeping in mind though to is that the subform is in datasheet view.
thanks for your prompt response on this matter. if you have any ideas,
please let me know.

 

[Marshall Barton]

There is a lot of confusion going on here, so much that I
don't even know if it's you, me or both of us that's
confused.

Your concern for an open connection is possibly valid in
some situations if you were using ADO, but I don't use that
so I can comment on that intelligently. However, you
declared the recordset with DAO and as a Dynaset so you can
not "disconnect" the recordset from the table.

In general, Access/Jet handles multiple users to the same
table pretty well using either optimistic or pessimistic
locking. The drawback with those record locking strategies
is that they lock a page of 4K (2K in older versions) bytes
so one user could, under just the right circumstances, block
another user even when they are not operating on the same
record. The later versions of Access/Jet also have record
level locking that you can use if it's really needed. (See
Help for details)

Since you are discussing multiple users, you must have split
your application into a front end/back end arrangement, in
which case maintaining an open "connection" to the table is
one of the recommended performance tweaks because making and
breaking the "connection" can be a very time consuming
operation.

Given all that and regardless of the confusion, I think you
are probably worried about nothing.

 

[Brandon Johnson]

ok well thankyou very much for the input. ya i was worried about the
system locking if two people happened to access the same information at
the same time. If what you say is correct then i should be in the clear
as far as i understand. i will just set the subform to the SQL that
corrisponds to what im looking for. thanks so much.

There is a lot of confusion going on here, so much that I
don't even know if it's you, me or both of us that's
confused.

Your concern for an open connection is possibly valid in
some situations if you were using ADO, but I don't use that
so I can comment on that intelligently. However, you
declared the recordset with DAO and as a Dynaset so you can
not "disconnect" the recordset from the table.

In general, Access/Jet handles multiple users to the same
table pretty well using either optimistic or pessimistic
locking. The drawback with those record locking strategies
is that they lock a page of 4K (2K in older versions) bytes
so one user could, under just the right circumstances, block
another user even when they are not operating on the same
record. The later versions of Access/Jet also have record
level locking that you can use if it's really needed. (See
Help for details)

Since you are discussing multiple users, you must have split
your application into a front end/back end arrangement, in
which case maintaining an open "connection" to the table is
one of the recommended performance tweaks because making and
breaking the "connection" can be a very time consuming
operation.

Given all that and regardless of the confusion, I think you
are probably worried about nothing.
--
Marsh
MVP [MS Access]

Im going to be having about 25 people accessing this same table so i
figured it would be beneficial to assign a recordset as a temporary
holding container so it will load the results into the subform and
immediatly close the connection rather tahn having a stable connection
with the table forever. If that makes any sense. now you suggest that i
dont do it this way? If i could, could i get your input on what your
thinking on this particular idea. I thought about it and i just dont
want to have any conflicts going on.