VBA Combo Box Properties

[PNGBill]

Hi Forum, Access 2010.
This code works - except for the .BoundColumn bit

With cboRefinanceID
        .RowSource = "SELECT TBLLOAN.LDPK, TblRefinance.RefinanceID, TblRefinance.NewApplicationID, TBLLOAN.ADPK, TblRefinance.RefinanceAmount, TblRefinance.RefinanceSID, TblRefinance.DateInitiated " & _
            "FROM TBLLOAN INNER JOIN TblRefinance ON TBLLOAN.LDPK = TblRefinance.OldLoanID " & _
            "WHERE (((TBLLOAN.LDPK)=DLookUp(""[LDPK]"",""TBLLOAN"",""[LDPK]="" & [Forms]![frmBankStatementsDataOPS]![subfrmMemberRepaymentsData]![cboLoanID])) AND ((TblRefinance.RefinanceSID) Is Null));"
        .ColumnCount = 5
        .BoundColumn = 2
        .ColumnWidths = "1cm;1cm;1cm;1cm;1cm"
    End With

The combo box control is populated with column 1 no matter what I do with the bound column code.

Any ideas what may be an issue here ??

 

[Steve R.]

As a quick observation you are selecting 7 fields but have only defined 5 columns.

I am not sure if this helps but, examine the combobox code below:

SELECT [artistfirst] & " " & [ArtistLast] AS expr1, tblCoverArtistList.ArtistFirst, tblCoverArtistList.ArtistLast, tblCoverArtistList.ArtistIDnum
FROM tblCoverArtistList
ORDER BY tblCoverArtistList.ArtistLast;

The bound column in my case, is column #1, but you will see that ArtistIDNum appears to be in the third position in the SQL statement. It is in column #1 in the underlying table. I have limited experience with this and it has been a long while since I wrote the code. It would seem that when referencing the bound column, it is its location in the underlying table not its relative position in the SQL statement. (I have not re-tested this to confirm if this is correct.)

 

[PNGBill]

Thanks Steve, I didn't notice the Column Count error and have fixed same but unfortunately it didn't resolve the Bound Column issue.

I don't see how the table order can be a factor because Row Source is the SQL and in this case, there are multiply tables.

Is your example an imbedded sql ?

In my case the sql is in the vba code and only becomes the Row Source when the Event occures.

 

[Steve R.]

You are correct. I re-checked my code and found that I had "two" steps, one was the query, but then the combobox actually used this code: "Select artistidnum, expr1 from CoverArtistListqry". So artistidnum was in the first position.

--------------------------------------------------------------------
It occurs to me that I may be getting off topic a bit, since your question concerns populating the combobox.

The combo box control is populated with column 1 no matter what I do with the bound column code.

While not "elegent" can you reconfigure your SQL statement so that the field you want is in the first position?
---------------------------------------------------------------------

I modified my table and code to mimic your approach, in a highly simplified manner. It works as "expected" with a caveat. I created a textbox bound to Combo0. When clicking on the Combobox it would only show the artistidnum. The bound textbox would show artistidnum when the bound column was set to 1. The bound textbox would show field1 when the bound column was set to 2. Does that help some?

    With Me.Combo0
        .RowSource = "Select artistidnum, field1 from CoverArtistListqry"
        .BoundColumn = 1
        .ColumnCount = 2
    End With

By modifying the select statement either artistidnum or field1 can be in the first position in the combobox. Both fields were visible. Which raises this question, do you see 5 columns in your combobox?

 

[PNGBill]

Thanks Steve, I moved on from this issue but will come back to resolve it soon.
I did notice that although the wrong data was stored as the control display when I did a MsgBox on the data the correct result was returned.
When I moved the sql fields the issue was resolved.

This may well be what you are describing and the Zero as first field issue.

I get 7 fields displaying but have to scroll sideways to view. Idealy the combo box increases in size when Clicked and then returns to normal when lost focus.

Working on the code to make things happen for a day or two and then back to the combo box