Populate Access 2003 form combo box with VBA ADODB query... 2

[Webkins]

Hello, I need to populate a Access 2003 form combo box with the following VBA code using ADODB:

Dim SQLa As String: Dim rs As ADODB.Recordset: Dim cn As ADODB.Connection: Set cn = New ADODB.Connection

cn.Open "driver={SQL Server};server=mxbarzcpms001;database=mwo;Trusted_Connection=Yes"

SQLa = "select work_order_number from mwo.dbo.mwo
where ((manager = 'Mike Smith') and (manager_status is null)) or ((manager = 'Mike Smith') and (manager_status = 'Hold'))"

Set rs = cn.Execute(SQLa, , adCmdText)

This query works fine and returns the data I need but I am not sure how to pass it to the form combo box.

Thanks for any and all help and suggestions

 

[dhookom]

I almost always use a saved pass-through query as the Row Source. Use a little DAO code to change the SQL property of the P-T query.

Currentdb.QueryDefs("qsptRowSource).SQL= SQLa

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

You may try something like:

SQLa = "select work_order_number " _
& " from mwo.dbo.mwo " _
& " where (manager = 'Mike  Smith') " _
& " and (manager_status is null or manager_status = 'Hold')"

Set rs = cn.Execute(SQLa, , adCmdText)
[blue]
cboMyCombo.Clear

Do While rs.EOF = False
    cboMyCombo.AddItem rs!work_order_number.value & ""
Loop

rs.Close
Set rs = nothing[/blue]

Have fun.

---- Andy

 

[Andrzejek]

Thanks for the star, but I missed:

Do While rs.EOF = False
    cboMyCombo.AddItem rs!work_order_number.value & ""[blue]
    rs.MoveNext[/blue]
Loop

Have fun.

---- Andy