I'm trying to optimise two simple functions in Access VBA.
(1) The first is a simple database activity logging function. When an event is triggered on any form I call a global function (i.e. stored in a module) "dms_log" which just writes some information into a log table. The function is pretty simple:
Public Sub dms_log(strForm As String, strAction As String, strDetail As String)
' Single quotes in expressions break our sql execute syntax
' Replace them with double quotes which don't...
strDetail = Replace(strDetail, "'", """")
' Add action to dms log
CurrentDb.Execute ("INSERT INTO z_dms_log (tstmp, user_id, form, action, detail) VALUES (Now(), " & gblUser & ", '" & strForm & "', '" & strAction & "', '" & strDetail & "');")
End Sub
and I call it like this (in this example I am saving an SQL string to my "detail" column):
Call dms_log(Me.Name, "Activity Imported", strSQL)
My first question is:
--------------------------
----------
--
Can I grab the calling form name from the global function, something like this:
CurrentDb.Execute ("INSERT INTO z_dms_log (tstmp, user_id, form, action, detail) VALUES (Now(), " & gblUser & ", '" & me.callingform.name & "', '" & strAction & "', '" & strDetail & "');")
(2) I also have a global data validation function:
Public Function validate_date(strDate As String) As Boolean
validate_date = False
If Not IsDate(strDate) Then
MsgBox ("Please enter a valid date")
validate_date = True
End If
End Function
which is used like this:
Private Sub txt_date_BeforeUpdate(Canc
el As Integer)
Cancel = validate_date(Nz(Me.Active
Control.Va
lue, ""))
End Sub
Private Sub txt_date_AfterUpdate()
Me.ActiveControl.Value = Format(CDate(Me.ActiveCont
rol.Value)
, "dd/mm/yyyy")
End Sub
My second question is:
--------------------------
----------
--
Can I alter the global function to something like:
If Not IsDate(callingfield.value)
Then
so that I can call it with just:
Private Sub txt_date_BeforeUpdate(Canc
el As Integer)
Cancel = validate_me
End Sub
Start Free Trial