Results 1 to 6 of 6
  1. #1
    Join Date
    May 2011
    Posts
    17

    Unanswered: Problem with show/hide subform

    I have a combo box in a main form where you choose which project a sample belongs to. Based on the choices one makes in the combo box, it will open different subforms, but the same subform opens whatever choices I make in the combo box. Im using the show / hide method in the combo box.

    Why do I get the same subform up regardless what I select in the combo box. I send with the database so you can see what I have done
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I don't run Access 2007/2010 and so can't open your file.

    Can you post the code you're using, in order to show/hide your various subforms?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK there's various things going on here
    frst off you need to limit the possible options to the presupplied list ONLY(set the properties of the combo 'limit to list' to true or yes. this means a user cannot add new values to the combo and break your code

    the bit of code that selects the form should be triggered in the "on click" event,
    not the after update event. the after update triggers after an update is completed, whereas I'm guessing you want to display the sub form when you are still doing data capture. you may need to add the call to select the switch based on the key up, lost focus events aswell. if so then move the sub form selection code to a sub routine and run it once there.

    I don't see where you are getting (or think where you are getting) the numeric value used in the combo. the values in the combo are string so you should be be testing for ="BRAIN" or "CTRL" or "TOP" not 1 and an else condition. I'd suggest that you alwasy have a definitive else statement not a catchall statement
    so in this case
    Code:
    Select Case Project.Value
    Case Is = "BRAIN": SetformVisibility (1)
    Case Is = "CTRL": SetformVisibility (2)
    Case Is = "TOP": SetformVisibility (3)
    Case Else: MsgBox "Project Value is borked" & vbCrLf & "Value is:" & Project.Value & vbCrLf & "I don't have a clue what to do with that value", vbCritical
    End Select
    placing a case else statement followed by something meaningful to you means that you don't chase your tail looking for exotic errors when the problem, is fairly basic. in this case you code tests for a numeric value, and the combo box returns a string value. you simple if x = 1 statement is always false so the else statement is executed. you spend hours blaming access becuase it doesn't do what you expect, yet Access sits there dumbly and does what you've told it to do.

    although you are hiding the sub forms in the "on current" record, I'd suggest you move that code that hides / shows the forms to a sub, called say SetformVisibility
    replace your code in on current with SetformVisibility(0)
    Code:
    Private Sub SetformVisibility(intItem As Integer)
    'first off hide all sub forms. any call will always do the following statements (upto the select case)
    'suggest you use SetformVisibility(0) to hide
    sub_BRAIN.Visible = False
    sub_CTRL.Visible = False
    Select Case intItem
    Case 1: sub_BRAIN.Visible = True
    Case 2: sub_CTRL.Visible = True
    Case 3: ' preumably sub_top.Visible = True
    End Select
    you can set a numeric value if you wish. I'm guessing at your stage of development knowledge that may be better when setting up a combo using the combo wizard.

    hard coding values in a combo box isn't neccesarily a smart call, unless you know these are once for a lifetime values. Id probably normally pull these values from a table which also pulled in the correct subform to display.. but thats my approach not neccesarily the right approach.

    FWIW (for what its worth), I still think your design is flawed (the give away is when you start seeing to tables with the same columns (tbl-RNA & tbl_WholeBlood) and or columns with the same names separated by an index (vol1,vol2...volx and matrix_code1, matrix_code2...matrix_codex). number of tubes suggests to me the number of samples taken, and presumably will tie in with the vol1...volx in plasma.

    finally I think you will benefit from learning about debugging in Access. one of the pleasures of the Access world is that its always had a very very powerful and flexible debugger built in to the IDE. that means you can put breakpoints on the code and examine what is actually going on in the code as opposed to what you think
    ms access debugging - Google Search
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by healdem View Post
    the bit of code that selects the form should be triggered in the "on click" event, not the after update event. the after update triggers after an update is completed
    Actually, most developers do, indeed, place this kind of code in the Combobox AfterUpdate event, which triggers when a selection is made. Unless you were speaking of not placing the code in the Form_AfterUpdate event, which only triggers when the entire record is updated and would, of course, be inappropriate for this purpose.

    As I said, I can't open the file.

    Linq ;0)>
    Last edited by Missinglinq; 06-26-11 at 14:12.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    my understanding of the db is that the sub form must have data so there is a risk that by putting the view subform stuff into the after update that a parent record may not have child record(s). granted you could put some code that makes certain a user cannot navigate off the parent record until there is some data in one of the child records. but I'd argue thats validation and that should happen in the before update event and the UI should stop saving data if there is no matching child record(s). the aim being to design out data integrity issues, so a user can only save a parent record if there is also a child record (assuming that is the business logic)

    ultimately its a design stylistc choice and I suspect the design chose for this app is going to turn out to be very very messy.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In such situations I usually use a single subform/subreport control on the parent form and assign its ObjetSource property to the name of one of the subforms I want to display at run time, according to the context:
    Code:
    Select Case Context
        Case 1
            Me.Child_SubForm.ObjetSource = "SubForm_1"
        Case 2
            Me.Child_SubForm.ObjetSource = "SubForm_2"
        Case ...
    End Select
    Have a nice day!

Posting Permissions

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