Sql problem in VBA

[macsta]

hiya all I am having another problem only this time it is with SQL in a VBA call I hope someone can help


'MYCODE:

Private Sub form_load()
Dim unitSQl As String, groupSQL As String, deptSQL As String


''''''''''''''''''Unit'''''''''''''''''''''
unitSQl = "SELECT tb_Main.Unit, First(tb_Main.[unit sorting colum]) AS [FirstOfunit sorting colum] " _
& "FROM tb_Main " _
& "GROUP BY tb_Main.Unit " _
& "ORDER BY First(tb_Main.[unit sorting colum])"

Me!lbCurrent.RowSource = unitSQl
'currentUnit = lbCurr
'MsgBox currentUnit

'''''''''''''''''group'''''''''''''''''''''
groupSQL = "SELECT tb_Main.[Group Heading], tb_Main.Unit FROM tb_Main WHERE (tb_Main.[Group Heading]) = '" & currentUnit & "'"

Me!GroupCombo.RowSource = groupSQL

''''''''''''''''''dept'''''''''''''''''''''
deptSQL = "SELECT tb_Main.Dept, tb_Main.deptSorting" _
& "FROM tb_Main" _
& "HAVING(tb_Main.[Group Heading]) = '" & currentGroup & "'""" _
& "ORDER BY tb_Main.deptSorting"

Me!DeptCombo.RowSource = deptSQL



End Sub

this unit part of the code works fine and updates the list box with no problems how ever when I try to use the value of the list box to update the GroupCombo and Dept Combo I get a syntex error at the line that is blue. I have tried all sorts but cant get it to work


thanks again

 

[bjackson]

if currentUnit is a string try
groupSQL = "SELECT tb_Main.[Group Heading], tb_Main.Unit FROM tb_Main WHERE (tb_Main.[Group Heading]) = " & """" & currentUnit & """"

 

[macsta]

thanks I will try that