Default query properties

[james4177]

I'm dynamically creating a form - in code - that contains several
labels with bound SQL Querys and modules. I have all of this working
just fine except for one item.

I want some of the querys to have subdatasheets. As I understand it
Access does not create the sub-datasheet, link child field, and link
master field properties by default. Instead, I need to append them to
the properties object for each particular query. I had no problem with
that. I wrote the code that created and set all of the values for these
properties.

Sample code for appending properties:

db.QueryDefs("all_" & rst!ccLName & "_" & rst!ccFname &
"Q").Properties.Append db.QueryDefs("all_" & rst!ccLName & "_" &
rst!ccFname & "Q").CreateProperty("subDataSheetName", dbText,
"completeCountT")

I made a very slight change to the SQL created by the VBA code and now
as the form begins to build I get the message that the property I'm
trying to add (sub-datasheet for a particular query) already exists.
The code change I made was very minor - just changing one criterian
field for another - and shouldn't have made any difference.

Its as if Access learned I wanted to add sub-datasheet and now does it
for me. It doesn't seem to be a global change because I created a new
database and the queries I make in there don't have subdatasheet by
default.

Is there a setting in Access to tell it what default properties to
create for queries?

Thanks for any assitance

James--

 

[Brian Wilson]

I'm dynamically creating a form - in code - that contains several
labels with bound SQL Querys and modules. I have all of this working
just fine except for one item.

I want some of the querys to have subdatasheets. As I understand it
Access does not create the sub-datasheet, link child field, and link
master field properties by default. Instead, I need to append them to
the properties object for each particular query. I had no problem with
that. I wrote the code that created and set all of the values for these
properties.

Sample code for appending properties:

db.QueryDefs("all_" & rst!ccLName & "_" & rst!ccFname &
"Q").Properties.Append db.QueryDefs("all_" & rst!ccLName & "_" &
rst!ccFname & "Q").CreateProperty("subDataSheetName", dbText,
"completeCountT")

I made a very slight change to the SQL created by the VBA code and now
as the form begins to build I get the message that the property I'm
trying to add (sub-datasheet for a particular query) already exists.
The code change I made was very minor - just changing one criterian
field for another - and shouldn't have made any difference.

Its as if Access learned I wanted to add sub-datasheet and now does it
for me. It doesn't seem to be a global change because I created a new
database and the queries I make in there don't have subdatasheet by
default.

Is there a setting in Access to tell it what default properties to
create for queries?

Thanks for any assitance

James--

If the error message is telling you the property already exists, then it
probably does - so your code should catch this error. In other words, if
the property already exists then change its value (or delete and re-create
it) and if it doesn't then create and append the property.

 

[james4177]

I did essentially what you suggested. I created some code to verify the
properties existence before appending it.

The main reason I posted was because I found the behavior puzzeling.
The first dozen or so times the code ran it added the property as I
expected. Then for no apparent reason every time I added the query def
to the querydefs object it included the subdatasheetname property by
default.

I was curious to see if anyone knew of a property that caused the Jet
Engine to learn that specific Access properties were being set
frequently and to then include them by default.

Thanks for your response

James--