Trying to call a function using EVAL 1

[FancyPrairie]

I have a public function, within a form module, that I'm having trouble executing.

Here's how it works...Form A contains a subform (Form B). When the users clicks a button on Form B, Form C is opened as a popup. And Form C is passed the name of the function (foo) it is supposed to call when the user selects something. So, Form C calls a public Function located in subform B. I have tryed using the following syntaxes but with no luck. Any suggestions?

1. Eval("forms!FormA!FormB.form.foo")
2. Eval("forms(""FormA"")(""FormB"").form.foo")
3. Eval("Forms.FormA.FormB.form.foo")

Note that all 3 methods work in Debug's Immediate window. Well, that is, they work without the Eval statement. For example, the function foo is opened if I type, for example, Forms!FormA!FormB.form.foo in debug's immediate window.

 

[MajP]

Not sure why this is not a public function in a public module. But the simplest fix

'Pass in something like A,B or C instead of the function name
if not trim(me.openargs & " ") = ""
select case me.openargs
Case "A"
forms("FormA").FormB.form.somefunction
Case "B"
forms("FormA").FormB.form.someOtherfunction
case "C"
forms("FormA").FormB.form.someOtherOtherfunction
end select
end if

Or instead why not have a single function in B that form C calls and passes a parameter to determine the action.

 

[FancyPrairie]

That won't work in this case because Form C does not know the name of the function until runtime. That is, other forms may popup Form C and each of these forms may have their own function that needs to be called.

 

[MajP]

Can you explain this design and what the function do. It just seems like a poor design: Multiple forms passing functions names to a pop up. What is the purpose?

 

[strongm]

I'm with MajP. However, having said that, I would suggest that you might like to investigate the CallByName function which should do exactly what you are looking for

 

[FancyPrairie]

The user is allowed to select the criteria for filtering the report they selected. They can then save the criteria they selected. They can then run the report via the task scheduler or, instead of having to reselect the criteria for the report selected, they can simply select the saved criteria.

So suppose the report they select, on a regular basis, is filtered based on a date range (i.e. Between Today and Today -3).
The user can select the date range values via comboboxes. The RowSource of the combobox contains the words: Today;Yesterday,First day of month, and so. The user can select the Word Today or enter Today - 3. Or the user can enter a date (i.e. 10/17/11). When the report is run, and it sees the word "Today" in the crtieria, it substitutes todays date.

Because the combox is not a date field, the built in calendar does not appear when the user clicks on the field. Nor should it since the user could select a value from the combobox. I can look at the first character entered in the combobox and do a bunch of stuff if it's a number. Regardless of what I do, if the user wants to select a date from the built in calendar, they have to click a couple times. So, I'm using a calendar control I built years ago.

The reason I need the name of the function passed to this calendar form is because the AfterUpdate event of the combobox (described above) is not fired when the Calendar control sets the value of the combobox to the date selected on the calendar. Therefore, the calendar form "may" need to let the calling form know that a change was made. Hence the reason for the name of the function. If the user selects a date from the calendar, the calling form may need to process some code if the value of control is changed.

 

[MajP]

Are you simply trying to return a value from a dialog form? There is a much better way to do that.

 

[FancyPrairie]

I tried the callByName function but couldn't get that to work either. What would the syntax be?

MajP, it is not a dialog form. So I can't just set a global variable in the calendar form and have the calling form check it.

 

[MajP]

calling form

Private Sub Command0_Click()
  Dim frm As Access.Form
  DoCmd.OpenForm "form2"
  Set frm = Forms("form2")
  Set frm.mFrm = Me
  frm.mFnc = "Test"
End Sub

Public Sub Test()
  MsgBox "hi"
End Sub

pop up form

Option Compare Database
Option Explicit
Public mFrm As Access.Form
Public mFnc As String

Private Sub Command0_Click()
  CallByName mFrm, mFnc, VbMethod
End Sub

 

[FancyPrairie]

Thank you. I misread the documentation on the CallByName and was passing the first argument as as string rather than as the form object.

 

[MajP]

This is the way I would do it if the called form is not dialog. I simply capture the called forms events in the calling form. This requires no code in the called form.

calling form

Public WithEvents ctrl As Access.CommandButton

Private Sub Command0_Click()
  DoCmd.OpenForm "frmPopUp"
  Set ctrl = Forms("frmPopUp").cmdBtn
End Sub

Private Sub ctrl_Click()
  'when you click the cmd button in the called form this is trapped in
  'the calling form
  MsgBox "Hi"
End Sub

 

[FancyPrairie]

I really like your example and may retrofit some code to use it. However, since there are multiple command buttons from which the user may choose, I think it would be cumbersome in this case.

 

[MajP]

I prefer to make make pop ups dialog. Then you can use this trick

If on your pop up form you put an OK, Cancel buttons.

In the OK click event
me.visible = false
In the Cancel click event
docmd.close acform, me.name

Now from any calling form you call a function to pop open your dialog form.
Change frmInputBox to your form name.

[/code]
Public Function myInputBox(optional myargs as string = "") As Variant
Dim strArgs As String
Dim frm As Access.Form
DoCmd.OpenForm "frmInputBox", , , , , acDialog, strArgs
If CurrentProject.AllForms("frmInputBox").IsLoaded Then
Set frm = Forms("frmInputBox")
'change the below to return the value of your control
myInputBox = frm.txtBxInput
DoCmd.Close acForm, frm.Name
End If
End Function

When you call the function code stops in the calling form until the pop up closes or becomes invisible.  Then the code resumes in the calling form.  The calling form then checks if the form is loaded (but invisible). It can then read any values off of the pop up form. Then if the form is loaded it closes it.