By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,397 Members | 1,467 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,397 IT Pros & Developers. It's quick & easy.

Enter Records Based on 2 Related, but Independent Comboboxes

P: 55
I had to present my DB today, but was granted a chance to fix something about it. In order to fix something, I added something...something that is giving me more trouble than it should. I have to present again tomorrow. Quick assistance would be greatly appreciated.

I have three forms (one main form and two subforms) with the following fields:

frmResultsFY
FiscalYear [PK]

sfrmResultsAgencies
FiscalYear [FK]
Agency [PK]

sfrmResults
AutoNumber
RecordDate
FiscalYear [FK]
Agency [FK]

Quantity
Product

They're related in 1-to-many relationships in this order:

frmFiscalYear > sfrmResultsAgencies > sfrmResults

I made a lookup combobox in FiscalYear that allows me to select a year and change records. That is, when I select "2000," it's on record "1"; "2001," it's on record "2"; etc.

But, after selecting the FiscalYear, I need to be able to select an Agency and have each combination of FiscalYear and Agency be a unique record:

Example:
2000 > DHS
2000 > ICE
2000 > FBI
2001> DHS
2001 > ICE
2001 > FBI

Agency is a combobox that refers to a table (tblAgencies) from where it gets its list of agencies. The problem is that I can select a year (this works fine 'cause I can see the navbar changing records as I select a different year), but then the Agency combobox doesn't change records on its navbar.

Basically, I want "2000 > DHS," for example, to pull up the same set of products, and when I switch to "2001 > DHS," I don't want the same DHS products to appear because they shouldn't. "2001 > DHS" should have its own set of products.

I guess I'm having a problem with the comboboxes and how they relate. I'd like the Agency combobox to both 1) look up values in a table, and 2) find a record in the subform (sfrmResults) after I select a FiscalYear. Most importantly, I want the products to still be there when I select the right combination of values in the comboboxes (e.g. 2000, DHS; 2000, ICE; 2001, DHS; 2000, ICE; etc.).

Any suggestions as to how to set this up correctly would be great. I hope this makes sense 'cause I surely need help and fast.
Dec 11 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,396
1) Agency can't possibly be a primary key.

2) If those truly are all the fields in your tables. Then your fiscal year table is redundant.

3) You have to make sure you set up your parent/child relationships correctly in the subform properties.
Dec 11 '07 #2

P: 55
1) Agency can't possibly be a primary key.

2) If those truly are all the fields in your tables. Then your fiscal year table is redundant.

3) You have to make sure you set up your parent/child relationships correctly in the subform properties.
Hi, Rabbit. Thanks for your prompt reply. I've decided to give up on this "piggyback" combobox approach. You're right: Agency can't possibly be a primary key because I want it to be repeated. I tried making it a concatenated key with the input from the FiscalYear table. Even still, I was ending up spending way too much time on this.

What I've decided to do is eliminate the FiscalYear altogether and just make the Agency a lookup combobox for related product records. I've set it up and it works fine. It'd be nice to look up products by FiscalYear, but my back-up plan is my print reports form: It allows one to generate reports using a date range. That should suffice.

Please don't spend anymore time on this because I certainly can't.
Dec 11 '07 #3

Rabbit
Expert Mod 10K+
P: 12,396
Ok, but if you wanted it to filter on both fiscal year and agency you just have to make sure you set up the parent/child relationship correctly with two fields in each and it should work.
Dec 12 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.