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));




Thanks,
SoggyCashew.....

 

[dhookom]

Just paste the SQL into the Row Source property. You can also use code in the on open or some other event in the form to set the RowSource propety.

Duane
Hook'D on Access
MS Access MVP

 

[oxicottin]

Duane I didnt know you could paste the SQL in and it would work.... I tried it in VBA and got it to work that way as well. I have a heck of a time converting SQL to VBA but I have a little .mdb I found a while back and it converts it for me. Any good tips on where to read up on converting SQL to VBA? 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

Thanks,
SoggyCashew.....

 

[dhookom]

The issue you have to worry about when using SQL statements in VBA is embedded quotes like Format([Yourdate],"mmm-dd") which might need to be changed to Format([Yourdate],'mmm-dd').

I like to get rid of extra junk in the SQL and use the values from the controls.

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;"

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

A couple of comments...

You can set a row source property equal to a SQL Statement however it is not a querydef object and can not be pre-compiled and therefore take a little longer to open your form (not that you are likely to notice on a modern computer unless you have a bunch of them).

Secondly, handling SQL in VBA code is all about making a valid SQL statement with code.

Concatenation - This is putting two strings together one after the other. In VBA the Amperstand (&) combines strings. "Hello " & "World" = "Hello World"... The trick in working with this stuff is to put the spaces in. Notice I put a space after Hello. I could have instead put it before World. A constant to know... vbCRLF - this is the Carriage Return (the newline characters you generally get when you push enter). You can not type an CRLF / 'enter' into your string, you have to concatenate it in. The constant is the best way.

Quotation marks - VBA uses Double quotes. Access SQL supports both single quotes and double quotes. Out of (argueably bad) habit I embed the double quotes. This is done by putting a pair of double quotes in... """" .... Thats a string because it begins and ends with double quotes... It contains one double quote or " because it has two next to each other. Alternately you just use single quotes for SQL. Most SQL variants including SQL server use single quotes. So your code is more portable if you use single quotes.

Lastly the underscore (_) is the line continuation operator... It just indicates that even though you put a new line in VBA, it should be read by the compiler as one line.
This is useful as SQL code is more readable when things, especially clauses, are on separate lines. The big thing to note is that an underscore inside a string is not a line coninuation... it is an underscore in a string.