Need help running dynamically created Query from controls on form

[sirchris]

I have 3 comboboxes and intend to have code that when a button (cmdSearch) is clicked will read the data from the comboboxes and dynamically create a query, here is my function:


Private Function fncGenerateQuery() As Boolean
Dim strSQL As String
Dim qdfTemp As QueryDef
Dim strWhere As String

strSQL = "SELECT Teacher.TeacherName, Subject.SubjectName, Work.YearGroup, Work.Dateset, Work.Datedue, Work.Details, Work.Additionalinfo, FROM Teacher INNER JOIN (Subject INNER JOIN Work ON Subject.SubjectID = Work.SubjectID) ON Teacher.TeacherID = Work.TeacherID "

If Not (Me.Combo17.Value = vbNullString) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " WHERE "
Else
strWhere = strWhere & " AND "
End If
strWhere = strWhere & " Teacher.Name = '" & Me.Combo17.Value & "'"

If Not (Me.cboSubject.Value = vbNullString) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " WHERE "
Else
strWhere = strWhere & " AND "
End If
strWhere = strWhere & " Teacher.Name = '" & Me.cboSubject.Value & "'"

If Not (Me.Combo21.Value = vbNullString) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " WHERE "
Else
strWhere = strWhere & " AND "
End If
strWhere = strWhere & " Teacher.Name = '" & Me.Combo21.Value & "'"

strSQL = strSQL & strWhere & ";"

Set qdfTemp = CurrentDB.QueryDefs("qryMain&quot
qdfTemp.SQL = strSQL
Set qdfTemp = Nothing

fncGenerateQuery = True

End Function


I have then tried to call up this function from the command button so:


Private Sub cmdSearch_Click()

Call fncGenerateQuery

End Sub


However, when I try this it displays the error message:


Run-time error "3296"
Join expression not supported


Which highlights the line:


qdfTemp.SQL = strSQL


Please help!

 

[Steve101]

Chris,

I'll first make a couple of points about your code; then I'll try to answer your question:

(a) Try to "break up" the assignment to the strSQL string. It makes it neater and easier to follow. For example:
[tt]
strSQL = "SELECT T.TeacherName, S.SubjectName, " & _
" W.YearGroup, W.Dateset, W.Datedue, " & _
" W.Details, W.Additionalinfo " & _
"FROM Teacher AS T " & _
"INNER JOIN (Subject AS S " & _
"INNER JOIN Work AS W " & _
"ON S.SubjectID = W.SubjectID) " & _
"ON T.TeacherID = W.TeacherID "
[/tt]

(b) In the above, I have used table aliases to abbreviate the table names, and thus simplify the SQL. Not manditory, but I like to do it, as it makes the SQL shorter and consequently simpler.

(c) Consider indenting your If-Then-Else statements, and other statements which control the flow of your code. For example your code:
[tt]
If Len(strWhere) > 0 Then
strWhere = strWhere & " WHERE "
Else
strWhere = strWhere & " AND "
End If
[/tt]
becomse much clear when indentation is used; eg.
[tt]
If Len(strWhere) > 0 Then
strWhere = strWhere & " WHERE "
Else
strWhere = strWhere & " AND "
End If
[/tt]

This becomes even more important if you have multiple statements in the then and else parts, and/or you have nested if-then-elses (as you have). It makes it easy for you to check that the parts start and end where you intend them to.

(d) Now I'll try to answer your question, relating to why you are getting the error. Not sure of the details relating to the message, but there's one fundamental error in the syntax of the SQL statement that you are generating; its the comma before the FROM teacher bit in your assignment to strSQL. Remove it, as I have done in my earlier point (a).

(e) Also your code which tests a control for Null will not work (eg. you use:

If Not (Me.Combo17.Value = vbNullString) Then

Replace this code with:

If not ISNULL(Me!Combo17.Value) then

Do with with all similar code in your application.
A couple of other points related to the above code. Note that I have replaced the full stop after Me with an explanation symbol. The full stop means that "what follows represents a property or method". The explanation means that what follows represents a contol name. Thus Me!Combo17.Value, returns the value property of the Combo17 control associated with the "me" form.

(f) And finally, take the time to give the controls on your form meaningful names; for example "Combo17" is meaningless, and will make it difficult for you to understand your own code in a weeks time. However, something like cmbSubjectList is much clearer.

I'm not sure it I've absolutely solved all the issues in this code; suggest you make corrections in line with the above, then if required, we'll move to second base.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[chaos18]

greetings!
i am not exactly an expert on sql but i recently had a query somewhat like yours and it used a join operation to join the information off one table and update it to my main table and i recieved a similar error. You say it highlighted the section "qdfTemp.SQL = strSQL"...this would indicate that that specific phrase is out of syntax and the computer cannot read it correctly. I would try to correct the syntax problem and it should work perfect. Hope this helps


Thomas Gunter
Network Administrator

 

[sirchris]

Okay, well I have implemented the
"If not ISNULL(Me!Combo17.Value) then"
line, but whenever I try the code now it brings up the error message:

"Compile error
Variable not defined"

 

[Steve101]

You need to identify the line that the error is referring to (it should be hilited in the code, when the error occurs). If you have used any new variables, make sure that they are Dim'd at the top; otherwise you may have a typing error.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)