Apply filter on other form on basis of field in subform

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I'm about to complete a database for the internship coordinator of my
university, but one problem is still standing in my way. I hope
someone can tell me how to solve it.

I have a main form (frmOverview) with a subform (subfrmInternship). A
student's (the one selected) personal data is presented in the
mainform. Summary data of the internships that he/she did are
presented in the subform. Only the summary data of one internship is
shown, but within the subform it is possible to navigate between
certain internships the student did. Then there is a button (more
info) on the subform that opens a popup form (frmExtraInfo) with extra
information regarding the internship that is selected on the subform.
If I want the correct data to be displayed in frmExtraInfo I need to
apply a filter on load of frmExtraInfo, since different internships
can be selected within the subform.

I tried to do this by running an ApplyFilter macro OnLoad of
frmExtraInfo. The WHERE condition of the macro was set as follows:

[Internship_ID]=[Forms]![subfrmInternship]![Internship_ID]

This works perfectly if I open the subform separately and then click
the "more info" button. However when the subform is opened as part of
the mainform it doesn't work and I get a blank frmExtraInfo form.

Then I tried to specify the field [Internship_ID] in the subform as
follows:

[Internship_ID]=[Forms]![frmOverview].[subfrmInternship]![Internship_ID]

This time I get a popup box that indicates that this field does not
exist. As far as I understand, because the mainframe has the focus and
not the subform.

I hope I made my problem clear. Does someone know how this works?
Thanx
 
Hi Paul

Don't apply the filter after frmExtraInfo is loaded - instead, specify the
filter criterion to the OpenForm method.

DoCmd.OpenForm "frmExtraInfo", _
WhereCondition := "[Internship_ID]" & Me![Internship_ID], _
WindowMode := acDialog
 
Graham, thanx for your reply.

I tried to do it in this way, but I think I did something wrong. I
copied the code and placed it in the code that's executed when
clicking the button "more Info" on the subform. I didn't add anything
to the code, nor did I delete something. Then when clicking the button
I got the following error: "Syntax error (missing operater) in query
expression '[internship_Id]1".
I guess I placed the code in the wrong place or maybe I forgot to add
basic stuff to the code. However, with the codes I'm just a rookie, so
I don't know.
What did I do wrong? thx
 
Hi Paul

Ooops - sorry :-) I left out an "=" sign...
WhereCondition := "[Internship_ID]=" & Me![Internship_ID], _
Click to expand...
Click to expand...

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Paul said:
Graham, thanx for your reply.

I tried to do it in this way, but I think I did something wrong. I
copied the code and placed it in the code that's executed when
clicking the button "more Info" on the subform. I didn't add anything
to the code, nor did I delete something. Then when clicking the button
I got the following error: "Syntax error (missing operater) in query
expression '[internship_Id]1".
I guess I placed the code in the wrong place or maybe I forgot to add
basic stuff to the code. However, with the codes I'm just a rookie, so
I don't know.
What did I do wrong? thx
Hi Paul

Don't apply the filter after frmExtraInfo is loaded - instead, specify the
filter criterion to the OpenForm method.

DoCmd.OpenForm "frmExtraInfo", _
WhereCondition := "[Internship_ID]" & Me![Internship_ID], _
WindowMode := acDialog

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand
Click to expand...
Click to expand...
 
Thx, it works perfectly. In the mean time I also figured it out how to
do it with macro's but this is easier.

For the macro you can refer to the field in the subform like this:

Forms!frmOverview!subfrmInternship.Form!Internship_ID

Graham Mandeno said:
Hi Paul

Ooops - sorry :-) I left out an "=" sign...
WhereCondition := "[Internship_ID]=" & Me![Internship_ID], _
Click to expand...
Click to expand...

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Paul said:
Graham, thanx for your reply.

I tried to do it in this way, but I think I did something wrong. I
copied the code and placed it in the code that's executed when
clicking the button "more Info" on the subform. I didn't add anything
to the code, nor did I delete something. Then when clicking the button
I got the following error: "Syntax error (missing operater) in query
expression '[internship_Id]1".
I guess I placed the code in the wrong place or maybe I forgot to add
basic stuff to the code. However, with the codes I'm just a rookie, so
I don't know.
What did I do wrong? thx
Hi Paul

Don't apply the filter after frmExtraInfo is loaded - instead, specify the
filter criterion to the OpenForm method.

DoCmd.OpenForm "frmExtraInfo", _
WhereCondition := "[Internship_ID]" & Me![Internship_ID], _
WindowMode := acDialog

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand
Click to expand...
Click to expand...
Click to expand...
 
Back
Top