Creating reports using code (1 Viewer)

[soames]

Function abadmin1()

Dim dbs As Database, field_name_all_proj As Variant, field_name_comp_proj As String
Dim rst As Recordset, test As Variant, IntI As Integer


' Return reference to current database.
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("job_codes", dbOpenTable)
field_name_all_proj = rst.GetRows(rst.RecordCount)
For IntI = 0 To UBound(field_name_all_proj, 2)

'creating the report
Dim rpt As report

Dim all_proj_date_lbl As Control, all_proj_jc_lbl As Control, All_proj_comp_date_lbl As Control, All_proj_comp_jc_lbl As Control
Dim intDataX1 As Integer, intDataY1 As Integer, intDataA1 As Integer, intDataB1 As Integer, intDataC1 As Integer, intDataD1 As Integer, intDataE1 As Integer, intDataF1 As Integer

Dim all_proj_date As Control, all_proj_jc As Control, All_proj_comp_date As Control, All_proj_comp_jc As Control, title_header As Control, cal_diff As Control
Dim intDataX As Integer, intDataY As Integer, intDataA As Integer, intDataB As Integer, intDataC As Integer, intDataD As Integer, intDataE As Integer, intDataF As Integer, intDataG As Integer, intDataH As Integer

intDataX = 100
intDataY = 100
intDataA = 2000
intDataB = 100
intDataC = 4000
intDataD = 100
intDataE = 6000
intDataF = 100
intDataG = 8500
intDataH = 100

intDataX1 = 600
intDataY1 = 200
intDataA1 = 3000
intDataB1 = 200
intDataC1 = 5000
intDataD1 = 200
intDataE1 = 6800
intDataF1 = 200


' Return variable of data type Report pointing to new Report object.
Set rpt = CreateReport
' Set properties for new report.
With rpt
.RecordSource = "SELECT DISTINCTROW All_projects.DATE, All_projects." & field_name_all_proj(0, IntI) & ", All_projects_compare.DATE, All_projects_compare." & field_name_all_proj(0, IntI) & " FROM All_projects LEFT JOIN All_projects_compare ON All_projects.DATE = All_projects_compare.DATE;"


'labels
Set all_proj_date_lbl = CreateReportControl(rpt.Name, acLabel, acPageHeader, , "Date Required", intDataX1, intDataY1)
Set all_proj_jc_lbl = CreateReportControl(rpt.Name, acLabel, acPageHeader, , "No. Req,", intDataA1, intDataB1)
Set all_proj_date_lbl = CreateReportControl(rpt.Name, acLabel, acPageHeader, , "Date Available", intDataC1, intDataD1)
Set all_proj_jc_lbl = CreateReportControl(rpt.Name, acLabel, acPageHeader, , "No. Available", intDataE1, intDataF1)
Set title_header = CreateReportControl(rpt.Name, acLabel, acPageHeader, , field_name_all_proj(0, IntI), "0", "0")


'data control boxes
Set all_proj_date = CreateReportControl(rpt.Name, acTextBox, acDetail, , "All_projects.DATE", intDataX, intDataY)
Set all_proj_jc = CreateReportControl(rpt.Name, acTextBox, acDetail, , "All_projects." & field_name_all_proj(0, IntI), intDataA, intDataB)
Set All_proj_comp_date = CreateReportControl(rpt.Name, acTextBox, acDetail, , "All_projects_compare.DATE", intDataC, intDataD)
Set All_proj_comp_jc = CreateReportControl(rpt.Name, acTextBox, acDetail, , "All_projects_compare." & field_name_all_proj(0, IntI), intDataE, intDataF)
Set cal_diff = CreateReportControl(rpt.Name, acTextBox, acDetail, , "=[All_projects_compare." & field_name_all_proj(0, IntI) & "] - [All_projects." & field_name_all_proj(0, IntI) & "]", intDataG, intDataH)

.Caption = "All Projects Report for " & field_name_all_proj(0, IntI) & " grade"
End With
' Restore new report.
DoCmd.Restore

DoCmd.Save acReport, rpt.Name
DoCmd.Close acReport, rpt.Name

DoCmd.Rename field_name_all_proj(0, IntI), acReport, "Report1"
Next IntI
rst.Close
Set dbs = Nothing

End Function

The following is used to creat a report and its controls. I want to be able to:
Increase the number of records on page

Change fonts, colours etc.
add a header and footer

any ideas??? tried msdn and KB, can get the basics but nothing else..eg the .caption, nice and helpful!!. If someone can point me in the right direction of some literature that would be good.

 

[David R]

Posting your questions in more than one Forum can divide the helpful responses you get, as well as annoy some people away from helping you (because they have to read it twice). Try to pick one Forum for each question, and stick with it until the problem mutates enough to require another posting or is solved.

 

[soames]

thanks david but as you can see it is that grey area between vba modules and reports, alas i did not know where to post it as i envisaged that this type of question might live in two areas.

Is there a set of guidelines available about posting and time allowed for responses so that i can cause no other offence for wanting help as it would be terrible to cause such offence again.

[This message has been edited by soames (edited 02-07-2002).]

 

[David R]

Nope, the multiple posting/replying in a new thread is the one that seems to get to most people the quickest.

Another thing to keep in mind is that some people read only from the "Today's Active Topics" screen. Check the datestamp on your post, if the only entry for today is after 4-5 pm then a lot of people may not see it. I try to wait if I have a problem in the afternoon and post it first thing in the morning. You can also post a followup polite "does anyone have any ideas to this?" in a couple days (in the morning, again), to 'bump' your topic up, but don't abuse it.

Sorry if I came down on you hard, I'm by no means a moderator here, I just saw the two new postings right after each other and thought you'd like to know. I agree it's a gray area, and I wish I had the skill to help you. Access Help is less than 'helpful' at times.

Good luck,
David R

 

[Pat Hartman]

You are attempting to replace the functionality of the existing report writer. It is already pretty good. If your users are so sophisticated that they can build their own reports, let them have access to the report builder.

If you are intent on doing this, I suggest that you download the "unprotected" wizards from the Microsoft download site. You will be able to view the code that the Access wizards actually use to build the reports. You may modify the code and incorporate the new versions in your own app.
http://www.microsoft.com/downloads/search.asp?