Monthly Expenditure Report

[grubnz]

Hi,

I have created this report for monthly expenditure the only problem is once the report is run it only fills nearly half a page what I would lilke to happen if possible is the format to fill the one page. It will never go over the one page. But it will have to adjust according to how much data is in that month.

The attached PDF Rpt_ExpenditureRSLFrm is the report created in Access 2010 and the Monthly_Expenditure_Receipts_2012-February was created in Excel.

I hope it makes sense thank you for anyone who can assist me.

 

[grubnz]

Hi,

I have gone a little bit further, but I am wondering if this can be done so it is more dynamic.

As the report sometimes can have 3 lines of data to say a 11 lines. So by doing it the way I have in the pdf files below it leaves a gap on the page.

I require the page to have 26 lines everytime not mattering if there are only 3 lines of data or a 11.

As always your time and assistance is greatly appreciated.

 

[JHB]

If you could put the result from the query (+ an empty field) into a temporary table, then you could use a recordset to fill in a number or other value until the number of rows are equal to the number of line you wanted as below.
To the left the table, to the right the report.

 

[grubnz]

Hi JHB,

Thanking you once again for your reply.

It is exactly what I want to achieve.

I have attached a .xls of what I have done so far.

2nd and 3rd screen print is the query with the extra empty field I called this linenumber1.
1st screen print is after I have created the temporary table.

I have been doing been doing some research on creating a recordset and unfortunately I am a bit confused.

Do you mind showing how you created the recordset you used in your example.

Thanking you and have a wonderful day.

 

[JHB]

Here it is, remember to set your name for "TempTable" and "LNumber" and "QueryCreateTable"

Private Sub ViewReport_Click()
  Dim dbs As Database, rst As Recordset, x As Integer, LineNumber As Integer, tblLoop
  
  LineNumber = 25
  
  Set dbs = CurrentDb
  For Each tblLoop In dbs.TableDefs
    If tblLoop.Name = "TempTable" Then
      dbs.TableDefs.Delete ("TempTable")
      Exit For
    End If
  Next tblLoop
  dbs.Execute ("QueryCreateTable")
  Set rst = dbs.OpenRecordset("TempTable")
  For x = 1 To LineNumber
    If rst.EOF Then
      rst.AddNew
      rst![LNumber] = x
      rst.Update
    Else
      rst.Edit
      rst![LNumber] = x
      rst.Update
      rst.MoveNext
    End If
  Next x

End Sub

 

[grubnz]

Hi JHB.

Thank you for your reply, I have finally got the code working. So that it opens up the report.

It took me a little while as I had never done this with a query before. Thank you very much for suppling the code I wouldn't have known where to start.

What I did notice when I tried it when I would click on the command button and open the report it would be fine then I would close it and open it again and the data could be at the bottom of the form as shown below in the .pdf files. This is not happening all the time but I was wondering if in the code that LNumber could be Ascending to try and stop this. As I tried the sort order in the report and got a weird response that I wasn't expecting as in another .pdf below.

If this is not too much trouble the next step I tried was to Open the frm_DateRange then put two date parameters into the form and once I clicked on the command button to run the query and code it wouldn’t work as it came up with few parameters as per the .xls file it looks like it is picking up the dates inputed in the form but of course only showing the one therefore coming up with the error.

The approach I have tried maybe totally wrong so any assistance in the right direction is appreciated as I am losing the plot searching on the web I don’t think I am putting in the right search criteria.

I have attached part of the database so you can see what I have done.

Have a wonderful weekend.

 

[JHB]

1. You've created LNumber as a String, it has to be a number, (here one of you problem started), text and numbers are not sorted in the same way.
2. You are really mixing up the fields name. If you look at the table it showed as "Date", if you set the table in design view, you see the real name is "MainDate", but where you try to open a recordset, you call it "DateRange".
You have to keep the tongue straight in your mouth, when you refrere to a field name, (don't call it what you remember the name was, but really check it).
I've attach you corrected database.

You also, about the weekend.

 

[grubnz]

Hi JHB,
It works beautifully. Thank you soooo much for your time and patience I have learnt a lot.
All the best

 

[JHB]

You're welcome.