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...som ething 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]
sfrmResultsAgen cies
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 > sfrmResultsAgen cies > 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.
3  1786  Rabbit 12,516
Recognized Expert Moderator MVP
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.
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.
Rabbit 12,516
Recognized Expert Moderator MVP
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.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Steve Jorgensen |
last post by:
Terminology question:
Is there a term for a set of records related directly or indirectly by key
value in several tables? For example, a single invoice record and its line
item records -or- a single customer, the customer's orders, the order lines
for those orders, the customer's invoices, and the invoice lines for those
invoices.
I'm thinking the term might be graph, but I'm not at all certain of this.
|
by: tom |
last post by:
I am new to SQL administration.
>From a list of IDs that are the primary key in one table (i.e. Customer
Table), I want to make changes in tables that use those IDs as a
foreign key.
Basically I want to say:
If fk_ID is in list then
do these statements to that record
|
by: Tony Williams |
last post by:
I have two tables one lists the names of committees and the other is a list
of documents they generate. I have a form based on the documents table which
gives details of the document including which committee generated it. On the
form I also want to indicate which other committees are interested in that
document, there could be 1 or say 8, it differs for each document. I had
thought of creating say 10 combo boxes based on the committee...
|
by: Jason |
last post by:
I am trying to filter records in a primary form based on records in
related tables. The data in the related tables is being displayed in
the primary form
through subforms. To be more specific, I have a primary form named
TestResults, which is connected to data in a table named TestResults.
There are basically two other tables that are related to the
TestResults table (and the primary form) named Names-Normalized and
SiteAddresses. The...
|
by: Kathy |
last post by:
In my custom menu one menu item opens a form to add new Makes to TblMakes and
another menu item opens a form to add new Models to TblModels. I have a form
that has a combobox for Makes and a combobox for Models. In both comboboxes I
use the NotInList event and code to allow users to enter Makes and Models that
are not in the database. When the form is open, I want to limit the users to
entering new Makes and Models in the comboboxes and not...
| | |
by: Colm O'Hagan |
last post by:
Hi there,
I having a problem with a database I'm setting up, I would be
delighted if someone out there could help.
The database I'm setting up is a task register datebase, it will be
used to create work schedules for workers in a metal work shop, based
on the tasks required to make a part and the tasks required to make
the subcomponents of the part.
|
by: DaveDiego |
last post by:
I've had a user delete one of the client records, I do have a version of
the DB with all records intact before the deletion occured.
Whats the best approach to getting all the related records in each of
the tables? I have about 12 tables to put data back into and multiple
records for each. Would I need to make an append or update query for each
table?
|
by: amywolfie |
last post by:
Is there a simple way to code:
If (no related records)
in VBA?
I would be going from frmMain to frm2, where frm2 may or may not have
a related record.
I do have a PK_ID in frmMain and a FK_ID in frm2.
|
by: RBohannon |
last post by:
I have a report with most fields populated by a query. However, some
of the fields are variable in such a way that their values cannot be
queried from a table. At present the values for these fields are
being entered by the user as parameter values when the query is run.
But there are four fields which need to be entered, and the pop-up
dialog boxes can be annoying.
I would like to have a form where the user can enter the data for...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |
