Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Location
    Spokane, WA, USA
    Posts
    56
    Thanks
    4
    Thanked 2 Times in 2 Posts

    Populating Fields Based Upon Another Field's Content (Access 2010)

    I am building a model train inventory database.

    I have a couple fields that I want to populate based upon the content of other fields.

    The fields involved:

    Field #1: Cat_Code (2 character text code for the item's category
    Field #2: Cat_Description (35 character text field describing the category code in field #1)
    Field #3: Type_Code (10 character text code for the item's type)
    Field #4: Type_Description (58 character text field describing the type code in field #3)

    There is a relationship that a category can have several types:

    Cat_Code Cat_Description Type_Code Type_Description
    ----------- ----------------------- ------------ ------------------
    FA Freight Car; Auto AUTO Auto 90' Tri-level
    XA XA Auto
    XA89 XA Auto89
    FB Freight Car; Box Car BOX 30W Box Car 30' Wood
    BOX 34W Box Car 34' Wood
    BOX 35 XM Box Car35

    I have an input form set up to use a combo box for the Cat_Code. I'd like the combo box to display a 2-column list of BOTH the category code (field #1) and the category description (field #2) and fill in the Cat_Description text box based upon the selected Cat_Code.

    Then I'd like the Type_Code combo box to display a 2-column list of the available types based upon the Cat_Code (field #1).

    And finally have the Type_Description text box filled in based upon the selected Type_Code from field #3.

    I've looked at the Help file ComboBox.BoundColumn Property but am confused.

    I currently have two tables set up - one for the category codes/descriptions and a separate table for the type codes/descriptions.

    If it would make things easier, I could set up one table for all four fields rather than two tables.

    What is the best way to make this happen? I do not know the programming process involved so hopefully this can be accomplished WITHOUT doing some type of coding.
    Last edited by paulp575; 2016-11-19 at 21:56.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    What you are describing sounds to me like what are typically described as Cascading Combo Boxes. Unfortunately, it typically requires some VBA code to make that happen. Here is a link to an article that describes a similar situation - Creating Cascading Combo Boxes and List Boxes on Microsoft Access Forms. It might be easier if you did it with a single combo box that listed all of the available options. Combo Boxes can display a number of fields in addition to the bound field, which you might want to add as a simple autonumber field. That does mean that you would need to enumerate all of the possibilities in the table of Types and Categories.
    Last edited by WendellB; 2016-11-20 at 16:42.
    Wendell

  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Spokane, WA, USA
    Posts
    56
    Thanks
    4
    Thanked 2 Times in 2 Posts
    Thanks for the reply.

    I noticed they have a sample database on that page so will explore it as well.

    The other part (maybe should have listed it separately) is to figure out how to populate field 2 with the text based upon what I enter in field 1; same for fields 4 (filled from what I entered in field 3).

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Relational databases can be tricky things to design, even after you've done many of them. Combo boxes are normally used to display text information that is more descriptive than the info used as the key to identify information in another table. I presume you have a table called "Train Cars" and you are populating information about the individual cars in that table. If that's the case, then you don't need (or want) to store both the Cat_Code and the Cat_Description in that table. The same is true for Type_Code and Type_Description. The idea is not to store the long description so that reduces the size of the individual car records. Tables that store info like Categories and Types are typically referred to as lookup tables.

    Unfortunately, it's not clear to me how you can tell what Type_Codes belong with what Cat_Codes, but hopefully you can define that. (I got quite interested in model trains a good many years ago, but then life got more complicated and I had to give it up, but it still is intriguing.) If you find that challenging as well, you might create a single lookup table that contains all of the legal combinations of categories and types, and just store a single Cat_Code in your cars table. In the long run, that might help to define the logic rules of how categories and types go together.
    Last edited by WendellB; 2016-11-20 at 17:00.
    Wendell

  5. #5
    Star Lounger
    Join Date
    Dec 2009
    Location
    Spokane, WA, USA
    Posts
    56
    Thanks
    4
    Thanked 2 Times in 2 Posts
    You are correct in that I do not store the listed fields in the main table called "My Train Catalog".

    I currently have one lookup table wherein all 4 fields listed in my original post are listed. I could create two separate lookup tables - one lookup table for fields 1 and 2 and a second lookup table for fields 3 and 4.

    As for the relationship between Type_Codes and Cat_Codes - that is kind of tricky. Fields 1 and 2 have a one-to-one relationship as well as fields 3 and 4. But - and this is the tricky part - field 1 has a one-to-many relationship with field 3. I've attached the Excel spreadsheet so everyone can see the relationships.
    Attached Files Attached Files

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    OK, understanding things a bit better, I would add an integer field (like your Seq_No) and make that the foreign key in your "My_Train_Catalog" table. The combo box would have 5 columns, with the first (bound) column bound, so a selection could be made by typing the Cat_Code into the combo box. This is the single table approach and avoids the issues with trying to do cascading combo boxes. To build the "lookup" table, you can simply copy down in Excel the Cat+Code and Cat_Description columns using the Fill Down feature, and then import the entire worksheet into Access. It appears that a given Type_Code can only belong to one Cat_Code, so if you were to split it, you would have to add the Cat_Code field to the Types table - and not much is saved by doing that. Hope this helps (and makes sense).
    Wendell

  7. #7
    Star Lounger
    Join Date
    Dec 2009
    Location
    Spokane, WA, USA
    Posts
    56
    Thanks
    4
    Thanked 2 Times in 2 Posts
    Thanks to all who replied - especially WendellB, but unfortunately I am still confused.

    So . . .

    Change of Direction/Plan

    Since I am the only person who will be using this database, I don't need to populate fields #2 thru 4 based upon the code I enter in field #1 (Cat_Code). Besides, I'm entering the data from a spreadsheet that already contains the Cat_Code (Field #1) and Type_Code (field #3).

    So I'm going to separate the 4 fields into 2 groups.

    How do I set up the input form so when I am at the Cat_Code (field #1) field, the description of that code will be automatically entered into field #2 (Cat_Description)? I've looked at the help file's ComboBox.BoundColumn Property instructions, but they are not clear.

    Thanks

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    First, you need to determine what you plan to store in your "Inventory" table - I chose that name since "Rolling_Stock" wouldn't include things like track and scenery. I don't think you want to store the Cat_Code and Type_Code as separate fields, and you also don't want to store the Cat_Description and Type_Description fields in that table. It would make the table larger than necessary, and give the user the chance to make errors, especially if they (you) are editing at the table level. As previously noted, I would store an integer value that identifies the unique combination of a Cat_Code and Type_Code. (That could well be the counter number in your spreadsheet, but you could use an autonumber in your lookup table.)

    To use a ComboBox in that scenario, you would use the lookup table as your rowsource, and the integer as your datasource - it would be a bound combobox since your Inventory table is the recordsource for the input form. You would then hide the combobox first column by making the column width of the first column equal to zero - the other 4 columns would be set the appropriate width to display the contents, or most of the contents of the lookup table column. Then when you type the first few characters of the Cat_Code into the combobox, it jumps to that point in the table where the various Type_Codes are displayed.

    It's not clear to me what value the "Code" fields add, as you could achieve the same result by just using the "Description" fields in the lookup table, but you undoubtedly have a better feel for the value they contribute. If I get a bit of spare time in the next day or so, I'll try to construct a simple demo of what I've just described.
    Wendell

  9. #9
    Star Lounger
    Join Date
    Dec 2009
    Location
    Spokane, WA, USA
    Posts
    56
    Thanks
    4
    Thanked 2 Times in 2 Posts
    The inventory contains ALL items in my collection including transformers, scenery, track, switches.

    Currently, I have the Category and Type table stored external to the database.
    The spreadsheet I'm using contains on the Category_Code and the Type_Code.
    When I input an item, I'm also inputting the Cat_Description and Type_Description.
    Since I've been working on this, I've decided I really don't need the descriptions in each item's record - although for now they are part of an item's record.

    The only place I'm using them is in the grouping(?) headers and footers of some of the reports.

    Hope this helps. And thanks for your help so far.

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Well, for what it's worth, I created the attached simple database (in zip format) with a single form that has a combo box that uses your Category and Type table, more or less. I did fill in the Category columns where they were blank, and I also eliminated the rows that were other than Rolling Stock. (The Excel fill-down feature made that pretty easy.) I also included a combo box for the gauge under the assumption some people might have multiple gauge layouts, and added a few fields that seemed like they might be useful. I also added a field that could be used to link to a photo of each car or locomotive just for grins. This is a .mdb format database, so storing photos in the database is generally a bad idea. Instead, you store a link, and use that to update the image control. The .accdb format does have a reasonably efficient way of storing photots.

    Having done all that, the value of a combo box is two-fold in data entry. First you can restrict data entries to a unique set of values, and second you can type just a few characters to get to the selection you want to make. In your situation, the text makes it difficult to find the entry you want to make, so I'm not sure it is of great value. The other use of combo boxes is to search for a specific record, and that really only works on a single column (the first visible one). Hope this helps.
    Attached Files Attached Files
    Wendell

  11. #11
    Star Lounger
    Join Date
    Dec 2009
    Location
    Spokane, WA, USA
    Posts
    56
    Thanks
    4
    Thanked 2 Times in 2 Posts
    Thanks WendellB for your work, but I'm still confused. Don't dabble much in databases so when I do I try to keep them simple.

    I'm attaching a copy of my database with many of the records removed, but hopefully just enough so you can see where I'm headed.

    As mentioned above, I'm the only one inputting data. I have a spreadsheet with most of the data therein, but need to be able to sort it.

    I don't need to have one table for all four previously mentioned columns.

    What I'd like is when I enter the input field for Cat_Code, the lookup codes display. When I enter a code, it automatically enters data in the Cat_Description field. (Right now I'm copying and pasting from the table.) When I enter the Type field, the same actions would happen.

    NOTE: While the tables Category and Type are contained within the database, they are both the same 5 column tables as I wasn't sure where this was going.

    Hope this helps,

    Paul
    Attached Files Attached Files

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
  •