MS Access ApplyFilter

teammatt3's picture

He has: 2,102 posts

Joined: Sep 2003

I'm making a MS Access interface to a MySQL database, but I'm having trouble opening forms with a filter applied.

I have a form that lists Courses (datasheet view), upon dblclick, I want to open another form that lists the Sections in each Course. I have two forms, one that lists all Courses (which is what I want) but I have a form that lists all Sections, but I want it to display the Sections in each Course. I know I could use a subform, but that's no going to work the way I want it.

I tried running a Macro that OpenForm's the Section form with a where clause like "[CourseID] = [forms]![frmCourse]![CourseID]" but that doesn't do anything. I thought maybe the control source wasn't working right, so I just did "[CourseID] = 5" but once again, that did nothing. Any ideas on how to open a form with a modification to the query that generates it?

teammatt3's picture

He has: 2,102 posts

Joined: Sep 2003

I guess I can't refer to values in subforms properly. What I ended up doing was making a text field on the main form, which held the value of the field on the subform. Then on my applyfilter, I just referenced the field on the main form, not the subform. Weird.

JeevesBond's picture

He has: 3,956 posts

Joined: Jun 2002

teammatt3 wrote: I tried running a Macro that OpenForm's

Hmmm, you were using a Macro or running some VB code on an event. They're two different things: Macros == old skool and bad, VB code == new skool approved by Microsoft goodness.

If you were to do this again (sorry I missed your post first time round Roll eyes), try setting the SQL the subform (is it a subform or a linked form?) is based on in the code of your DoubleClick event. Does that make sense?

Glad you got it working though. Smiling

a Padded Cell our articles site!

Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.