OK, I wrote something which may be re-inventing the wheel... or not, which I
want to share, based on what Douglas gave me (below).
Suppose I have a form "assignment" with fields "personID" and "personName"
and then some.
I want the user to be able to double click on the"personName" field to open
a "personForm" with details about the person. Now I want this to happen
wherever the "assignement" form is used all by itself or as a subform of any
other form "superForm". And I want to write code only once in the form
"assignment".
I wrote the following module:
Sub OpenForm(orgForm As Form, destFormName As String, _
orgIdFldName As String, destIdFldName As String)
Dim strParentName As String
Dim strOrgFormName As String
Dim strWhereCond As String
On Error Resume Next
strOrgFormName = orgForm.Name
If Not IsNull(orgForm(orgIdFldName)) And strOrgFormName <> destFormName
Then
strParentName = orgForm.Parent.Name
If Len(strParentName) = 0 Or Err.Number = 2452 Then ' form used by
itself
strWhereCond = "[" & destIdFldName & "]=[Forms]![" &
strOrgFormName & _
"].[" & orgIdFldName & "]"
' ex: [personID]=[Forms]![assignment].[personID]
Else ' form used as a subform of "superForm"
strWhereCond = "[" & destIdFldName & "]=[Forms]![" &
strParentName & _
"]![" & strOrgFormName & "].[Form]![" & orgIdFldName & "]"
' ex:
[personID]=[Forms]![superForm]![assignment].[Form]![personID]
End If
DoCmd.OpenForm formName:=destFormName,_
WhereCondition:=strWhereCond, DataMode:=acFormEdit
End If
End Sub
Now all I have to do is write a one-line DoubleClick event for the
"personName" field
Private Sub personName_DblClick(Cancel As Integer)
OpenForm Me, "personForm", "personID", "personID"
End Sub
and forget about it (well, until tomorrow morning as it's now late at night)
--
Fjordur
Douglas J Steele said:
Don't use macros: use VBA.
You'd have a line of code
DoCmd.OpenForm FormName:="Detail", _
WhereCondition:= "IDDetail='" & [Forms]!
- .[F] & "'"
rather than what you're showing for your macro.
You'd have to change that to something like:
Dim strParent As String
On Error Resume Next
strParent = Me.Parent.Name
If Err.Number = 2452 Then
strParent = ""
End If
If Len(strParent) > 0 Then
DoCmd.OpenForm FormName:="Detail", _
WhereCondition:= "IDDetail='" & Forms(strParent)!
- .Form![F] &
"'"
Else
DoCmd.OpenForm FormName:="Detail", _
WhereCondition:= "IDDetail='" & [Forms]!
- .[F] & "'"
End If
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Fjordur said:
I understand.
But, OTOH, testing that would not help me much, as the reference to data in
the macro would still have to be modified; OK this could be done in code...
May I reformulate my problem:
- form f call macro m
- macro m references data from form f
- f is used as subform in forms f1, f2, f3
- therefore the data references in the macro have to change
Or do they? Isn't there a syntax I could use in the macro to reference data
from f that would be invariant to the place where f is used?
--
Fjordur
Douglas J Steele said:
You could check whether or not List is a subform by looking at its Parent
property. Unfortunately, you can't do this using a macro, because
looking
at
the Parent property when the form isn't being used as a subform will raise
Error 2452, and you can't do error trapping with a macro.
I have a form "List" displayed as continuous form. The form has a field
"F"
that I want to doubleClick to open a detail form "Detail". I wrote a macro
for the DoubleClick event of F; the macro goes:
OpenForm
Form Name: Detail
Where Condition: IDDetail=[Forms]!
- .[F]
Now the same "List" form is used as a subsheet in another form,and of
course
the doubleClick doesn't work anymore bacuse the reference to field F is
now
incorrect.
Of course I could duplicate the "List" form and the macro but that's a
shame
because anyway I'm still using the very same data to do the very same
action. I keep thinking there must be a better way to reuse the same form
in
different contexts.
Is there? How?