Can a parameter be fed to a CrossTab Query using VBA? 
Author Message
 Can a parameter be fed to a CrossTab Query using VBA?

Hello,

I have a series of software contracts that are Word 2000 mail merge
documents with a MS Access CrossTab query for a datasource. Basically, I
would like to have my user to double click on the proper Word Template
for a given software contract. A dialog would pop up and a dropdown
combo would display company names from the Access database . The company
name selected would then be passed to the Access crosstab query as a
parameter and that would ultimately fill the mail merge doc.

The query works great in Access only and the mail merge doc is created.
But getting the company name to drive the query is escaping me. I want
to use VBA code instead of hard wiring a parameter [Parameter] into the
query. Here what I have so far as an event in the Word template and it's
not working:

Private Sub CommandButton1_Click()

  Dim doc As Document
  Dim mrg As MailMerge
  Dim strSQL As String
  Dim db As String

  db = "D:\Program Files\Platinum Software\Clientele 7.0\ctel.mdb"

  'Open the main document.
  Set doc = Documents.Open("F:\Testing\WordMerge1.doc")
  'Create a SQL string to select the source data.
  strSQL = "SELECT * FROM qryOrgsandProducts_CrossTab1 " & _
  "WHERE ((qryOrgsandProducts.SearchName)= Renaissance Energy Ltd.))"

  'Return a MailMerge object for the document.
  Set mrg = doc.MailMerge
  With mrg
   ' If the data source isn't attached...
   If .State = wdMainDocumentOnly Then
   ' connect to the data source.
->      .OpenDataSource Name:=db, sqlstatement:=strSQL
   'End If
   ' Determine the destination.
   .Destination = wdSendToNewDocument
   ' Perform the mail merge.
    .Execute
  End With
  Set mrg = Nothing
  Set doc = Nothing

End Sub

Two bad things are happening. I keep getting "Error 5922 - Word was
unable to open the data source." And my strSQL does not get passed to
the database. I can't even test if my Select statement is working. I
don't think it is.

This 5922 error is happening right at the arrow. The info I have been
able to grab indicates that my querystring is longer than 255
characters.

Any help out there?

Bruce



Sat, 09 Nov 2002 03:00:00 GMT  
 Can a parameter be fed to a CrossTab Query using VBA?
Hi Bruce

The problem is in the Where clause of your select statement.  Because
SearchName is a text field, the selection value (Renaissance Energy Ltd.)
must be enclosed in quotes.

Try this:

Quote:
>   strSQL = "SELECT * FROM qryOrgsandProducts_CrossTab1 " & _
>   "WHERE SearchName=' Renaissance Energy Ltd.';"

Note the single quotes (apostrophes) around the company name.
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.

Please post new questions to newsgroups.


Quote:
> Hello,

> I have a series of software contracts that are Word 2000 mail merge
> documents with a MS Access CrossTab query for a datasource. Basically, I
> would like to have my user to double click on the proper Word Template
> for a given software contract. A dialog would pop up and a dropdown
> combo would display company names from the Access database . The company
> name selected would then be passed to the Access crosstab query as a
> parameter and that would ultimately fill the mail merge doc.

> The query works great in Access only and the mail merge doc is created.
> But getting the company name to drive the query is escaping me. I want
> to use VBA code instead of hard wiring a parameter [Parameter] into the
> query. Here what I have so far as an event in the Word template and it's
> not working:

> Private Sub CommandButton1_Click()

>   Dim doc As Document
>   Dim mrg As MailMerge
>   Dim strSQL As String
>   Dim db As String

>   db = "D:\Program Files\Platinum Software\Clientele 7.0\ctel.mdb"

>   'Open the main document.
>   Set doc = Documents.Open("F:\Testing\WordMerge1.doc")
>   'Create a SQL string to select the source data.
>   strSQL = "SELECT * FROM qryOrgsandProducts_CrossTab1 " & _
>   "WHERE ((qryOrgsandProducts.SearchName)= Renaissance Energy Ltd.))"

>   'Return a MailMerge object for the document.
>   Set mrg = doc.MailMerge
>   With mrg
>    ' If the data source isn't attached...
>    If .State = wdMainDocumentOnly Then
>    ' connect to the data source.
> ->      .OpenDataSource Name:=db, sqlstatement:=strSQL
>    'End If
>    ' Determine the destination.
>    .Destination = wdSendToNewDocument
>    ' Perform the mail merge.
>     .Execute
>   End With
>   Set mrg = Nothing
>   Set doc = Nothing

> End Sub

> Two bad things are happening. I keep getting "Error 5922 - Word was
> unable to open the data source." And my strSQL does not get passed to
> the database. I can't even test if my Select statement is working. I
> don't think it is.

> This 5922 error is happening right at the arrow. The info I have been
> able to grab indicates that my querystring is longer than 255
> characters.

> Any help out there?

> Bruce



Sun, 10 Nov 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. create a table from a crosstab parameter query in VBA

2. Using Parameters from VBA Code in a Parameter Query Export

3. a crosstab + parameter query question..

4. How to supress a prompt when running a report via VBA that uses parameter queries

5. VBA for crosstab query

6. parameter query in VBA when parameter not a field

7. Please tell me what I am doing wrong - DAO parameter Append Query :(

8. SQL for CROSSTAB QUERIES using Oracle 7.3 / ADO

9. Using Parameter Queries with Optional Parameters

10. Passing Parameters to stored parameter queries using VB 5's Data Controls

11. multiple queries into a crosstab query in VB

12. multiple queries into a crosstab query in VB

 

 
Powered by phpBB® Forum Software