Dimensioning as a Report - Type mismatch

[ironfelix717]

Greetings,

I am attempting to access vba defined report properties for a specific report.

Dev hut has a good article on setting the object to allow intellisense here:

VBA Programming a Form or Report with Intellisense

How to declare a form or report variable in VBA to gain Intellisense for that object and simplify your coding.

www.devhut.net

While intellisense will work after dimensioning as the specific report module, it will not run when setting the object.

Sub Test()
   Dim rpt                   As Report_Rep_PurchaseOrder

   Set rpt = Reports(Report_Rep_PurchaseOrder).Report 'ERROR HERE Type Mismatch
   Set rpt = Reports![Rep_PurchaseOrder].Report 'also with this syntax
   
   
   rpt.P_PoinvID = "6000"
End Sub

This also is true if the report is open or closed.

Thanks for any help

 

[MajP]

Here is an example where I have a report with a simple myName custom property

Public Sub OpenReport()
  Dim rpt As Report_rpt_All_Municipalities
  DoCmd.OpenReport "rpt_All_Municipalities", acViewPreview
  Set rpt = Reports("rpt_All_Municipalities")
  rpt.MyName = "Test"
  MsgBox rpt.MyName
End Sub

 

[MajP]

So this does show that VBA has limited poymorphism. If you declare rpt as an Access.report you get the properties of the superclass. If you declare rpt as Report_rpt_all_municipalities you get the specific custom properties of the sub class.

 

[ironfelix717]

@MajP

Thanks for your reply and good insight on polymorphic behavior of VBA...

This doesn't solve my issue though. The object will not set. The report is spelled correctly as "Rep_PurchaseOrders" and the full module name being "Report_Rep_PurchaseOrders".

The intellisense is visible and I can see my properties. But does not set in the test routine.

Any ideas?


EDIT: Note, my example sets my variable to the .Report property of the object, and yours does not. But trying without that property still does not allow the object to set.

 

[ironfelix717]

Update:

Found the core issue. I was passing the report object as an index to the Reports collection, therefore it was throwing a type mismatch. Silly me.
Thanks.

Public Sub SomeFunctionName()
    Dim rpt                   As Report_Rep_PurchaseOrder
   
    DoCmd.OpenReport "Rep_PurchaseOrder", acViewPreview
   
   
    Set rpt = Reports("Rep_PurchaseOrder") 'HERE IS THE FIX
   
   
    rpt.P_PoinvID = "6000"
End Sub

Thanks

 

[MajP]

FYI you can do this without docmd or the reports collection. You need to leave a module level variable and make it visible or the report immediately closes when the function ends.

Private rpt  As Report_Rep_PurchaseOrder
Public Sub SomeFunctionName()
   Set rpt = new Report_Rep_PurchaseOrder
   rpt.visible = true
   rpt.P_PoinvID = "6000"
End Su

 

[ironfelix717]

FYI you can do this without docmd or the reports collection. You need to leave a module level variable and make it visible or the report immediately closes when the function ends.

Private rpt  As Report_Rep_PurchaseOrder
Public Sub SomeFunctionName()
   Set rpt = new Report_Rep_PurchaseOrder
   rpt.visible = true
   rpt.P_PoinvID = "6000"
End Su

GREAT INFO!

Opening the report was cumbersome.

I'll play around with it and see what is the easiest/cleanest to implement.


My idea was this...

Dim RPT                   As Report_Rep_PurchaseOrder


    DoCmd.OpenReport "Rep_PurchaseOrder", acViewReport, , , acHidden
  
    Set RPT = Reports("Rep_PurchaseOrder")
    
    
    RPT.RecordSource = "SELECT * FROM POINV_Line WHERE " & _
        "PoinvID = 'INV6001' ORDER BY LineItem;"
        
    RPT.P_PoinvID = "INV6001"
    RPT.LoadReport  ' <========= refreshes the report with properties passed to the report class

 

[MajP]

In vb.net it is real nice because you can declare a variable as a form, then set the properties, then open it. You can not do that in access with any form or report objects. You have to open first. This is probably one of the reasons for openargs in a very limited way. You can pass in values as you open. But it is pretty limited to what you can do. A lot of people will pass in a delimited string and then split it to set multiple properties.

I have to play with reports because off the top of my head I cannot remember what you can modify once you open. For example there is no recordset.

FYI. The second technique I showed allows you to open multiple form or report instances, if you ever need to do this. For example you can open the same form for two employees and compare them. It is not something I do very often, but in some applications it is important.

Microsoft Access tips: Managing Multiple Instances of a Form

How to open more than one instance of a form at once in a Microsoft Access database, and manage the instances independently of each other.

allenbrowne.com

However, I think in Access what you show is still the cleanest.
dim rpt as Report_somereport
docmd.open "someReport"
set rpt = reports("someReport")

 

[ironfelix717]

@MajP

You've answered all my questions that I had, including the ones I didn't ask! Bravo.

As for delimited strings. This is exactly what prompted this question. I have half a dozen reports that take an uncomfortable amount args via OpenArg string. I'm talking functions to just handle passing the args. Then joining them with a delim, then finally passing to the report. This collection of functions made a somewhat easy way to do this with my reports, but finally I sat down last night and it occurred to me "This is ridiculous" and over-engineered.

I wasn't sure if you could set properties prior to opening. That would be nice, but I guess one can always make it hidden first, which is what i am doing. Are we 100% certain that there is no way to set properties before making visible / opening? Maybe a solution, albeit probably overly complex exists. Just curious.

Great points on opening multiple instances. I knew that was possible, as I read that somewhere long ago. But wasn't entirely certain. I will certainly have a use for this some day.

Thanks again.

 

[MajP]

I have half a dozen reports that take an uncomfortable amount args via OpenArg string. I'm talking functions to just handle passing the args. Then joining them with a delim, then finally passing to the report. This collection of functions made a somewhat easy way to do this with my reports, but finally I sat down last night and it occurred to me "This is ridiculous" and over-engineered.

OpenArgs IMO is very rare to be required. The only time I use it if I open the form or report in ACDIALOG. This is different than just Modal. When you use the ACDIALOG argument in the Docmdopenform / report method code execution stops at that point until the form/report is closed. Because of this you cannot set properties of the form/report after opening because code execution stops in the calling code. There are reasons to use ACDIALOG, but a majority of the time there is not. If you do not open ACDIALOG then you can do exactly what you have done. Make it invisible if needed, set all the properties that you can from the calling code, then show the form/report.

I use ACDIALOG in those cases where I open a Form and I do want to wait until something is done in the called form. Then code starts up again where I left off opening the popup form, and I can use some value from the called form once the pop up closes. In this case since it is ACDIALOG I have to pass in some properties values with OpenArgs.

 

[ironfelix717]

@MajP

Yep, you may or may not remember me inquiring some months ago about achieving a 'dialog' form for use in VBA to grab form info and the way I was originally doing it was with arguments and temporary table values. It was absolutely reprehensible and a troubleshooting madness. You provided what you describe above and i've been using that method since. It has cleaned up my applications immensely.

I have several dialog forms and they are wrapped with a wrapper function. The form has a function that returns the value that is needed from the form. The wrapper function returns this value, but simultaneously provides a port for passing VBA parameters, which are then spliced together to the open arg string. Here is an example of how I used your advice:

Public Function ListForm(Source As String, Caption As String, Optional Height As Integer, _
                         Optional ColumnWidths As String, Optional Columns As Integer, _
                         Optional TextSize As Integer, Optional Locked As Boolean = False) As Integer
'SEE FORM FOR SPECIFIC ARG INFO
'SOURCE IS RECORDSET OR STRING OF VALUES (ex:  1A,1B,1C;2A,2B,2C...)
'RECORDSET DEFAULTS COLUMN COUNT UNLESS SPECIFIED
formname = "frmListSelect"
delim = "|"

If IsSQLStatement(Source) Then
   'assume this is sql statement
   args = "rs=" & Source & delim & _
          "ht=" & Height & delim & _
          "cols=" & Columns & delim & _
          "colwids=" & ColumnWidths & delim & _
          "cap=" & Caption & delim & _
          "txtsize=" & TextSize & delim & _
          "Locked=" & Locked
Else
   args = "vals=" & Source & delim & _
          "ht=" & Height & delim & _
          "cols=" & Columns & delim & _
          "colwids=" & ColumnWidths & delim & _
          "cap=" & Caption & delim & _
          "txtsize=" & TextSize & delim & _
          "Locked=" & Locked
End If
'Code execution stops after next line of code
DoCmd.OpenForm formname, , , , acFormEdit, acDialog, args

If CurrentProject.AllForms(formname).IsLoaded Then
  ListForm = Form_frmListSelect.ReturnValue
  DoCmd.Close acForm, formname
Else
    'closed
    ListForm = 0
End If
End Function



'IMPLEMENTATION:'
Private Sub Test()
dim MyValue as integer

MyValue = ListForm(SQL,"Select an item")

If MyValue > 0 then
    msgbox "User selected item: " & MyValue
else
    msgbox "User selected nothing."
end if

End Sub

 

[MajP]

Here is another technique that could simplify what you did since you are setting a lot of properties. Any class can trap another objects events.
1. Do not open the form ACDIALOG
2. This allows you to directly set the properties on the form. No need for open Args.
3. Then you can trap the popup forms Close event and pull any values you want.

So to trap another forms events you define a variable to that object and use withevents.

Private WithEvents FP As Access.Form

Private Sub cmdPopup_Click()
  DoCmd.OpenForm ("frmPopUp")
  Set FP = Forms("frmPopUp")
End Sub

Private Sub FP_Close()
  Me.txtGetFromPopup = FP.txtReturn
End Sub

Once I identify FP as an Access form with Events Intellisense will let you choose its events.