Solved

microsoft access vba code to capture the click event when a form opens within a subrouting

Posted on 2011-09-07
38
564 Views
Last Modified: 2013-11-28
I am trying to capture if a button (button name is 'PrintPartNameSummary') on a form has been clicked when trying to open a form within a subroutine.  This provides a 'pause' in the coding for the user to enter info into the form and then click the 'PrintPartNameSummary' button.  If the button is clicked then coding proceeds.  Not sure how to do this........Need expert help please.
Sub UpdatePatientDays()

Dim rs2 As DAO.Recordset, rs4 As DAO.Recordset
Dim qdf4 As DAO.QueryDef, prm As DAO.Parameter
Dim qry4 As String
Dim tbl As String

Set db = CurrentDb()

DoCmd.OpenForm "CRFMetricSupportDatadialogbox", acNormal               'FORM OPENS HERE

If Forms!CRFMetricSupportDatadialogbox!PrintPartNameSummary.Click = True Then     'I WANT TO CAPTURE THE CLICK BUTTON HERE

'......PERFORM MORE TASKS ETC HERE

End If

Open in new window

0
Comment
Question by:sxxgupta
  • 12
  • 12
  • 10
  • +1
38 Comments
 
LVL 75
ID: 36497035
Why not create a Global Variable and set if when the button is clicked, then reset it where ever it makes sense.  Then you can test that variable in the code above and act accordingly.

mx
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36497040
why not just call the the click event in the load event of form ""CRFMetricSupportDatadialogbox"

private sub from_load()

PrintPartNameSummary_click

end sub
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36497054

When you open the form the way you have, any code following the OpenForm method will fire immediately.  If you use the acDialog as an optional WindowMode parameter to the OpenForm method, then code that follows the OpenForm method will not process until the 2nd form (CRFMetricSupportDatadialogbox) is either closed or is hidden.

One method would be to continually loop to see whether that form is still open, and if so, use the Click event of that form to set the Tag property.  Then check the Tag property inside your loop.  But this would be very CPU intensive.

What would make more sense is to put the code you want to run, if the button is clicked, in the Click event of that 2nd form.
0
 

Author Comment

by:sxxgupta
ID: 36497237
I understand that it would make more sense to put the entire coding behind the button's click event........but need to do this backwards.........

Hi MX:
How would you implement your suggesting in coding?  Not sure how to even start..........
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 167 total points
ID: 36497304
What happens when you currently click the PrintPartNameSummary button on the popup form?  After printing the summary, does the form close?

If you insist on doing this ass backwards, I would add a line of code to the PrintPartNameSummary click event:

Private Sub PrintPartNameSummary_Click

    'whatever you are doing now

    me.Tag = "PrintPartNameSummary Clicked"

End Sub

Then, on your main form, in the code that opens the second form, do something like:

DoCmd.OpenForm "CRFMetricSupportDatadialogbox", acNormal               'FORM OPENS HERE

'This will be extremely CPU intensive
While currentproject.allforms("CRFMetricSupportDatadialogbox").isloaded
    doevents
    If Forms!CRFMetricSupportDatadialogbox!Tag = "PrintPartNameSummary Clicked" Then

         'do something here

    End If
Wend


0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 167 total points
ID: 36497325
In a regular VBA module, add this:

Option Compare Database
Option Explicit

    Public fBtnWasClicked As Boolean


In your button Click code ... add this

Private Sub btnYourButtonName_Click()

 ' whatever code
   fBtnWasClicked = True

End Sub


In your code above:

Sub UpdatePatientDays()

Dim rs2 As DAO.Recordset, rs4 As DAO.Recordset
Dim qdf4 As DAO.QueryDef, prm As DAO.Parameter
Dim qry4 As String
Dim tbl As String

Set db = CurrentDb()

DoCmd.OpenForm "CRFMetricSupportDatadialogbox", acNormal               'FORM OPENS HERE

If fBtnWasClicked = True 'I WANT TO CAPTURE THE CLICK BUTTON HERE

'......PERFORM MORE TASKS ETC HERE
    fBtnWasClicked =False ' reset flag
End If

End Sub
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36497343
Joe,

You are forgetting that the code following the OpenForm method is going to fire immediately unless the OP adds the acDialog WindowMode parameter.
0
 

Author Comment

by:sxxgupta
ID: 36497950
If I step thru the subroutine, all works well as intended.  When I run in full auto, the form opens and does not wait for the user to click the button and fires all the way thru............
0
 
LVL 75
ID: 36497973
Change this:

DoCmd.OpenForm "CRFMetricSupportDatadialogbox", acNormal

To:

DoCmd.OpenForm "CRFMetricSupportDatadialogbox", acNormal,,,,acDialog
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36497978
that is why I put in the loop to check on the form being open.  If you don't have that in there, the code will drop straight through the initial subroutine as soon as it opens your 2nd form.

That is why most of us recommended alternatives.

If you want to use MX's global variable, it would look like:

'This will still be extremely CPU intensive
While currentproject.allforms("CRFMetricSupportDatadialogbox").isloaded
    doevents
    If fBtnWasClicked = True  Then

         'do something here

    End If
Wend


0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36497987
Joe,

the OP is not going to be able to test for the click Event with acDialog, unless the click event also hides the form, or closes it.

Dale
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36497994
sxxgupta,

what are the codes executed in the click event of  "PrintPartNameSummary"


if you will describe in plain english, what you are trying to achieve, maybe we can find an alternative way of doing it,.


like placing the codes from the click event in a Sub or Function..
you can then call it anywhere based on conditions
0
 
LVL 75
ID: 36498000
Dale ... using the DoEvents loop to simulate acDialog will such probably 50% of system resources ...

mx
0
 

Author Comment

by:sxxgupta
ID: 36498007
fyed:

I am taking a look at your recommendation:

mx:
acdialog option does change the unintended outcome
0
 

Author Comment

by:sxxgupta
ID: 36498022
cap:

the code behind the PrintPartNameSummary is

BtnWasClicked = True
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36498032
MX,

I know, thus my comment about being "extremely CPU intensive".  But with acDialog, none of the code following the OpenForm method will fire until the 2nd form is either closed or hidden.  That is why I recommended in my original post moving the code to the Click event of the button on the 2nd form.
0
 

Author Comment

by:sxxgupta
ID: 36498094
As I step thru the code in my subroutine, the form opens and when I click the code (click event) is executed and that is it.  The rest of the code behind the subroutine is not executed............

So now I most probably have to put the entire coding behind the click event of the form................unless there is some other method..........
Sub UpdatePatientDays()
Dim rs2 As DAO.Recordset, rs4 As DAO.Recordset
Dim qdf4 As DAO.QueryDef, prm As DAO.Parameter
Dim qry4 As String
Dim tbl As String

Set db = CurrentDb()
DoCmd.OpenForm "CRFMetricSupportDatadialogbox", acNormal, , , , acDialog

While CurrentProject.AllForms("CRFMetricSupportDatadialogbox").IsLoaded
DoEvents
If fBtnWasClicked = True Then
blah
blah
blah
Wend
'******************************************

Private Sub PrintPartNameSummary_Click()
BtnWasClicked = True
End Sub

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36498103
<the code behind the PrintPartNameSummary is

BtnWasClicked = True>

ok.. i really don't grasp the idea behind all this...


0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36498115



how about describing what your intentions are, doing this

0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36498116
If you drop the acDialog, then the While/Wend will continue to loop as long as CRFMetricSupportDatadialogbox is open.

But as stated above, the While/Wend loop will be extremely CPU intensive.
0
 

Author Comment

by:sxxgupta
ID: 36498118
was just trying to follow what MX recommended........
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36498124
BTW, you used two different variables in the following code.  In the click event you set "BtnWasClicked" to True, but in the While/Wend loop, you are testing for fBtnWasClicked.

While CurrentProject.AllForms("CRFMetricSupportDatadialogbox").IsLoaded
DoEvents
If fBtnWasClicked = True Then
blah
blah
blah
Wend
'******************************************

Private Sub PrintPartNameSummary_Click()
BtnWasClicked = True
End Sub
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36498126
ok,..

describe what you want to happen/ your intentions



0
 

Author Comment

by:sxxgupta
ID: 36498189
The user is executing the code directly by opening the vba module that contains the sub.  The user executes this code by clicking on the "Play" button located on the toolbar.  The sub to be executed is:

Sub UpdatePatientDays()

This sub opens a form called "CRFMetricSupportDatadialogbox".  The form contains a field for a start date and a field for an end date with a button called  'PrintPartNameSummary'.  When the user clicks this button, I would like to continue running the rest of the code in the subroutine 'UpdatePatientDays'
Sub UpdatePatientDays()
Dim rs2 As DAO.Recordset, rs4 As DAO.Recordset
Dim qdf4 As DAO.QueryDef, prm As DAO.Parameter
Dim qry4 As String
Dim tbl As String

Set db = CurrentDb()
DoCmd.OpenForm "CRFMetricSupportDatadialogbox", acNormal

While CurrentProject.AllForms("CRFMetricSupportDatadialogbox").IsLoaded
DoEvents
If Forms!CRFMetricSupportDatadialogbox!Tag = "PrintPartNameSummaryClicked" Then

qry4 = "CRFMetricSupportDataQuery_Summary"
tbl = "CRFMetricData"
Set rs2 = CurrentDb.OpenRecordset(tbl, dbOpenDynaset, dbSeeChanges)
Set qdf4 = db.QueryDefs(qry4)
For Each prm In qdf4.Parameters
    prm.Value = Eval(prm.Name)
Next prm
Set rs4 = qdf4.OpenRecordset(dbOpenDynaset)

Do Until rs2.EOF
    If rs4.EOF = True Then
        Exit Do
    ElseIf Year(rs2![Date]) >= 1996 Then
        Do Until rs4.EOF
            If rs4![Year] = Year(rs2![Date]) And rs4![Month] = Month(rs2![Date]) Then
                With rs2
                    rs2.Edit
                    ![AvgDaysOpen] = rs4("[AvgDaysOpen]")
                    ![#ComplaintsAvg] = rs4("[#ComplaintsAvg]")
                    ![#Opened] = rs4("[#Opened]")
                    ![#Closed] = rs4("[#Closed]")
                    ![#CCRs] = rs4("[#CCRs]")
                    .Update
                End With
                rs4.MoveNext
                If rs4.EOF = True Then
                    Exit Do
                End If
            
            End If
            rs2.MoveNext
        Loop
    End If
    rs2.MoveNext
Loop
   
rs2.Close
rs4.Close
Set rs2 = Nothing
Set rs4 = Nothing
Set qdf4 = Nothing

DoCmd.Close acForm, "CRFMetricSupportDatadialogbox"

End If
Wend

MsgBox "Done."

End Sub

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36498227
So, as stated by Cap1, MX, and myself in our earlier posts, why can't you put this code in the click event of form CRFMetricSupportDatadialogbox?

That is where it belongs!
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36498248


why not just place  the  codes  in the click event of the button ?

what is preventing you from doing this simple thing?
0
 

Author Comment

by:sxxgupta
ID: 36498284
I already did and it works as expected.  Thanks.    I just wanted to see if I could capture the click event via VBA by running the code directly from the module........ass backwards in fyed's terms:))
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 166 total points
ID: 36498317
<I just wanted to see if I could capture the click event via VBA by running the code directly from the module.>  

Yes you can!!!

from the module

Forms!CRFMetricSupportDatadialogbox!PrintPartNameSummary_Click
0
 

Author Comment

by:sxxgupta
ID: 36498360
cap so how do I write this in the module

if Forms!CRFMetricSupportDatadialogbox!PrintPartNameSummary_Click = true then
etc
etc
end if
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36498475
just call the click event with


Forms!CRFMetricSupportDatadialogbox!PrintPartNameSummary_Click

nothing more..
but check first if the form is open...
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36498479
and you may have to use

Public Sub PrintPartNameSummary_Click()

not

Private Sub PrintPartNameSummary_Click()
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36498493
Cap1,

OP said "if I could capture the Click event", not "if I can execute the click event"
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36498507
what is the purpose of capturing the click event ?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36498558
I don't know, but that was what the OP asked for, and why we have 33 posts in this thread!

I think I'm going to go home!
0
 

Author Comment

by:sxxgupta
ID: 36498662
Because I want the code to continue to run AFTER the user enters custom start and end dates in the form when it opens.........what am I missing experts?


Have a good evening fyed.
0
 

Author Comment

by:sxxgupta
ID: 36498683
So I am sorry if I am not getting the exact lingo correct.  I need the button to execute the rest of the code once the user clicks it which is why I was asking on how to capture this because the coding stops that the click event when the form opens and I click the button to continue executing the code AFTER I have give the user the opportunity to change the start and end dates.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36498754
sxxqupta,

Your lingo is fine.  Our point was that Access is an Event driven application.  You define actions you want performed based on events (Before, On, or After something, do something else).

In your case, you wanted to perform an event after a button on FormB was clicked, so we recommended that you use the Click event of that button to execute the code.  For some reason, you didn't want to do that, so we proposed a couple of work-arounds.  There are almost always work-arounds, my While / Wend loop is an example of those work-arounds, but generally when you try to do this, you either create loads on the system, or get unexpected results.  

It just makes more sense to use the events that are defined and structure your application within those standards.
0
 

Author Comment

by:sxxgupta
ID: 36498888
Understood Fyed, and I am using the predefined events.  As always, this website always provides valuable insight to newbies like me.  Thank you.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now