Need help with user permissions

[karinski]

I have a form that allows users to select criteria for a report. One of
the controls on the form is a multi-select list box. Using the MS
Knowledge Base, I wrote my code to pull the items from the list and
make a querydef for the report. Everything runs great for me but if I
log on as one of my users, I get the "You do not have the necessary
permissions to use the "myquery" object" error. I have given the users
modify permission on this query. The saved query is a RWOP query. I
even tried giving the users administer permissions on the query and it
still will not run. Can anyone tell me what I am doing wrong?

After building the SQL string I run the following:

Set DB = CurrentDb()
Set Q = DB.QueryDefs("myquery")
Q.SQL = strSQL
Q.Close
Set Q = Nothing
Set DB = Nothing

DoCmd.OpenReport "myreport", acViewPreview

[MGFoster]

karinski wrote:

I have a form that allows users to select criteria for a report. One of
the controls on the form is a multi-select list box. Using the MS
Knowledge Base, I wrote my code to pull the items from the list and
make a querydef for the report. Everything runs great for me but if I
log on as one of my users, I get the "You do not have the necessary
permissions to use the "myquery" object" error. I have given the users
modify permission on this query. The saved query is a RWOP query. I
even tried giving the users administer permissions on the query and it
still will not run. Can anyone tell me what I am doing wrong?

After building the SQL string I run the following:

Set DB = CurrentDb()
Set Q = DB.QueryDefs("myquery")
Q.SQL = strSQL
Q.Close
Set Q = Nothing
Set DB = Nothing

DoCmd.OpenReport "myreport", acViewPreview

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Instead of changing the query you could use the Report's Filter property
to get the records you want. Use the report's On Open evetn. E.g.:

' get the selected items from the list box
strListBoxItems = getSelectedListItems(Me!MyListBox)

' filter on the selected items
Me.Filter = "column_name In (" & strListBoxItems & ")"
Me.FilterOn = True

' continue w/ the report
...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQherPIechKqOuFEgEQKTmQCgqqyuCeBZoiKzXWge8Ma63O +OplcAniWY
j8FpaXQMHokywr+frcSlHCo+
=Z6Sq
-----END PGP SIGNATURE-----

[karinski]

Thanks for getting back so quickly. Your solution would work for me but
the report has a sub report that runs indirectly to the same query. The
report and sub report are NOT linked. The sub report does a "group by"
record count on one of the report's fields. I didn't use the group by
header/footer in the report because I wanted to show all the counts
together at the end of the report. In other words "5/28/05 = 5 items
6/10/05 = 2 items 7/20/05 = 3 items". When I tried to update the
sub report's filter in code I got a cannot change record source after
the report is open error.

[Captain Nemo]

Hi

After building the SQL string I run the following:

Set DB = CurrentDb()
Set Q = DB.QueryDefs("myquery")
Q.SQL = strSQL

I don't understand why you're using an existing query and then setting the
query's SQL property. If you're going to do this, you might as well create
a temporary QueryDef.

Set Q = DB.CreateQueryDef("")
Q.SQL = strSQL

Try it - you'll like it!

[Captain Nemo]

I now see that I misread your post, and didn't realise you were using this
query as the datasource for a report. That complicates matters.

Have you tried adding WITH OWNERACCESS OPTION to the end of your query?

I must confess I've often had problems with Access's security model and
found things didn't seem to work as they should. Sometimes you find that
creating a new database and copying and pasting the objects into it solves
the problem.

If all else fails you could try:

creating a new querydef (with the appropriate name)
adding it to the querydefs collection
opening the report
deleting the querydef in the report's OnClose event

At what point do you get the error message? When you try to open the report
or sooner?

[karinski]

Hey Captain Nemo,

The last part of your message did the trick!

creating a new querydef (with the appropriate name)
adding it to the querydefs collection
opening the report
deleting the querydef in the report's OnClose event

At first I couldn't understand why the user could delete the query
when they couldn't even modify it before, but I guess because it is
created after they have logged in, they are the "owner" of the query.

Works like a charm now! I wish I had posted sooner. I was afraid I
would get told to "read the help files".

Thanks everyone.