Sophie said:
Hi to all
I use code like this on several of my forms, and it works well. Each of
these forms has an option group called opgMode. The only difference from
one
form's code to another is the reference to the form's name - in this case
frm_A.
Private Sub opgMode_AfterUpdate()
Dim mySQL As String
mySQL = "UPDATE tblConstants"
mySQL = mySQL + " SET tblConstants.ConstantValue = "
& Forms.frm_A.opgMode
mySQL = mySQL + " WHERE tblConstants.ConstantName = 'opgMode'"
CurrentDb.Execute mySQL, dbFailOnError
End Sub
What I'd like to do is have a sub in a standard module that passes the
name
of the form to the sub so that it can be called by each form's
opgMode_AfterUpdate event. As a new programmer, I can't seem to get the
syntax right. Help would be appreciated!
So what you have in mind is a public sub or function that receives the name
of the form or a reference to the form, and updates the tblConstants table
appropriately? You could do that easily enough with a function like this:
----- begin function version A -----
Function Update_opgMode(FormName As String)
Dim strSQL As String
strSQL = _
"UPDATE tblConstants SET tblConstants.ConstantValue = " & _
Forms(FormName)!opgMode & _
" WHERE tblConstants.ConstantName = 'opgMode'"
CurrentDb.Execute strSQL, dbFailOnError
End Function
----- end function version A -----
I made it a Function instead of a Sub because a Function can be called
directly from the AfterUpdate property of a control, using a function
expression like this:
=Update_opgMode([Form].Name)
That is, you don't even have to create an AfterUpdate event procedure.
As an alternative, you could pass an object reference to the form itself,
instead of the name of the form:
----- begin function version B -----
Function Update_opgMode(frm As Access.Form)
Dim strSQL As String
strSQL = _
"UPDATE tblConstants SET tblConstants.ConstantValue = " & _
frm!opgMode & _
" WHERE tblConstants.ConstantName = 'opgMode'"
CurrentDb.Execute strSQL, dbFailOnError
End Function
----- end function version B -----
And call it from the AfterUpdate event procedure using a function expression
like this:
=Update_opgMode([Form])
A third and maybe even simpler alternative, since all you seem to need in
the function is the value of the option group, would be just to pass that,
using a function definition like this:
----- begin function version C -----
Function Update_opgMode(varMode As Variant)
Dim strSQL As String
strSQL = _
"UPDATE tblConstants SET tblConstants.ConstantValue = " & _
varMode & _
" WHERE tblConstants.ConstantName = 'opgMode'"
CurrentDb.Execute strSQL, dbFailOnError
End Function
----- end function version C -----
... and call it from the AfterUpdate event procedure using a function
expression like this:
=Update_opgMode([opgMode])
There is also a fourth alternative, which is to rely on the form being the
active form when the function is called. In that case, you don't need to
pass anything at all to the function:
----- begin function version D -----
Function Update_opgMode()
Dim strSQL As String
strSQL = _
"UPDATE tblConstants SET tblConstants.ConstantValue = " & _
Screen.ActiveForm!opgMode & _
" WHERE tblConstants.ConstantName = 'opgMode'"
CurrentDb.Execute strSQL, dbFailOnError
End Function
----- end function version D -----
To call this version, you can just use this function expression:
=Update_opgMode()
So there you have 4 alternative ways to do this.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
