displaying query by code

[mostuniquename]

I need to display the results of a query on screen when somebody clicks a button on the form. So I put code in the event of the click that looks like this:

Private Sub Command20_Click()
On Error GoTo Err_Command20_Click

Dim stDocName As String

stDocName = "HHC Report"
DoCmd.OpenQuery stDocName, acPreview

Exit_Command20_Click:
Exit Sub

Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click

End Sub

So far, so good, but my problem is that this query is one row with a lot of columns - that creates a lot of pages to look through. Is there a command that would loop it around on the same page, or rotate it clockwise so it's going down the page instead of across?

 

[shudini]

Why don't you change the acViewPreview to acViewNormal so it opens in a spreadsheet with a scrollbar?

 

[mostuniquename]

because

I don't want users to be able to change any values, I just want to show them what's in the query, it's a security issue

 

[pbaldy]

I would create a report based on the query and open that. You'd have a lot more control over the presentation.

 

[mostuniquename]

It would be the same thing as saying acReadOnly, like I said the problem is that I have a lot of columns in this query and just one row, so the row displays going from left to right on multiple pages, which is not the best way to view it

 

[mostuniquename]

How would I create a report from the query? This is the input query, that allows users to create new records, but not edit or delete. It's based on a table, which I change sometimes. So I don't want to create a report and put a bunch of fields/columns in there, and then when I add or delete columns in my table I have to go edit the report. I just want to be able to print this query in its entirety in a readable format.

 

[pbaldy]

Oh; that seems different than this:

I don't want users to be able to change any values, I just want to show them what's in the query, it's a security issue

 

[mostuniquename]

my bad, actually its not an input query, forget about that. its a simple query that shows one row of a big table, and I want to just show it, so that users don't have access to change it, and I wanna do it in a nice format

 

[KeithG]

So why does Paul's suggestion of using a report not suite your needs?

 

[petehilljnr]

Report might not fit needs if the user wants to copy data into Excel or something like that.

My suggestion would be to create a form based on the query and view it in datasheet view, switching Allow Edits, Allow Additions, Allow Deletions all to false. That could be the easiest option.

Regards,
Pete.

 

[petehilljnr]

Oh - but then that doesn't stop you having to alter the form when the table is altered ....

But you could get around that by dynamically changing the form controls ... but that's a lot of guff for a simple need.

How about creating some link to a dummy table on a field that is always in your base table and therefore the query becomes read only?