Problem with QueryDefs

[vgersghost]

Hello:

When the program gets to this line of code ---

Set qdf = LocalDB.QueryDefs("qryFilterQuery")

The program does not update the query definition. The program ends. The query exists, I've checked all the references, all there. The line works okay in a similar program. The qdf is define as Dim qdf As DAO.QueryDef.

Any ideas.
Thank you.

 

[ready4data]

If you have warnings turned off you might not be noticing that you are getting a message that the query already exists. The existing query has to be deleted first.

Scott

 

[WayneRyan]

vg,

The line:

Set qdf = LocalDB.QueryDefs("qryFilterQuery")

does not update the query def. It must be followed by:

qdf.SQL = "..."
qdf.Close

Do you have an error handler? If so what does it do?

Have you run the code with the Debugger?

Wayne

 

[vgersghost]

Thanks for the response.

I have tried various option, but still no luck. This line of code is duplicated in another database with just a few changes for the - strSQL- and works great.
I stop prior to this Set qdf = LocalDB.QueryDefs("qryFilterQuery") to check the strSQl string and it transfer to a query by just pasting from results of ?strsq in Immediate Window. The query works fine. I recieve no error messages. The program just stops.
Here is the code complete.
thank you

Sub PrintReport(Optional lngView As Long = acViewPreview)
On Error GoTo PrintReport_err

Me.Visible = False

'Update Filter RowSource - qryFilterQuery
Dim strSQL As String
Dim intBeginShift As Integer 'Begin Shift ID if 0 set to 0
Dim intEndShift As Integer 'End Shift ID if 0 set to 999
Dim intBeginWC As Integer 'if 0 set to 0
Dim intEndWC As Integer 'if 0 set to 999
Dim dteStart As Date
Dim dteEnd As Date

dteStart = Me.txtStartDate
dteEnd = Me.txtEndDate

If Me.cboShiftID <> 0 Then
intBeginShift = Me.cboShiftID
intEndShift = Me.cboShiftID
Else 'this will show all shifts
intBeginShift = 0
intEndShift = 999
End If

If Me.cboWorkCenterID <> 1 Then
intBeginWC = Me.cboWorkCenterID
intEndWC = Me.cboWorkCenterID
Else 'this will show all workcenters
intBeginWC = 0
intEndWC = 999
End If

strSQL = "SELECT tblShiftLogEntries." & _
"ShiftLogEntryID " & _
"FROM tblShiftLogEntries " & _
"WHERE (((tblShiftLogEntries.ShiftID)>=" & intBeginShift & " " & _
"And (tblShiftLogEntries.ShiftID)<=" & intEndShift & ") AND " & _
"((tblShiftLogEntries.WorkCenterID)>=" & intBeginWC & " " & _
"And (tblShiftLogEntries.WorkCenterID)<=" & intEndWC & ")" & _
" AND ((tblShiftLogEntries.Date) Between " & _
"#" & dteStart & "# And #" & dteEnd & "#) AND ((tblShiftLogEntries.PcsPerHrGoal) Is Not Null));"

Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim fExit As Boolean

Set qdf = LocalDB.QueryDefs("qryFilterQuery")
qdf.SQL = strSQL
Set qdf = Nothing

Set rs = LocalDB.OpenRecordset("qryFilterQuery")
'if no data then exit sub

If (rs.BOF And rs.EOF) Then fExit = True

Set rs = Nothing

If fExit = True Then Exit Sub

'Update Source Query to properly aggregate the data.
Dim strFormat As String
strFormat = Choose(Me.fraAggregateData, "Short Date", "w", "ww", "mm", "yyyy")

strSQL = DLookup("SQL", "tblCharts", "ChartID=" & Me.lstSelectAChart)
strSQL = ReplaceString(strSQL, "Short Date", strFormat)

Set qdf = LocalDB.QueryDefs(Me.lstSelectAChart.Column(3))
qdf.SQL = strSQL
Set qdf = Nothing


'Update the report to the correct rowsource
Dim lngRowSource As Long 'this is the query they selected by ID number
Dim strReportName As String

lngRowSource = Me.lstSelectAChart
strReportName = Me.lstSelectAChart.Column(4)

If UpdateChartRowSource(lngRowSource, strReportName) = False Then
MsgBox "an error occurred when updating rowsource"
End If

'Now open the report and let the report do the rest.
DoCmd.OpenReport strReportName, lngView
DoCmd.Maximize
DoCmd.RunCommand acCmdFitToWindow

PrintReport_Exit:
Exit Sub

PrintReport_err:
GlobalErrorHandler Err.Number, Erl, Err.Description, "Form_frmCharting", "PrintReport", ""
GoTo PrintReport_Exit
End Sub