How do I get the ORDER BY into this statement

[Billp]

Hi,

I am so happy I got this statement to work thru trial and error

Forms!frmSER![cboContactID].RowSource = "SELECT
[FirstName],[LastName],[CustomerID],[LineID]" & _
"FROM
[tblCustomerContacts]" & _
"WHERE [CustomerID] =
""" & [Forms]![frmSER]![cboCompany_Name].Column(2) & """"

Now how do I add the ORDER BY [LastName] into it with all those quote marks
- cause its causing me grief and I have run out of ideas of trial and error.

Thank you so much in advance
Bill

 

[Steve Sanford]

Try this:

Forms!frmSER![cboContactID].RowSource = "SELECT
[FirstName],[LastName],[CustomerID],[LineID]" & _
" FROM
[tblCustomerContacts]" & _
" WHERE [CustomerID] =
""" & [Forms]![frmSER]![cboCompany_Name].Column(2) & """" & _
" ORDER BY [LastName]"


Note: I added a space in front of the FROM and WHERE clauses.

If [cboContactID] and [cboCompany_Name] are on the form "frmSER", you could
use
Me.[cboContactID].RowSource

instead of

Forms!frmSER![cboContactID].RowSource


(and the same goes for the other combo box)


HTH

 

[Jeanette Cunningham]

Hi Bill

like this

Dim strSQL As String
Dim lngCustomerID As Long

lngCustomerID = [Forms]![frmSER]![cboCompany_Name].Column(2)

strSQL = "SELECT FirstName,LastName, CustomerID, LineID" _
& "FROM tblCustomerContacts " _
& "WHERE CustomerID = " & lngCustomerID & " " _
& "ORDER BY tblCustomerContacts.LastName"
Debug.Print strSQL
Forms!frmSER![cboContactID].RowSource = strSQL



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Billp]

Thanks so much to you both.
Cheers
Best Regards
Bill

Hi Bill

like this

Dim strSQL As String
Dim lngCustomerID As Long

lngCustomerID = [Forms]![frmSER]![cboCompany_Name].Column(2)

strSQL = "SELECT FirstName,LastName, CustomerID, LineID" _
& "FROM tblCustomerContacts " _
& "WHERE CustomerID = " & lngCustomerID & " " _
& "ORDER BY tblCustomerContacts.LastName"
Debug.Print strSQL
Forms!frmSER![cboContactID].RowSource = strSQL



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi,

I am so happy I got this statement to work thru trial and error

Forms!frmSER![cboContactID].RowSource = "SELECT
[FirstName],[LastName],[CustomerID],[LineID]" & _
"FROM
[tblCustomerContacts]" & _
"WHERE [CustomerID] =
""" & [Forms]![frmSER]![cboCompany_Name].Column(2) & """"

Now how do I add the ORDER BY [LastName] into it with all those quote
marks
- cause its causing me grief and I have run out of ideas of trial and
error.

Thank you so much in advance
Bill