Using Class module for click event

[Kicker]

I have an Excel workbook that uses a Class module to add a click event to a userform that contains over 30 labels. Result = click a label and depending on the label's caption.

I now have an Access form with close to 50 TextBoxes. I am rebuilding the form to contain over 100 Textboxes (and still counting). Rather than have a Click_event for each text box, I would much prefer to have a click event that takes action depending on the Textbox's name. Problem is I haven't been able to get the Excel example to work in Access.

Examples would be appreciated.

The TextBox names are Text1, Text2, Text3....Text100.

The click event will take the name of the text box to determine which report to open. The value of the text box is really not important.

If necessary, I could sent a blank copy of the form but wouldn't be able to include any data because of it's sensitivity.

[OBP]

Kicker, Access doesn't work like Excel in terms of Forms.
What your are doing is a bit Time consuming, do you really have 100 reports to run?
You do have a more advanced option that should work, and that is to use the Reports Collection to create a table of Report names and then use that data in a combo box to select the report name.
As it is if you have a new report, or change a report name you will have to Edit the Design of the form.
It could also be that you do not need 100 reports, just a few advanced Queries.

Can you explain what the Reports display?

Anyway back to your question, you can use a Module and the Text Box's Label to pass the value to a String Variable which then passes it to the Module which opens the report.

[Kicker]

OBP
Really good questions.

First of all, I guess I should describe it in a little more detail. The Form is displayed when someone opens the database (eventually to be converted to mde file). By looking at the form, plant managers (et al) can get various information about the work scheduled for the next 15 weeks. Clicking on any textbox needs to display some drill down report information.

In reality, there are 30 rows of 16 each Text boxes that get filled when the form opens. 10 rows are actually repeated 3 times depending on selection criteria. Each ROW contains a specific element such as Orders Approved, Parts, Engineering Review, etc..... Each COL represents a specific work week such as Col 1 = current week, Col 2 = next week ....Col 16 = 15 weeks from now.

All 16 COL run the same report for the specific row. If I click on any textbox in column n, I run the same report. The data for the report is determined by the column label. For example: if I click on Text5, I know I am in the 5th column and the 5th label.caption identifies the work week which is a parameter for the query which is the data source for a specific report. Basically Rows 1, 11, and 21 run the same report with the ROW number used as a second parameter for the query.

Bottom line is that I need to have a click_event for every textbox. That is a minimum of 300 lines of code not to mention the associated code to select and run the reports. In Excel I replaced all that vba with a dozen lines of code. I would like to do the same thing in Access.

I appreciate your assistance and thank you in advance for any help/ideas you can povide.

[OBP]

Kicker, have you considered using Combo drop down lists or Option Groups to make the choices instead of all of those text boxes?

[Kicker]

Let me say it again. THERE IS NO OPTION for lists or combo dropdowns. It is a metric page that 90% of top managers will print and carry with them every day. The display value is percentage number representing how will the particular category (row) is ready for the particular work week (col). It also gets published in a pdf format for other purposes.

[OBP]

Why does a "Menu" that selects Report Parameters need to be printed out by Managers?
They can't click on a piece of paper.
In Access each object has to have it's own Click event, so you are stuck with 300+ lines of code.
Even with a Module you still need lines of code in each Text field to Call the Module.

[Kicker]

With all due respects to your 1,900+ posts, it appears that you are trying to design my project rather than answer my question. I am not sure you understand what a metric is or what top management does or does not need to print for daily reference.

I have already explained that list or combo boxes are not an option. It doesn't matter what data the reports display or where it comes from. What matters is that each of the Text Boxes has a click_event associated with the entire collection of Text Boxes. The entire process takes less than 25 lines of vba code in Excel. But, as you pointed out, Access works differently. That is why I asked the question.

I don't want to make a separate click_event for each textbox. That is ridiculous to even consider.

Thank you for your efforts.

[OBP]

It just so happens that I designed databases & Quality reports for senior Management for Ford Motor CO. for 25 years as a Quality Engineering Supervisor and trained as a Systems Engineer.

Access uses "Got Focus" or the Activecontrol, i.e. whatever Form Control has the "Focus" is the Control that is evaluated when it is Clicked.
What I was trying to do was find an alternative to the 300 lines of code and a form design that used less intense form design, ie. not 300+ text fields. i.e. Taking advantage of the way that Access works as compared to how Excel works.
If you don't want to use anything other than text fields to activate an event then the only way that I know of to do what you want would be to use the Form's Detail's On Click Event to trigger the event that you want and then use the ActiveControl's name as the Report Parameter. However that requires 2 Clicks (or a Tab and a Click), one to go to the Text field and another to activate the code.
The advantage is that you only need one set of Code.
The Form Detail on click event does not change the focus of the currently highlighted (Active) field, a Command Button can't be used as it would have the Focus.

[OBP]

Kicker, I don't know if you are still working on the "300" text fields or whether you have found a solution to the on click event, but I have found a possible answer on the other Forum that I spend most of my time on.
It will involve clicking on the Text box and then pressing the "Enter" key (or another key) to initiate the vba code.

[Kicker]

OBP. I sent you a private message but will apologize publicly for my frustration in my last message. I too, have extensive background in db application development. However, due to restrictions placed on us at work, I normally haven't been forced to use MS Access with vba. With that said, I find it hard to believe that Microsoft wouldn't put a comparable feature into MS Access. I can't even use this forum from my work computer.

I would love to have a link to your other forum. Thank you.

[Kicker]

OBP. As I mentioned, I am stubborn and don't give up very easy. I found a 5 year old post (I had made and that is why I knew it would work) and played with the code a little. This works. :beerchug:

The test form has 5 Textboxes on it but could easily have thousands.
Option Compare Database
Option Explicit

Public colDate As Collection
Public colText As Collection

Private Sub form_load()
Dim ctl As Control
Dim clText As clsTextBox

Set colText = New Collection
Set colDate = New Collection
For Each ctl In Controls
If TypeName(ctl) = "Textbox" Then
Set clText = New clsTextBox
Set clText.mLText = ctl
colText.Add clText
End If
Next

End Sub

The clsTextbox module has the following code
Option Compare Database

Public WithEvents mLText As TextBox

Private Sub mLText_click()
Debug.Print mLText.Name
End Sub

This results in the individual name of the text box being printed to the immediate window. As that relates to my project, I no longer have to have hundreds of click events with a minimum of 3 lines of code each. I have the text boxes named Text1, Text2, etc....I can use a Select statement to group the boxes into a matrix.

Thank you for your patients

[OBP]

Kicker, well remembered. That is what I proposed in post #8, but you will still have to identify the Field that has Focus as well as it's name.
How do you use the On Click Event to run the code you need?

[Kicker]

In reality, there are two on_click events. One for the original textbox control and one for the collection. I only use the one for the collection. In the cls file, there are two options.

Option one would be to put all the code in the mLText_Click() sub. Option 2 would be to call another sub using part of the name of the clicked textbox as a parameter. For example:

My WorkWeek codes are in Text208 -> Text 224. I have a function that will get the value and manipulate it into a workable string called strRevision. That means, I can use the following code (or something like it).

mLText.name is the name of the Textbox control. Therefore, val(mid(mLText.name,5)) will give me the number of the textbox.

Select case n 'number of Textbox
Case 1 to 16
strRevision = get_RevCode(208 + n)
DoCmd.OpenReport "rpt_OpenOrders", acViewPreview, 'Revision like ' " & strRevision & "'"
Case is 17 to 32
strRevision = get_RevCode(182 + n) 'always have to evaluate to 208...
DoCmd.OpenReport "rpt_EngReview", acViewPreview, 'Revision like ' " & strRevision & "'"
Case ....
end select

This requires a single click on the TextBox.

Thank you. I am going to visit your other forum.

[OBP]

Can you post your final solution & code so that I can note it for future use, should this question come again in the future?
I am sure that there are lot's of users who would like a One Click method of running one of multiple reports or opening one of multiple forms.

[Kicker]

I can't send the original work copy but will be glad to gen something up with the Test file I have been using. You might even want to post it on the KB. It might take me a few days as I am busy with the real project...but I will get it done.

[Kicker]

OBP. I was looking the code over and decided it would not take long to get it ready to send to you. I used the Northwind customer table and made 5 quick reports for the example.

This example only uses 5 text boxes. Imagine how useful it will be if you have hundreds of "grouped" controls.

[OBP]

Kicker, no you should post it on the KB, if you can find the time, as it is something that I have never seen before. I have never seen the WithEvents used in that fashion allowing module code to be called without referring to the Sub by name. It is very usefull for creating Menus etc.
I take my hat off to you. :beerchug:

Would you like to post it on the TechGuy Tip & Tricks?
I will certainly mention it to others.