Results 1 to 13 of 13

Thread: bound column

  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: bound column

    Hello all,
    why can't I specify a boundcolumn in Combo Box when using a loop:

    Code:
    With Me![tblErrors subform].Form.RecordsetClone
            Do Until .EOF
            strMessage1 = !IDX_ErrID.Column(1) & vbNewLine & strMessage1
            .MoveNext
            Loop
        End With
    When I run this code, I get "object doesn't support this property or method"
    When I leave the .Column(1) off, the code runs but give me the id(number)field in the table instead of the name field I want displayed. On the form for this combo box the recordsource is table table with and id field and a name field.
    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Your combobox isn't contained in the recordsetclone.
    Think of the form's recordsetclone as a query, not a copy of the form.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jul 2004
    Posts
    214
    Ok I apologize but I am not following. What do I need to change? Do I need to reference something else?

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    First things first...

    You do know that the index in the Column property is actually 0 based, right?
    In other words, Column(0) is the first column, Column(1) would be the second column, etc

    I'm not really sure what it is you're trying to do. I guess my answer would depend on your answer to the above question.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Jul 2004
    Posts
    214
    Yes. I do know that the column property is 0 Based. I am trying to display the second column but only get the first column.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by slimjen
    display the second column but only get the first column.
    Come again?
    George
    Home | Blog

  7. #7
    Join Date
    Jul 2004
    Posts
    214
    !IDX_ErrID is displaying the column property (0) instead of column property (1) the recordsource is a query displaying the fields from a table. the first field is ID which displays a number and the second field is a name which displays the name of in the field
    i.e. 23 trace
    12 expression
    I want the name; not the number.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I suggest you take a ook at the combobox wizard that pops up whenever you want to create one... You can chose to "hide key field" and what not. This means that it stores the keyfield but returns the other columns to the screen.
    So the combobox would display the description but all calculations refering to the combobox would pick up the number.
    George
    Home | Blog

  9. #9
    Join Date
    Jul 2004
    Posts
    214
    the bound column property in the combo box is set to 1 to display the name field but i can't specify this in the loop. i.e. !IDX_ErrID.Column(1) which gives me an error.

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You are on the right track in your first post then,
    but you'll need to use DLOOKUP to find the approriate "name" based on
    the ID contained in the recordsetclone.
    Inspiration Through Fermentation

  11. #11
    Join Date
    Jul 2004
    Posts
    214
    I have never done a dlookup with a loop. can you give me an ex. ?

  12. #12
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I'm going clear back to your first post, which I never saw that you understood what was wrong there. It appears that you are trying to get a list of all errors that are available to pick from in the combo box, OR you are trying to get a list of all errors from the table that is the source table of the form where the combo box is. Either way, your code will not work, but you already know that. If what you want is a list as mentioned in the second part of my post here, then rather than using "!IDX_ErrID.Column(1)" to get the error message, you need to use "!IDX_ErrID" in a DLookup to get the error message. Because the combo box is using
    On the form for this combo box the recordsource is table table with and id field and a name field.
    whatever table you referenced here, then that is the table to use in the DLookup. Therefore, your loop code would look like this:
    Code:
    With Me![tblErrors subform].Form.RecordsetClone
            Do Until .EOF
            strMessage1 = DLookup("Name Field", "table table", "ID Field=" & !IDX_ErrID) & vbNewLine & strMessage1
            .MoveNext
            Loop
        End With
    Please note that I changed your code to put in the DLookup where you did have the 2nd column from the combo box.

    Here is the reasons: The recordset clone is a clone of the recordset that the form is using. That would be the "tblErrors" table (I believe from your first post). The combo box uses the IND_ErrID field from the form recordset, and within the combo box the RowSource for the combo box references another table that has the error id and the name field. This second table is the one that you need to use in the DLookup.

    Hope this helps,

  13. #13
    Join Date
    Jul 2004
    Posts
    214
    thanks Vic for clearing this up. I'll try this

Posting Permissions

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