Results 1 to 4 of 4

Thread: Combo Box Query

  1. #1
    Join Date
    Apr 2003
    Posts
    9

    Question Unanswered: Combo Box Query

    Brief abstract explanation of my problem:

    In table #3 (field #3) I have a combo box that I ONLY want to show data from table #2 (field #2) that equals data entered/selected earlier in same table #3 (field #2), which contains data from table #1 (field #2). Confused? Me too!


    Detailed explanation, my tables, fields, properties, sample data, and relationships:

    tblPublicationTypes (#1)
    numPublicationTypeID AutoNumber
    txtPublicationType Text

    Sample Data:
    1, Book
    2, Magazine

    tblPublicationTitles (#2)
    numPublicationTitleID AutoNumber
    numPublicationTypeFKID Number
    txtPublicationTitle Text

    Sample Data:
    1, 1, Book Title 1
    2, 1, Book Title 2
    3, 1, Book Title 3
    4, 2, Magazine Title 1
    5, 2, Magazine Title 2
    6, 2, Magazine Title 3

    tblPublications (#3)
    numPublicationID AutoNumber
    numPublicationTypeFKID Number
    Field Properties set so I can chose an existing publication type by name
    Combo Box
    Table/Query
    SELECT DISTINCTROW tblPublicationTypes.* FROM tblPublicationTypes;
    Bound Column = 1
    Column Count = 2
    Column Widths = 0;2
    numPublicationTitleFKID Number
    Field Properties set so I can chose an existing publication title by name
    Combo Box
    Table/Query
    SELECT DISTINCTROW tblPublicationTitles.* FROM tblPublicationTitles;
    Bound Column = 1
    Column Count = 2
    Column Widths = 0;2

    Relationships:
    tblPublicationTypes!numPublicationTypeID - One-To-Many - tblPublicationTitles! numPublicationTypeFKID

    All the above works fine. However as I would really love to limit the choices in combo box #2, field #3, table #3 to ONLY show titles that matches type selected in previous field same table.

    I tried the following, which does not work, it just prompt me for a value when I open the table for data input:
    SELECT DISTINCTROW tblPublicationTitles.* FROM tblPublicationTitles WHERE ((([tblPublicationTitles].[numPublicationTypeFKID])=[numPublicationFKID]));

    I appreciate any solution to this problem, thanks.

  2. #2
    Join Date
    Oct 2002
    Location
    Greensboro, NC
    Posts
    50

    Re: Combo Box Query

    do you have a sample form or something to help me understand what you're trying to accomplish?

    QUOTE]Originally posted by DKDiveDude
    Brief abstract explanation of my problem:

    In table #3 (field #3) I have a combo box that I ONLY want to show data from table #2 (field #2) that equals data entered/selected earlier in same table #3 (field #2), which contains data from table #1 (field #2). Confused? Me too!


    Detailed explanation, my tables, fields, properties, sample data, and relationships:

    tblPublicationTypes (#1)
    numPublicationTypeID AutoNumber
    txtPublicationType Text

    Sample Data:
    1, Book
    2, Magazine

    tblPublicationTitles (#2)
    numPublicationTitleID AutoNumber
    numPublicationTypeFKID Number
    txtPublicationTitle Text

    Sample Data:
    1, 1, Book Title 1
    2, 1, Book Title 2
    3, 1, Book Title 3
    4, 2, Magazine Title 1
    5, 2, Magazine Title 2
    6, 2, Magazine Title 3

    tblPublications (#3)
    numPublicationID AutoNumber
    numPublicationTypeFKID Number
    Field Properties set so I can chose an existing publication type by name
    Combo Box
    Table/Query
    SELECT DISTINCTROW tblPublicationTypes.* FROM tblPublicationTypes;
    Bound Column = 1
    Column Count = 2
    Column Widths = 0;2
    numPublicationTitleFKID Number
    Field Properties set so I can chose an existing publication title by name
    Combo Box
    Table/Query
    SELECT DISTINCTROW tblPublicationTitles.* FROM tblPublicationTitles;
    Bound Column = 1
    Column Count = 2
    Column Widths = 0;2

    Relationships:
    tblPublicationTypes!numPublicationTypeID - One-To-Many - tblPublicationTitles! numPublicationTypeFKID

    All the above works fine. However as I would really love to limit the choices in combo box #2, field #3, table #3 to ONLY show titles that matches type selected in previous field same table.

    I tried the following, which does not work, it just prompt me for a value when I open the table for data input:
    SELECT DISTINCTROW tblPublicationTitles.* FROM tblPublicationTitles WHERE ((([tblPublicationTitles].[numPublicationTypeFKID])=[numPublicationFKID]));

    I appreciate any solution to this problem, thanks.
    [/QUOTE]

  3. #3
    Join Date
    Apr 2003
    Posts
    9

    No form yet - Just a table

    Thanks for your fast reply.

    There is now form as of now, only the tables described.

  4. #4
    Join Date
    Apr 2003
    Posts
    9

    Solution

    Ok, the following works on a form:

    SELECT tblPublicationTitles.* FROM tblPublicationTitles WHERE [tblPublicationTitles].[numPublicationTypeFKID]=[Forms]![frmPublicationsTEST]![numPublicationTypeFKID];

    But only if I refresh the form data like this:

    Private Sub Form_Current()
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
    End Sub

    Private Sub numPublicationTitleFKID_Enter()
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
    End Sub

    Thanks for all the advice...

Posting Permissions

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