Apply filter on other form on basis of field in subform

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
 
G

Graham Mandeno

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
 
P

Paul

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
 
G

Graham Mandeno

Hi Paul

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

--
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
 
P

Paul

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], _

--
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top