JiscMail Logo
Email discussion lists for the UK Education and Research communities

Help for ACCESS-VBA-DISCUSS Archives


ACCESS-VBA-DISCUSS Archives

ACCESS-VBA-DISCUSS Archives


ACCESS-VBA-DISCUSS@JISCMAIL.AC.UK


View:

Message:

[

First

|

Previous

|

Next

|

Last

]

By Topic:

[

First

|

Previous

|

Next

|

Last

]

By Author:

[

First

|

Previous

|

Next

|

Last

]

Font:

Proportional Font

LISTSERV Archives

LISTSERV Archives

ACCESS-VBA-DISCUSS Home

ACCESS-VBA-DISCUSS Home

ACCESS-VBA-DISCUSS  July 2003

ACCESS-VBA-DISCUSS July 2003

Options

Subscribe or Unsubscribe

Subscribe or Unsubscribe

Log In

Log In

Get Password

Get Password

Subject:

Re: Display a querydef recordset as a query datasheet

From:

"Winter P.D." <[log in to unmask]>

Reply-To:

Technical aspects of Access and Visual Basic list <[log in to unmask]>

Date:

Fri, 11 Jul 2003 14:16:32 +0100

Content-Type:

text/plain

Parts/Attachments:

Parts/Attachments

text/plain (146 lines)

Martin

I wonder whether [interview invite query] is a query on another query in
which you are asking for a parameter? 

Paul 

> -----Original Message-----
> From: Rushton, Martin [LIS] [mailto:[log in to unmask]] 
> Sent: 11 July 2003 13:46
> To: [log in to unmask]
> Subject: Re: Display a querydef recordset as a query datasheet
> 
> 
> Paul,
> thanks.  I was hoping to avoid writing the SQL string in VBA 
> but it looks like I'll have to even if it isn't creating the 
> entire query (nightmares here I come but at least its 
> practice).  I take it that wrap has caused WHERE to start a 
> new line.  Fortunately my parameters will all be text.
> 
> Unfortunately though it doesn't work. Despite providing the 
> parameter for qtemp (it's in the design grid even if I leave 
> the prompt blank) the openquery prompts me with a dialog box 
> for the ref parameter!!
> 
> The SQL for qtemp reads
> 
> SELECT *
> FROM [interview invite query]
> WHERE [Master_Ref] = "WLCA/23/05/03";
> 
> Interview invite query now has no parameters
> 
> The parameters will be coming from controls on a form which 
> will vary by user selection. The sub for the query will fire 
> from an event sub on the form which will also open the 
> mailmerge main document in word.  The open event of the Word 
> document will handle the mailmerge.  I prefer the VBA being 
> in Word because Word's VBA seems more like the Excel VBA I am 
> familiar with (particularly when it comes to methods) and 
> Word also has the macro recorder.
> 
> Martin
> 
> 
> 
> > -----Original Message-----
> > From: Winter P.D. [SMTP:[log in to unmask]]
> > Sent: Friday, July 11, 2003 12:23 PM
> > To:   [log in to unmask]
> > Subject:      Re: Display a querydef recordset as a query datasheet
> >
> > Martin
> >
> > What you could do is create a query identical to "interview invite 
> > query" with no parameters. Have a temporary query which 
> you'll use for 
> > storing the actually query to be run (e.g. qTemp)
> >
> > Then you could use something like
> >
> > Dim db As Database
> > Dim rs As Recordset
> > Set db = CurrentDb()
> > Set qd = db.QueryDefs("qTemp")
> >
> > strSQL = "Select * from [interview invite query with no parameters] 
> > WHERE [Ref] = " & _
> >          CHR(34) & "=lss/lcm/09/05/03" & CHR(34)
> >
> > qd.SQL = strSQL
> > DoCmd.OpenQuery("qTemp")
> > Set db = Nothing
> > ----
> >
> > You'll have to add the additional parameters  by using lines like 
> > strSQL = strSQL & " AND [Other Field] = " & CHR(34) & "stuff" & 
> > CHR(34)
> >
> > Or if it is a number field then
> > strSQL = strSQL & " AND [Number Field] = 25"
> >
> > Or if it is a date field then
> > strSQL = strSQL & " AND [Date Field] = #mm/dd/yyyy# "
> >
> >
> > How are you going to deal with the mailmerge in VBA?
> >
> >
> > Paul
> >
> > > -----Original Message-----
> > > From: Rushton, Martin [LIS] [mailto:[log in to unmask]]
> > > Sent: 11 July 2003 11:43
> > > To: [log in to unmask]
> > > Subject: Display a querydef recordset as a query datasheet
> > >
> > >
> > > I am trying to execute an existing parameter query by passing 
> > > variable parameters via VBA and then display the results in query 
> > > datasheet mode for use in a mail merge.
> > >
> > >
> > > From one of the sites Paul recommended in answer to my 
> last post and 
> > > a search on google I have got this far. Sub test() Dim db As 
> > > Database 'current database Dim rs As Recordset 'holds query 
> > > resultset Dim qd As QueryDef 'the actual query object
> > >
> > >   Set db = CurrentDb()
> > >   Set qd = db.QueryDefs("interview invite query")
> > >   qd.Parameters![Please Enter Ref:] = "lss/lcm/09/05/03"
> > > .
> > > .
> > > .
> > > .
> > >   Set rs = qd.OpenRecordset()
> > > qd.execute
> > >  End Sub
> > >
> > > The dots represent parameters I want to add when I get it to work.
> > >
> > > My problem is the last 2 lines from the different sources. 
> > > OpenRecordset doesn't display the query results and execute won't 
> > > work because its not an action query.  Now I can see a couple of 
> > > solutions here neither of which I'd like to do.
> > >
> > > First, I could make it a make (temporary) table Action 
> query do the 
> > > mail merge from the table and then delete the table. This to me 
> > > seems like taking a pile driver to crack a nut. Alternatively I 
> > > could create a new query on the fly everytime from 
> scratch in VBA.  
> > > Well I'm an Access novice still struggling to get to grips with 
> > > Access VBA and SQL so the thought of creating a query in 
> the former 
> > > using the latter would give me nightmares.
> > >
> > > Oh how I wish OpenQuery had a Parameter argument to pass 
> parameters 
> > > to the query.  Can anybody suggest another solution or 
> will I have 
> > > to resort to the pile driver or nightmares??
> > >
> 

Top of Message | Previous Page | Permalink

JISCMail Tools


RSS Feeds and Sharing


Advanced Options


Archives

May 2011
April 2011
May 2008
April 2008
January 2008
October 2007
July 2006
September 2005
August 2005
March 2005
December 2004
July 2004
June 2004
April 2004
March 2004
September 2003
July 2003
June 2003
April 2003
March 2003
February 2003
January 2003
November 2002
August 2002
July 2002
June 2002
May 2002
March 2002
February 2002
January 2002
December 2001
November 2001
October 2001
September 2001
August 2001
July 2001
June 2001
May 2001
April 2001
March 2001
February 2001
January 2001
December 2000
November 2000
October 2000
September 2000
August 2000
July 2000
June 2000
May 2000
April 2000
March 2000


WWW.JISCMAIL.AC.UK

Secured by F-Secure Anti-Virus CataList Email List Search Powered by the LISTSERV Email List Manager