Access Public Macro Question

[kaempfe]

Hello.

I created a form for tracking project times. It has a stop button that contains a macro to grab the most recent time from previous logs, place it as the new start time and put the current time as the end time. The button refers to a private sub called Stop_Click. It works very well and I have no issues with it.

I now want to have a desktop icon that launches the same form but if the form is launched from the desktop icon, it would automatically launch the code associated with this button.

I am not sure how to do this. The way I decided to do it, was to make a duplicate form with all of the same functionality EXCEPT that I wanted it to run the Private Sub upon opening the form. I can't figure out how to do this. In the research I have done, it appears that I can run a Function in a module that calls the sub. But...the sub is private so I can't do that. I realized that I should make the sub public and I have have some options. So, I renamed the code so I didn't screw up the working version and changed it to public. But with the public sub, it didn't know (or I don't know is probably the better way to say it) what to do with the Me. terms like Me.[Start Time] = UserStartTimeInput.

I think I need to switch all the Me. to actually callout the specific form since it is a public sub.

I have attached both the working Private sub and non-working Public Sub.

Can someone tell me what I am doing wrong in the public sub? I'm open to other ideas on how to do this but if I can get the Public Sub to work, I would say I am close to getting the icon to work so I would rather figure out how to get the Public Sub to work.



Working Private Sub
Private Sub STOP_Click()
On Error GoTo Err_STOP_Click
Dim LastStopTime As Date
Dim UserStartTimeInput As Date



'Check to see if user already inputted Start Time
'If they did, simply populate End Time
'If empty, check to see if there is a previous entry and use that end time for the new entry start time

If IsNull(Me.[Start Time]) Then

' Check to see if there is a previous entry with an end time
If IsNull(Me.LastTimeGrab) Then
UserStartTimeInput = InputBox("Please Enter a Start Time:", "Start Time")
Me.[Start Time] = UserStartTimeInput
Else
LastStopTime = Me.[LastTimeGrab].Value
'MsgBox LastStopTime
Me.[Start Time] = LastStopTime
End If

'Grab Current Time at Press of Button and place value in End Time field
Me.[End Time] = time()

'Calculate Elapsed Hours and Place in Hours Field
[Hours] = [End Time] - [Start Time]

'Refresh Form
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Else
'Grab Current Time at Press of Button and place value in End Time field
Me.[End Time] = time()

'Calculate Elapsed Hours and Place in Hours Field
[Hours] = [End Time] - [Start Time]

'Refresh Form
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End If


Exit_STOP_Click:
Exit Sub

Err_STOP_Click:
MsgBox Err.Description
Resume Exit_STOP_Click

End Sub



Non-Working Public Sub

Public Sub STOP_ClickPublic()
On Error GoTo Err_STOP_ClickPublic
Dim LastStopTime As Date
Dim UserStartTimeInput As Date



'Check to see if user already inputted Start Time
'If they did, simply populate End Time
'If empty, check to see if there is a previous entry and use that end time for the new entry start time

If IsNull([TimeLogForm_FromStopButton].[Start Time]) Then

' Check to see if there is a previous entry with an end time
If IsNull([TimeLogForm_FromStopButton].[LastTimeGrab]) Then
UserStartTimeInput = InputBox("Please Enter a Start Time:", "Start Time")
[TimeLogForm_FromStopButton].[Start Time] = UserStartTimeInput
Else
LastStopTime = [TimeLogForm_FromStopButton].[LastTimeGrab].Value
MsgBox LastStopTime
[TimeLogForm_FromStopButton].[Start Time] = LastStopTime
End If

'Grab Current Time at Press of Button and place value in End Time field
[TimeLogForm_FromStopButton].[End Time] = time()

'Calculate Elapsed Hours and Place in Hours Field
[TimeLogForm_FromStopButton].[Hours] = [TimeLogForm_FromStopButton].[End Time] - [TimeLogForm_FromStopButton].[Start Time]

'Refresh Form
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Else
'Grab Current Time at Press of Button and place value in End Time field
[TimeLogForm_FromStopButton].[End Time] = time()

'Calculate Elapsed Hours and Place in Hours Field
[TimeLogForm_FromStopButton].[Hours] = [TimeLogForm_FromStopButton].[End Time] - [TimeLogForm_FromStopButton].[Start Time]

'Refresh Form
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End If


Exit_STOP_ClickPublic:
Exit Sub

Err_STOP_ClickPublic:
MsgBox Err.Description
Resume Exit_STOP_ClickPublic

End Sub

 

[xenou]

I guess all things being equal I'd say leave the code in the form (as a private sub). You can run the code when the form opens by calling it from the form's Open event - this will run the code when the form opens, allow you to continue to use the Me keyword and keep the form code private to the form.

ξ