Show all command button for subform (1 Viewer)

[ili_sophia]

Hi there,

I have a form where the users can select the date and it will display the data in the subform. I would like to have a command button to show all the available records in the subform.

I have attempted a code below. but I understand that from Dmin and Dmax is wrong.

how do I reference a subform?

Private Sub Command151_Click()
Dim task As String
Me.KnittingStartDate = DMin("Date", "[Knitting Summary subform]")
Me.KnittingEndDate = DMax("Date", "[Knitting Summary subform]")
task = "Select * from [Knitting Summary subform] Order by Date]"
Me.subKnitting.Form.RecordSource = task

End Sub

 

[isladogs]

Use the source table or query in your button code instead of the subform

 

[ili_sophia]

I have used the query builder in the subform hence I cannot use the query.
If I use the source table, it will show me the raw table data

 

[isladogs]

Ok. Create a variable strSQL and define as a string.
Set strSQL = the record source for the subform

Then substitute the subform in the code you posted with strSQL

 

[ili_sophia]

they tell me i have a syntax error in my select statement.

strSQL ="SELECT Knitting.Date, Budget.[Knitting Budget Morning (LBS)], Sum([AM - KG]*2.2046) AS [Actual Morning (LBS)], Budget.[Knitting Budget Night (LBS)], Sum([PM - KG]*2.2046) AS [Actual Night (LBS)], Budget.[Knitting Machine Capacity Morning], fnCountKnittingDistinctMachine([Knitting].[Date],"AM") AS [Machine used Morning], Budget.[Knitting Machine Capacity Night], fnCountKnittingDistinctMachine([Knitting].[Date],"PM") AS [Machine used Night]" &;_

"FROM Knitting INNER JOIN Budget ON Knitting.Date = Budget.Date" &;_

"GROUP BY Knitting.Date, Budget.[Knitting Budget Morning (LBS)], Budget.[Knitting Budget Night (LBS)], Budget.[Knitting Machine Capacity Morning], Budget.[Knitting Machine Capacity Night];"

 

[JHB]

Try the below:

    strSQL = "SELECT Knitting.Date, Budget.[Knitting Budget Morning (LBS)], Sum([AM - KG]*2.2046) AS [Actual Morning (LBS)], Budget.[Knitting Budget Night (LBS)], Sum([PM - KG]*2.2046) AS [Actual Night (LBS)], Budget.[Knitting Machine Capacity Morning], fnCountKnittingDistinctMachine([Knitting].[Date],'AM') AS [Machine used Morning], Budget.[Knitting Machine Capacity Night], fnCountKnittingDistinctMachine([Knitting].[Date],'PM') AS [Machine used Night] " & _
    "FROM Knitting INNER JOIN Budget ON Knitting.Date = Budget.Date " & _
    "GROUP BY Knitting.Date, Budget.[Knitting Budget Morning (LBS)], Budget.[Knitting Budget Night (LBS)], Budget.[Knitting Machine Capacity Morning], Budget.[Knitting Machine Capacity Night];"

 

[arnelgp]

here i added toggle button instead. review the code of main form esp those with my comment.