Convert combo rowsource to VBA SQL statement

[oxicottin]

Hello, I have a combobox (cboProductLength) that has a Row Source that is a query. I would like to get rid of the query and use a sql statement in VBA as it row source if I can. How can I do this? Thanks!

Row Source Querys SQL"


SELECT tbl_ProductData.Item, tbluProducts.ProductID, tbl_ProductData.strProductLength, ImpToDec([strProductLength]) AS [Decimal], Round(ImpToDec([strProductLength])) AS SortDecimal, tbl_ProductData.IsInactiveFROM tbluProducts INNER JOIN tbl_ProductData ON tbluProducts.Product = tbl_ProductData.Product
WHERE (((tbluProducts.ProductID)=[Forms]![frm_ShiftDay]![frm_ShiftMachinesRanSubfom].[Form]![frm_MachineOutputSubform].[Form]![cboProductID]) AND ((tbl_ProductData.IsInactive)=False));

[ranman256]

You really want it to be a query. Why do you need SQL?

[jonh]

Just set the rowsource property to your sql string instead of a query/table name.


dim sql as string
sql = "select w from x where y = z"
cboProductLength.rowsource = sql

[oxicottin]

I have it working with query Im learning so I wanted to try to do it in VBA. I wanted to see how the SQL get changed so that it works in VBA, like what can be taken out ect...

[oxicottin]

Thanks...

Dim strSql As String


strSql = "SELECT tbl_ProductData.Item, tbluProducts.ProductID, tbl_ProductData.strProductLength, ImpToDec([strProductLength]) AS [Decimal], Round(ImpToDec([strProductLength])) AS SortDecimal, tbl_ProductData.IsInactive " & vbCrLf & _
"FROM tbluProducts INNER JOIN tbl_ProductData ON tbluProducts.Product = tbl_ProductData.Product " & vbCrLf & _
"WHERE (((tbluProducts.ProductID)=[Forms]![frm_ShiftDay]![frm_ShiftMachinesRanSubfom].[Form]![frm_MachineOutputSubform].[Form]![cboProductID]) AND ((tbl_ProductData.IsInactive)=False));"

cboProductLength.RowSource = strSql

[ranman256]

Thats the point here, no VBA is needed. The param query does all the work.

[jonh]

I really can't see it making any difference on modern PCs whether the query is pre compiled or not.