Results 1 to 5 of 5

Combobox Query - in subform

  1. #1
    ngonzalz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    5

    Question Combobox Query - in subform

    Hi all, first time poster bear with me..

    DETAILS:
    tblShows


    ShowID(key)
    Show Name
    ...etc
    tblBooths
    BoothID(key)
    ShowID
    Booth Name (calculated short text)
    ...etc
    tblShowServices
    RefID(key)
    ShowID
    BoothName
    ...etc
    SPLITFORM MAIN - fmShows SUB - sbfmBooths
    SUB - sbfmShowServices


    Each Show has multiple Booths
    Each Show has multiple Show Services, with multiple/duplicate Booths assigned

    In a Show Services subform (datasheet) I have a combobox to select the Booth Name of which Show Service belongs to.

    PROBLEM:
    I cannot seem to restrict the Booth Name by Show ID using the query. (It is currently showing all Booths in the list)

    Here is the last query I ended on:

    Code:
    SELECT [Show Services].[Show ID], Booths.[Booth Name]
    FROM Booths INNER JOIN [Show Services] ON Booths.[Show ID] = [Show Services].[Show ID]
    WHERE ((([Show Services].[Show ID])=[Booths].[Show ID]))
    ORDER BY Booths.[Booth Name];

    Thanks for your time and help!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,113
    Welcome to the forum and thanks for using code tags!
    Not sure which table is what here - kinda confusing
    I have a combobox to select the Booth Name of which Show Service belongs to.
    Open the query in design view, change the joint to outer (either option 2 or 3). Can't say which for sure, but probably would end up being a left join. See which version gives you the desired results. If neither, there could be other issues (a calculated short text name value?).
    - "doesn't work" is no help. What's happening? Error messages? Where??
    - Use code tags for code/sql. Implement changes in copies of your database.
    Irregardless, ain't no such word as "reoccur".

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,356
    This is called cascading or dependent combobox.

    Don't see need to join tables for combobox RowSource.

    SELECT [Booth ID], [Booth Name] FROM Booths WHERE [Show ID] = [Show ID];

    Then probably need code to requery combobox. Suggest the combobox GotFocus event.

    Advise not to use spaces nor punctuation/special characters in naming convention.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,113
    Well, if there's more than one combo involved, I sure missed that. Have re-read and still don't see how there's any cascading going on.
    Do see how I misread the requirement, i.e. there are too many records returned vs not enough, hence my mistake on suggesting the join. Now thinking that the issue is that the combo criteria ought to be including a parameter, such as another form control reference. The way the original sql reads I would expect all booths to be returned because there's no filter involved
    e.g.
    [Show Services].[Show ID])= Forms!frmMyForm.myControlNameThatHasNumericData))

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,356
    Right - the [Show ID] parameter would likely be a combobox for selecting [Show ID] on a form bound to table [Show Services].

    I find it is not necessary to use full form name path when referencing controls on same form when the SQL is in the combobox RowSource as opposed to referencing a query object. I would name the ShowID combobox cbxShow then the SQL would be:

    SELECT [Booth ID], [Booth Name] FROM Booths WHERE [Show ID] = [cbxShow];


    Be aware cascading combobox will not play nice with continuous or datasheet form.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Updating a Subform Query using a ComboBox
    By thexxvi in forum Access
    Replies: 2
    Last Post: 06-04-2015, 10:30 AM
  2. Replies: 1
    Last Post: 01-16-2015, 08:28 AM
  3. Replies: 1
    Last Post: 12-10-2013, 02:15 PM
  4. Replies: 1
    Last Post: 09-06-2011, 12:47 PM
  5. Replies: 0
    Last Post: 08-24-2010, 05:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums