Hi Tom,
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?
ANSWER:
Function MyFunction()
Dim ctl As Control
Set ctl = Screen.ActiveControl
CurrentDb.QueryDefs("qrySales").SQL = "Select " &
ctl.Name & " From
tblSales"
DoCmd.OpenQuery "qrySales"
Set ctl = Nothing
End Function
In each each OnClick event of the textbox controls you would like to call
the function have
=MyFunction()
1. Do you have any idea as to how I can fix the error 3075?
ANSWER:
- the way to debug this is to see what you are passing to the QueryDef
- Modify the function to the following
Dim MySQLstr As String
MySQLstr = "Select " & ctl.Name & " From tblSales"
MsgBox "Done"
Exit Function
Dim ctl As Control
Set ctl = Screen.ActiveControl
CurrentDb.QueryDefs("qrySales").SQL = "Select " &
ctl.Name & " From
tblSales"
DoCmd.OpenQuery "qrySales"
Set ctl = Nothing
- After you click the message box "Done" open the Visual Basic Editor
window and click the menu option View > Immediate Window
- Copy what's in the Immediate Window
- Create a new query in design view WITHOUT adding a table
- Select the menu option View > SQL View
- Paste what you copied in the Immediate Window over what's in the SQL View
- Try running the query..... from here you can see what syntax errors
Microsoft Access does not like.
Then from there you should be able to see what you need to modify for the
line
CurrentDb.QueryDefs("qrySales").SQL = "Select " & ctl.Name & " From
tblSales"
I hope this helps! If you have additional questions on this topic, please
respond back to this posting.
Regards,
Eric Butts
Microsoft Access Support
(e-mail address removed)
"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" <
[email protected]>
| References: <
[email protected]>
<
[email protected]>
<
[email protected]>
| Subject: Re: Parameter query based on text field
| Date: Thu, 12 Aug 2004 08:31:15 -0400
| Lines: 195
| 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: <
[email protected]>
| Newsgroups: microsoft.public.access.queries
| NNTP-Posting-Host: 199.211.115.87
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:209877
| X-Tomcat-NG: microsoft.public.access.queries
|
| 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'.
|
| &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
|
|
|
|
|
|
|
|
|
|
| | > 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
| >
| >
| > | > > 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
| > > (e-mail address removed)
| > > "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" <
[email protected]>
| > > | 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: <
[email protected]>
| > > | 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
| > > |
| > > |
| > > |
| > > |
| > > |
| > > |
| > > |
| > >
| >
| >
|
|
|
