note: I use "VB" and "VBA" interchangeably to specifically reference Access's Visual Basic

I know how to create/run queries using VBA, but I am not as familiar with reports.

I have a form that uses 15-20 fields, using VBA I can dynamically create and run a query. I was wondering if it was possible to use this temp. query to also create a report (from inside VB).

I am not accustomed to using report functions in VB. The most I've done is use VB to open pre-designed (saved) reports in the db, so I dont know if creating a dynamic report is possible.


If you have any example or code, or anything I can look up, I'd be much obliged.

I'm getting a lot of views, but no responses. Was I silly to ask such a thing? Any reasonable response is better than no response.

Hi Modest,

Yes, it is possible to create a report using VBA. I
don't think it would be worth the extreme amount of
effort required though.

I would suggest creating a report that contained
ALL of your information. Then you can use VBA to
programmatically make some of your unneeded controls
invisible.

The latter effort would be about twenty times easier
than trying to create the whole report in code.

Wayne

You can change the reports recordsource in the code behind the report, if that's what your after?

But creating the whole report, not easy...maybe you could create a set of reports and then code could pick the one most suitable to the query you create.

Well the Query is run using a form. Currently there are 15-20 fields and perhaps the vision to add more.

Wayne,
If I make a predefined report, and the user only selects 3 fields, there will be a lot of wasted space/paper.

Ryan,
That goes to say, if I also make several reports and the user chooses all the fields, many reports will be made.

In my head I have an idea to create a report, loop through the fields and set their width on the report using the window.width(). Would anyone know how I could at least get started and where I could read about it?


I know there are programs out there similar to Crystal Reports that do just this, but I want something basic, and free. :) Thus I must do it myself.


Thank you all for your responses

have you thought about exporting to excel??

sounds like it could be more suitable, as the basic case would be to fill an array and then shove array on to a sheet.

This array has the column headers, the values and even formula if needed.
obviously this can be dynamic as well.

I'm sorry, I probably didn't explain myself well (I'm from United State :) )

My reference to Crystal Reports isn't that I need any sort of charts or graphs, I was just trying to point out that I need something dynamic or "on the go" that is adaptable to the user.

I don't think excel would be the optimum choice. If I had to export, I think Ms Word would be better. The report is pretty much going to be the same as the query, just in an easier to read format. The users will also be printing off the report.

Right now, I have them pressing ctrl+p to print off the queries (I don't see a need to add a print button -- just wasted space, and wasted effort).


I'm not going to be with this company much longer and would like to leave them in good hands. If I gave them a dynamic report, I think their life would be easier, and they wouldn't have to contact anyone to make any adjustments.

I suggested excel because its the closest match to the access report. Word would be a pain because again you'd run into the same problem as access, in that the number of fields change.

With Excel you could have a template with a lot of the formatting done, and export as much or as little data to it, and as you can do all this from access it can just be a single click.

I would still rather this be done all in Access. If not for the experience of taking on a new project, I would like to waste my time and create a new dynamic report in Access if someone could point me down the right path =)

I would still rather this be done all in Access. If not for the experience of taking on a new project, I would like to waste my time and create a new dynamic report in Access if someone could point me down the right path =)

You must be a masochist :) I am no expert but I have always found the Access report writer to be almost as frustrating as the England football teams World Cup Performances.

I agree with cable 100% that using precreated Excel Templates for loads of numbers or Word Templates for fancy looking invoices etc and, VBA/ADO or ODBC to export data to be a way simplar and more controlable process than fiddling about drawing little boxes that never seem to fit the page, page settings that seem to have some sort of random function built in each time you open it. etc.

I think that Microsoft have just ignored the Access Report Writer in development, concentrating more on integrating office apps instead. It is just way easier to produce reports in Word and Excel as they have a far richer features for displaying and printing data than the Access report writer. Access reports are like data access pages, something that just never seems to work properly.

I have never tried to create a dynamic access report but from my experiences of static reports in Access I wish you good luck.

Regards

Jon

Thank you, and I appreciate your suggestions... but that still leaves me with wanting to do this in Access, and no one telling me how :o

I guess the moderators can delete this thread, because there was no answer :-/

I have just been involved in creating reports dynamically on the fly. I loop thru the variables and add them and their labels, set their width, alignment depending on their type, add an image, sort all on the fly. If you're interested still I can post my code although it may not all be relevant to you depending on your experience level

I would very much like to see your code, or if you could post an example database, that might even be better.

Ok I have not got much time so I am just dumping the code with a few words of explanation. My field names are listed in an external ini accessable using function profilegetitem (do a search on forum)

[report1]
Output=Preview;Print
Caption=Summary List
orientation=landscape
text0=lname
text1=iname
text2=cause
text3=ddate
text4=value
sortOrder=ddate

I also have a table which lists all the field names against their labels and types

Now for the code. I will attempt to remove anything irrelevant!

Private Sub cmdReport1_Click()
Dim rpt As Report
Dim txtReport As String
Dim txtOrientation As String
appPath = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
txtReport = Me.ComboReports
txtOrientation = ProfileGetItem(txtReport, "orientation", "", appPath & "config\control.ini")
Dim iNow As Integer
Dim letterNow As String
Dim tempNow As String


For iNow = 1 To Len(CStr(Now)) + 1
Do Until iNow = Len(CStr(Now)) + 1
letterNow = Mid(CStr(Now), iNow, 1)
If ((letterNow = ":") Or (letterNow = "/") Or (letterNow = " ")) Then
Else
tempNow = tempNow + letterNow
End If
iNow = iNow + 1
Loop
Next iNow
tempNow = appPath & "config\cdms" & tempNow & Chr(46) & "csv"

If ObjectExists("report", txtReport) Then
If CurrentProject.AllReports(txtReport).IsLoaded Then
DoCmd.Close acReport, txtReport, acSaveNo
Else
End If

DoCmd.DeleteObject acReport, txtReport

Else
End If


'Open report in design view to write properties to objects

Application.Echo False

Set rpt = CreateReport
'this saves it with a name -txtreport is variable
DoCmd.Save , txtReport
DoCmd.OpenReport txtReport, acDesign
'The next few lines don't work in office 2000 only 2003

' With Reports(txtReport).Printer
' If txtOrientation = "portrait" Then
'.Orientation = acPRORPortrait
'ElseIf txtOrientation = "landscape" Then
'.Orientation = acPRORLandscape
'End If

'End With






Const DM_LEGAL = 5
Const DM_PORTRAIT = 1
Const DM_LANDSCAPE = 2
Dim RetVal As Variant
DoCmd.SetWarnings False



If txtOrientation = "portrait" Then

RetVal = CheckCustomPage(txtReport, 28.75 * 21.25, DM_PORTRAIT)
Reports(txtReport).Width = 12240
Else


RetVal = CheckCustomPage(txtReport, 28.75 * 21.25, DM_LANDSCAPE)
Reports(txtReport).Width = 15200

End If
DoCmd.SetWarnings True

Dim ReportWidth As Integer


ReportWidth = Reports(txtReport).Width

'set recordsource
Reports(txtReport).RecordSource = "temp"



Dim i As Integer


Dim TextBox As Control
Dim txtLabel As String
Dim ControlCount As Integer

txtLabel = "dummy"
'assuming you have no more than 20 fields
i = 0
Do While i < 21



txtLabel = ProfileGetItem(txtReport, "text" & i, "", appPath & "config\control.ini")
If txtLabel <> "" Then
ControlCount = ControlCount + 1
Set TextBox = CreateReportControl(txtReport, acTextBox, acDetail, , txtLabel, i * 900, 0, 900, 200)
'deals with dates stored as serial number
If (DLookup("field3", "control", "[field1]='" & txtLabel & "'") = "date") Then
TextBox.ControlSource = "=format(" & txtLabel & ",""dd/mm/yyyy hh:mm:ss"")"
End If
TextBox.Name = "txtbox" & ControlCount
If (DLookup("field3", "control", "[field1]='" & txtLabel & "'") = "date") Or (DLookup("field3", "control", "[field1]='" & txtLabel & "'") = "double") Then
TextBox.TextAlign = 1
Else
End If
'creates the labels in the pageheader
Set TextBox = CreateReportControl(txtReport, acLabel, acPageHeader, , DLookup("field2", "control", "[field1]='" & txtLabel & "'"), i * 900, 700, 400, 200)
TextBox.Name = "label" & ControlCount
i = i + 1

Loop

Set TextBox = CreateReportControl(txtReport, acLabel, acPageHeader, , , 2000, 200, 4000, 700)
TextBox.Name = "LabelCaption"
TextBox.Caption = ProfileGetItem("general", "Progname", "", appPath & "config\control.ini") & " " & Me.ComboReports.Column(1)
Set TextBox = CreateReportControl(txtReport, acTextBox, acPageFooter, , , 2000, 200, 4000, 700)
TextBox.Name = "LabelFooter"
TextBox.ControlSource = "=date()"

ReportWidth = Reports(txtReport).Width
Reports(txtReport).Detail.Height = 200

i = 1
Do Until i = ControlCount + 1
Dim Ctl As String
Ctl = "txtbox" & i
Reports(txtReport)(Ctl).Width = ((ReportWidth) / (ControlCount)) - 200
Reports(txtReport)(Ctl).Left = (((ReportWidth) / (ControlCount)) - 200) * (i - 1)


Ctl = "label" & i
Reports(txtReport)(Ctl).FontWeight = 600
Reports(txtReport)(Ctl).FontUnderline = True
Reports(txtReport)(Ctl).Width = ((ReportWidth) / (ControlCount)) - 200
Reports(txtReport)(Ctl).Left = (((ReportWidth) / (ControlCount)) - 200) * (i - 1)
i = i + 1
Debug.Print Reports(txtReport)(Ctl).Left
Debug.Print Reports(txtReport)(Ctl).Width
Loop



Dim varGroupLevel As Variant

Dim txtSort As String
txtSort = ProfileGetItem(txtReport, "sortorder", "", appPath & "config\control.ini")

Dim Letter As String
Dim LongSource As String
Dim tempString As String

For i = 1 To Len(txtSort) + 1
Do Until i = Len(txtSort) + 1
Letter = Mid(txtSort, i, 1)
If Letter = ";" Then
varGroupLevel = CreateGroupLevel(txtReport, tempString, False, False)

tempString = ""
Else
tempString = tempString + Letter
End If
i = i + 1
Loop
Next i
'note the false values at the end of next line - allows sorting without a groupheader etc
varGroupLevel = CreateGroupLevel(txtReport, tempString, False, False)

' This sets height of header/footer sections.
'Reports(txtReport).Section(acGroupLevel1Header).H eight = 0
'This makes header/footer sections invisible
'Reports(txtReport).Section(acGroupLevel1Header).V isible = False
Set TextBox = CreateReportControl(txtReport, acImage, acPageHeader, , , , , 600, 600)
TextBox.Picture = appPath & ProfileGetItem("general", "splash", "none", appPath & "config\control.ini")
TextBox.PictureType = 1

TextBox.SizeMode = 1


'Reports(txtReport).Section(acGroupLevel1Footer).H eight = 0
Reports(txtReport).Caption = ProfileGetItem(txtReport, "caption", "", appPath & "\config\control.ini")


'Close design view without prompting to save changes

DoCmd.Close acReport, txtReport, acSaveYes
Application.Echo True
DoCmd.SetWarnings False

'Open finished report in preview view
If Me.ComboOutput = "preview" Then

DoCmd.OpenReport txtReport, acPreview

DoCmd.RunCommand acCmdZoom100

DoCmd.Maximize
ElseIf Me.ComboOutput = "Print" Then
DoCmd.OpenReport txtReport, acViewNormal
End If

Exit_Close_Click:
Exit Sub

Err_cmd_Click:
MsgBox Err.Description
Resume Exit_Close_Click

DoCmd.SetWarnings True



End Sub

still haven't had any time to look into this.... still becomming acquainted with my new job ---- just letting you know i'm still grateful

Hi Happy YN,

I just come accross this thread today. Some time ago i had posted a sample db here:
DynamicXtabRpt (http://www.access-programmers.co.uk/forums/showthread.php?t=62587)

It's a dynamic report but there are still some limitations. Is it possible that you could use VB codes to conditionally alter the print orientation and number of colomns when required?

Peter