FORM event proc for a Button - Can reuse code ?? 2

[Dylan]

I have a database with multiple forms that req the same Button. My problem is when I copy the Button to the addl forms the event proc is not copied also.

My question is:
Is there a way to reference this ONE Event Proc from multiple Forms with the same button ??? I dont want to have to copy and paste the same rtn in 23 forms.

Thanks for any help on this ...

Tom


Tom Moran
Lansing, Michigan

 

[Philly44]

Its actually pretty easy. The following is an example of code I use when a button is clicked, this button appears on 35 forms in 10 deffirent database's(this code is placed in each mind you)

Public Function CustomerClick(FormName As String)
On Error GoTo Err_CustomerClick

DoCmd.Close acForm, FormName
DoCmd.OpenForm "frmCustomer"

Exit_CustomerClick:
Exit Function

Err_CustomerClick:
MsgBox Err.Description
Resume Exit_CustomerClick

End Function

The code above is placed in a module in the database and then is referenced from the buttons on click event as CustomerClick(Me.Name).

 

[Dylan]

Not sure on how Modules work. Can youo help ???

Tom

Tom Moran
Lansing, Michigan

 

[Philly44]

In the database design window go to the modules window and choose New. Create the function in the format I used above making sure to use proper naming conventions. Then save the module and your good to go. You can re-use custom procedures and functions over and over this way.

 

[Dylan]

Thanks for you help. Heres what I did:

I created a Module called UpdateUnassigned in my database.

I then place my Function into the new module as listed below
I then placed UpdateUnassigned(me.assigned_to,me.empname) as the name for the On Click property for my button.

Then when I clicked the button to test it I got mssg
"Cannot find macro 'UpdateUnassigned(me.assigned_to,me.empname)"

Your help is greatly appreciated ...

Tom


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Public Function UpdateUnassigned_Click(strrepnbr As String, strempname As String)
On Error GoTo Err_UpdateUnassigned_Click

Dim stranswer, strrepnbr, strempname As String

create_and_run_updateqry (strrepnbr), (strempname)

Exit_UpdateUnassigned_Click:
Exit Function

Err_UpdateUnassigned_Click:
MsgBox Err.Description
Resume Exit_UpdateUnassigned_Click

End Function

**********************************************************
Public Sub create_and_run_updateqry(strrepnbr, strempname)
Dim db As Database
Dim qrystring As String
Dim qryDef As QueryDef
Set db = CurrentDb
' delete the qry before we re-create it ...
db.QueryDefs.Delete ("qry_temp")
' create the qry string ...
qrystring = "UPDATE tbl_allreports SET tbl_allreports.assigned_to = '" & strempname & "' WHERE (((tbl_allreports.assigned_to)= 'Unassigned') AND ((tbl_allreports.reportnbr)= " & strrepnbr & "));"
' Now create the query that will be run ...
Set qryDef = db.CreateQueryDef("qry_temp", qrystring)
db.TableDefs.Refresh
'Now run the query that was created ...
'Stop
DoCmd.OpenQuery "qry_temp", acViewNormal, acReadOnly

End Sub

Tom Moran
Lansing, Michigan

 

[CajunCenturion]

You need to reference the name of the function in the module, not the name of the module itself.
Change:
UpdateUnassigned(me.assigned_to,me.empname)
to
Call UpdateUnassigned_Click (me.assigned_to,me.empname)
or to
UpdateUnassigned_Click me.assigned_to,me.empname
without the parens.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Philly44]

The first problem I see is the function you wrote is called UpdateUnassigned_Click and you reference it as UpdateUnassigned . The second problem I see is the way you are calling the procedure. Whe you are passing arguments you do it in one set of brackets and seperate with comma's. Its usually better to call this stuff from inside the code. Set the OnClick property to [Event Procedure]. Mostly though I don't see why you don't just call the sub instead of the function(though you should really redefine your Public Sub in the module you created and then call it instead). In Your OnClick event you could then put CreateandRunUpdateQry(me.assigned_to,me.empname)

 

[CajunCenturion]

Whe you are passing arguments you do it in one set of brackets and seperate with comma's.

There is nothing wrong with calling the function
create_and_run_updateqry (strrepnbr), (strempname)
In fact this is the proper syntax for calling the function if your intent is to pass both parameters By Value, instead of the default By Reference

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Philly44]

CajunCenturian: I don't know where you learned to program but that is defenitly not the proper way to do it. I'm not trying ot be rude or even defensive but I have taken amny classes on programming in many languages and have been programming for quite some time and I have never seen an example of doing it that way.

 

[CajunCenturion]

Well, there you go Philly44, you learn something new every day.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Philly44]

Heres a star for you.

 

[PHV]

Public Function UpdateUnassigned_Click(strrepnbr As String, strempname As String)
Dim stranswer, strrepnbr, strempname As String
I'm wonder if strrepnbr would be a Variant ?
Just to say I don't think it's a good idea to have local variables with same name as parameters ...


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CajunCenturion]

I'm in the process of writing an FAQ on the subject, but for a quick explanation, anytime an individual parameter is enclosed in parenthesis, it is evaluated as an expression, and the results of an expression are always passed by value. If not enclosed in parens, then the variable is passed by the default mechanism, which in VBA is by reference. This holds true (with perhaps the default mechanism) in almost every language.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

PHV The statement
Dim stranswer, strrepnbr, strempname As String
will result in stranswer and strrepnbr as Variants, and strempname as a String.


Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[PHV]

CajunCenturion, obviously I knew that.
I just outlined that the parameters will be masked by the local variables ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CajunCenturion]

It will be masked by the local variables in some languages PHV, but not in VBA. In VBA, you get a duplicate variable definition error.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[PHV]

Ah, so strrepnbr isn't a Variant .

 

[CajunCenturion]

To get back to the original post,

How is it going Dylan?

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Dylan]

HI Guys,

I was able to get it to work but I still had to code an EP for each form (24 of em) with one line containing:


Call UpdateUnassigned (me.assigned_to,me.empname)

I could not get syntax right to just put it on the "On Click" property of the Command Button.

Thanks for all your help

Tom (from Mich) GO WINGS

Tom Moran
Lansing, Michigan

 

[PHV]

just put it on the "On Click" property of the Command Button.
If UpdateUnassigned is coded as a function, I think you can set the OnClick property to something like:
=UpdateUnassigned(your parameters list)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244