Creating reports using code

[soames]

Wasnt sure where to put this: so here goes:

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
add a header and footer

any ideas??? tried msdn and no luck. 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.

 

[trickg]

How would you do a sort on this report? I don't see it in the code. If you could answer this question for me, it would solve a big problem for me in that I am passing an SQL string to the report for the report's record source and the sorted field can change each time it is run.