Module to Modify Report Controls Based on Form (1 Viewer)

[Adam.Furtado]

Custom Report Fields based on Combo Boxes

Hello,

I am trying to create a customizable report that would allow the users to choose fields. I have a pretty common code that I found online and adapted it, but it fails on the first SetReportControl function.

The error reads: Run-time error 2465: Application-defined or object-defined error

Could it be something as simple as an incorrect reference? I have checked multiple times, but I am stumped.

Option Compare Database
Option Explicit

Sub MakeReport()
'On Error GoTo Err_MakeReport

'Open report in design view to write properties to objects
 DoCmd.OpenReport "rptProductListIndTest", acDesign

'Read combo box selections and use subroutine to set report object properties
[COLOR="Red"] SetReportControls Forms!frmCustomReport.cboCurrentPrice.Value, Reports!rptProductListIndTest.lblCurrentPrice, Reports!rptProductListIndTest.txtCurrentPrice[/COLOR]
 
 SetReportControls Forms!frmCustomReport.cboHistory1.Value, Reports!rptProductListIndTest.lblHistory1, Reports!rptProductListIndTest.txtHistory1
 
 SetReportControls Forms!frmCustomReport.cboHistory2.Value, Reports!rptProductListIndTest.lblHistory2, Reports!rptProductListIndTest.txtHistory2
 
 SetReportControls Forms!frmCustomReport.cboHistory3.Value, Reports!rptProductListIndTest.lblHistory3, Reports!rptProductListIndTest.txtHistory3

'Close design view without prompting to save changes
 DoCmd.Close acReport, "rptProductListIndTest", acSaveYes

'Open finished report in preview view
 DoCmd.OpenReport "rptProductListIndTest", acPreview
Exit_MakeReport:
 Exit Sub
Err_MakeReport:

 MsgBox Err.Description
 Resume Exit_MakeReport

End Sub
Sub SetReportControls(varFieldName As Variant, conLabel As Control, conTextBox As Control)
'Check if selection is 'null'
 If IsNull(varFieldName) Then 'Blank out the relevant objects
 conLabel.Caption = " "
 conTextBox.ControlSource = ""
 Else 'Write the selected field name to the appropriate objects
 conLabel.Caption = varFieldName
 conTextBox.ControlSource = varFieldName
 End If
End Sub

This is a trial run, in the end I need to be able to open a report, then adjust the Report controls within 1 or more subreports inside the main report. That is a battle for another day.

Any assistance would be greatly appreciated.

 

[Adam.Furtado]

Hello,

I am trying to create a customizable report that would allow the users to choose fields in a form via combo boxes. I have a pretty common code that I found online and adapted it, but it fails on the first SetReportControl function.

The error reads: Run-time error 2465: Application-defined or object-defined error

I believe there is something wrong in the subroutine, as it is an older code.

Also, my field names are dates (2/9/2015), would that affect the code?

Option Compare Database
Option Explicit

Sub MakeReport()
'On Error GoTo Err_MakeReport

'Open report in design view to write properties to objects
 DoCmd.OpenReport "rptProductListIndTest", acDesign

'Read combo box selections and use subroutine to set report object properties
[COLOR="Red"] SetReportControls Forms!frmCustomReport.cboCurrentPrice.Value, Reports!rptProductListIndTest.lblCurrentPrice, Reports!rptProductListIndTest.txtCurrentPrice[/COLOR]
 
 SetReportControls Forms!frmCustomReport.cboHistory1.Value, Reports!rptProductListIndTest.lblHistory1, Reports!rptProductListIndTest.txtHistory1
 
 SetReportControls Forms!frmCustomReport.cboHistory2.Value, Reports!rptProductListIndTest.lblHistory2, Reports!rptProductListIndTest.txtHistory2
 
 SetReportControls Forms!frmCustomReport.cboHistory3.Value, Reports!rptProductListIndTest.lblHistory3, Reports!rptProductListIndTest.txtHistory3

'Close design view without prompting to save changes
 DoCmd.Close acReport, "rptProductListIndTest", acSaveYes

'Open finished report in preview view
 DoCmd.OpenReport "rptProductListIndTest", acPreview
Exit_MakeReport:
 Exit Sub
Err_MakeReport:

 MsgBox Err.Description
 Resume Exit_MakeReport

End Sub
Sub SetReportControls(varFieldName As Variant, conLabel As Control, conTextBox As Control)
'Check if selection is 'null'
 If IsNull(varFieldName) Then 'Blank out the relevant objects
 conLabel.Caption = " "
 conTextBox.ControlSource = ""
 Else 'Write the selected field name to the appropriate objects
 conLabel.Caption = varFieldName
 conTextBox.ControlSource = varFieldName
 End If
End Sub

This is a trial run, in the end I need to be able to open a report, then adjust the Report controls within 1 or more subreports inside the main report. That is a battle for another day.

Any assistance would be greatly appreciated.

 

[jdraw]

I note that you have 10 posts in this forum.
Do you have experience with Access? Reports? or vba?

Creating reports via vba is not trivial nor an introductory undertaking.
What exactly do you mean

Also, my field names are dates (2/9/2015), would that affect the code?

I think you should do some intro tutorials and get some familiarity with relational database -tables - relationships and design- and concepts - Normalization; as well as some intro to Access -- tables/Queries/forms/Reports -- naming conventions...

Tell us more about yourself, and the requirement/issue/opportunity leading you to designing reports in Access using vba (and procedures you found online).

 

[gemma-the-husky]

very likely. I doubt if a / character is legal in a field/control name

I can't imagine why you would be getting into anything as complex as what you seem to be trying to do, though, as JDarw suggested.

 

[Adam.Furtado]

I'm familiar with Access, this is my second database I am developing, although this is much more complicated than the first. I have used VBA, but I am still definitely a beginner.


Jdraw: I wouldnt need to create the report, only modify the data source of the controls.

The current field names (with "/") work fine in all other forms and reports I have been creating, but I was wondering if it was not being communicated properly in VBA.

I am developing a pricing system for a complicated market. I would like to have the option to create a custom price sheet for customers who fall outside of our normal pricing terms. The query that I am pulling from has the following (relevant) fields:
- Customer
- Product
- 2/9/2015
- 1/12/2015
- 12/8/2015
etc...

This is really only for showing price change history. Most customers move at the same time, but some will have price changes that stray from the normal.

I have read about others using this code with success, so I assume there is something different about my data that is causing the error.

 

[CJ_London]

Re: Custom Report Fields based on Combo Boxes

Just throwing in some thoughts

Have you tried stepping through the code?

looks like you are trying to assign a numeric value to a caption

If you are running this from the frmCustomReport form then

Forms!frmCustomReport.cboCurrentPrice.Value

would be simply

cboCurrentPrice

or
me.cboCurrentPrice

without testing, I don't know if you can pass report controls in this way

are the two report fields on the main report or a subreport?

 

[MarkK]

The parameter varFieldName is expecting a string that is the name of the field. I would change the routine as follows . . .

Sub SetReportControls(FieldName As String, txt As Textbox)
   dim lbl as Access.Label
   set lbl = txt.controls(0) [COLOR="Green"]'the label will be the first child control of the textbox[/COLOR]
   
   lbl.Caption = FieldName
   txt.ControlSource = FieldName
End Sub

. . . and then call the routine with . . .

SetReportControls "CurrentPrice", Reports!rptProductListIndTest.txtCurrentPrice

 

[pbaldy]

FYI, duplicate threads merged since they both had replies.

 

[MarkK]

Thanks Paul, I hadn't noticed. I don't usually contribute to double posted threads on principle.

 

[Adam.Furtado]

I apologize for the double posting, I meant to delete the first one. I did not realize it was frowned upon. My bad.

MarkK,

I have tried to tweak the code as you described, but still ran into the same "Application or Object Defined error".

Is this somewhat parallel to your train of thought?

Option Compare Database


Option Explicit

Sub MakeReport()
'On Error GoTo Err_MakeReport

'Open report in design view to write properties to objects
 DoCmd.OpenReport "rptProductListIndTest", acDesign

 SetReportControls "CurrentPrice", Reports!rptProductListIndTest.txtCurrentPrice
 
 SetReportControls "History1", Reports!rptProductListIndTest.txtHistory1
 
 SetReportControls "History2", Reports!rptProductListIndTest.txtHistory2
 
 SetReportControls "History3", Reports!rptProductListIndTest.txtHistory3

'Close design view without prompting to save changes
 DoCmd.Close acReport, "rptProductListIndTest", acSaveYes

'Open finished report in preview view
 DoCmd.OpenReport "rptProductListIndTest", acPreview
Exit_MakeReport:
 Exit Sub
Err_MakeReport:

 MsgBox Err.Description
 Resume Exit_MakeReport

End Sub

Sub SetReportControls(FieldName As String, txt As TextBox)
   Dim lbl As Access.Label
   Set lbl = txt.Controls(0) 'the label will be the first child control of the textbox
   
   lbl.Caption = FieldName
   txt.ControlSource = FieldName
End Sub

Am i missing something? For example, I would assume there should be a reference to the combo box that is feeding the field?

Thanks and sorry again.

 

[pbaldy]

I apologize for the double posting, I meant to delete the first one. I did not realize it was frowned upon. My bad.

No worries. It's not a big deal, but it is frowned upon. The question may get answered on one thread, and then somebody sees the other and invests time and energy trying to solve it, not realizing it's already been solved.

 

[MarkK]

What line causes the error?

 

[Adam.Furtado]

It's the same line as before: the one that calls the subroutine. I stepped through each one, and they all failed the same way.

 

[MarkK]

I don't know, what kind of module is this in?

But more than that, you don't need to do this in design view, and I would get the report to do it. Like, you reach into the report using code outside the report. My philosophy is always to make objects able--as much as possible--to fend for themselves. Consider something like this in a standard module . . .

Function GetReportFields As Variant
[COLOR="Green"]'   Returns an array of field names for the report to use to determine
'   what fields to show
'   For more flexibility, this routine might read these names from an open form
[/COLOR]   GetReportFields = Split("CurrentPrice History1 History2 History3")
End Function

. . . and then when the report opens, it consumes that list . . .

Private Sub Report_Open(Cancel As Integer)
   dim vField

   for each vField in GetReportFields
      with me.controls("txt" & vField)
         .controlsource = vField
         .controls(0).caption = vField
      end with
   next
End Sub

So that's the bare essentials. The field list might be generated outside the report so we expose that list as a function in a standard module. Then, when the report opens, it looks for that list, processes it, and adjusts itself based on what it finds there. Whats still missing is the clear instructions about how to supply the report with that list, if it fails, but this is the core functionality.

 

[pbaldy]

I agree with Mark's thoughts and will add another. If you have users with the runtime version of Access, your code will break because they can't go into design view. The same may be true if you distribute an mde/accde, though I can't recall for sure.

 

[Adam.Furtado]

Hello gentlemen, my apologies. I have been travelling and havent had the opportunity to continue this project.

MarkK, I would like to go ahead and tweak your code for my specific task, but I do not know how/where to include the functions.

I have fields that I can select from combo boxes (ex. [2/9/2015], [1/12/2015], etc.). How would I grab that information so "txtCurrentPrice" would be [2/9/2015], etc?

Also, I ran your code for kicks and giggles and it failed at this line:

Private Sub Report_Open(Cancel As Integer)
   Dim vField

   For Each vField In GetReportFields
      [COLOR="Red"]With Me.Controls("txt" & vField)[/COLOR]
         .ControlSource = vField
         .Controls(0).Caption = vField
      End With
   Next
End Sub

 

[MarkK]

Oh, I see, you want to pass in a list of name/value pairs, don't you?

ControlName1/ControlSource1
ControlName2/ControlSource2

Is that right?

 

[Adam.Furtado]

To be honest, I looked into name/value pairs and I can't be sure if that's what I need.

I've attached the bare essentials of the operation. The form 'frmChooseFields' contains the combo boxes that allow the user to select which fields to display in the report 'rptProductListIndTest'. Your code is in there now, behind Report_Open as well as the module 'Custom Report'. Does this help to explain what I'm trying to do?

 

[MarkK]

You need to back up and do some research on "database normalization," and maybe Entity-Relational Diagram, or ERD, or ERP. Just looking at the data you have here, you should have a Customer table, Product table, and then some other table for all these dated amounts, but probably an Order table, and an OrderDetail table. How you are doing this is not really going to bear fruit in this form. You are conceiving of a database table like it's a spreadsheet and they are not the same kind of thing. Everything will seem way easier if the tables are designed according to rules of normalization.
Hope this helps,

 

[Adam.Furtado]

I suppose I should've explained, this file^ is part of a much bigger relational database. I only included the relevant objects for this function, to keep it under the size limit. Customer Name is the primary key in the Customer List, because we have multiple locations and many customers have more than one customer number.

I could've seperated the tblProductDatabase into multiple tables, but that would make for more of a hassle when updating pricing.

This is a pricing tool, so we do not record any orders, but there are some data entry functions for recording notes.