OpenArg not passing to one report

[Monterey_Manzer]

Hi All,



I am having the most vexing of problems. My code is executing inconsistently. I have a form I am designed for record audits. It randomly selects individuals and returns counts for various types of records associated with the individual. I am trying to pass arguments to a set of reports, restricting their recordsets to records for a particular individual. In all cases I am using an ID number as an OpenArgs argument and passing it in using the same code. However, when i step through code execution I find something rather perplexing. In some reports, when the code execution moves from the Form object to the individual record object, the Me.OpenArgs value is shown as null despite the fact that the ID variable passed in had a value at the time the DoCmd.OpenReport action was executed. The most frustrating thing about this is, it works with some reports using the exact same code. I would appreciate any insights anyone might have.

Some code snippets are below:

Private Sub btnInt1_Click()  '<- This one works just fine and the ID value passes effectively to the "Internship-RT SS"  report
Dim ID As String
    ID = Me.ID1
    DoCmd.OpenReport "Internship-RT SS", acViewReport, , , , ID
End Sub
Private Sub btnEmp1_Click() '<- This ID value won't effectively pass to the "rptEmp" report, despite ID having a real value when the DoCmd.OpenReport method is called
Dim ID As String
    ID = Me.ID1
    DoCmd.OpenReport "rptEmp", acViewReport, , , , ID
End Sub

And before you ask why I created an ID variable that I pass to the OpenArgs property rather than just using the Me.ID1 reference, the "Internship-RT SS" report didn't open properly until I did that. Anyway, if anyone has had experience trouble shooting this type of error I would love some suggestions.

Thanks!
Ryan

[rpeare]

I have encountered all sorts of weird problems with the various report views in access 2007+. Have you tried using just the print preview instead of the report view

Docmd.openreport "rptEmp", acViewPreview, ....

Or alternately forcing your report to run on a query where the Criteria is determined in the query running the report and not as an openargs variable?

[Monterey_Manzer]

Okay, glad I'm not the only one experiencing odd problems.

Unfortunately I need the controls on the reports to be active since one of the features I am deploying is an Edit button that will open the relevant form for that specific record. I am trying to ensure that I can use the same report objects that are normally used to view all records of each type. I was thinking of having the On Open event set the report record source using a [Forms]![frmAudit].[Me.ID1] reference but I would still need a Boolean value to indicate whether the report should use this approach or the standard code that runs when the report is opened outside of the record auditing process. Does VBA have something similar to a try...catch statement? That way I wouldn't trip an error when I try to reference a non-instantiated control if I open these reports without having the audit form open.

[Missinglinq]

You've shown us your code for passing the OpenArgs, but where is the code, in the Reports, that actually uses the OpenArgs after they are passed?

Linq ;0)>

[Monterey_Manzer]

Oh, good call Linq!

Here is the basics of what I'm doing:

strSQL = SELECT SQL statement that collects all the fields for this report from various tables

If IsNull(Me.OpenArgs) = True Then 
 ' A bunch of code that allows users to filter records by various criteria.  Essentially building a WHERE statement
' that will be appended on to the strSQL statement and set as the report's record source.

Else
    Me.RecordSource = strSQL & " WHERE tableName.ID = " & Me.OpenArgs 
    Exit Sub
End If

The filtering code (not shown) has been a part of these reports since their construction and work just fine. What is more, there is no difference in the OnOpen code for the "rptEmp" and "Internship-RT SS" reports except for the SQL statement. The fields filtered are the same and both have the above listed code used to determine whether to go through those steps or open simply based on the OpenArgs value passed in. Let me know if this elucidates anything for you. Otherwise I'm thinking I will use a set of On Error statements to guide execution of the OnOpen events for the reports I'm concerned with. It seems like sloppy coding to me, since I'm not explicitly controlling the code execution, but at this point I will be happy to have something that works and I can go back and suss out the preferred method at a later time.

Thanks for the back and forth thus far, it has actually helped me organize my thinking about this problem.

[rpeare]

Any chance you could upload a sample database, I don't think I've ever tried to open a form to edit a record by clicking a button on a report.

[Monterey_Manzer]

Sorry to say, rpeare, but I've had no luck with compressing this thing. The edit feature from the report actually works like a charm! I've got a report of monthly payments to individuals that includes multiple payment types and all the pertinent information that determines those amounts. My users were quite happy to be able to use the report to screen their records using the report and then only opening the form for the record they want to edit. It's pretty simple. I just put the PK (usually an autonumber) for the main table in the report but make it invisible since it's not useful info for my users. The edit button simply performs the DoCmd.OpenForm event with the PK as the OpenArgs argument. The form already has an SQL statement written in to it's On Open event and I just add the "WHERE ID = " & Me.OpenArgs to the string and set that as the Form recordsource. By making my form small and setting the Pop Up feature to Yes, it makes editing large record sets easier as they can have both the Form and Report open at the same time and seeing all pertinent records displayed has allowed my users to identify and catch systemic mistakes. I don't know if that is considered poor database structure/management but it really works nicely for my application.

[ItsMe]

Maybe use a debug.print me .openargs line in the On Load event for the report.

[Missinglinq]

What is the Datatype of the ID Field being passed in the Reports that don't work properly?

Linq :0)>

[Xipooo]

The ID value you are passing is a string type, but you are comparing it to the ID field on your table which is most likely a numeric data type. I think that's where Linq is noticing a problem too.

[Monterey_Manzer]

There are a couple different ID types in this case. The ID used to identify a specific person is a string. This is the ID type passed to the reports and the field, referenced as tableName.ID in my last code segment, is also a string.

However, this still misses the point. As I stated initially, I have stepped through every line of code execution from the button click event to the report's On Open event. While the code execution is still in the On_Click event the ID variable has a value that is included in the DoCmd.OpenReport statement. However, as soon as code execution moves to the report On Open event, the Me.OpenArgs property has a value of "Null". The most infuriating part of this is that it is not consistent across all reports I attempt to open in this manner. Stepping through code execution and monitoring all variable values, I have found some reports where the Me.OpenArgs property has the correct value (the ID string) and the report recordsource filters appropriately. I added the ID = Me.ID1 step to the code execution for the On_Click event and one of the reports that was not passing the OpenArgs value started working but the others remain obstinate in their disobedience.

[Monterey_Manzer]

Okay, I seem to have solved the issue although I wish I knew more about the root cause. However, someone else might find this information useful so I'll mention it here. The code I've shown above now executes perfectly with the OpenArgs values passing to the reports (and any forms that are being opened from that report). What did I change you ask? I selected the Compact and Repair Database option from the Tools section of the Database Tools menu. I am guessing that, since I compile my dBase after any significant coding changes, the multiple versions of my code that get generated must have been conflicting somewhere. Lesson learned, if your code is correct and still won't execute, try Compact & Repair.

Thanks for all the comments everybody! Just having the discussion is a very useful process and helps me reason through my code.

[John_G]

Hi -

You said that the ID is a string, but in this statement:

Me.RecordSource = strSQL & " WHERE tableName.ID = " & Me.OpenArgs

you are not treating it as a string. When strings are used in WHERE cluases and SQL, they must be in quotes. Try this:

Me.RecordSource = strSQL & " WHERE tableName.ID = '" & Me.OpenArgs & "'"

It might be that if ID is a number formatted as a string that Access can sometimes figure it out - but you cannot count on it.

HTH

John