How can I get a command button to perform two seperate functions? 1

[MPBCHM]

I need a command button to perform two functions:


1. Follow a HyperLink
2. Populate a textbox with todays date and time.

The hyperlink Command buttons are on a form called frmSend_Log.

So once I click the command button the hyperlink runs a batch file. Next,when the batch file has finished, I want a textbox to show the Time and date this function was executed. "Like a log file to track batch file runs."

Once all the batch files (Hyperlinks) have finished, I want to populate a table with this info. (File names and dates)


Can this be done?

Thanks for the help.

Mark

 

[tunsarod]

Here's an idea:

I am assuming a table exists called tblLog which has three fields: fldProcess Text; fldAction Text; fldWhen as Date/Time.

Copy the contents of the current command button's click event to a separate procedure and name the procedure something like:

Private Sub Call_Hyperlinks()
insert the code copied from the cmdBtn
End sub


Now create 2 Append queries:

Append_StartHyperlinks which adds a record to your log table thus: fldprocess = "Run BatchFiles"
fldAction = "START"
fldWhen = Now()

Append_FinishHyperlinks which adds a record to your log table thus: fldprocess = "Run BatchFiles"
fldAction = "FINISH"
fldWhen = Now()


In the original command button's click event type:

Private Sub cmdbtn_click()
SetWarnings = False
Docmd.OpenQuery "Append_StartHyperlinks"

Call_Hyperlinks

Docmd.OpenQuery "Append_FinishHyperlinks"
SetWarnings = True

End Sub

The point here is that when the cmdBtn calls the Call_Hyperlinks procedure the focus moves to that procedure. But when the Call_Hyperlinks procedure has completed the focus should move back to the cmdBtn_click event procedure at which time the rest of the code should run the second query and thereby giving you a log of the start and finish times of the batch process.

This might seem long winded but its easy enough to set up and should be very reliable. SetWarnings = False means you don't get pestered by Access telling you every time your code is about to add a record to the Log table.

hope this helps,
Rod

 

[tunsarod]

Oh I forgot to add: place the instruction to populate the textbox anywhere in the button's click event.

txtDateTime = Now()

Obviously where you place this statement in the event code will determine whether the date/time stored is before or after the batch process ran.

Rod

 

[MPBCHM]

My first question is:
How do you get the "Append Query" to update [tblLog]?
What its doing now is when I click the command button I get a message saying "Your about to append data..." after I click "ok" the next popup tells me that I am about to append "0" records to the table. What am I doing wrong with the query?

My second question is:
I am using the Hyperlink address option to put in my hyperlink so I have nothing to copy from the click event of the command button. You mention copying data from here and placing it into an "Event Procedure". Do I understand you correctly?

Thanks again for the help...

Mark

 

[tunsarod]

As before this assumes a table called tblLog with fields:
fldProcess - type: Text
fldAction - type: Text
fldWhen - type: Date/Time

There is no particular need for a specific primary key field because the queries use the Now() function to place the full date and time in the fldWhen field, which will not only be unique for each record but also sequential.

Now: Copy this code to your form's code module window.


Private Sub Call_Hyperlink()
Application.FollowHyperlink "your_hyperlink_address", , True
End Sub


Private Sub cmdBtn_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "BatchFile_Start"

Call_Hyperlink

DoCmd.OpenQuery "Batchfile_Finish"
DoCmd.SetWarnings True
txtFinished = Format(Now(), "hh:mm:ss&quot
End Sub



Now: Remove the reference to the hyperlink in your button's properties box and transfer the hyperlink name into the above code where I have written your_hyperlink_address

Also you should rename the button in the above code according to your preference.

Previously I miss wrote the code for SetWarnings as: SetWarnings = True

This should have been written as:
Docmd.SetWarnings True

This will stop the messages about updating records.

Now the Queries:

I created two queries with meaningful names - if a little different than previously. Here is the SQL code which in both cases you can simply copy directly into an empty query's SQL window.

SQL for query: BatchFile_Start

INSERT INTO tblLog ( fldProcess, fldAction, fldWhen )
SELECT "Run Batch Files" AS Expr1, "Start" AS Expr2, Now() AS Expr3;


SQL for query: BatchFile_Finish

INSERT INTO tblLog ( fldProcess, fldAction, fldWhen )
SELECT "Run Batch Files" AS Expr1, "Finish" AS Expr2, Now() AS Expr3;


I have set this up on my PC and it works fine.

Good luck.
Rod

 

[tunsarod]

Hi Mark,

Lets start again.
Type the following code into your Form's Code Module Window


Private Sub Call_Hyperlink()
Application.FollowHyperlink "your_hyperlink_address", , True
End Sub


Private Sub cmdBtn_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "BatchFile_Start"

Call_Hyperlink

DoCmd.OpenQuery "Batchfile_Finish"
DoCmd.SetWarnings True
txtFinished = Format(Now(), "hh:mm:ss&quot
End Sub



Now remove the reference to the Hyperlink from your button's Hyperlink property and place it into the above code to replace the phrase: your_hyperlink_address.

You will also need to set your button's On-Click event to 'Event Procedure' and rename the above cmdBtn_Click procedure to match your button's actual name.

Note the corrected code for the SetWarnings method, which should stop those messages.


Create two empty Update action queries called BatchFile_Start and BatchFile_Finish and insert the following code into their respective SQL code windows.


SQL for query: BatchFile_Start

INSERT INTO tblLog ( fldProcess, fldAction, fldWhen )
SELECT "Run Batch Files" AS Expr1, "Start" AS Expr2, Now() AS Expr3;


SQL for query: BatchFile_Finish

INSERT INTO tblLog ( fldProcess, fldAction, fldWhen )
SELECT "Run Batch Files" AS Expr1, "Start" AS Expr2, Now() AS Expr3;


I have tested this on my machine and it works fine.

Good Luck
Rod

 

[MPBCHM]

Now this I understand!!!
One more question...

I don't believe I mentioned this prior...

I have several command buttons on my form. How do I incorporate them into the "Forms Code Module Window" The example is only for one Command Button. At the moment I have ten buttons, and growing.

Thanks for the help... This is what I was looking for.

Mark

 

[tunsarod]

"Ten green buttons, hanging on a Form" sounds like a song.

Well Mark, you need to study the Access Help files on Command Buttons and Events especially Click Events as that's the commonest buttton event used in VBA code.

Just about any coded action (method) can be incorporated into a button's Click-Event just as I have done in the example.

In answer to your question: If your ten buttons are each used to fire off a different batch file then you can still use the example I sent you but it needs modification.

Now I used Docmd.RunSQL and copied the SQL from the queries. I found that if I copy an SQL string from a query and embed it into VBA code I have to remove the semicolon at the end of the string else the code won't work - I'm not sure why. Also where I have used filenames for the hyperlinks (so I could see it work) I discovered that although the file Document.doc was in my C:\My Documents\ folder if I typed the full path and filename into the command button's click-event the code erred claiming a syntax error. However when I wrote the path as shown it ran fine. Again I don't understand what is happening there - so if anyone does then please explain.

So here is the modified code:


Option Compare Database
Option Explicit
Dim MyHyperLink As String


Private Sub Call_Hyperlink()
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblLog ( fldProcess, fldAction, fldWhen ) SELECT '" & Nz(MyHyperLink, "&quot & "' AS Expr1, 'Start' AS Expr2, Now() AS Expr3"

Application.FollowHyperlink MyHyperLink, , True

DoCmd.RunSQL "INSERT INTO tblLog ( fldProcess, fldAction, fldWhen ) SELECT '" & Nz(MyHyperLink, "&quot & "' AS Expr1, 'Finish' AS Expr2, Now() AS Expr3"
DoCmd.SetWarnings True
txtFinished = Format(Now(), "hh:mm:ss&quot
End Sub

Private Sub cmdBtn1_Click()
MyHyperLink = "..\GoodNews.doc"
Call_Hyperlink
End Sub

Private Sub cmdBtn2_Click()
MyHyperLink = "..\Document.doc"
Call_Hyperlink
End Sub

Private Sub cmdBtn3_Click()
MyHyperLink = "..\Prophets.doc"
Call_Hyperlink
End Sub

Private Sub cmdBtn4_Click()
MyHyperLink = "..\PhoneProgramming.doc"
Call_Hyperlink
End Sub


You don't need the two Queries now as the SQL runs directly from the VBA code.

To add more buttons simply click the build button beside the Click Event field for each button in turn and copy the on-click code from one of the button events above into the new click event procedure. Replace my psuedo hyperlinks for the real batchfile addresses and remember to remove the hyperlink address from each button's hyperlink property else you might have the batchfile being called twice.

Best Wishes
Rod

 

[MPBCHM]

Hey Rod

Thanks for the help... Worked like a charm.

One thing, I had to enter the entire file path for the hyperlink to work.

I tried your way >>>> "..\PhoneProgramming.doc" and that didn't work for me.

I used &quot;\\rptdbase\encdata\batch\filename.bat&quot; and that worked fine. <<Temperamental stuff this VB>>

Again thanks for the help.

Mark