Prompt for name (1 Viewer)

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