Type mismatch ComboBox.List = Application.Transpose(rst.GetRows)

[frozbie]

Hi,

Just thought I'd share this. Am not certain I know why I fixed this, but would be grateful for any pointers if I've got it wrong.

Am developing an Excel forms front end Access backend application. Got an error this morning while loading a form that has loaded fine for months.

Error was when populating a combobox from a database query:

Private Sub UserForm_Initialize()

    With cmd
        .CommandText = "qry_populatecombobox"
        Set rst = .Execute
    End With

    If Not rst.EOF Then       
        rst.MoveFirst
        Combobox.List = Application.Transpose(rst.GetRows)
    End If

Error was
Run-time error '13'
Type mismatch

Line highlighted was

Combobox.List = Application.Transpose(rst.GetRows)

Turns out that the data the query has been bringing in was updated yesterday and a Null value introduced into the column being brought in by the query. This Null value was appearing first in the recordset when I checked it this morning.

I think that the Null was causing the error when Application.Transpose tried to do it's thing.

Setting the query to Is Not Null on the criteria for that column fixed the issue.

Hope this is helpful (and if you know the real reason the code broke - do let us know

Mark

 

[arnelgp]

i think its worksheetfunction.transpose()