Prompt for name

[kitty77]

I'm using the following code on a form with a command button.
Instead of just opening Report A, I would like to be prompted for the name instead. Is that possible?

Dim strDocName As String
Dim strWhere As String

strDocName = "Report A"

strWhere = "[recordid]=" & Me!recordid
DoCmd.OpenReport strDocName, acViewReport, , strWhere

 

[RonPaii]

Use message box

strDocName = inputbox("Enter Report Name","Report ID:" & me!recordid,"Report A")

A custom form with a combo box listing all the valid reports would be better for the user.

 

[GPGeorge]

I agree with Ron.

It's all about supporting the user.

If you force the user to a) remember the EXACT name of the report, and b) type it correctly, you provide two failure points.

If you allow the user to a) scroll a list of available reports and b) click on the one they want, you eliminate those failure points.

 

[RonPaii]

Long ago I create a lookup form and function to make this type of input easer.
The call would look like the following

Lookup("Report A;Report B;Report C",1,1,"1in","Select Report",True)

It can also take a named query or a query string plus paramiters.

 

[LarryE]

This query will give you a list of all saved reports that do not have "sub" in their name. You can then use this query for a combo box that allows the user to select the report they wish to open.

SELECT DISTINCT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "*Sub*") AND ((MSysObjects.ParentId)=-2147483645))
ORDER BY MSysObjects.Name;

 

[RonPaii]

This query will give you a list of all saved reports that do not have "sub" in their name. You can then use this query for a combo box that allows the user to select the report they wish to open.

SELECT DISTINCT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "*Sub*") AND ((MSysObjects.ParentId)=-2147483645))
ORDER BY MSysObjects.Name;

My lookup function call would look like this with your query.

Lookup("SELECT DISTINCT MSysObjects.Name " & _
       "FROM MSysObjects " & _
       "WHERE (((MSysObjects.Name) Not Like "*Sub*") " & _
       "AND ((MSysObjects.ParentId)=-2147483645)) " & _
       "ORDER BY MSysObjects.Name;", _
       1,1,"1in","Select Report",True)

 

[MarkK]

And with a single table you can omit the table name, like..

SELECT DISTINCT Name FROM MSysObjects 
WHERE ParentId = -2147483645 AND Not (Name Like '*Sub*')
ORDER BY Name;

 

[isladogs]

You also don't need DISTINCT as Access won't let you create two objects of the same type with the same name

FWIW I prefer to filter by Type = -32764 (less to type!), but the outcome is identical to that using ParentID = -2147483645

 

[Ken Sheridan]

The following is the RowSource for a list box in a database of enslaved people on a group of plantations which I and my American partner (she did the research, I designed the app) created for a museum in the USA.

SELECT
    ReportName,
    ReportDescription
FROM
    EnslavedPeopleReportList
ORDER BY
    ReportIndex;

The first column is hidden by setting the ColumnWidths property to zero, so users see the plain English descriptions of the reports rather than cryptic report names. The ReportIndex column is a column of integer data type which determines the order in which the reports are listed. The code to open the selected report references the list box's value.

 

[GPGeorge]

The following is the RowSource for a list box in a database of enslaved people on a group of plantations which I and my American partner (she did the research, I designed the app) created for a museum in the USA.

SELECT
    ReportName,
    ReportDescription
FROM
    EnslavedPeopleReportList
ORDER BY
    ReportIndex;

The first column is hidden by setting the ColumnWidths property to zero, so users see the plain English descriptions of the reports rather than cryptic report names. The ReportIndex column is a column of integer data type which determines the order in which the reports are listed. The code to open the selected report references the list box's value.

I regularly used this approach for client work. Column 1 (index 0) is the Access-friendly name. Column 2 (index 1) is the human-friendly name. Win-Win!

 

[kitty77]

Use message box

strDocName = inputbox("Enter Report Name","Report ID:" & me!recordid,"Report A")

A custom form with a combo box listing all the valid reports would be better for the user.

That was just what I was looking for. Yes, I agree. Having a list would be helpful. Can you show me? Thanks.

 

[Ken Sheridan]

That was just what I was looking for. Yes, I agree. Having a list would be helpful. Can you show me? Thanks.

If you take a look at my reply in Post #9 you'll see that the RowSource for a list box is a query which returns two columns. The first column is the name of the report, the second is a description of the report by which the users can identify the report, and is what the users will see in the list. A third column, Report Index, is used to determine the order of the items in the list. So, you first need to create a table of the relevant reports, with three columns, ReportName, ReportDescription, and ReportIndex. The first two columns are of short text data type, the third is an integer number data type. In the ReportIndex column enter sequential numbers, starting with 1 for the report you want to be first in the list, 2 for the report you want to be second and so on.

Set the BoundColumn property of the list box to 1, its ColumnCount property to 2, and its ColumnWidths property to 0. In the case of the ColumnWidths property Access will automatically insert whatever unit is set for your system. Setting it to zero will hide the first column, so the user will see only the descriptions. When a selection is made in the list box the value of the control will be the name of the report from the hidden column.

Add a command button to the form with code along these lines in its Click event procedure:

Dim strReport As String
strReport = Me.lstReports

DoCmd.OpenReport strReport, View:=acViewPreview

This will open the report in print preview. If you want to open it in another view then change the View argument to the appropriate constant in place of acViewPreview.

 

[RonPaii]

That was just what I was looking for. Yes, I agree. Having a list would be helpful. Can you show me? Thanks.

I posted a sample in code repository. See sample form for examples.