Can you set RecordSource to Null?

[jguscs]

I've noticed that a Report will re-use the value of RecordSource from its previous run.
I want to avoid this by setting the value of RecordSource to Null
when the Reports opens (and have a user enter a different value).
I need to set it to Null because if the user cancels the option to enter a value for RecordSource, I need to do an IsNull check which will close the Report if the value is Null after the user query.

 

[Fizzio]

Are you sure you mean the recordsource? This is normally a table, query or SQL statement thet supplies the report with the records. I suspect you mean a filter or parameter used in the supplying criteria. If you open the report in design mode whilst it is filtered, it will save that filter as default. Delete this from the report properties and it should be restored.
If I'm on the wrong track, get back.

 

[jguscs]

Actually, it seems that a Report's RecordSource is normally saved (like every other Property variable) when you save the Report.
My problem is that I want to have the user select the RecordSource each time the Report is run.
I'm using a modal Form do have the user enter the data.
The form has an OK button and a Cancel button.
If the Cancel button is pressed, I want the modal form to close (done with a DoCmd.Close line) and I want the Report to close (which I can not figure out how to do... the report keeps running from the return point of the DoCmd.OpenForm line).
I figured that by checking the value of the RecordSource after the return point of the DoCmd.OpenForm line, I could see whether the value had been changed in the modal form. If it had not been changed, I would have some code to close the Report. I can not figure out the code. Another DoCmd.Close will close the whole database... I just want to close the Report.

 

[jguscs]

Ah!
It seems a simple "End" will do it!

If RecordSource = "DefaultSource" Then
MsgBox "Focus returned to the report with Default Value"
End
End If

What I did was set my Report's RecordSource to "DefaultValue" (which is not a table I have in my database). Then after the user clicks Cancel on the modal form, the modal form closes and the code resumes in the Report_Open procedure. Then, like I said before, I check to see if RecordSource is still the same as "Default Value" and if it is, ... END!

Now, back to my original question: Is it possible to set the RecordSource of a report to NULL somewhere in the code (it is NOT possible to leave the RecordSource field blank, and hence Null, in the Design Mode of the Report... when you run the report, even before it goes through any code, it gives you an error. SO, can you set RecordSource to Null within the Code? I want to do this (and compare RecordSource to Null) instead of setting RecordSource to "DefaultValue" because feasibly, "DefaultValue" could be the name of a table.

 

[dcx693]

If you want to blank out a report's recordsource, set it to "" , that will make it a zero-length string.

If you want to close a report without saving the changes to it, use this code:
DoCmd.Close acReport, reportname, , acSaveNo

 

[jguscs]

Thanks for the advice, dcx693, but:

RecordSource = ""
does not work...

Private Sub Report_Open(Cancel As Integer)
RecordSource = ""
DoCmd.OpenForm "TableChooser", acNormal, , , acFormPropertySettings, acDialog, ""
If IsNull(RecordSource) Then
DoCmd.Close acReport, Report, , acSaveNo
End If
End Sub

When the Form is "done" doing its thing, and the "code-focus" returns back to the Report, and error message pops up saying that "The expression you entered has a field, control or property name that Microsoft Access can't find. [End] [Debug]"

Also, your code:
DoCmd.Close acReport, Report, , acSaveNo
Gave me an error message upon compilation: "Compile Error: Wrong number of arguments or invalid property assignment."
Do you need parenthases or more or less commas?

 

[WayneRyan]

jguscs,

I don't understand why your report needs a variable
recordsource. The report has a static number of
fields, each bound to a particular source. It is
not variable.

Is it just variable criteria in the query that is
involved here? If so there are ways to address
that.

You can also look up QueryDefs in the Search
facility of this forum.

Just some thoughts,
Wayne

 

[jguscs]

WayneRyan:
My report needs a variable RecordSource because my users will be using the same exact report with different tables of data. (These different tables of data will have the same columns/fields.) Re-using a report for different tables, etc...
And I don't want them to have to go into the design mode of the Report and change the RecordSource there every time they need to change it.
As far as variable criteria in a query... I haven't looked into that yet. I suppose that would be helpful in case the different tables the users want to use in the report have slightly different column names. Then I could use a query to do some checking and comparing and build an appropriately columned "table" from that.

 

[WayneRyan]

jguscs,

Good, then look at QueryDefs, there are several examples
of them in this forum.

Basically, you can dynamically build a SQL statement containing
the table(s), field(s) and criteria you need. You can then
save it in a query.

That way, your report can use the same query all the time,
but the query keeps changing.

Wayne

 

[dcx693]

jguscs, WayneRyan's advice on building SQL strings for dynamically creating queries for the recordsource is ultimately the best way to go for anything more than basic recordsource change, but since you don't seem persuaded:

You can't just use RecordSource="" by itself. Since you are putting this into the Report's open event, use Me.RecordSource=""

The problem with DoCmd.Close acReport, Report, , acSaveNo is that you need to substitute the name of the report you want to close and put it into quotation marks like this:
DoCmd.Close acReport, "name of report to close", , acSaveNo

 

[Fizzio]

Just a curiosity, why do you have many tables with the same fields holding the same type of data? Could you not have had one table with a variable marker to demonstrate the differences in a record?

 

[jguscs]

Fizzio:
The many tables with same fields (and different data) are created at different times. Perhaps monthly. And it is important to keep the tables separated, as they apply to different groupings. But it is also important to be able to apply them to the same report (though not simultaneously). It is also important to be able to retain previously (imported) Tables for future report re-generation.
My only requirement to my users is that their tables must have colum fields that match my template so that the report knows what field to use for what control.
I suppose it would be possible to generate a report from a query of their table that would be able to use operators such as "Like" that would ease the restrictions of column header name standards. Right?

dcx693:
As you know, I'm still pretty new at this and I'm still trying to figure out SQL strings...
Also, even when I used Me.RecordSource="" (instead of RecordSource="") I still got an error. Reports just don't like working without something in the RecordSource.
Also, I did try the {DoCmd.Close acReport, Report, , acSaveNo} code, but alas. I continue to get an error. This time it's "Compile error: wrong number of arguments or invalid property assignment." And yes, I did re-name the report field correctly in quotations. I have a feeling using the "End" command isn't kosher, but it does work. I would like to get the DoCmd.Close command working properly, however.

 

[dcx693]

Regarding your database design, if the fields in the tables won't change, you can easily store all the data within one table. All you need is a field telling you which particular run the data belongs to. For example, March, April, today, yesterday, run 1375, whatever you want. That way you have one recordsource for your report and you just need to set the criteria to pick out the data you need.

I ran my report with the Me.Recordsource="" in the Open event and it worked fine. It produced a blank report, but it ran. I'm using Access 2002, so perhaps that has something to do with the OpenReport command working differently. Try typing in the command and watching the parameters and seeing if they are correct as you type them in.

 

[jguscs]

I suppose it very well could be a difference between Access 2000 and 2002. This is my Report function (that "calls" the Form):

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = ""
DoCmd.OpenForm "TableChooser", acNormal, , , acFormPropertySettings, acDialog, ""
If IsNull(RecordSource) Then
End
End If
End Sub

The report is actually OK with the Me.RecordSource = "" BEFORE the DoCmd.OpenForm "TableChooser", acNormal, , , acFormPropertySettings, acDialog, ""
but AFTER the modal Form has been closed and the code returns to the Report_Open function, it gives me the error and points to the first line of the code in the Report code (which is just some simple conditional control hiding stuff:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Len(Me.AddDate > 0) Then <<---THIS IS WHAT IT POINTS TO.
Me!AddMfg.Visible = True
End If

 

[Fizzio]

Is AddDate the name of the field or the name of the control. Will Not IsNull(me.AddDate) not suffice?

 

[jguscs]

AddDate is the name of the control.
I do like your {Not IsNull(me.AddDate)} code, it seems much more "code-appropriate" than checking to see if the length is zero. I've been having a tough time figuring out how Access (or VB) handles Null.
Thanks for the code tip, Fizzio.