Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Location
    Spokane, WA, USA
    Posts
    82
    Thanks
    16
    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 20:56.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,641
    Thanks
    3
    Thanked 65 Times in 64 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 15:42.
    Wendell

  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Spokane, WA, USA
    Posts
    82
    Thanks
    16
    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,641
    Thanks
    3
    Thanked 65 Times in 64 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 16:00.
    Wendell

  5. #5
    Star Lounger
    Join Date
    Dec 2009
    Location
    Spokane, WA, USA
    Posts
    82
    Thanks
    16
    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,641
    Thanks
    3
    Thanked 65 Times in 64 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
    82
    Thanks
    16
    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,641
    Thanks
    3
    Thanked 65 Times in 64 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
    82
    Thanks
    16
    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,641
    Thanks
    3
    Thanked 65 Times in 64 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
    82
    Thanks
    16
    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

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,641
    Thanks
    3
    Thanked 65 Times in 64 Posts
    Unfortunately, I don't see any good way of doing precisely what you want without resorting to cascading combo boxes, and that involves some Visual Basic for Applications. If I were to design this, I would begin by "normalizing" the table design. See MS Normalization Basics and Wikipedia Normalization for an introduction to the topic. I would probably end up with three tables as you did, but the Category table would have only two fields, and they would be the Code and Description. You would end up with 21 records in that table - you can filter for does not equal blank to see that. The Types table would have three fields, the Category Code, the Type Code, and the Type Description. That table would have 616 records based on your current My Train Catalog Table - Type and I would not store the descriptions for either in your My Train Catalog table, just the Codes. Then each time you added a new record or editing an existing record, the AfterUpdate event would trigger code that filtered the combo box for Type to restrict it's recordset to records to just those that match the Category Code.

    A couple of other observations after looking at your 8-record database: It appears there are several other opportunities for lookup tables, such as Roads, Status, Configuration, and Manufacturer. Also, I would add a primary key to the main table - as a general rule of thumb all tables should have a primary key, as it makes queries and reports run faster. If you are using Combo Boxes to set the values in the table, there is no need to store both values in your main table, which would make your database a fair bit smaller. Your database is currently 5MB with only 8 records, and with two attachment types, it will grow rapidly. Do run the Compact and Repair function regularly to keep bloat in control. Access databases have a limit of 2GB in size, so you would run out of space at about 3000 records assuming the 8 records are typical. Finally, I would be cautious using field names that contain special symbols - you have a couple that start with % and in some Access databases that is a reserved symbol. Hope this helps clarify the situation, but feel free to post back with further questions.
    Wendell

  13. #13
    Star Lounger
    Join Date
    Dec 2009
    Location
    Spokane, WA, USA
    Posts
    82
    Thanks
    16
    Thanked 2 Times in 2 Posts

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

    Thanks for taking time to work on this, but I'm still somewhat confused.

    I looked at your form frmRollingStock and don't see how the txtDescription gets automatically entered when you select the intCatType. Looks like it's manually entered.

    Regardless, I've done some more work and got something really mixed up because now when I attempt to enter a code in the Cat_Code field on the Input Form, I get an error message, but can ignore it and continue.

    I've combined the category and type codes into one table and have a sequence number as the last column.

    Even though I'm the only one entering data, I'd like to make some of the data fill in automatically when I enter something in a code field and the description gets automatically filled.

    I've created a Railroads table to the database.

    Can you show me what entries I need to make in the "My Train Catalog" table (column bound, for example) and also the Input Form for the Railroad Name Code and have the Railroad Name Description field automatically filled? I think if I fully understand that process, I may be able to reduce a lot of data input (except for the category and type fields which I'll get to those later).

    Thanks
    Attached Files Attached Files

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,606
    Thanks
    0
    Thanked 32 Times in 32 Posts
    The reason your Cat_Code and Type combo boxes show an error is that the tables those controls use are missing from that database, those tables are [My Train Catalog Table - Category] and [My Train Catalog Table - Type]

  15. #15
    Star Lounger
    Join Date
    Dec 2009
    Location
    Spokane, WA, USA
    Posts
    82
    Thanks
    16
    Thanked 2 Times in 2 Posts
    Yup - fixed!

Page 1 of 2 12 LastLast

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
  •