Newbee Question

[gemma-the-husky]

try this instead

strReportName = "Nodata1"
DoCmd.openreport strreportname

 

[danbl]

Neither appear to work still showing #ERROR.
I have tried it with referencing the text box and tried including the formula??

 

[vbaInet]

Can we see a stripped down version of your db?

 

[danbl]

Okay ... I think this will come to you?

It is the only Report included.

 

[vbaInet]

As previously suggested, these worked:

=IIf(IsError([Enter Report Period]),Null,[Enter Report Period])

=IIf([HasData], [Enter Report Period], Null)

 

[danbl]

You are right in that the formula works, but what needs to happen is the report period needs be shown whether there is data or not. Suggestions??

 

[vbaInet]

In that case you need to create a pop up form and on that form you would have textboxes in which the user will enter the two criteria.

If the [Enter Report Period] textbox is called txtRptPeriod and the form is called frmInputCriteria, in the query replace [Enter Report Period] with [Forms]![frmInputCritera]![txtRptPeriod]. You can also use that as the control source of the textbox in the report.

That form must remain open so consider hiding it after OK is pressed and also close it when the report is Unloaded or Closed.

 

[danbl]

This process works using the form you suggested .... great.

Is there a way to have a drop down list that shows the all reports in the data base so that an individual could be generated in the same way? What would be the command structure look like if ths can be done?

 

[vbaInet]

Yes you can either do it in code or via a SELECT statement. But if your reports don't have intuitive names then it just wouldn't look nice.

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32764));

 

[danbl]

Okay this is a bit beyond my understanding??
Would this go in the control source fieldof a combo box?
Is MSysObects.Name a reference to where the reports are located in the Access database?
What does MSysObjects.Type refer to? and what is -32764?

 

[vbaInet]

It goes in the row source of a listbox.

 

[boblarson]

Okay this is a bit beyond my understanding??
Would this go in the control source fieldof a combo box?
Is MSysObects.Name a reference to where the reports are located in the Access database?
What does MSysObjects.Type refer to? and what is -32764?

MSysObjects is a system table in an Access database which keeps track of things like tables, queries, forms, and reports.

MSysObjects.Name (name is a field in that table which holds the name of the object)

Type is just that a query, form, report, table, linked table, etc.

And -32764 is the type which means a REPORT type.

 

[danbl]

I placed the exact formula as you indicated and got the following message when I opened the form:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32764)); may not be a valid setting for the row source type property, or there was a compile error in the functiion. ?????

 

[boblarson]

I placed the exact formula as you indicated and got the following message when I opened the form:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32764)); may not be a valid setting for the row source type property, or there was a compile error in the functiion. ?????

That means you put it in the wrong place. You put it in the row source TYPE property which is expecting either

Table/Query

or

Value List

as the exact entry.

Try moving it up one line to the ROW SOURCE property instead.

 

[danbl]

Bob,

I did that and did not get the error message, but there is nothing in the list when I open the form??

 

[boblarson]

Bob,

I did that and did not get the error message, but there is nothing in the list when I open the form??

Do you have any reports in the database?

 

[danbl]

Bob

I will strip down the data base and send it shortly

Thanks

 

[danbl]

Bob,

The form I tried to add the list box to is Date Range Form2

Dan

 

[boblarson]

RowSourceType needs to have Table/Query selected.

 

[danbl]

Okay that works !!

On the command button I want it to print the selected report. So what would I put in the OnClick property?