Code:
Public Sub GrandTotal()
Dim total_lines As Integer
Dim num_lines_to_create As Integer
Dim source As String
Dim grand_total As Double
'calculate the total service
grand_total = DSum("[Extended]", "CreateQuoteQry")
'get total records from query/table
total_lines = DCount("1", "CreateQuoteQry")
'calculate extra lines to pad
num_lines_to_create = LINES_PER_PAGE - (total_lines Mod LINES_PER_PAGE) - 1
'create Union query
source = "SELECT [SelectBox],[TypeName],[Quantity], [Manufacturer], [Part], [Comments], [Unit Price], [Extended], [JobID], [LampType], [NumberOfLamps], [LED] FROM CreateQuoteQry" & _
"UNION ALL " & _
"SELECT TOP " & num_lines_to_create & " 99991,NULL, NULL, NULL, NULL FROM CreateQuoteQry " & _
"UNION ALL " & _
"SELECT TOP 1 99999, NULL, NULL, 'Grand Total', " & grand_total & " FROM CreateQuoteQry;"
Reports![Quoterpt].Report.RecordSource = source
End Sub
The above code was supplied by the courtesy of @arnelgp in an example DB he posted in another one of my threads.
I have never used a union query before, so am unsure if I modified it correctly. CreateQuoteQry is the control source behind my report. When I try using this code, I get a syntax error due to issue with the FROM clause when opening the report. When I comment out this module, the report opens. So the query CreateQuoteQry itself is fine.
Did I maybe need to increase the amount of times it calls NULL? I could certainly use the help of someone who better understands what the union is doing.
Last edited: