Report generation help needed in VBA

[rushitshah]

Hello.
I need help regaring report generation with VBA coding.
I want to use two different tables for report generation on If Else statement.
These two tables are basically queries run for different parameters and products. SO I am doing this. It works correctly and generates the query tables, asks for input from user to save report, But the report is empty. It does not print all the record sets from the corrosponding table to the report.
What I am doing wrong?
______________________________________________
Dim rpt As Report
Dim strReportName As String

If IsNull(Me.Product) = True Or Me.Product = "" Then
strSQL = "SELECT t.* " & _ 'this query will generate table
"INTO [" & Me.Parameters & "] " & _
"FROM [" & Me.PCycle & "] t " & _
"WHERE t.parm_nm = '" & Me.Parameters & "';"
DoCmd.RunSQL strSQL
strReportName = InputBox("Enter the report name", "Report Name")
Set rpt = CreateReport
rpt.RecordSource = "SELECT p.* FROM [" & Me.Parameters & "] p"
DoCmd.Save , strReportName
DoCmd.Close , , acSaveYes
DoCmd.OpenReport strReportName, acViewPreview


ElseIf IsNull(Me.Parameters) = True Or Me.Parameters = "" Then
strSQL = "SELECT t.* " & _
"INTO [" & Me.Product & "] " & _
"FROM [" & Me.PCycle & "] t " & _
"WHERE t.infr_itm_nm = '" & Me.Product & "';"
DoCmd.RunSQL strSQL
strReportName = InputBox("Enter the report name", "Report Name")
Set rpt = CreateReport
rpt.RecordSource = "SELECT p.* FROM [" & Me.Product & "] p"
DoCmd.Save , strReportName
DoCmd.Close , , acSaveYes
DoCmd.OpenReport strReportName, acViewPreview

End If

 

[Keizo]

It's not much convincing for me to create a new report every time...
But it's not the most important...

Do you make setting only for the record source when generating a report in design-view mode?
Won't you make setting also for the control source by pasting something like a textbox?

 

[rushitshah]

Thanks Keizo,
But actually I did not get what u r saying?

I am only doing the previous coding in the button click event of my form.

 

[Keizo]

Ok.
Try running this code:

Sub TestReport()
Dim rpt As Report
Set rpt = CreateReport
DoCmd.Restore
End Sub

Then you will see a blank report.

That's the reason for my previous comment.

 

[Keizo]

What I want to say is:
The reason for your blank report is that the result of the record source assigned to the report is EMPTY.

This means that the parameters for the record source assigned to the report are wrong or that the parameters entered do not meet the requirements of extract.

 

[Keizo]

One more thing I cannot ignore is, as I previously said:

: Set rpt = CreateReport

If you in fact put no arguments here, you can only see a report with no controls.
Without controls, that means there's no way to display the result of extract.

 

[rushitshah]

Thanks..
But won't rpt.RecordSource = "SELECT p.* FROM [" & Me.Product & "] p"
statement assigns all the records from the table to record source.

If not then what should I do to display the records of those query tables in to report??
Please help with this.
Thx again..

 

[rushitshah]

Hi Keizo..

rpt.RecordSource = "SELECT p.* FROM [" & Me.Product & "] p"
Set txtReportColumns = CreateReportControl(rpt.Name, acTextBox, , , "infr_itm_nm")

This one puts field infr_itm_nm on the report.

But what I want is to output all the fields of the table on the report, so should I do this for all fieldnames? The thing is that in my code the table can be anything as input, so there are not fixed column names.

Can you give me code for some kind of loop to output all the fields of a particular table without putting specific column names?

Thx..

 

[rushitshah]

I am trying this kind of loop logic.... Me.Parameters is the table name

Dim dbD As DAO.Database
Dim dbF As DAO.Field
For i = 0 To dbD.TableDefs([" & Me.Parameters & "]).Fields
For Each dbF In dbD.TableDefs([" & Me.Parameters & "]).Fields

Set txtReportColumns = CreateReportControl(rpt.Name, acTextBox, , , [" & dbF.Name & "], i*1500)

Next
Next

But it gives and error that

"Microsoft office access can not find field '|' referred to in your expression" at the first For loop.
Have any idea?? Plz Help..

 

[Keizo]

If you will not prepare a report with controls such as textboxes in advance,
you have to assign arguments for each parameter on the generated report
whenever printing.

Do you want to change the layout (number of controls, positioning of them) of your report dynamically every time?

If not, I recommend you prepare a report(s) on which controls are set out in advance, though this will be against the subject you posted...

 

[rushitshah]

yes..
I need to change the layout of report dynamically...Because the table I am getting output from the query statement can be having different field names each time. So need to have some kind of loop logic i put as above. Is there not any other way because I am not going to know the field names of the table, so how can i assign controls to each text boxes as the column names are not known!!

Can you help me out with that logic.