Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    New York, New York, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Statement (Access 97)

    The following SQL statement is supposed to take the value selected in one combo box and use it to populate a second combo box:

    SELECT DISTINCT qrySongWriter.WriterID, qrySongWriter.WriterName
    FROM qrySongWriter
    WHERE qrySongWriter.SongID
    In (SELECT qrySongWriter.SongID
    FROM qrySongWriter
    WHERE qrySongWriter.WriterID = [Forms]![frmQuerySongsfor2Writers]![cboWriter1].[Column(0)])
    ORDER BY qrySongWriter.WriterName;

    I set the Row Source property of the second combo box to this SQL statement, but when I run it, the second combo box is empty.

    If I replace the WHERE clause with:

    WHERE qrySongWriter.WriterID = 29

    the second combo box has the correct entries. So the error must be in the expression:

    [Forms]![frmQuerySongsfor2Writers]![cboWriter1].[Column(0)]

    I built as much of this expression as I could with the Expression Builder, so I'm sure there is no spelling error in the name of the form or the combo box.

    Can anyone tell me where the error is?

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: SQL Statement (Access 97)

    Assuming the syntax of your statement is correct, are you sure you want Column(0) instead of Column(1). Typically, if using the expression builder, column (0) is hidden and may contain an id or other value. Try using column(1).

    HTH
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    New York, New York, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (Access 97)

    Thanks for the suggestion, but it didn't work. In any event, I need the WriterID which is in the first column.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (Access 97)

    When I needed a statement to refer to a specific column in my combo box, I remember I had a hard time getting the code to include the column number. I read somewhere to wrap it in an eval statement like the following and when I did that it worked like I wanted it to.

    dim strCriteria as string
    strCriteria = Eval([Forms]![frmEntryForm]![cmbPeopleLookup].Column(6))
    DoCmd.FindRecord strCriteria, , True, , True
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: SQL Statement (Access 97)

    Two other things to check.

    1 - ensure the form you are referring to is open.
    2 - take .[column(0)] out of the brackets, use .column(0)
    Regards,

    Gary
    (It's been a while!)

  6. #6
    Star Lounger
    Join Date
    Apr 2002
    Location
    New York, New York, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (Access 97)

    Thanks for the suggestion to use an Eval function. I tried it, but the second combo box is still empty.

  7. #7
    Star Lounger
    Join Date
    Apr 2002
    Location
    New York, New York, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (Access 97)

    Thanks Gary. I tried both suggestions, but the second combo box is still empty.

  8. #8
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (Access 97)

    Did you break the code to see if the Eval function was returning the value you expected?

    Peter

  9. #9
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (Access 97)

    Did you requery the second combo box after setting the new row source?
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  10. #10
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: SQL Statement (Access 97)

    .. Actually, lets make this a bit simpler. You said you are using combo boxes. Since you are not using a multiselect listbox, your statement can only evaluate to one answer, thus, why use the in statement at all.

    Why not use,

    SELECT DISTINCT qrySongWriter.WriterID, qrySongWriter.WriterName
    FROM qrySongWriter
    WHERE (((qrySongWriter.SongID) = [Forms]![frmQuerySongsfor2Writers]![cboWriter1]))
    ORDER BY [qrySongWriter].WriterName;

    Since you said cboWriter1 is a combo box, you can only choose one item. Thus, there is no need for the in statement at all.
    Regards,

    Gary
    (It's been a while!)

  11. #11
    Star Lounger
    Join Date
    Apr 2002
    Location
    New York, New York, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (Access 97)

    Thanks Peter, but this is a SQL statement, not code, and I don't think there is any way to break execution. I wish there were - it would make debugging much easier. Also, to Bryan, the suggestion to requery combo box 2 got me excited, but it turns out I did think of that and the requery statment is in the after update event for combo 1.

    To Gary: To keep my initial post simple, I didn't explain the full situation. This is a music database, and I'm trying to display the songs writen by 2 people. So for example, if the name is combo 1 is Jerome Kern, I should get 7 names in combo 2, reflecting the 7 people Kern wrote with. As I say, if I replace this troublesome expression with Kern's WriterID, 27, then combo 2 gets filled with exactly the 7 correct names.

  12. #12
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (Access 97)

    So this is a saved query then?

    Here are a couple of ideas to help debugging.

    1) In the AfterUpdate event of the first combo box put this line:

    MsgBox [Forms]![frmQuerySongsfor2Writers]![cboWriter1].[Column(0)]

    This will let you know if the value is what you are expecting.

    2) Once you change Combo1 open the query and see if it returns the results you expect.

    If this doesn't give you some more clues, how about pulling out the parts and posting the troublesome form, query and table (With sample data in it)
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL Statement (Access 97)

    Since you're creating this in code, try this instead:
    <pre>strSQL = "SELECT DISTINCT qrySongWriter.WriterID, qrySongWriter.WriterName " _
    & "FROM qrySongWriter " _
    & "WHERE qrySongWriter.SongID " _
    & "In (SELECT qrySongWriter.SongID " _
    & "FROM qrySongWriter " _
    & "WHERE qrySongWriter.WriterID = " _
    & [Forms]![frmQuerySongsfor2Writers]![cboWriter1].Column(0) & ") " _
    & "ORDER BY qrySongWriter.WriterName;"</pre>

    Charlotte

  14. #14
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (Access 97)

    Have you tried setting this up as a saved query so that you can use the builder to set up the criteria and see the results in the query?
    Also a it should run faster from a saved query than from a SQL statement.

    Peter

  15. #15
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: SQL Statement (Access 97)

    You said, << If I replace this expression with Kern's WriterID, 27, then the combo 2 gets filled with exactly the 7 correct names>>

    Where exactly are you storing the "27" that needs to drive the Combo box. For example, is the 27 in the first Combo box or is it based on a query that gets the name from the combo box and creates a recordset to link to?

    I would suggest the following. Create a new query where you can type in 27 and get the correct answer when the query is run. Change the parameters of the query to filter on the selection of the combo box and re-run the query. If it still doesn't work then you are either linking on the wrong column of the combo box or the "27" does not exist as part of the combo box parameters. If this works, then you can link the second combo box to this query and in the on change event of the first combo box input Refresh to refresh the screen whenever the user selects a new entry in the first combo box.

    Any chance you could post your db as this should be fairly easy to do.

    HTH
    Regards,

    Gary
    (It's been a while!)

Page 1 of 2 12 LastLast

Posting Permissions

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