Executing a query through a form (1 Viewer)

[KirRoyale]

I am trying to make selection on and run a query from a form and have plagiarised the following code:

Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryNewJobLocator")
strSQL = "SELECT JobDetailNew.* " & _
"FROM JobDetailNew " & _
"WHERE JobDetailNew.Source='" & Me.cboSource.Value & "’ " & _
"AND JobDetailNew.Region='" & Me.cboRegion.Value & "’ " & _
"ORDER BY JobDetailNew.Date,JobDetail.Source;"
qdf.SQL = strSQL
DoCmd.OpenQuery "qryNewJobLocator"
DoCmd.Close acForm, Me.Name
Set qdf = Nothing
Set db = Nothing
End Sub

I’m getting the following error message, which I assume related to the positioning of single or double quotes:
Run-time error ‘3075’:
Syntax error (missing operator) in query expression
‘JobDetailNew.Source=” AND JobDetailNew.Region=’”.
However, I cannot see where I’m going wrong and would be very grateful if somebody could help.

 

[Mihail]

Try this
strSQL = "SELECT JobDetailNew.* " & _
"FROM JobDetailNew " & _
"WHERE JobDetailNew.Source=" & Me.cboSource.Value & _
" AND JobDetailNew.Region=" & Me.cboRegion.Value & _
" ORDER BY JobDetailNew.Date,JobDetail.Source;"

 

[Galaxiom]

The error detail shows nothing being concatenated for cboSource suggesting that cboSource has no value.

 

[KirRoyale]

Thanks for the reply, Mihail.
Unfortunately I now get asked for parameter values for the value that I enter in each of the 2 combo boxes.

 

[Mihail]

Can't understand while I can't see your database.
For me seems that the data types not match, but I can't be sure.
If you can to upload the database (with few records to test) I'll take a look.

 

[JHB]

Instead of using Value try use Text.

...
..
[FONT=Arial]"WHERE JobDetailNew.Source='" & Me.cboSource.Text & "’ " & _[/FONT]
[FONT=Arial]"AND JobDetailNew.Region='" & Me.cboRegion.Text & "’ " & _[/FONT]
...
..

 

[pr2-eugin]

Could you please post the following information, RowSource, ColumCount, ColumnWidth, BoundColumn information of the ComboBoxes cboSource and cboRegion..

 

[KirRoyale]

Thank you all for your replies. The reason for my delayed response is that my database had a number of table lookups and I thought that may be causing me a problem, too.
Now that I have restructured the database, I have included JHB’s modifications in my code. I have added an additional search criteria, so now have 3 combo boxes:
Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryNewJobLocator")
strSQL = "SELECT TblApplication.* " & _
"FROM TblApplication " & _
"WHERE TblApplication.SourceID='" & Me.cboSource.Text & "’ " & _
"AND TblApplication.RegionID='" & Me.cboRegion.Text & "’ " & _
"AND TblApplication.CompanyID='" & Me.cboCompany.Text & "’ " & _
"ORDER BY TblApplication.Date,TblApplication.SourceID;"
qdf.SQL = strSQL
DoCmd.OpenQuery "qryNewJobLocator"
DoCmd.Close acForm, Me.Name
Set qdf = Nothing
Set db = Nothing
End Sub

This time, I get the following error message:
“Run-time error ‘2185’:
You can’t reference a property or method for a control unless the control has the focus.”
Does that mean I have to “SET focus” somewhere?

It might be worth noting that my table “TblApplication” now only includes the ID numbers form the “Source”, ”Region” and “Company” tables and the “Row Source” for my 3 combo boxes reflects this. (Previously, the “Source”, ”Region” and “Company” names were input into the “TblApplication” table by selecting from the table lookup). The fact that I just have the ID numbers and not the names to use as a criteria for the query may pose their own problems but I do not know how else to approach this as only the ID numbers – and not the ‘names’ – are contained in the “TblApplication” table.

In response to Paul’s post, the information is as follows:
· cboSource: RowSource is the specially created 1 field table ‘SourceIDList‘ showing the distinct Source ID values
· cboRegion: Similarly, the RowSource is the RegionIDList table.
· cboCompany: as above the CompanyIDList table was used.
(for all 3, the Row Source Type is “Table/Query”.
For all 3 combo boxes”
· ColumCount = 1
· ColumnWidths = [Blank]
· BoundColumn = 1

Thank you very much and I hope you can help me further.

 

[Mihail]

“Run-time error ‘2185’:
You can’t reference a property or method for a control unless the control has the focus.”
Does that mean I have to “SET focus” somewhere?

The error message appear because the .Text property can be used only when the control has the focus.

I have a question for you:
You wish to test if this code can worked (or can be tweaked to work) in order to learn something or you wish to accomplish a task (to have a query that select some data) ?

If you wish to learn, you are on the right way but, if you wish to have a query, the faster way is to design a parametrized query. The parameters would be yours combos.

 

[Galaxiom]

The Text property is only available when the control has the focus.

JHB's suggestion is incorrect. The problem is more likely related to the combo properties as suggested by Paul in post 7.

 

[KirRoyale]

Reply to Mihail.
Thanks for the reply and the information.
I have managed to get a query selection on a form to work where the combo boxes reference a select query. Is this similar to what you are suggesting?
SELECT TblApplication.ID, TblApplication.Date, Source.Source, Region.Region, TblApplication.Ref, TblApplication.Position, Company.Company, TblApplication.Direct, Status.Status, TblApplication.Comments
FROM Status RIGHT JOIN (Company RIGHT JOIN (Region RIGHT JOIN (Source RIGHT JOIN TblApplication ON Source.ID = TblApplication.SourceID) ON Region.ID = TblApplication.RegionID) ON Company.ID = TblApplication.CompanyID) ON Status.ID = TblApplication.StatusID
WHERE (((Source.Source)=[Forms]![JobLocator]![cboSource]) AND ((Region.Region)=[Forms]![JobLocator]![cboRegion]) AND ((Company.Company)=[Forms]![JobLocator]![cboCompany])) OR (((Region.Region)=[Forms]![JobLocator]![cboRegion]) AND ((Company.Company)=[Forms]![JobLocator]![cboCompany]) AND ((([Source].[Source]) Like [Forms]![JobLocator]![cboSource]) Is Null)) OR (((Source.Source)=[Forms]![JobLocator]![cboSource]) AND ((Company.Company)=[Forms]![JobLocator]![cboCompany]) AND ((([Region].[Region]) Like [Forms]![JobLocator]![cboRegion]) Is Null)) OR (((Company.Company)=[Forms]![JobLocator]![cboCompany]) AND ((([Source].[Source]) Like [Forms]![JobLocator]![cboSource]) Is Null) AND ((([Region].[Region]) Like [Forms]![JobLocator]![cboRegion]) Is Null)) OR (((Source.Source)=[Forms]![JobLocator]![cboSource]) AND ((Region.Region)=[Forms]![JobLocator]![cboRegion]) AND ((([Company].[Company]) Like [Forms]![JobLocator]![cboCompany]) Is Null)) OR (((Region.Region)=[Forms]![JobLocator]![cboRegion]) AND ((([Source].[Source]) Like [Forms]![JobLocator]![cboSource]) Is Null) AND ((([Company].[Company]) Like [Forms]![JobLocator]![cboCompany]) Is Null)) OR (((Source.Source)=[Forms]![JobLocator]![cboSource]) AND ((([Region].[Region]) Like [Forms]![JobLocator]![cboRegion]) Is Null) AND ((([Company].[Company]) Like [Forms]![JobLocator]![cboCompany]) Is Null)) OR (((([Source].[Source]) Like [Forms]![JobLocator]![cboSource]) Is Null) AND ((([Region].[Region]) Like [Forms]![JobLocator]![cboRegion]) Is Null) AND ((([Company].[Company]) Like [Forms]![JobLocator]![cboCompany]) Is Null));
I just thought that the VBA route would be a more robust and flexible solution – and help me learn more in the process.
Reply to Galaxiom,
Thank you. I understand. I will research the properties in more detail.

 

[Mihail]

WHERE (((Source.Source)=[Forms]![JobLocator]![cboSource]) ......

My understanding in SQL area is limited but seems to be what I suggested.

I just thought that the VBA route would be a more robust and flexible solution

I don't know about "robust" but, in my opinion, is far away from "flexible".
I say that because, if you wish to change something in your query, you must manage to change the SQL string. And for me this strings are like the old Mandarin language .
On the other hand, if you design the query (and keep this query, because I think that you already have one and the SQL is copied from this query) the changes should be a little boy game.

 

[Galaxiom]

The VBA is more flexible beause it allows the query to run with any number of combos active.

It is best done as a function that concatenates additional conditions into the Where clause depending on whether they have something selected or not.

A stored query to do this is incredibly complex. I expect that might be what KirRoyle has posted in Post 11. It is too hard to read to be sure.

BTW KirRoyle. Queries and code are more readable if you use a code tags around them.

 

[KirRoyale]

I am trying to persevere with this and have now found and tweaked the following code:

[FONT=Arial][COLOR=#000000]Private Sub cmdOK_Click()[/COLOR][/FONT]
[COLOR=#00b050][FONT=Arial]' Pointer to error handler[/FONT][/COLOR]
[FONT=Arial][COLOR=#000000]   On Error GoTo cmdOK_Click_err[/COLOR][/FONT]
[COLOR=#00b050][FONT=Arial]' Declare variables[/FONT][/COLOR]
[FONT=Arial][COLOR=#000000]   Dim db As DAO.Database[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Dim qdf As DAO.QueryDef[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Dim strSource As String[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Dim strDepartment As String[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Dim strCompany As String[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Dim strSQL As String[/COLOR][/FONT]
[COLOR=#00b050][FONT=Arial]' Identify the database and assign it to the variable[/FONT][/COLOR]
[FONT=Arial][COLOR=#000000]   Set db = CurrentDb[/COLOR][/FONT]
[COLOR=#00b050][FONT=Arial]' Check for the existence of the query, create it if not found,[/FONT][/COLOR]
[COLOR=#00b050][FONT=Arial]' and assign it to the variable[/FONT][/COLOR]
[FONT=Arial][COLOR=#000000]   If Not QueryExists("qryJobQuery") Then[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       Set qdf = db.CreateQueryDef("qryJobQuery")[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Else[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       Set qdf = db.QueryDefs("qryJobQuery")[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   End If[/COLOR][/FONT]
[COLOR=#00b050][FONT=Arial]' Get the values from the combo boxes[/FONT][/COLOR]
[FONT=Arial][COLOR=#000000]   If IsNull(Me.cboSource.Value) Then[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       strSource = " Like '*' "[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Else[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       strSource = "='" & Me.cboSource.Value & "' "[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   End If[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   If IsNull(Me.cboRegion.Value) Then[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       strRegion = " Like '*' "[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Else[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       strRegion = "='" & Me.cboRegion.Value & "' "[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   End If[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   If IsNull(Me.cboCompany.Value) Then[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       strCompany = " Like '*' "[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Else[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       strCompany = "='" & Me.cboCompany.Value & "' "[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   End If[/COLOR][/FONT]
[COLOR=#00b050][FONT=Arial]' Build the SQL string[/FONT][/COLOR]
[FONT=Arial][COLOR=#000000]   strSQL = "SELECT TblApplication.Date, Source.Source, Region.Region, TblApplication.Ref, TblApplication.Position, Company.Company, TblApplication.Direct, Status.Status, TblApplication.Comments " & _[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]            "FROM Status RIGHT JOIN (Source RIGHT JOIN (Region RIGHT JOIN (Company RIGHT JOIN TblApplication ON Company.ID = TblApplication.CompanyID) ON Region.ID = TblApplication.RegionID) ON Source.ID = TblApplication.SourceID) ON Status.ID = TblApplication.StatusID " & _[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]            "WHERE Source.Source" & strSource & _[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]            "AND Region.Region" & strRegion & _[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]            "AND Company.Company" & strCompany & _[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]            "ORDER BY TblApplication.Date,TblApplication.SourceID;"[/COLOR][/FONT]
 
[COLOR=#00b050][FONT=Arial]' Pass the SQL string to the query[/FONT][/COLOR]
[FONT=Arial][COLOR=#000000]   qdf.SQL = strSQL[/COLOR][/FONT]
[COLOR=#00b050][FONT=Arial]' Turn off screen updating[/FONT][/COLOR]
[FONT=Arial][COLOR=#000000]   DoCmd.Echo False[/COLOR][/FONT]
[COLOR=#00b050][FONT=Arial]' Check the state of the query and close it if it is open[/FONT][/COLOR]
[FONT=Arial][COLOR=#000000]   If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryJobQuery") = acObjStateOpen Then[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       DoCmd.Close acQuery, "qryJobQuery"[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   End If[/COLOR][/FONT]
[COLOR=#00b050][FONT=Arial]' Open the query[/FONT][/COLOR]
[FONT=Arial][COLOR=#000000]   DoCmd.OpenQuery "qryJobQuery"[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]cmdOK_Click_exit:[/COLOR][/FONT]
[COLOR=#00b050][FONT=Arial]' Turn on screen updating[/FONT][/COLOR]
[FONT=Arial][COLOR=#000000]   DoCmd.Echo True[/COLOR][/FONT]
[COLOR=#00b050][FONT=Arial]' Clear the object variables[/FONT][/COLOR]
[FONT=Arial][COLOR=#000000]   Set qdf = Nothing[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Set db = Nothing[/COLOR][/FONT]
[COLOR=#000000][FONT=Arial]Exit Sub[/FONT][/COLOR]
[FONT=Arial][COLOR=#000000]cmdOK_Click_err:[/COLOR][/FONT]
[COLOR=#00b050][FONT=Arial]' Handle errors[/FONT][/COLOR]
[FONT=Arial][COLOR=#000000]   MsgBox "An unexpected error has occurred." & _[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       vbCrLf & "Please note of the following details:" & _[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       vbCrLf & "Error Number: " & Err.Number & _[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       vbCrLf & "Description: " & Err.Description _[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       , vbCritical, "Error"[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Resume cmdOK_Click_exit[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]End Sub[/COLOR][/FONT]

Now, I get an error message:
“Compile Error
Sub or Function not defined”
and the “QueryExists” is highlighted in the following lines:

[FONT=Arial][COLOR=#000000]   If Not [B][U]QueryExists[/U][/B]("qryJobQuery") Then[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       Set qdf = db.CreateQueryDef("qryJobQuery")[/COLOR][/FONT]

This may be a simple point but I’m afraid it’s still beyond my knowledge. I can’t see what’s not defined….
If somebody could help, I would be very grateful.

 

[Galaxiom]

QueryExists would be a user defined function in the code sample where you got the rest of the code from.

 

[KirRoyale]

Thank you, found it. It was in a separate module. I’m still a bit fixated on class modules!