Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2008
    Posts
    13

    Unanswered: Combo Box runs 'parameter' query to populate list box - question

    I have the following code in my db to select a value on a combo box, and then run a query which the data from the query is used to populate a listbox on my form...one issue...the query was initially a parameter query, which I've change to attempt to grab the 'criteria' from the combo box, based a unique field 'ID'. But when I run the following I return an error, and I know its probably my syntax of how I'm coding my SQL statement...any suggestions on what to put in the area I've marked red below to make my value in my combo box be the criteria for the query I'm running...

    btw, the query runs a-okay when I hard code the criteria


    Private Sub tradedesc_Change()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String, strItem As String

    strSQL = "SELECT R_Risk.AccountNumber, R_Risk.InvestorName, R_Risk.TradeDescription, R_Risk.PositionQty, R_Risk.AddRemoveContracts, R_Risk.ID FROM R_Risk WHERE (((R_Risk.AddRemoveContracts)>0) AND ((R_Risk.ID)=([Forms]![ListBox].[tradedesc])))"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    Do Until rs.EOF
    strItem = rs.Fields("InvestorName").Value & ";" & rs.Fields("ID").Value
    Me.List1.AddItem strItem
    rs.MoveNext

    Loop
    rs.Close
    Set rs = Nothing
    Set cn = Nothing
    End Sub

  2. #2
    Join Date
    Apr 2008
    Posts
    13

    more info

    The name of my combobox is "tradedesc"

  3. #3
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    Change your SQL statement to this if the combo returns a number:
    Code:
    strSQL = "SELECT R_Risk.AccountNumber, R_Risk.InvestorName, R_Risk.TradeDescription, R_Risk.PositionQty, " _
    & " R_Risk.AddRemoveContracts, R_Risk.ID FROM R_Risk WHERE (((R_Risk.AddRemoveContracts)>0) AND " _
    & " ((R_Risk.ID)=(" & [Forms]![ListBox].[tradedesc] & "))); "
    and if it return a text:
    Code:
    strSQL = "SELECT R_Risk.AccountNumber, R_Risk.InvestorName, R_Risk.TradeDescription, R_Risk.PositionQty, " _
    & " R_Risk.AddRemoveContracts, R_Risk.ID FROM R_Risk WHERE (((R_Risk.AddRemoveContracts)>0) AND " _
    & " ((R_Risk.ID)=('" & [Forms]![ListBox].[tradedesc] & "'))); "
    Last edited by Aran1; 05-21-08 at 00:22.
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  4. #4
    Join Date
    Apr 2008
    Posts
    13

    Thank You!

    Thanks A Million!!! Am new at this...so often I encounter challenges. Thanks a ton.

  5. #5
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    You are welcome
    Cheers
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

Posting Permissions

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