Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select field with Combo box

    Dear All,

    I

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select field with Combo box

    Now, let's see:

    You don't have a field called:

    "[Forms]![frm_ChoosePrn]![cbo_ChoosePrn]"

    but you do have a control that contains the name of the field

    so, try this:
    "SELECT tbl_colectPriceList.ColRef, tbl_colectPriceList.PrnCol, tbl_colectPriceList.Tonnes, tbl_colectPriceList." & [Forms]![frm_ChoosePrn]![cbo_ChoosePrn] & ", AS fld_GotField FROM tbl_colectPriceList;"

    IOW concatenate three strings, as follows:
    string1 & control.value string & string2.

    Try it once. I can't really test it, but I think it should work. The key should be in where and how many quotes you use.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select field with Combo box

    Dear Ken,
    It now says

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select field with Combo box

    That's a <img src=/S/bummer.gif border=0 width=15 height=15>, I had such high hopes for you.

    But it looks like you're a bit closer. I am pretty much a newcomer to Access and so I may not always be that much of a help. I am not certain whether you're running the SQL statement in an interactive query (is that the way to put it?) or in a VBA sub.

    I'll show you a test I did in my own code (Access 2000). The procedure is as follows (it fills a listbox with values):

    <font face="Comic Sans MS">Private Sub FillPartsList()
    Me.lstParts.RowSource = _
    "SELECT fldQuantity, fldPartID,fldAutoNum FROM tblRecov_Tmp WHERE " & _
    "fldCarID ='" & Me.txtCarID & "'"
    Me.lstParts.Requery
    End Sub</font face=comic>

    I then changed it to include a variable to take the place of a field name. So the SQL statement takes a fieldname from a variable (could also be a cbobox value, I suppose) and, at the end, a value from another textbox.

    <font face="Comic Sans MS">Private Sub FillPartsList()
    Dim myFieldVar As String
    myFieldVar = "fldAutoNum"

    Me.lstParts.RowSource = _
    "SELECT fldQuantity, fldPartID," & myFieldVar & " FROM tblRecov_Tmp WHERE " & _
    "fldCarID ='" & Me.txtCarID & "'"
    Me.lstParts.Requery
    End Sub</font face=comic>

    The second proc runs as well as the first procedure.
    That's why I was thinking that it is definitely possible
    to have a variable or control supply the name of a field
    in a SQL statement.

    What happens when you to assign the SQL statement to a test variable in the Debug(Immediate) window and then play with it till the statement comes out right when you say:
    ? testvar

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

    Re: Select field with Combo box

    Try this (you both had a stray comma before the As fld_GotField):

    <pre>strSQL = "SELECT tbl_colectPriceList.ColRef, " _
    & "tbl_colectPriceList.PrnCol, " _
    & "tbl_colectPriceList.Tonnes, " _
    & "tbl_colectPriceList." _
    & [Forms]![frm_ChoosePrn]![cbo_ChoosePrn] _
    & " AS fld_GotField FROM tbl_colectPriceList;"</pre>

    Charlotte

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select field with Combo box

    Dear Ken & Charlotte,

    Its still not working as I hoped. I think I

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

    Re: Select field with Combo box

    Post the code called from the form. Then we'll be able to see what you might be doing. There isn't any way I know of to write a query that will return a different field the way you're trying to do, at least not as I understand it. If you show us your code, something may click.
    Charlotte

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select field with Combo box

    Dear Charlotte,

    Its all right now, I entered each field name manually and printed the reports. It
    Attached Files Attached Files

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select field with Combo box

    Hi Rupert,

    Well, I think I have the solution for you.

    After I poked around your program a bit, I came to the conclusion that you need to change the query definition for qry_GetCol. This is the query with field A in it.

    The question then became: When and how can I change the query. The best time to change it is after you pick a letter from the combobox. So I decided on the AfterUpdate event. While I was at it, I also wanted to make sure that the 2Tonne textbox updated correctly. There's some code for that in the event procedure as well.

    The other question was: how to change it. After a bit of reading I came to the conclusion that I needed to change the SQL property of the qry_GetCol query in QueryDefs collection. I couldn't find this in ADO but in DAO it was available. This meant I needed to register DAO 3.51 Object Library in your database.

    To make it work, add the following code to your form:
    <font face="Comic Sans MS">
    Private Sub cbo_ChoosePrn_AfterUpdate()
    Dim SQLStatement As String
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qry_GetCol")

    SQLStatement = "SELECT tbl_colectPriceList.ColRef, tbl_colectPriceList.PrnCol," _
    & "tbl_colectPriceList.Tonnes, tbl_colectPriceList." _
    & Me.cbo_ChoosePrn & " AS GotFld FROM tbl_colectPriceList;"

    qdf.SQL = SQLStatement

    qdf.Close
    db.Close
    Set qdf = Nothing
    Set db = Nothing

    Me.txt_2tonRate.ControlSource = "=DLookUp(""GotFld"",""qry_get2TonneRate"")"
    End Sub
    </font face=comic>
    It appears to work. <img src=/S/smile.gif border=0 width=15 height=15>
    Attached Files Attached Files

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select field with Combo box

    Dear Ken,

    It works a treat! Thank you very much. You are definitely a

Posting Permissions

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