Changing a Query's Recordset Type via Macro or Module

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

Hi - Because I often use queries to examine my data (e.g.,
sort, filter etc.)I change the "RecordsetType" Property
to "Snapshot" in all of them so that I never accidentally
change values while quickly examining the data. When I do
want to make changes to the values, however, I always have
to go into the design View and make the Recordset Type
change manually.

What I would like to be able to do is attach a macro or
module to a toolbar button or shortcut key that would
change the RecordsetType to "Dynaset (Inconsistent
Updates)" to enable me to edit the records.

Can someone please tell me if this is possible and if so,
how I would go about creating the macro or module.

Thanks
Rob
 
Once set, the Recordset Type property is read-only. In other words, you
can't change the type of a recordset: you have to open a new recordset of
the desired type.
 
Snapshots and dynasets are completely different, internally. You can not
"change" one to the other, at runtime. You have to recreate the snapshot
with the type you want.

But this is all what >forms< are for. Use a wizard to create a continuous
form for your table. By adjusting the form properties, you could make it
look just like a query does. Set the form's AllowEdits and AllowDeletions
properties to False, to stop the users editing or deleting records. Then
have a command button with a Click event that changes those properties to
True. Then the users can click that button, when they need to edit or delete
records.

HTH,
TC
 
Thanks for the responses,

Because of the number of queries that I regularly create I
find making forms for each too cumbersome. For me,
queries are the way to go and I have always used the
snapshot to view the data and the dynaset to edit the
data.

I realize that the recordset type cannot be changed
without requerying but that is fine. Manually going to
the query properties (via keyboard and mouse)is also fine
for me I just wanted to simplify it for others (i.e.,
putting the step into a button).

I noticed that an "open query" macro can somehow open a
query and set it to Read Only. I have never been able to
find a property setting for queries that would allow the
selection of Read only or Data Editing; hence the reason I
use Snapshot to achieve the same effect

Rob
 
When the query datasheet is open on the screen, it has methods & properties
similar to those of a form. You cn get to those via the ActiveDatasheet
object. There might be some properties that you could set, to make the
datasheet (or perhaps, the individual fields within the datasheet)
non-editable. (I don't have Access here to check, or I could tell you for
sure.) If so, you could have a >single< form that accepted a query name
(typed by the user), opened that query, progrmatically made it read-only,
and provided a button for making it editable again. That way you could have
one form, but any # of queries.

To check out the datasheet properties, write a small unbound form with a
command button that does this:

(untested)

dim p as property
docmd.openquery "some query"
with activedatasheet.properties
debug.print p.name;
on error resume next
debug.print p;
if err.number <> 0 then debug.print err.description;
on error goto 0
debug.print
end with

That will show you the available properties of the datasheet, in the debug
window (type ^g to see them).

HTH,
TC
 
Back
Top