Query Datasheet Not Displaying Fields but Showing Record Count (1 Viewer)

[Hayden86]

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.

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:

 

[CJ_London]

possibly because [Start Date & Time] breaks all the rules about field names. It includes spaces and non-alphanumeric characters plus Date and Time are reserved words. In theory the use of square brackets will get round this. But not always

A similar thread with much the same issue was resolved by removing the non-alphanumeric characters and replacing the spaces with underscores. Not saying it will solve your issue, but worth a try.

Not sure why you are needing to alias anyway, since the relevant subform label can have whatever you want as a caption

 

[Hayden86]

possibly because [Start Date & Time] breaks all the rules about field names. It includes spaces and non-alphanumeric characters plus Date and Time are reserved words. In theory, the use of square brackets will get round this. But not always

Thanks for your reply. I have removed all non-alphanumerics and aliases and still nothing. If I replace all fields with

SELECT * ,

it still does the same thing.

Not sure why you are needing to alias anyway, since the relevant subform label can have whatever you want as a caption

Because it's a Navigation Subform. Either I cant edit field names with a VBA Query or i don't know how.

 

[theDBguy]

Hi. Are you able to post a sample copy of your db?

 

[Hayden86]

Hi. Are you able to post a sample copy of your db?

I keep getting a security token error.

 

[CJ_London]

perhaps show your frmdowntime (I presume that is the form in your navigationsubform control) in design view.

And I presume you have not set all columns to hidden

 

[arnelgp]

you are displaying Different Columns on same Form, how was that?
you are looking at the wrong direction.
you use NavigationButton.

create forms for each conditions with those columns (different ones).
on your select case, change the NavigationTargetName to the correct form.
then set the subforms recordsource.