SQL Query that will run when text object is clicked

[SPRTRMP]

I want to run a query when the user clicks a text object on a form. There are several hundred text objects, each is the name of a publication, and all of the pubs are stored on a table with a unique ID No. When the text object is clicked, i want the query to run and use that pub's ID as a parameter, then open a form to display the related information. Can anyone help with this?

 

[pbaldy]

I have your sample db. One thought, similar to my last, is that your approach is very much a "brute force" solution. You have hard coded forms for each possible selection. This is a maintenance nightmare. I know this is an older application so too late to change, but you might keep it in mind for later. There would be really elegant ways to build the application that didn't require new forms or design changes to forms when new publications came out.

The simplest answer to your question is that code like this behind that label will open the form filtered to the appropriate record.

Private Sub Label80_Click()
    DoCmd.OpenForm "TMName", , , "PubName = '" & Me.Label80.Caption & "'"
End Sub

That's on your form "WTm13655" behind the label for "TM 1-3655-230-23P". You could also hard-code the "TM..." bit in place of the Me.Label80.Caption part, but I'm too lazy for that. It would look like:

DoCmd.OpenForm "TMName", , , "PubName = 'TM 1-3655-230-23P'"

Either way, you're telling Access to open the form named TMName and filter it to records where the PubName field has the value of "TM 1-3655-230-23P". Like I mentioned in my email, you don't need a separate query for every option.

 

[MSAccessRookie]

I want to run a query when the user clicks a text object on a form. There are several hundred text objects, each is the name of a publication, and all of the pubs are stored on a table with a unique ID No. When the text object is clicked, i want the query to run and use that pub's ID as a parameter, then open a form to display the related information. Can anyone help with this?

With the form opened in DESIGN Mode, examine the Properties of any given Text Object. Among the properties is a property called "On Click", which assigns an action to be performed when a user clicks the Text Object.

There are three options:

1. A Simple Formula or Query can be entered on the line.
2. The name of a Macro that handles the Event "On Click" can be entered on the line
3. Clicking the "..." on the right side of the of the line will allow you to define a VB Method to handle the Event.

As a Side Note, if all that you need to do is Click on the Object, then a Button Object might serve you as well, and perhaps better.