Results 1 to 11 of 11
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Subform based upon textbox (Access 2003)

    I have a main form/subform scenario. The form is frm Organisations.

    I have a textbox on the main form that is populated with a number that is used for a combo box in the subform so it only shows those combobox entries that are the same as the textbox. After adding a couple of subform records all is well.

    Then I change the value in the textbox to add another couple of subform records. As soon as I go to add a record in the subform any records in the subform that are not the same as the textbox shows a clear combobox.

    When I clear the textbox I get to see all the records in the subform when I click in the combobox of one of the blank records.

    The SELECT statement of the subform's combobox is:
    SELECT [tbl Categories].CatID, [UnitName] & " " & [CatName] AS CategoryName
    FROM [tbl Units] INNER JOIN [tbl Categories] ON [tbl Units].UnitID = [tbl Categories].UnitID
    WHERE ((([tbl Categories].UnitID)=[forms]![frm Organisations]![textUnitID])
    AND (([forms]![frm Organisations]![textUnitID]) Is Not Null) AND (([tbl Categories].OptionO)=True)) OR ((([forms]![frm Organisations]![textUnitID]) Is Null) AND (([tbl Categories].OptionO)=True));

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform based upon textbox (Access 2003)

    You can place a text box over the combo box that covers it completely except for the dropdown arrow. You must change the record source of the subform to a query that includes the text to be displayed. See attached version.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Subform based upon textbox (Access 2003)

    Here is a slightly modified version.

    I don't like the way you see an empty combo if you click in one which is not in the current set.

    So I have added the current value to the query, then requery in the on current event of the subform.
    Regards
    John



  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Subform based upon textbox (Access 2003)

    <P ID="edit" class=small>(Edited by patt on 14-May-06 09:25. Added comment)</P>Thanks Hans
    That works well.

    I had forgotten that technique.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Subform based upon textbox (Access 2003)

    Thanks John,
    If you are describing my database, then fair enough.

    However, if you are describing Hans solution then your code does not need to applied. I don't think you need the OnCurrent event code in the subform at all.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Subform based upon textbox (Access 2003)

    I have not seen yours, only the one Hans posted.

    The one I posted needs the oncurrent event because the row source of the combo box is different for every row of the subform.
    In each case it includes the value currently in the combo as an element in the row source, so that you never see blanks.
    Regards
    John



  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Subform based upon textbox (Access 2003)

    But the text box is the one on top of the combobox, so that users don't see the combobox contents only the text box contents.

    I must not be understanding something here if that's not the case.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform based upon textbox (Access 2003)

    In the database I attached, the following problem occurs:
    - You select a unit in the combo box at the top of the main form, let's say Key Categories.
    - You click on the dropdown arrow of the CatID combo box on the subform in a record whose value belongs to a different unit, for example to Community Advocacy.
    - This will bring the combo box to the front, and since the value's unit is different from the unit in ComboUnit, the combo box and its dropdown list are empty.
    John's modification takes care of this problem by adding the current value to the row source of the combo box.

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Subform based upon textbox (Access 2003)

    Thanks Hans,
    I missed that subtlety in John's reply.

    Thanks again John

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Subform based upon textbox (Access 2003)

    Sorry that my original post did not explain my point for fully. I was just about the write out a proper explanation, but I find that Hans has done it for me. thanks.

    I usually take this a step further (and this also requires a requery in the on current event).

    I am not sure in your case, but often you do not want the same item to be chosen more than once. Now you can catch this by using an index on two fields, but I prefer to exclude from the values in the combo box any that have already been chosen so I add a subquery to the query providing the row source, with a 'not in' clause.

    Have a look at this version.
    Regards
    John



  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Subform based upon textbox (Access 2003)

    <P ID="edit" class=small>(Edited by patt on 14-May-06 14:40. added extra comment re 'not in')</P>I have already introduced a unique index to trap those.

    I will look at your example for the 'not in' condition. That's a good idea, thank you.

    I also had to change the solution Hans sent in that when saving the subform record I needed to hit the 'enter' key twice.

Posting Permissions

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