Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2010
    Posts
    7

    Unanswered: Cascading combo boxes, stored field values

    Hi,

    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?

    Many thanks,

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  3. #3
    Join Date
    Aug 2010
    Posts
    7
    Can you be a bit more specific with the query criteria? How do I join the two tables together on the ID field?

    Thanks -- I am a novice when it comes to Access so definitely appreciate the patience.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  5. #5
    Join Date
    Aug 2010
    Posts
    7
    Thanks!

    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...

    Thanks!

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  7. #7
    Join Date
    Aug 2010
    Posts
    7
    Paul,

    Many thanks for all your help.

    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.Requery
    Me.SegmentBox.Value = ""
    Me.SubsegmentBox.Requery
    Me.SubsegmentBox.Value = ""
    End Sub

    Private Sub SegmentBox_AfterUpdate()
    Me.SubsegmentBox.Requery
    Me.SubsegmentBox.Value = ""
    End Sub

    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.

    Any suggestions?

    Thanks,
    Jerome

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Is the form in datasheet or continuous view? I've never tried it, but my understanding is that trying to do cascading combos on a continuous/datasheet form is problematic.
    Paul

  9. #9
    Join Date
    Aug 2010
    Posts
    7
    Currently using continuous form view. However, seems to be giving me problems even when I switch to Single From view.

    -Jerome

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •