MS Access
August 20, 2004 5:47 PM Subscribe
MSAccessFilter: I give up...how can I have the current selection in one control dynamically re-configure the choices available in another? [more inside, obviously]
I've been using Access on and off for a couple of years now, and I've been working with relational DBs since the early '80s, so I know that what I'm trying to do isn't outrageous, but I cannot, for the life of me, figure out how to do this in Access. (I'm using Access 2003, by the way.) Just to be precise, and not waste anyone's time explaining what I've already tried, here's what I'm trying to do:
1) I've got multiple tables/queries set up--as a general example, let's use "Companies" and "Employees". Employee records are, of course, related by a foreign key to the Company records. (Assume there's an established 1-to-many relationship between the first table and the second, which Access knows about explicitly through the "Relationship" diagram. Referential integrity, the whole thing.)
2) Let's say I've got a datasheet or a form that's got two pull-downs, one for "Company", and the other for "Employee". If I choose "Company A" under the first pull-down, I'd like to dynamically constrain the choices in the "Employee" pull-down to only show records that relate back to that specific Company record. (I'd like the "Employee" pull-down to dynamically show only employees of "Company A", in other words, even though the "Employees" table contains all employees of all companies.)
Is that feasible, using either a table/query datasheet or a form? I know how to create a "hard-wired" SQL query that only pulls out Employees related to an explicit Company key, but that doesn't do me any good. I've tried using "DLookup()", but even copying-and-pasting the example code, I keep getting errors. (And anyway, DLookup() only returns one value, anyway--not the multi-record mini-query I'd need).
- Is there some way to create a dynamic SQL query using "Forms![Form_Name]![Field_Name]" that I'm just missing because of some syntax weirdness? I've tried many ways of making that work, but haven't had any luck.
- Is there just a way to use sub-forms hierarchically, so that the pull-down on one area of a form can constrain the data that's retrieved by a child sub-form?
I kind of feel silly asking something this simple, but I've searched the Access online help base a bazillion different ways, googled endlessly, and gone so far as to buy 2 or 3 Access "how-to" books--I still cannot find a definitive answer to this (even if that answer is "It's impossible.") Is it impossible in a datasheet, but doable somehow in a form? Am I just being an idiot, and missing something blatantly obvious?
Like I said, I know this makes me an old fart, but I started dealing with relational DBs on a CompuColor using dBase II (I actually used to talk to Tate on tech support calls), but that was all pre-SQL. Maybe I'm just being a total moron, or maybe Access just sucks (surprise!), but it's driving me crazy.
Thanks in advance for any help.
I've been using Access on and off for a couple of years now, and I've been working with relational DBs since the early '80s, so I know that what I'm trying to do isn't outrageous, but I cannot, for the life of me, figure out how to do this in Access. (I'm using Access 2003, by the way.) Just to be precise, and not waste anyone's time explaining what I've already tried, here's what I'm trying to do:
1) I've got multiple tables/queries set up--as a general example, let's use "Companies" and "Employees". Employee records are, of course, related by a foreign key to the Company records. (Assume there's an established 1-to-many relationship between the first table and the second, which Access knows about explicitly through the "Relationship" diagram. Referential integrity, the whole thing.)
2) Let's say I've got a datasheet or a form that's got two pull-downs, one for "Company", and the other for "Employee". If I choose "Company A" under the first pull-down, I'd like to dynamically constrain the choices in the "Employee" pull-down to only show records that relate back to that specific Company record. (I'd like the "Employee" pull-down to dynamically show only employees of "Company A", in other words, even though the "Employees" table contains all employees of all companies.)
Is that feasible, using either a table/query datasheet or a form? I know how to create a "hard-wired" SQL query that only pulls out Employees related to an explicit Company key, but that doesn't do me any good. I've tried using "DLookup()", but even copying-and-pasting the example code, I keep getting errors. (And anyway, DLookup() only returns one value, anyway--not the multi-record mini-query I'd need).
- Is there some way to create a dynamic SQL query using "Forms![Form_Name]![Field_Name]" that I'm just missing because of some syntax weirdness? I've tried many ways of making that work, but haven't had any luck.
- Is there just a way to use sub-forms hierarchically, so that the pull-down on one area of a form can constrain the data that's retrieved by a child sub-form?
I kind of feel silly asking something this simple, but I've searched the Access online help base a bazillion different ways, googled endlessly, and gone so far as to buy 2 or 3 Access "how-to" books--I still cannot find a definitive answer to this (even if that answer is "It's impossible.") Is it impossible in a datasheet, but doable somehow in a form? Am I just being an idiot, and missing something blatantly obvious?
Like I said, I know this makes me an old fart, but I started dealing with relational DBs on a CompuColor using dBase II (I actually used to talk to Tate on tech support calls), but that was all pre-SQL. Maybe I'm just being a total moron, or maybe Access just sucks (surprise!), but it's driving me crazy.
Thanks in advance for any help.
Response by poster: Thanks, madman...hadn't heard of that site before.
posted by LairBob at 8:22 PM on August 20, 2004
posted by LairBob at 8:22 PM on August 20, 2004
Best answer: LairBob, you have to learn just a little about controls, their properties, and their events, and it is easy.
Let's take a typical case: I want to pick one thing (say, a fiscal year) from a combo box, and have a second combo box then show just the customers for that year.
How to do this? A combo box has a .Rowsource property, and we can set it to a query. When? Access Controls have Events, that are triggerred by user actions. The .AfterUpdate event is what you want here: after you update a control, but just before it loses the focus, it is triggered. So, you need to run a little VBA, or even an Access macro attached to .AfterUpdate of ComboBox1 that sets the .RowSource of ComboBox2 to an Access query with a paramater of the first combo box in the WHERE clause. Pretty much any of the intermediate level Access books out there will have step by step examples of this technique.
posted by crunchburger at 11:39 PM on August 20, 2004
Let's take a typical case: I want to pick one thing (say, a fiscal year) from a combo box, and have a second combo box then show just the customers for that year.
How to do this? A combo box has a .Rowsource property, and we can set it to a query. When? Access Controls have Events, that are triggerred by user actions. The .AfterUpdate event is what you want here: after you update a control, but just before it loses the focus, it is triggered. So, you need to run a little VBA, or even an Access macro attached to .AfterUpdate of ComboBox1 that sets the .RowSource of ComboBox2 to an Access query with a paramater of the first combo box in the WHERE clause. Pretty much any of the intermediate level Access books out there will have step by step examples of this technique.
posted by crunchburger at 11:39 PM on August 20, 2004
Best answer: Hmm, some of your other questions:
You can make a form have a query as its data source.
You can set the .Rowsource of a control directly to SQL like "SELECT a.* FROM b WHERE a.f1 = " & Me.Combo1, or to a savcd query by name.
Subforms can be linked , yes. There is a Parent/Child ID property for subforms - set it to the key in related tables and the subform will reset on its own.
Good luck!
posted by crunchburger at 11:59 PM on August 20, 2004
You can make a form have a query as its data source.
You can set the .Rowsource of a control directly to SQL like "SELECT a.* FROM b WHERE a.f1 = " & Me.Combo1, or to a savcd query by name.
Subforms can be linked , yes. There is a Parent/Child ID property for subforms - set it to the key in related tables and the subform will reset on its own.
Good luck!
posted by crunchburger at 11:59 PM on August 20, 2004
Response by poster: crunch, thanks so much. I've actually done a bit of VB programming, so controls and events aren't totally foreign to me, but I guess I was just looking or hoping to establish the "constraint" relationship more directly through Access and SQL features, rather than force the behavior through my own code. I've basically managed to avoid any scripting on the project I'm working on so far, but I guess it's maybe time to start.
This is really helpful, though--I'll explore this avenue more later today, especially the parent/child hierarchy in subforms. Now that I know it's possible, I can pursue it more aggressively.
posted by LairBob at 4:12 AM on August 21, 2004
This is really helpful, though--I'll explore this avenue more later today, especially the parent/child hierarchy in subforms. Now that I know it's possible, I can pursue it more aggressively.
posted by LairBob at 4:12 AM on August 21, 2004
« Older Switching Identities in Microsoft Outlook | After I replaced C: on Windows XP Pro, the D drive... Newer »
This thread is closed to new comments.
posted by madmanz123 at 7:38 PM on August 20, 2004