Event From a subform (97)

Topic

New Reply

A new challenge.
I have three forms. A main form with a subform (frmMain), the subform (sfrmMain), and separate form that contains a single memo field (frmNotes). The plan was to double click on a field in sfrmMain, and have that open frmNotes. IF I open sfrmMain by itself, all works great. The frmNotes form pops open, and shows the notes for the relevant record on the (sub)form.

However, if I open the main form (frmMain), which includes sfrmMain as a subform, and double click I get a “Enter Paramater Value” for the field used as the key/link between sfrmMain and frmNotes.

But when running just the form, not as a subform of the main form, it works correctly. Is there a workaround for this?

Thanks in advance for your ideas and help.

Ken

Reply | Quote

Replies

It probably has to do with the way you refer to the control on the subform sfrmMain. When you open it by itself, you can refer to a text box txtLink as Forms!sfrmMain!txtLink.

However, if you open the main form, the subform is not part of the Forms collection; you must refer to it through the main form: Forms!frmMain!sfrmMain!txtLink or Forms!frmMain!sfrmMain.Form!txtLink. Here, you must use the name of the subform as a control on the main form; this is not necessarily the same as the name of the subform in the database window.

Of course, you must use the name of your “link” control instead of txtLink. If this doesn’t help, post back with more details (how do you open frmNotes; what is the record source of frmNotes?)

Reply | Quote

Thanks Hans. The following is the code I used in the Event of the control.

Private Sub txtTermDate_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = “frmNotes”
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Record source for form frmMain is tblPerson, which includes an Employee ID field that is used as the parent.
Record source for subform sfrmMain is tblRehire, which includes an Employee ID field that is used as the child.
Record source for frmNotes is tblRehire, which includes a primary key that acts as the link to sfrmRehire.

sfrmMain contains three visible controls (txtTermDate, txtReason, chkRehire) and on hidden control (pkey).
frmNotes contains two controls, memNotes (visible) and pkey (hidden)
tblRehire contains the following fields:
pkey
fk_EmployeeID
dtmHireDate
dtmTermDate
strReason
logRehire
memRemarks

Does this help?

Thaniks,
Ken

Reply | Quote

Hi Ken,

It is not clear from your description how frmNotes “knows” which record to display. You don’t set a value for stLinkCriteria in txtTermDate_DblClick, and the record source for frmNotes is a table, not a query with criteria.

Does frmNotes have code to select a record or records, for example in the On Load or On Open event?

Reply | Quote

Hans,
I’m sorry about the lack of clarity.
frmNotes “knows” which record to display based on the query that feeds the form:
SELECT tblRehire.pkey, tblRehire.memRemarks
FROM tblRehire
WHERE (((tblRehire.pkey)=[Forms]![sfrmMain]![txtpkey]));

In other words, when they double click on the control on the record displayed in the subform I use the txtpkey to relate to the appropriate record in tblRehire. Does that make sense?

Again, I apologize for the lack of information.

Thanks,
Ken

Reply | Quote

In Access 97, you have to include the Form reference in order to be sure you are pointing at the subform’s control rather than the subform control on the parent form.

WHERE (((tblRehire.pkey)=[Forms]![sfrmMain].[Form]![txtpkey]))

However, if the form you are popping up is already open, you may wind up with unexpected results unless you force the form to requery. It would probably be faster and cleaner to open the form directly from the dblClick event using DoCmd.OpenForm and pass in a WhereCondition to specify the where clause. Then you could simply base the popup form on tblRehire instead of having to force a requery on it to get the condition to be recognized.

Reply | Quote

I was confused because in your previous reply, you wrote that the record source for frmNotes was tblRehire. Now it turns out to be a query instead.

Charlotte has already pointed out that it’s more elegant to put the where condition in the On DblClick code that opens frmNotes:

Private Sub txtTermDate_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = “frmNotes”
stLinkCriteria = “pkey=” & Me.[sfrmMain].[Form]![txtpkey]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

In that case, the Record Source of frmNotes should be tblRehire instead of the query.

If you prefer not to adapt the code, and to keep the query as record source of frmNotes, the WHERE part should be

WHERE (((tblRehire.pkey)=[Forms]![frmMain]![sfrmMain].[Form]![txtpkey]));

Reply | Quote

Charlotte and Hans,

THANKS! ! !
I tried it both ways and it worked! You guys are awesome.

Charlotte,
RE: “However, if the form you are popping up is already open”
No. The form can only be opened from the double click event, and when open is modal forcing user to close to notes form before going anywhere else. There may be caveats to that approach, but I don’t want the user to have the ability to have a bunch of forms, report previews, etc. open at one time.

THANKS for sharing your brilliance.

Ken

Reply | Quote