Simple-Moderate Question: Why isn't QueryDefs Property Working?

[Bill Bisco]

I have a subform which is tied to a query and on this subform there is a button which when pressed is supposed to change the query on this subform.

Here is my code for the button

Private Sub cmdMoveProcessOff_Click()

Dim dbsCurrent As Database
Dim qryName As QueryDef
Dim strSQL As String

Set dbsCurrent = CurrentDb
Set qryName = dbsCurrent.QueryDefs("qryProcessSelectLSSubform")

strSQL = "SELECT tblProcesses.[Process Name], tblProcesses.ProcessSEQ, tblProcesses.StationID, tblStations.[Station Name], tblStations.Side, tblStations.StationSEQ, tblZones.[Zone Name], tblZones.ZoneSEQ, tblProcesses.Time, tblProcesses.ProcessType, tblProcesses.ProcessSubType, tblZones.LineSpeed, tblStations.StationID, tblStations.StationSide, Sum(tblElements.ElementTime) AS SumOfElementTime"
strSQL = strSQL & "FROM tblZones INNER JOIN (tblStations INNER JOIN (tblProcesses INNER JOIN tblElements ON tblProcesses.[Process ID] = tblElements.[Process ID]) ON tblStations.StationID = tblProcesses.StationID) ON tblZones.[Zone ID] = tblStations.[Zone ID]"
strSQL = strSQL & "GROUP BY tblProcesses.[Process Name], tblProcesses.ProcessSEQ, tblProcesses.StationID, tblStations.[Station Name], tblStations.Side, tblStations.StationSEQ, tblZones.[Zone Name], tblZones.ZoneSEQ, tblProcesses.Time, tblProcesses.ProcessType, tblProcesses.ProcessSubType, tblZones.LineSpeed, tblStations.StationID, tblStations.StationSide;"

'qryName.SQL = strSQL
 strSQL = qryName.SQL

End Sub

Now, when I click the button, I don't receive any error messages, and I believe the code has run, but visually I can tell that the Query has not changed. I have tried refreshing the form as well as exiting out and opening it back up. However, the Query SQL has not changed.

Does anyone have any ideas?

Sincerely,
Bill

 

[Joe4]

Two things:

1. You have the wrong line commented out. The line you want is:

qryName.SQL = strSQL

2. In building your SQL code, I see you have it broken down into three section (strSQL). Make sure when you are combining each piece, that you include a space between each, else they will run into each other and cause errors. You can put it at the end of the previous piece, or at the beginning of the current piece, i.e.

strSQL = strSQL & " FROM ...
strSQL = strSQL & " GROUP...

(note the spaces after the quote and before FROM, and after the quote and before GROUP).

To make sure everything looks good, you can do a MsgBox to return what you have built at the end of your code, i.e.

MsgBox strSQL

 

[Bill Bisco]

Dear Joe4,

Your perceptions were spot-on. Thank you so much. Once again, You've helped me a bunch

Sincerely,
Bill