Prompt for name (2 Viewers)

[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!