Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Dec 2011
    Posts
    25

    Unanswered: Help with Combo Boxes

    I have a subform to choose which employee ( combo box) and you manually enter in their score, and I need a combo box for ResultsModuleID so it shows the only 2 possible Results.ModuleID's for the course. If I set up the combo box like you normally do, (Find the value based on another table) it shows ALL the ResultModulesID, when I only need it to show the 2 per course.

    For example

    EWP Course , the resultsmoduleID are 13,and 14
    ECP Course, the resultsmoduleID are 10, and 12.

    I need to be able to click the combo box in form view for the EWP course and ONLY have the option to select 13 and 14, right now it shows 13, 14, 10, 12. And to not have the resultsmoduleID duplicate when I enter in another employees score
    Last edited by cheeser89; 12-07-11 at 11:23. Reason: left out info

  2. #2
    Join Date
    Oct 2011
    Posts
    71
    chester89, I'm going to attempt to answer your question but it will be alittle vague due to lack of some information.

    For a combo box that you are using a table to populate the list of selections, you can also create a query and that is how you will limit what is shown in your combo box.

    In order to use the query properly you will have to feed into it paramaters telling it what specific resulltsmoduleID to choose. This can be done by passing paramters from controls on your form to your query in the combo box.

    The exact way to setup the query will depend on how your tabels/form is setup.

    Hope that helps!

  3. #3
    Join Date
    Dec 2011
    Posts
    25
    Thanks for your reply!

    So, as of right now, for the combo box field, I have ALL of the information that i need, ex. 1,2,3,4,5, and so on.

    I did some research, aka, google , and I think I could set up a filtered query to perhaps filter out so only the 2 numbers i need show, not the 50 numbers.
    I am just having trouble setting up the query, and what I use TO filter it. ( function?)

    Any help is appreciated!!

    Something along the lines of *show ModuleID, ONLY per course." ?

  4. #4
    Join Date
    Oct 2011
    Posts
    71
    Alot depends on whether you want to use VBA or SQL to write the query. If your not familar with SQL then ACCESS qry maker is handy if you know basic access functions.

    Select ModuleID
    From table_name
    Where course_name = "math101"

    This is the easiest sql statement to write. Though remember this is hard coding the answer. Instead of the "math101" you would need to put a control, whether its a text box or a combo box.

    If you don't want to mess with the SQL easiest way is to have the querey maker do it.

    Bring down the ModuleID field and the course_name field and under and in the criteria section of course_name type ="math101" (or the control name)

    Let me know how that works for you, hope it helps.
    Last edited by ericx1; 12-08-11 at 15:24. Reason: Correct typing mistake

  5. #5
    Join Date
    Dec 2011
    Posts
    25
    Wow! I've been figuring this out all day and I didnt think of that lol! ( can you tell I am VERY new to access) So for the 'Math101'', is there a way to replace that with something that can somehow find the link between the two to only show JUST the CourseTitle's ModuleID's? without parameters, or macros, etc

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    there is nearly alwasy a way to do things in the systems world
    sometimes the cost isn't worth the effort, but usually its "just" gettign a clear ides of what you want to do

    but it does depend on how good the basic design is
    a properly normalised table structure supports most demands palced on it over the following years
    a properly implemnted db likewise.
    however one of the problems of developing int he Access world is that its almost too easy to get something up and running. and as a consequence the design isn't fully though through before implementation starts and changes become expensive.
    Access also doesnt' help by providing the 'lookup wizard' in table design which is a nice idea but prone to problems down the line

    being able to present limited options is realtively easy if your desigfn supports it (ie how does is the data stored such that
    'EWP Course , the resultsmoduleID are 13,and 14
    ECP Course, the resultsmoduleID are 10, and 12.'
    when you detect the course then re repopulate the combo box.

    one thign to bear inmind with that design motif is that if someone changes the course then the reults modules also need to be forced to change. also you need to cater for the option where soemone decides to change the course, then resets it.. you need to be able ot keep the resultsmodules scores.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2011
    Posts
    71
    As normal healdem is absolutely correct. A couple of ideas on how to make everyhing fluid and please correct me if I'm wrong anywhere, as I have only been programming in access for a little over a month. I like to make everything fluid, when I have to make a change, I only have to make it in once place and the software takes care of the rest.

    A control setup on your form and it needs to contain your course name. so the control name is something like box_course.

    As I was talking about earlier, in the query instead of ="math101" it would be =me.box_course

    (me.) just means its that current form incase you didn't know. As I didn't just starting out.

    Anyways, with the understanding of how to use contorol boxes in your query setup things become much easier.

    Hope it helps!
    Last edited by ericx1; 12-08-11 at 16:21. Reason: more info

  8. #8
    Join Date
    Dec 2011
    Posts
    25
    Let me re-phrase!

    Basically, I would need to create a row source query, that displays

    Field-ModuleID
    Table-Module
    Sort-Ascending
    Criteria- ( and somehow say, based on the CourseID OR CourseTitle, ONLY display the TWO modules that are linked the CourseID)

    Does that make sense? I cannot figure out HOW to input the criteria

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    now
    IMHO what is required are the details of your design
    we can't guess what is required.
    at present we don't have a scooby as to how a module relates to course
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Oct 2011
    Posts
    71
    How is completely based on the setup of your database. Since we do not know how it is setup it is very hard to help you with that.

    Asumming:
    There is a primary key for courses [Course_ID]
    There is a primary key for ModuleID [Course_ID] [ModuleID]
    There is a relationship between course.course_ID -> module.course_ID
    The relationship is show all records course and only matching on module.

    Then it would look something like this

    Field: Course_ID
    Table: Course
    Sort: (however)
    Criteria: = [box_CourseID] (This being your control that tells you what course your on)

    Field: ModuleID
    Table: Module
    Sort: (However)
    Criteria: (Empty)


    That is the basics with the assumptions taken into account.
    Hope it Helps!

  11. #11
    Join Date
    Dec 2011
    Posts
    25
    Your assumptions are CORRECT!

    I did everything you mentioned in your past post, and for the Criteria=[box_CourseID], a parameter box comes up . I need this done without manually entering a parameter

  12. #12
    Join Date
    Oct 2011
    Posts
    71
    The reason that is coming up as a paramater box, is because you do not have a control(text box, combo box, list box) on your form named box_CourseID (my naming convention you can use what every one you want).

    Once you have put your control on your form and you set that controls source to course_ID (or what ever that field name is) that it should do the rest for you.

    [box_CourseID] tell the query to look at the current form and find a control named "box_CourseID" and put its value in place of [box_CourseID]. So if the value of box_CourseID was Math101 then the query would read = "Math101"

    Hope it helps

  13. #13
    Join Date
    Dec 2011
    Posts
    25
    ohhh okay! I apologize, I am very new to access

    Okay so Im gonna explain it a little bit further as that did not work, or I am just retarded lol.

    I have a subform called Assignments Results Subform.

    In the subform is the combo box NAME-Results.ModuleID and the CONTROL SOURCE- Results.ModuleID.

    So for the critieria in the Row Source Query should be
    = [Results.ModuleID_CourseID]?

    an error comes up, Invaild Bracketing of Name

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok so what does
    an error comes up, Invaild Bracketing of Name
    suggest to you
    take a step out of code, what does that fragment suggest to you in English?
    having understood that what do you think may be the problem?
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Dec 2011
    Posts
    25
    hmm, well it does say INVAILD BRACKETING, but there are NO invalid brackets, unless it is the (.) in the criteria, which I am thinking that it is and I am trying to fix it

Posting Permissions

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