Run function from macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm trying to find a way on how to use a macro that gets a condition from a
function.

The function returns a true/false from a lookup on a calendar table. The
table contains schedulled holidays.

I have the function 'ConfirmCalendar' which returns true if the day is not a
holiday and false if the day is a holiday.

So what I would like to do with the macro is:

1. Run the ConfirmCalendar function
2. Compare the condition
3. On being true run two other functions 'Kanban' and 'Generate Reports'
4. On being false just quit

I can run the function by using RunCode but don't understand how to capture
the results and use them in a condition statement.

Any help please?

Function:

Public Function ConfirmCalendar() As Boolean
On Error GoTo Err_ConfirmCalendar

Dim rst As New ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT YEAR, CALENDAR_DATE, DESCRIPTION " & _
"FROM TBL_CALENDAR " & _
"WHERE ((TBL_CALENDAR.YEAR='" & Format(CurrentDate, "yyyy") & "')
And (TBL_CALENDAR.CALENDAR_DATE=#" & Format(CurrentDate, "dd/mm/yyyy") &
"#));"

With rst
.Open strSQL, cnn, adOpenStatic, adLockReadOnly

If .RecordCount <> 0 Then
ConfirmCalendar = False
Else
ConfirmCalendar = True
End If

.Close
End With

Exit_ConfirmCalendar:
Exit Function
Err_ConfirmCalendar:
ConfirmCalendar = False
Resume Exit_ConfirmCalendar
End Function
 
Marcus,

If I understand you correctly, I think you would just put this in the
Condition column of the macro design window, for the RunCode actions:
ConfirmCalendar()=True

However, I am not sure, because I do not know what CurrentDate refers to
in your code.

In any case, it really seems to me that the ConfirmCalendar function is
a very over-complicated approach to a simple exercise. For one thing,
the Where clause in your SQL assesses both the date and the year, which
is unnecessary... if the dates are equal, the year will autmatically
match without needing to specify.

But the whole thing could be replaced by one line:

ConfirmCalendar = DCount("*","TBL_CALENDAR","[CALENDAR_DATE]=Date()")
 
Back
Top