VBA and SQL, Using SQL as rowsource for combo box

[WarScribe]

Hi all, this is my first post here.

I've had a look around using the search function and using google on the wider web, but nothing seems to resolve my problem.

I'm trying to use an SQL query as a rowsource. As part of this query I have a table which is a master record of patients, and what I'm trying to do is set a second table dynamically in which is recorded details of treatment cycles (one table for cycle 1, another for cycle 2 etc etc). When the form is opened from a switchboard the record source is set dependant on the choice of cycle (this works fine) and i'm trying to filter a combo box so that the only patients available to select are the ones who DO NOT already have a record in the cycle table.

This works fine if i make the query in access and hard code the table, but this would require me to change the table used before each input.

So far this is my code, run on form opening:

Private Sub Form_Load()
'Dim db As database
'Dim qdf As querydef
Dim source As String
Dim sourceid As String
Dim strSQL As String
'db = CurrentDb
'qdf = db.querydefs("qrySource")
source = Me.RecordSource
sourceid = "source.[ID]"
strSQL = "SELECT tblPatientInfo.[ID], tblPatientInfo.[Initials], tblPatientInfo.[Casenote] " & _
            "FROM tblPatientInfo " & _
            "LEFT JOIN ' &  source & ' ON ' & sourceid & ' = tblPatientInfo.[ID] " & _
            "WHERE ' & sourceid & ' Is Null " & _
            "ORDER BY tblpatientInfo.[ID];"
Me.patient_cbo.RowSource = strSQL
End Sub

the problem that is arising is i'm getting the "JOIN expression not supported" error

I've had no luck in resolving this at all, probably because I'm completely new to SQL and am rusty with my VBA.

The db and qdf are taken out at the moment as I either get " Compile Error: User-defined type not defined" or if i switch on Microsoft DAO 3.6 in TOOLS>REFERENCES I get "Comile Error: Invalid Use of Property"

Using Access 2010

....help...please!

 

[WarScribe]

Never mind! I've sorted the problem!

my ' and " were are mess, and throwing up syntax problems, which have now been solved with judicious use of msgbox!

for anyone thats interested, this is what I have now and it seems to work a treat

Private Sub patient_cbo_enter()
Dim strSQL As String
Dim source2 As String
source2 = Me.RecordSource
strSQL = "SELECT tblPatientInfo.[ID], tblPatientInfo.[Initials], tblPatientInfo.[Casenote] " & _
"FROM tblPatientInfo LEFT JOIN " & source2 & " " & _
"ON " & source2 & ".[ID] = tblPatientInfo.[ID] " & _
"WHERE " & source2 & ".[ID] Is Null " & _
"ORDER BY tblpatientInfo.[ID];"
'MsgBox source2 & " " & source2 & ".[ID]", vbOKOnly, "Test"
Me.patient_cbo.RowSource = strSQL
End Sub

I've also changed this to run on entering the combobox rather than on form load as this seems to give everything chance to load like recordsource