Changing a sub from the on click event of a control to a public function

[tmort]

I have a form/subform. On the main form criteria for a query are
entered and when a command button is pressed the subform displays the
results. I've added another button to open the results as an Access
database. I'd like this button to rerun the code that is on the other
button.

To do this I planned on making this code a public function and calling
it from the other two buttons. The code has a lot of Me. statements
which I have converted to forms!form name!control name. I think I am
on the right track but at about the last line I have a statement that
sets the subform (child0) recordsource as an SQL statement. The line
of code is:

Me.Child0.Form.RecordSource = strFinalSQLStatement

If I set it to Forms!child0 it doesn't work. How do I accomplish this
from a public function?

Thanks

 

[Steve Schapel]

Tmort,

Try...
Forms![form name]!Child0.Form.RecordSource

Probably should be a Public Sub rather than a Function.

If both the buttons are on the same form, you don't need to have the
code in a standard module, which it sounds like you are doing. It could
still be in the form's module, and as such the Me. syntax would still work.

Another approach is to put code on the second botton like this...
Private Sub SecondButton_Click()
Call FirstButton_Click
End Sub

 

[Guest]

Thanks,

They were on the same form so I just used Call

Tmort,

Try...
Forms![form name]!Child0.Form.RecordSource

Probably should be a Public Sub rather than a Function.

If both the buttons are on the same form, you don't need to have the
code in a standard module, which it sounds like you are doing. It could
still be in the form's module, and as such the Me. syntax would still work.

Another approach is to put code on the second botton like this...
Private Sub SecondButton_Click()
Call FirstButton_Click
End Sub

--
Steve Schapel, Microsoft Access MVP

I have a form/subform. On the main form criteria for a query are
entered and when a command button is pressed the subform displays the
results. I've added another button to open the results as an Access
database. I'd like this button to rerun the code that is on the other
button.

To do this I planned on making this code a public function and calling
it from the other two buttons. The code has a lot of Me. statements
which I have converted to forms!form name!control name. I think I am
on the right track but at about the last line I have a statement that
sets the subform (child0) recordsource as an SQL statement. The line
of code is:

Me.Child0.Form.RecordSource = strFinalSQLStatement

If I set it to Forms!child0 it doesn't work. How do I accomplish this
from a public function?

Thanks