Re: Parameter query based on text field

From: Tom (anynomys_at_newsgroup.com)
Date: 08/12/04 

Date: Thu, 12 Aug 2004 08:31:15 -0400

Eric:

Thanks for the feedback... this looks very promising.

I added/modified the function as you suggested. The table = "tblSales" and
the query name = "qrySales".

I took out the "-" in the control name... I believe it caused some
confusion, so
the field names have the syntax YYYY-QQ (e.g. '2004Q1').

When I now click on the 2004Q1 textbox, the error listed below (between
&&&s) pops up.

At this time, I have 2 questions:

1. Do you have any idea as to how I can fix the error 3075?

2. Also, I might have dozens of textboxes (which require the same process)
in a single form. Would you recommend a "smarter" or "smoother" approach...
e.g. saving the function into another function... which then can be called
each time??? If yes, how would I do that?

Thanks so much in advance,
Tom

************************************

Private Sub Ctl2004Q1_Click()

    Dim ctl As Control
    Set ctl = Screen.ActiveControl
    CurrentDb.QueryDefs("qrySales").SQL = "Select " & ctl.Name & " From
tblSales"

    DoCmd.OpenQuery "qrySales"
    Set ctl = Nothing

End Sub

************************************

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Run-time error '3075':

Syntax error (missing operator) in query expression '2004Q1'.

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

"Tom" <anonomys@home.com> wrote in message
news:u2uw27$fEHA.3192@tk2msftngp13.phx.gbl...
> Eric:
>
> Thanks for your feedback... I will try this tomorrow morning at work asap.
>
> I'll let you know either way... I probably have a follow-up question
though.
>
> Thanks for watching this thread,
>
> --
> Tom
>
>
> "Eric Butts [MSFT]" <ebutts@online.microsoft.com> wrote in message
> news:3t6ItY$fEHA.740@cpmsftngxa06.phx.gbl...
> > Hi Tom,
> >
> > Add the following code into each OnClick event of the textbox controls
in
> > question:
> >
> > Dim ctl As Control
> > Set ctl = Screen.ActiveControl
> > CurrentDb.QueryDefs("Query1").SQL = "Select " & ctl.Name & "
From
> > Authors"
> > DoCmd.OpenQuery "query1"
> > Set ctl = Nothing
> >
> > NOTES on the above:
> > - Replace "query1" with the name of your Query
> > - Replace "Authors" with the name of your table(s) your query is pulling
> > from
> >
> > I hope this helps! If you have additional questions on this topic,
please
> > respond back to this posting.
> >
> >
> > Regards,
> >
> > Eric Butts
> > Microsoft Access Support
> > ebutts@online.microsoft.com
> > "Microsoft Security Announcement: Have you installed the patch for
> > Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
> > you to review the information at the following link regarding Microsoft
> > Security Bulletin MS03-026
> > <http://www.microsoft.com/security/security_bulletins/ms03-026.asp>
and/or
> > to visit Windows Update at <http://windowsupdate.microsoft.com/> to
> install
> > the patch. Running the SCAN program from the Windows Update site will
> help
> > to insure you are current with all security patches, not just MS03-026."
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights
> >
> >
> >
> > --------------------
> > | From: "Tom" <anynomys@newsgroup.com>
> > | Subject: Parameter query based on text field
> > | Date: Wed, 11 Aug 2004 13:14:18 -0400
> > | Lines: 36
> > | X-Priority: 3
> > | X-MSMail-Priority: Normal
> > | X-Newsreader: Microsoft Outlook Express 6.00.2720.3000
> > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
> > | Message-ID: <uKDBka8fEHA.2352@TK2MSFTNGP09.phx.gbl>
> > | Newsgroups: microsoft.public.access.queries
> > | NNTP-Posting-Host: 199.211.115.72
> > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> > | Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:209759
> > | X-Tomcat-NG: microsoft.public.access.queries
> > |
> > | I need some help w/ defining a dynamic query.
> > |
> > | 1. Let's say I have a table that contains 4 fields:
> > | - Name
> > | - Address
> > | - City
> > | - State
> > |
> > | 2. Now I create a query that lists all 4 fields as well.
> > |
> > | 3. I then create a form which a Command Button calls the query.
> > |
> > | 4. In the same form, I then copy/paste that Command Button code into
> each
> > of
> > | the 4 fields (OnClick event).
> > |
> > | 5. Now, if I click on "City" in the form, I want to see only "City" of
> the
> > | query. I don't want to see "Name" nor "Address" nor "State".
> > |
> > | So, my question is: how do I create this dynamic parameter query that
> > uses
> > | the control name as input for showing a specific field (the field that
> has
> > | been clicked on)?
> > |
> > |
> > | Or, maybe there is even a "smarter" way than doing what I wrote down
> > | above... as long as the outcome is the same, I'd appreciate any advice
> > that
> > | would solve this problem.
> > |
> > |
> > | Thanks,
> > | Tom
> > |
> > |
> > |
> > |
> > |
> > |
> > |
> >
>
>

Quantcast