Hi, I am having issue displaying results in a navigation subform table. The record source is a dynamic query done in VBA. When the query is run manually it shows results with all requested fields. However, when done in runtime it only shows that it's picking up the records but not the fields.
Strangely it works elsewhere for the same table.:banghead:
Code:
Private Sub navDowntime_Click()
'On Error Resume Next
Dim strSQL As String
Me.Auto_Header0.Visible = True
Me.Auto_Header0.Caption = "Downtime Manager"
'[Forms]![frmZMGRDate].Visible = False
Call closeforms
DoCmd.OpenForm "frmRecord", acNormal
Select Case varSecurityLevel
Case Is < 3
strSQL = "SELECT DISTINCT dbo_tblDowntime.StartDateTime AS [Start Date & Time], dbo_tblDowntime.OperatorName AS Operator, dbo_tblDowntime.ProductionOrderNumber AS [Order Number], dbo_tblDowntime.Minutes, dbo_tblDowntime.Area, dbo_tblDowntime.IssueDescription AS Issue " & _
"FROM dbo_tblDowntime " & _
"WHERE DepartmentName = '" & varDepartment & "' " & _
"AND WorkCentreName = '" & varWorkCenter & " '" & _
"GROUP BY dbo_tblDowntime.StartDateTime, dbo_tblDowntime.OperatorName, dbo_tblDowntime.ProductionOrderNumber, dbo_tblDowntime.Minutes, dbo_tblDowntime.Area, dbo_tblDowntime.Cause, dbo_tblDowntime.IssueDescription"
Case Is = 3
strSQL = "SELECT DISTINCT dbo_tblDowntime.StartDateTime AS [Start Date & Time], dbo_tblDowntime.OperatorName AS Operator, dbo_tblDowntime.ProductionOrderNumber AS [Order Number], dbo_tblDowntime.Minutes, dbo_tblDowntime.Area, dbo_tblDowntime.IssueDescription AS Issue " & _
"FROM dbo_tblDowntime " & _
"WHERE DepartmentName = '" & varDepartment & "'" & _
"GROUP BY dbo_tblDowntime.StartDateTime, dbo_tblDowntime.OperatorName, dbo_tblDowntime.ProductionOrderNumber, dbo_tblDowntime.Minutes, dbo_tblDowntime.Area, dbo_tblDowntime.Cause, dbo_tblDowntime.IssueDescription"
Case 4 Or 5
strSQL = "SELECT DISTINCT dbo_tblDowntime.StartDateTime AS [Start Date & Time], dbo_tblDowntime.OperatorName AS Operator, dbo_tblDowntime.ProductionOrderNumber AS [Order Number], dbo_tblDowntime.Minutes, dbo_tblDowntime.Area, dbo_tblDowntime.IssueDescription AS Issue " & _
"FROM dbo_tblDowntime " & _
"GROUP BY dbo_tblDowntime.StartDateTime, dbo_tblDowntime.OperatorName, dbo_tblDowntime.ProductionOrderNumber, dbo_tblDowntime.Minutes, dbo_tblDowntime.Area, dbo_tblDowntime.Cause, dbo_tblDowntime.IssueDescription"
End Select
'[Forms]![frmDowntime].Form.RecordSource = strSQL
[Forms]![NavigationForm]![NavigationSubform].Form.RecordSource = strSQL
'Me.Refresh
'On Error GoTo 0
End Sub
Strangely it works elsewhere for the same table.:banghead:
Last edited: