shortcut menu reuse issues

[Dale Fye]

I've got a shortcut (popup) menu that I want to use with several forms. It
is a simple menu and function call, but I would like to avoid multiple
similar copies of the shortcut menu or function that the menu implements to
just handle the name of a table. Basically, the popup calls a function
(fnSelect) and passes it a value (-1, 0, or 1) which will determine whether a
field (IsSelected) in the table of interest is set to true, false, or is
inverted.

The challenge I'm having determining what table to use. I'd like to use
this same menu for three different forms (each using a separate table as its
RecordSource), all of which are either subforms, or sub-subforms. I wrongly
assumed that I could refer to the Screen.ActiveForm to identify the form that
the shortcut was activated from, but that did not work. I then tried
Screen.Activeform.ActiveControl, and got the control name of the subform that
was active, but that did not help for identfying the sub-subform. I'm stuck
at:

Public Function fnSelect(OptionValue as integer) as boolean

Dim TableName as string
Dim strSQL as string

'Need a way to determine which sub or sub-sub form the
'shortcut menu was activiated from. This worked for a
'sub-form, but not for a sub-sub form
Select Case Screen.Activeform.ActiveControl
Case "frm_1"
TableName = "tbl_1"
Case "frm_2"
TableName = "tbl_2"
Case "frm_3"
TableName = "tbl_3"
Case Else
Goto SelectError
End Select

strSQL = "UPDATE [" & TableName & "] " _
& "SET [IsSelected] = " & iif(OptionValue = 1, _
" NOT
[isSelected]", _
OptionValue) & " " _
& "WHERE [Act_ID] = " & Forms("frm_Activities").txt_Act_ID
Currentdb.execute strsql, dbFailOnError

'Insert a line here to requery the appropriate sub or sub-sub form


SelectExit:
fnSelect = true
Exit Function
SelectError:
msgbox "Error updating IsSelected in table '" & TableName & "'"
fnSelect = false
End Function

Would appreciate any help I can get. Thanks.

 

[Albert D. Kallal]

One simple solution is to place the public function in each form (it will
have to be in the parent (top most) form. This could actually could work
to ones advantage as then the code would know what form (table) it is in
and you would not need the case/select to determine the table name. However
this would result in 3 copies of the code (bad).

So, kind in mind a public function placed in the form is run FIRST by the
menu, and ONLY
if the menu don't find the function in the current active form (top most
form, not subform), then it runs the function in a standard module with the
function name it is looking for. (this allows us to use the same menu bar
for many different forms).

However, we **can** code this in ONE public routine and I suggest you do...

Here is how you code can look:

dim frmSub as form
dim strSql as string
dim strTable as string

set frmSub = GetSubForm(Screen.ActiveForm)

Tablename = frmSub.RecordSource
strSQL = "UPDATE [" & TableName & "] " _

etc....


The function GetSubform is:

Public Function GetSubForm(f As Form) As Form

Static fs As Form

If f.ActiveControl.ControlType = acSubform Then
GetSubForm f.ActiveControl.Form
Else
Set fs = f
End If

Set GetSubForm = fs
End Function

The above is what we call a recursive routine (it calls it self). Thus,
this code will work for a main form, or 5 sub-forms deep. The function
will **always** return the form that was clicked on, and it works even
if there is no sub-form..

You can grab any value from the sub-form as:

frmsub.Controls("name of control").Value

(the value property is usually optional).

And, to requery..you can go

frmSub.Requery


In the above, I also grabbed the "table" of the current sub form via

frmSub.RecordSource

In fact, the form could be based on a query, and recordSource would return
that. This suggestion was give as a possible way to avoid your case
statement to determine what table the form is based on.

You can however still grab the name of the select sub-form via:

msgbox "the name of the sub form you clicked on is " & frmSub.RecordSource.

 

[Albert D. Kallal]

msgbox "the name of the sub form you clicked on is " & frmSub.RecordSource.


should read

msgbox "the name of the sub form you clicked on is " & frmSub.Name