Report generation with VBA code 3

[kosala1981]

I need to create an access report uisng VBA code. i want to be able to create a report name and save it and also create labels and text boxes in the report using the vba code and those fields should come from the table in the database. and data of the table should be displayed in the report under each field in the report as well.

im new to access and vba. and i want to get this job done quickly.someone please please help me on this and it would be really greatfull. i hope there'll be someone to help me........

 

[dhookom]

You don't ask for much do you ;-)
I am a fairly experienced Access developer and quite comfortable with writing VBA. I would not attempt to create a report with VBA.

Do you really have a requirement for this type of functionality? Can you get by with creating a record set that can be easily pushed to Excel for formatting and reporting?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kosala1981]

well my requirement is to generate customizable reports.i've already developed a form having four combo boxes. two of them filled with two tables data and other two filled with field names of tables. so when a user selects these items from the combo boxes then selected items go as parameters to a Cross tab query and created a recordset from that. i've created a temporary table and insert data into that table using that Recordset(Cross tab query). now i want to create a report from that table. i have a code taken from the Internet that would create a report using vba code. but i've tried it but it gives an error saying that "u must be in design view to create controls". so im stuck at this moment.

as u said is it possible to create this report using Excel. please can u help me.....!

 

[dhookom]

There is a sample of several dynamic crosstab reports at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

There is also a ad-hoc query by form applet that allows you to easily push your data to Excel (or several other destinations).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Crowley16]

actually, it's not that difficult to do something simple.

to create a report with code does need design mode, however what you can do is create a blank report, and then format it in the on-open event without going into design mode.

creating controls on the report will require design mode, but you can get around this as well by directly "paiting" the report instead of using controls, e.g. using me.paint...

put this code in the page event of a blank report:

dim idx as integer, idj as integer
dim crx as long, cry as long

for idx = 0 to 5
   for idj = 0 to 5
      me.CurrentX = crx
      me.CurrentY = cry

      me.print "someValue"

      crx = crx + 50
   next idj

   cry = cry + 50

next idx

this is just a demonstration which I've not tested but will give you an idea of how to achieve a rather ugly report with vba.

--------------------
Procrastinate Now!

 

[Crowley16]

p.s. if you look in the reports object, you'll see other methods including line, circle, textwidth, textheight...

I've not played with them myself, but they sound like stuff which allows you to do more complicated formatting of your generated report...

--------------------
Procrastinate Now!

 

[sxschech]

If you don't need fancy graphical report, you could use code to create a text based report like in the old dbase days. Unfortunately haven't done it for years, so don't have an example handy.

 

[FancyPrairie]

I've created wizards that create forms, reports, etc for me and generate the code behind the scenes. To make this work, I take advantage of Microsoft's wizards. These wizards can be called via vba. I believe they are the same wizards Microsoft uses to create forms and reports.

Check out this link

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q148506&