how do you create a report from scratch through vba?

I'm not sure this can be done, but I'd like to be able to build a report based on user input on a form and create text boxes and position them assign a control source and run the report.  Can it be done?


Who is Participating?
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
I agree with jkorz,....too much trouble.

Here is a possible solution. You could just create separate reports (different layouts) for each user.  But use the same Record Source for all the Reports.

Then make a "Menu" Form to select the "Layout" they want. The menu could use an Option Group (Frame) and would look something like this:

Choose your Layout:
( ) User 1
( ) User 2
( ) User 3
_____     ________
| OK  |   | Cancel  |
--------   ------------

Name the Reports:                     "rptUser1", "rptUser2", "rptUser3" ,...ect
Name the Form:                         "FrmReportsMenu"
Name the Option Group (Frame): "fraLayout"
Name the OK button:                   "btnOK"
Name the Cancel Button:              "btnCancel"

On the OK Button, the code would look something like this

Private Sub btnOK_Click()
    If fraLayouts = 1 then
        Docmd.OpenReport "rptUser1", acViewPreview
    ElseIf fraLayouts = 2 then
        Docmd.OpenReport "rptUser2", acViewPreview
    ElseIf fraLayouts = 3 then
        Docmd.OpenReport "rptUser3", acViewPreview
    End If
End Sub

The code on the "Cancel button would look something like this:

Private Sub btnCancel_Click()
End Sub

Would this do what you need?
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define "based on user input on a form".
JMorschAuthor Commented:
I would have them input the qty of text boxes needed according to the data they need to print, and the positioning needed, using x and y values, as well as point to the table that contains the info to be printed.  ...  it may be better to use a form instead of a report and just have it print the form.
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

JMorschAuthor Commented:
first, can a report be created and fields be moved/edited through vba?
I may be reading this wrong, but you can create a report that is based on a query of the data in the form and just limit the query results to what's in the form. Obviously if the form data is only temporary, the form will have to remain open when the report runs. If the data entered into the form is going to be stored in a table, then the report could be run any time by allowing the user to define their report criteria (i.e. date range, id#....etc.). Again, I may be mis-reading your question.

JMorschAuthor Commented:
I'd like to be able to modify the layout of the report not just the data.

as in the actual text box x and y values (positioning on the page) as well as the number of visible text boxes.  (to be able to dynamically create a new text box on an existing report would be helpful as well. )

Hope that clears things up a little.
JMorschAuthor Commented:
I just came across a posiblility.  for example, if I had a form with 3 text boxes and one button.  txt box for the x value, txt box for y value, and txt box for number.  button to print report.  

the print report function saves the information to a table and runs a report based on the table.  

the report, in it's design1.format (right event?) event, has a move command on a text box and pull the values from the table (is this possible?)  and move it to those positions, and set the control source for the text box to the third field's value.
jkorzConnect With a Mentor Commented:
what you are talking about could theoretically be done but it would be such a long and difficult process that I can't see any situation where the time you would spend coding it would be less time than it would be to train users in how to create reports with the wizard
JMorschAuthor Commented:
unfortunately to create a new report for each layout i'd need would exceed the 32000 object max in access.  I'm deciding the best way to design a production print shop type job.  the users would enter specifics like how many variables per page and paper size as well as layout.  Using drop down lists for common setups.  i've built this program for our main printer, but we want to expand to all printers, so i can't use the printer language i have been.  I want to probably end up in straight VB6 but I was exploring the access option being that reporting is usually easier and i have a good way of printing the barcodes i need in an access report.
Jeffrey CoachmanMIS LiasonCommented:
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Sounds like a developer's nightmare, creating/destroying controls in design time before deploying it.  I'd either look to another reporting engine (maybe Crystal?), or standardize this enough to create reports that don't dramatically change.

In my opinion, making report items visible or not, subreports that are actually additional reports, and changing the text in labels-textboxes based on hidden fields is quite acceptable, but anything beyond that I'd avoid.

JMorschAuthor Commented:
I appologize for not closing this question, I forgot about it.

The answer to my question I figured out through trial and error, though the suggestions here were helpful.

The following code put on the report itself:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    txt1.Move Me.teststr1, Me.teststr2, 1000, 1000
    txt1.Visible = True
End Sub

Will allow me to move report text boxes prior to print and change the properties, which I could assign from an input form or table previously populated by user input.  

This "Detail_format" event is what I was looking for.

However, this operation proved a little slow for my app (as pointed out above), so I've since moved to straight VB programming and populating a blank form for a "preview" of what will be printed, while building the printed document in the "printer" function. (the "preview" form is less precise in measurements and line sizes than the printer function - due to having to convert to pixels to show on screen)

I will however split the points as suggested, the info was good, though not specifically answering my question.

Jeffrey CoachmanMIS LiasonCommented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.