Unanswered: Cascading combo boxes, stored field values
I am relatively new to Access (no SQL experience).
My problem is pretty straightforward. I have a table of companies that I am classifying according to their Sector, Segment, and Subsegment. To do this, I have a set of 3 cascading combo boxes on an input form. These query the 3 tables (Sector, Segment, and Subsegment) to show appropriate category depending on user selection.
Each table contains ID and Name fields. For example, structure of the Sector table is as follows:
SectorID: 1, SectorName: New Media
SectorID: 2, SectorName: IT
The Segment table then expands on each sector:
SectorID: 1, SegmentID: 1, SegmentName: Advertising
SectorID: 1, SegmentID: 2, SegmentName: Video
The Subsegment table then further expands on the Segment:
SegmentID: 1, SubsegmentID: 1, SubsegmentName: Ad network
SegmentID: 1, SubsegmentID: 2, SubsegmentName: Ad exchange
Problem is, in order for the cascading combo boxes to work, I'm binding the Sector and Segment combo boxes to their ID fields, which results in the ID rather than their actual label getting stored into the Company record.
Consequently, when I later want try to display a Company record, I get the ID field instead of the Name field, e.g., 1 vs. "New Media" if user had originally selected "New Media" as the Sector.
This seems like a very simple problem. Any suggestions as to quickest and easiest way to resolve?
Storing the ID value is the normal method. To view the data on a report, base the report on a query that joins the two tables together on the ID field, enabling you to make the descriptive field available in the report.
Start a new query and add both tables to the grid. If you have relationships set up, there may already be a line between the ID field in both tables. If not, click/drag to create one. Double click on the fields you want displayed so they appear in the grid below. Run the query and see if it displays what you want.
I can get the query to run correctly. However, how do I get it to display on a form or report?
For example, I have a separate, more detailed input form for all company records which should display the Sector / Segment / Subsegment Names for these companies based on what the user selected in the first input form. There is no need for changing these values in this second, more detailed form, and this form should display the Sector / Segment / Subsegment Names for each company in locked form. (The idea is to force users to log-in this information once only using the first input form).
Any suggestions as how best to do this? Not sure if textbox, listbox, etc. is appropriate...
I would just base the report on the query (start with the report wizard). For a form, I'd probably just use a combo box to display the value (leaving the form bound to the main data table). You'd run the risk of creating a read-only query depending on the joins. If that wasn't an issue, you could base the form on the query.
One more thing -- I noticed something weird going on with my cascading combo boxes. The sequence of these boxes is Sector --> Segment --> Subsegment. I noticed that whenever I change the Sector selection for one record, the Segment selection for the other records are affected (they get set to the default "" value). However, when I change the Sector selection of that one record back to the original choice, the Segment selections for the other records also revert to their original value.
I suspect that this has to do with how I'm handling the "After Update" event requery.
Here's my AfterUpdate() code:
Private Sub SectorBox_AfterUpdate()
Me.SegmentBox.Value = ""
Me.SubsegmentBox.Value = ""
Private Sub SegmentBox_AfterUpdate()
Me.SubsegmentBox.Value = ""
What I'm trying to do with the requery is to update the Segment and Subsegment boxes if the user changes the Sector box selection.