Results 1 to 10 of 10
  1. #1
    Join Date
    May 2013
    Posts
    6

    Unanswered: Issue with ComboBox Correctly Filtering Subform

    Hello Everyone at dBforums,

    I am trying to put something together using Microsoft Access 2013 and have run into a bit of a stumbling block trying to get my form to correctly filter and list the information that I want it to. I am certain this question and has been answered but I am unsure on what I am doing wrong and am fairly new to Access. This is a self teaching projecto so advice on how to better structure the database if I messed that up would be much appreciated.

    What I am trying to setup is a printer cartridge database where I can using a combobox select the printer manufacturer and from that an appropriate model. That part is working. The issue I am having now is getting it to filter out a subform which will list for me the cartridge names, colors, and leave me with an editable field for the inventory so I can change it as the numbers shift.

    Ideally what I should see on the form is the two comboboxes and below that I want the subform to list the Cartridge Names, Cartridge Colors, and current inventory. I am not completely sure where I messed up with the table design but any guidance you fellows can offer would be much appreciated. I would like to drop the Manufature Name and Model Name from the subform if possible also but I have been including them because I thought I needed them for the lookup filter.

    This project has two purposes. The first is to better my understanding of Access and the second is to put together a database system which I can use to better track the inventory supplies so I can reorder when it gets low.

    I look forward to hearing back from you. If you have any questions please let me know.

    Temorel
    Attached Thumbnails Attached Thumbnails Form Image Goals.png  
    Attached Files Attached Files
    Last edited by Temorel; 05-02-13 at 17:39.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Have you linked the sub form to the main form on the manufacturer and model name fields? Doing this will do all the heavy lifting for you.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    May 2013
    Posts
    6
    Hello Weejas,

    Thanks for the response. I have a link in the relationships tab between the different tables but I think I may have the dependencies backwards for the forms. It says my form which contains the comboboxes for selecting is dependent on the form that contains my tables. I found I was originally using the wrong subform and got something closer to what I was looking for in design implementation but maybe I messed up the creation order.

    I have uploaded a new version of the file I put together while working on this can you tell me what you think?
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Wow, okay.

    Your main problem was not invoking referential integrity. This is what powers relational databases, which is what Access wants to be when it grows up. The idea is that you store something once, and refer to it whenever it's needed elsewhere. It means that you don't need to store the name of the manufacturers more than once, for example.

    I've created a new version of the database, with a new set of tables and forms alongside yours. I've also started populating the new tables to give you an idea of how it's supposed to work. Have a play and take it apart.
    Attached Files Attached Files
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    May 2013
    Posts
    6
    Hello Weejas,

    I wanted to write back and say thanks for the example but i have been so busy I have not had a chance. Sorry about the delays. I do have a few questions after kicking the forms you put together. The first is it possible to use drop boxes to create the filter for the subform rather than using the buttons on the bottom to navigate?

    The second is that when I tried to rebuild the cartridge subform you had I always end out getting an error when I try to have it reference the color tables for resolution of the color id to the color name. I was wondering how did you build that form? I thought it was made first using the quick datasheet form but found that layed out similar but grouped the fields where yours were individually assigned. Seeing this I tried manually creating a form and linking it to the table but it would still not successfully resolve the ColorID field to the color text using the CID to translate it. I must be missing a step but am not sure what it is.

    Thanks so much for pointing me along the path, I am very grateful for the chance to study your forms, I am just messing something up along the way for creating these the way I want.

  6. #6
    Join Date
    May 2013
    Posts
    6
    Well scratch the second question I figured out what I was doing. I had set the wrong reference field. I was referencing column 1 instead of 2. Was ondering why I was just getting the numbers instead of the colors. That narrows it down to just needing to figure out how to use a pair of comboboxes to filter out the cartridge table.

  7. #7
    Join Date
    May 2013
    Posts
    6
    Hello Weejas,

    I have been reworking the file and I have attached an updated copy of my work. I know I am starting to get close to what I am looking to accomplish but don't know how to get the final step yet. I have attached a view of what the form now looks like and the only thing I think I need to do now is make it so that the filters for the printer are selected by the printer combobox rather than the toolbox at the bottom. Any advise? In the iamge I posed it shows what it looks like and the order I want the system to look things up. It should start with a selection of the Manufacturer, then a selection of the printer model, and that should populate the list of cartridges for that model and the inventory for them.

    Thanks again for your help it has pointed along the path and I am learning a lot from this.
    Attached Thumbnails Attached Thumbnails Updated Design.jpg  
    Attached Files Attached Files
    Last edited by Temorel; 05-14-13 at 16:56.

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Glad to hear that things are moving along.

    Once you start using combo boxes to filter form contents, you're moving into the realm of using VBA to update the form, and possibly amend record sources. I can help if you want to go this path. If you want to read up a bit more on the subject, search this forum (or the rest of the Internet) for "cascading combo boxes".
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  9. #9
    Join Date
    May 2013
    Posts
    6
    Hello Weejas,

    First off I know its been a while since I last responded but I wanted to say, thanks for the help. I was able to get what I wanted for the checker form and I am using your design for now for updating the database to add new devices into the lists.

    I do have a question which is related to a navigational forms. I tried plugging the two master forms into the navigational form but it breaks the FormCartridgeFilter form's second dropdown menu and I am not sure how to fix it.

    The second dropdown which filters a query table uses the below string in the Row Source field.

    SELECT TablePrinter.Model, TablePrinter.PID FROM TablePrinter WHERE (((TablePrinter.ManID)=Forms![FormCartridgeFilter]!ManuCombo));

    When I try to use the dropdown on the Master Form I get the below error when I click on it. I know this is related to the reference to the form page but I am not certain which form I am supposed to point it finds the ManuCombo dropbox on that form.

    Forms!FormCartridgeFilter!ManuCombo


    Also do you have any good advise on how to build a data entry page so I can make it easy to add Manufacturers/Printers/Cartridges that does not involve viewing all of the other fields? I ask because I want to try and protect the fields that have been entered from being messed with.

    Thanks for the help once again I have made much more progess than I thought I would in a much shorter time frame than I expected.

    Temorel
    Attached Files Attached Files
    Last edited by Temorel; 05-30-13 at 19:30. Reason: Uploading attachment

  10. #10
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    As soon as you start using forms as sub-forms, you are liable to break any code that relies on form names. If you really need to have a main form with everything else hanging off it, you will probably need to either rewrite the RowSource property of dependant combo boxes, or use VBA to customise it on the fly as the forms are in use. If you search this forum for "Cascading combo boxes", you will find a number of hits (the more recent of them advising people to search this forum...) with tips, instructions and links on how to accomplish this.

    With regard to maintaining the lists of manufacturers, printers and colours, this would be either metadata or dimension maintenance (depending on your background). The easiest thing is to use the form wizard to create a simple form for each table, and when it's finished, amend the properties of the ID field so that it is not editable (Locked = True, Enabled = False). Keep these forms individual, and create a switchboard form to open them. Again, if you search either this forum or Google for "Access switchboard forms", you'll find more than enough information to get you started.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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