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

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

sxxguptaAsked:
Who is Participating?
 
Dale FyeCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
Rey Obrero (Capricorn1)Commented:
why not just call the the click event in the load event of form ""CRFMetricSupportDatadialogbox"

private sub from_load()

PrintPartNameSummary_click

end sub
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dale FyeCommented:

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
 
sxxguptaAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
Dale FyeCommented:
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
 
sxxguptaAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Change this:

DoCmd.OpenForm "CRFMetricSupportDatadialogbox", acNormal

To:

DoCmd.OpenForm "CRFMetricSupportDatadialogbox", acNormal,,,,acDialog
0
 
Dale FyeCommented:
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
 
Dale FyeCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Dale ... using the DoEvents loop to simulate acDialog will such probably 50% of system resources ...

mx
0
 
sxxguptaAuthor Commented:
fyed:

I am taking a look at your recommendation:

mx:
acdialog option does change the unintended outcome
0
 
sxxguptaAuthor Commented:
cap:

the code behind the PrintPartNameSummary is

BtnWasClicked = True
0
 
Dale FyeCommented:
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
 
sxxguptaAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
<the code behind the PrintPartNameSummary is

BtnWasClicked = True>

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


0
 
Rey Obrero (Capricorn1)Commented:



how about describing what your intentions are, doing this

0
 
Dale FyeCommented:
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
 
sxxguptaAuthor Commented:
was just trying to follow what MX recommended........
0
 
Dale FyeCommented:
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
 
Rey Obrero (Capricorn1)Commented:
ok,..

describe what you want to happen/ your intentions



0
 
sxxguptaAuthor Commented:
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
 
Dale FyeCommented:
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
 
Rey Obrero (Capricorn1)Commented:


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

what is preventing you from doing this simple thing?
0
 
sxxguptaAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
<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
 
sxxguptaAuthor Commented:
cap so how do I write this in the module

if Forms!CRFMetricSupportDatadialogbox!PrintPartNameSummary_Click = true then
etc
etc
end if
0
 
Rey Obrero (Capricorn1)Commented:
just call the click event with


Forms!CRFMetricSupportDatadialogbox!PrintPartNameSummary_Click

nothing more..
but check first if the form is open...
0
 
Rey Obrero (Capricorn1)Commented:
and you may have to use

Public Sub PrintPartNameSummary_Click()

not

Private Sub PrintPartNameSummary_Click()
0
 
Dale FyeCommented:
Cap1,

OP said "if I could capture the Click event", not "if I can execute the click event"
0
 
Rey Obrero (Capricorn1)Commented:
what is the purpose of capturing the click event ?
0
 
Dale FyeCommented:
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
 
sxxguptaAuthor Commented:
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
 
sxxguptaAuthor Commented:
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
 
Dale FyeCommented:
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
 
sxxguptaAuthor Commented:
Understood Fyed, and I am using the predefined events.  As always, this website always provides valuable insight to newbies like me.  Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.