[AccessD] weird query def

Jim Dettman jimdettman at earthlink.net
Tue Jul 1 12:54:39 CDT 2003


John,

  The original developer is Paul Litwin.  The code was published as part of
an article for Smart Access, published by Pinnacle.

  As to the copyright issue, I'm not sure if that would reside with Paul or
Pinnacle, but certainly not me.  While I did make substantial improvements
to it, they were based on the original work, which is why Paul's name still
appears in the merge module.

  I would assume that Pinnacle would hold the copyright if there is one, but
that's a guess on my part.  I'm not sure how copyrights are handled when an
author publishes.  Susan could probably tell you more about that.

Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Colby
Sent: Tuesday, July 01, 2003 12:52 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] weird query def


Jim,

Where did it come from originally?  I looked but see names but no copyrights
or organizations.  I have done a ton of work on it including extracting much
of the functionality into a class.

John W. Colby
www.colbyconsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim Dettman
Sent: Tuesday, July 01, 2003 10:26 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] weird query def


John,

  I'm glad I never tried to extend that interface into a MDA.  Sounds like
it's a real problem.

Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Colby
Sent: Tuesday, July 01, 2003 10:06 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] weird query def


>Could it be a null concatenation problem?

The query was filtered such that it returned no data and the querydef still
says it has no fields.  In the end it doesn't matter why it happens, only
that it does.  I am trying to build a system where a database administrator
sets up mail merge documents by selecting a query, a merge doc, and then the
wizard allows him to select (match) a field from the query and a bookmark
from the doc.  It is easy to do this manually for a single document but I
have a system where they will have dozens of documents, and want to be able
to add tem as they need.  The wizard needs to allow the dba to easily set up
a new document for mail merge visually, in an Access form.

It was all working, but it was embedded inside the database where the mail
merge was taking place.  I am trying to extract the concept into an MDA and
make it a true wizard, that can be used from any FE by anyone with the
smarts to understand the concept.  This problem didn't exist before simply
because the entire thing was performed inside the FE itself.  In fact the
combo displaying the query field list did so simply by using the field list
row source type.  Doing that requires the QUERY to be inside the wizard, a
no-no since the query belongs to the FE OR that the wizard be inside the FE
which makes it non-portable.  Thus my attempt to generate a comma delimited
list of field names for the combo to use with a value list row source type.

This actually works quite well with most queries.  However as soon as you
build a query with an aliased field where the data returned is run through a
custom function inside the FE where the query is stored, the querydef field
list "disappears".  Yep, just disappears.  No fields in the querydef.fields
collection!

8-(

John W. Colby
www.colbyconsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mike and Doris
Manning
Sent: Tuesday, July 01, 2003 9:50 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] weird query def


Could it be a null concatenation problem?

Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: Tuesday, July 01, 2003 9:25 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] weird query def


I've discovered what the problem is.  The query in question uses a function
to append City, State and zip into a single string.  That function is
defined in code in the FE where the query resides.  Without that field in
the query its fields show up in the query def.  With that function in the
query, the qureydef thinks it has no fields.  This seems pretty strange to
me since I am using the code that Gustav provided, opening the FE as a
database, setting a qdf from its querydefs collection and manipulating that.
You'd think it could see the functions inside the database but apparently
not.

This is a real show stopper since the wizard needs to be able to grab the
query field names to match against the bookmarks in the doc being merged.

Bummer!

John W. Colby
www.colbyconsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Colby
Sent: Monday, June 30, 2003 12:57 PM
To: AccessD
Subject: [AccessD] weird query def


I have a weird one.  I am opening a query def and reading all of the field
names out into a comma delimited string to use as a row source for a combo.
All that stuff works.  One of the queries in the db I am trying to work on
says it has NO fields.  If I execute the query I get data.  If I look at the
querydef itself, I can see the name of the query, the SQL property, etc. But
ask it for the .Fields.Count and it comes back 0.

Very unsettling since I need to use this method for displaying field names
to the user to select to match against document book marks in a mail merge
wizard.

I compacted / repaired both the wizard as well as the FE that actually holds
the query in question.  Two other queries that I am performing this
operation on work perfectly.  One apparently has no fields.

John W. Colby
www.colbyconsulting.com


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list