I am a bit out of my depth here. I started a simple accounts program that enters information into a MS Access database, so far so good. As well as enter the data I can view records one at a time and edit or delete them.
Now I need to make a report. My main table has fields holding the following: id, date, category, supplier, amount, notes. Firstly I want to create reports between a user selectable start date and end date. On the report I want to list all the records between these dates, ordered by categories, and show a total of the amounts under each category as well as a final total at the end.
I have been trying to use Data Reports and can generate a report showing all the fields, but so far it shows all records. I have no idea how to add a start date and end date, or how to add in totals.
Thanks for the help. Now I am getting errors "Report width is larger than the paper width". How would I stop this? I have almost nothing in the report at the moment.
I have no idea how to use the group headers/footers. I drag a field into a group header and I get a "Report sections do not match data source" error. If anyone can give me a few hints on using the group headers / footers I would really appreciate it.
You need to look for help on the SHAPE command. I only ever use it as an SQL statement, so if you're using it like that I can help, but if you use the data environment, I'm can't help you.
The shape command, basically allows you create different recordsets within the one recordset eg one for each header, and then one for the detail lines.
I have hunted everywhere for info on the SHAPE command. I haven't been able find anything that I can understand. In the mean time I have been getting my code to make basic reports. I am hoping you can give me a few hints on using the shape command. I have 2 calendars to choose a start date and an end date for the report, then the comand button has this code in it.
Code:
Private Sub cmdShowReport_Click()
Dim Srchflag As Boolean
Dim i As Integer
'Setting Initial data for recordset
rs.Close
rs.Open "Select * from Expenses WHERE Expense_Date>='" & Calendar4.Value & "' AND Expense_Date<='" & Calendar5.Value & "' ORDER BY Category ;", cnAP, adOpenDynamic, adLockOptimistic
Set DataReport1.DataSource = rs.DataSource
With DataReport1
With .Sections("Section1").Controls
For i = 1 To .Count
If TypeOf .Item(i) Is RptTextBox Then
'The datamember should be always blank while creating dynamic data reports
.Item(i).DataMember = ""
.Item(i).DataField = rs.Fields(i - 1).Name
End If
Next i
End With
With .Sections("Section2").Controls
For i = 1 To .Count
If TypeOf .Item(i) Is RptLabel Then
'The datamember should be always blank while creating dynamic data reports
.Item(i).Caption = "Records for period from " & Calendar4.Value & " to " & Calendar5.Value
End If
Next i
End With
End With
If rs.EOF <> True Then
DataReport1.Show
DataReport1.SetFocus
Srchflag = True
Exit Sub
End If
If Srchflag = False Then 'Display msg when search not found
MsgBox "Search Not Found", vbInformation, "Search Result"
End If
End Sub
One of the fields is a Currency amount. What I want to do is put a line underneath each category showing a total value of the category, and then at the bottom put a total of everything. At the moment I have no group headers, my report has 1 RptLabel in the Page Header and 6 unbound RptTextBox's in the Detail Section.
Any hints on how to use the shape command with the above code would be appreciated. Thanks.
Shape APPEND Clause
The shape command APPEND clause appends a column or columns to a Recordset. Often these columns are chapter columns, which refer to a child Recordset.
Syntax
SHAPE [parent-command [[AS] parent-alias]] APPEND column-list
Description
The parts of this clause are as follows:
parent-command
Zero or one of the following (you may omit the parent-command entirely):
A provider command within curly braces ("{}") that returns a Recordset object. The command is issued to the underlying data provider, and its syntax depends on the requirements of that provider. This will typically be the SQL language, although ADO does not require any particular query language.
Another shape command embedded in parentheses.
The TABLE keyword, followed by the name of a table in the data provider. parent-alias
An optional alias that refers to the parent Recordset. column-list
One or more of the following:
An aggregate column.
A calculated column.
A new column created with the NEW clause.
A chapter column. A chapter column definition is enclosed in parentheses ("()"). See syntax below:
SHAPE [parent-command [[AS] parent-alias]]
APPEND (child-recordset [ [[AS] child-alias]
RELATE parent-column TO child-column | PARAMETER param-number, ... ])
[[AS] chapter-alias]
[, ... ] child-recordset
A provider command within curly braces ("{}") that returns a Recordset object. The command is issued to the underlying data provider, and its syntax depends on the requirements of that provider. This will typically be the SQL language, although ADO does not require any particular query language.
Another shape command embedded in parentheses.
The name of an existing shaped Recordset.
The TABLE keyword, followed by the name of a table in the data provider. child-alias
An alias that refers to the child Recordset. parent-column
A column in the Recordset returned by the parent-command. child-column
A column in the Recordset returned by the child-command. param-number
See Operation of Parameterized Commands. chapter-alias
An alias that refers to the chapter column appended to the parent.
Note The "parent-column TO child-column" clause is actually a list, where each relation defined is separated by a comma.
Note The clause after the APPEND keyword is actually a list, where each clause is separated by a comma and defines another column to be appended to the parent.
sorry can't help u with the code...
regards
1st NF - a table should not contain repeating groups.
2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
3rd NF - there should be no dependency between non key fields in same table.
- E. Petroutsos -
I'm going to include code here which produces grouped reports. We have a unit stocking system (a garage has cars on their forecourt for sale, we provide the finance for these cars). The garages supply us with orders, we determine if they have available credit etc, and if so we approve the order. They then have to send us an invoice, which we match to the order. Sometimes they decide not to go ahead with the order and forget to tell us, so we never cancel the order. What this program does, is report on orders that don't have invoices, or on orders where the invoice doesn't match the order amount.
The project has two reports, take a look at the second one (rptOrdersWithoutInvoices), and take a look at the event cmdOrdersNoInvoices_Click from the main menu.
The report produces a detail on each individual franchise (read garage) - this is done via the group section.
Thanks erickwidya for the quote, I did read a lot of that, but it doesn't mean much to me. I have been using a database with VB for about 3 days so far. Try to learn as fast as I can.
Thanks for the sample jp140768, thats exactly what I need. Hopefully I can work out what I need from this.
Sorry to be a pain, when I try to run your code I get an error "Can't find project or library" on this part of the code "Dim objIni As Utilities.clsWinAndIni" What should I do to stop this error?
Sorry the project won't run, it was only meant as an example. To get the project to run I'd have had to send you the database as well as some of the DLLs - definitly can't send you the database.
If you look at the report and the function that I mentioned yesterday, you should see how it works. If not feel free to ask me about it.
* The Best Reasons to Target Windows 8
Learn some of the best reasons why you should seriously consider bringing your Android mobile development expertise to bear on the Windows 8 platform.