Report List Query

[psyc0tic1]

I have a form with 2 comboboxes and a command button.

Combobox 1 is irrelevant for this question.
Combobox 2 is for pulling a list of all reports.
Command button is for generating the report from the choices in the comboboxes.

Combobox 2 has this imbedded query in the Row Source:

SELECT [MSysObjects].[Name] FROM MsysObjects WHERE (Left$([Name],1)<>"~") And ([MSysObjects].[Type])=-32764 ORDER BY [MSysObjects].[Name];

However there are some reports I do not want everyone to see and are for admin use only.

Anybody know how to modify this query to be able to exclude reports? And should I create a proper query rather than using it embedded? I embedded it because that is what the instructions said to do when I found the code.

 

[June7]

Could structure report names to include some indicator they are for admin only. Then use that as filter criteria for the query.

Embedded or not is personal preference. I would embed.

 

[isladogs]

The easiest way to hide these reports from the list would be to rename them to start with a ~ which your query specifically excludes.

Embedded SQL statements are fine as row sources

 

[psyc0tic1]

The easiest way to hide these reports from the list would be to rename them to start with a ~ which your query specifically excludes.

Embedded macros are fine if you're happy with them.
Personally I avoid them completely as it makes it much harder having to look in different places when troublshooting

I agree on the embedding... I have changed that since I started this thread.

Putting a tilde in front of the report name didn't hide it... it displayed with the tilde in the report name in the combobox.

 

[psyc0tic1]

I tried changing the "~" to "Admin" and then renaming a report with Admin at the beginning and it still displayed all reports including the one with Admin at the beginning. I guess I have no idea what the tilde is doing.

 

[Gasman]

I went a different way.
I used code like you have, but that was to select the Forms/Queries/Reports in the system for use in a form.
This form frmObject wrote to a table tblObject and in that I held data as to what type of Object it was, name of object, description, and also an optional where clause.

If you had a table like that you could also have a field that defines access level.?

HTH

 

[Gasman]

Something wrong with your #4 post?
I've just used that select statement and then reran after putting a ~ in front of a report name. That report name did not show up on second run. ?

 

[psyc0tic1]

I attached a screenshot of it so you can see it didn't work.

 

[isladogs]

I'm also surprised as I use exactly that method to hide database objects I don't want to appear in listboxes.

It will also work preceding with Admin providing you change code to
Left([Name],5)<>'Admin'

I changed my post before you replied when I realised you weren't talking about embedded macros but embedded sql statements.

 

[Gasman]

Well here is mine

So I'd check your code.

SELECT [MSysObjects].[Name] FROM MsysObjects WHERE (Left$([Name],1)<>"~") And ([MSysObjects].[Type])=-32764 ORDER BY [MSysObjects].[Name];

 

[psyc0tic1]

That worked... at first I thought it didn't because after saving the query and renaming the report... the report showed up in the combobox with the changed name but then I closed it and re-opened and the renamed report was gone.

Thanks Gasman, Colin and June7

 

[Pat Hartman]

psyc0tic1,
I know it seems redundant but it is oh so much easier for us if every time you make a change to the SQL, you post the version that you ran that didn't work. We can only guess at what typo exists in what you ran.

 

[psyc0tic1]

psyc0tic1,
I know it seems redundant but it is oh so much easier for us if every time you make a change to the SQL, you post the version that you ran that didn't work. We can only guess at what typo exists in what you ran.

Sorry about that... my post was intended to follow Colins' but another post snuck in between.

I used

Left([Name],5)<>'Admin'

and it worked after closing and re-opening the form. I cannot say the others didn't work too because I was not closing and re-opening the form (I thought I tried that but maybe not).

However I realize that now the admin reports are no longer in the list for admins. Being this is a query... how would I make it so people with AccessLvl = 1 can see all reports?

This is the complete current query:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left([Name],5))<>'Admin') AND ((MsysObjects.Type)=-32764))
ORDER BY MsysObjects.Name DESC;

 

[psyc0tic1]

If it is easier I can make another report generator page only accessible from the admin console page.

 

[isladogs]

Just use an if ... else...end if condition depending on user level.
In each section, write sql for the listbox row source.
Something like this (air code):

If AccessLvl = 1 Then
Me.listboxname.RowSource ="SELECT MsysObjects.Name FROM MsysObjects WHERE ((MsysObjects.Type)=-32764) ORDER BY MsysObjects.Name DESC;
Else
Me.listboxname.RowSource="SELECT MsysObjects.Name FROM MsysObjects WHERE (((Left([Name],5))<>'Admin') AND ((MsysObjects.Type)=-32764)) ORDER BY MsysObjects.Name DESC;

 

[Gasman]

You would need to contsruct the sql for the query depending on who the user is. leave out the test for Admin if an Admin user.?

Sorry about that... my post was intended to follow Colins' but another post snuck in between.

I used

Left([Name],5)<>'Admin'

and it worked after closing and re-opening the form. I cannot say the others didn't work too because I was not closing and re-opening the form (I thought I tried that but maybe not).

However I realize that now the admin reports are no longer in the list for admins. Being this is a query... how would I make it so people with AccessLvl = 1 can see all reports?

This is the complete current query:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left([Name],5))<>'Admin') AND ((MsysObjects.Type)=-32764))
ORDER BY MsysObjects.Name DESC;

 

[psyc0tic1]

Just use an if ... else...end if condition depending on user level.
In each section, write sql for the listbox row source.
Something like this (air code):

If AccessLvl = 1 Then
Me.listboxname.RowSource ="SELECT MsysObjects.Name FROM MsysObjects WHERE ((MsysObjects.Type)=-32764) ORDER BY MsysObjects.Name DESC;
Else
Me.listboxname.RowSource="SELECT MsysObjects.Name FROM MsysObjects WHERE (((Left([Name],5))<>'Admin') AND ((MsysObjects.Type)=-32764)) ORDER BY MsysObjects.Name DESC;

I like the looks of this but it has me a little stumped. I tried using this in the query but I believe this belongs in the VBA code so I added it in the code for the form like this (code in red):

Option Compare Database
Option Explicit

Private Sub cmdGenerateReport_Click()

    On Error GoTo Error
    If Me.cboReports & "" <> "" Then
        DoCmd.OpenReport cboReports, acViewReport, , IIf(IsNull(Me.cboFacility), "", "Facility='" & Me.cboFacility & "'")
    Else
     MsgBox ("You Must First Select a Report To Print!")
     cboReports.SetFocus
   End If
   
   [COLOR="Red"]If Credentials.AccessLvlID = 1 Then
    Me.cboReports.RowSource = "SELECT MsysObjects.Name FROM MsysObjects WHERE ((MsysObjects.Type)=-32764) ORDER BY MsysObjects.Name DESC;"
   Else
    Me.cboReports.RowSource = "SELECT MsysObjects.Name FROM MsysObjects WHERE (((Left([Name],5))<>'Admin') AND ((MsysObjects.Type)=-32764)) ORDER BY MsysObjects.Name DESC;"
   End If[/color]

   cboReports = ""
   Exit Sub
Error:
   
End Sub

but I get an empty combobox. It is now an unbound combobox because it no longer has a row source of the query

I know I did this wrong.

 

[psyc0tic1]

Ok so I moved the code to the before update event like this:

Option Compare Database
Option Explicit

Private Sub cboReports_BeforeUpdate(Cancel As Integer)
   If Credentials.AccessLvlID = 1 Then
    Me.cboReports.RowSource = "SELECT MsysObjects.Name FROM MsysObjects WHERE ((MsysObjects.Type)=-32764) ORDER BY MsysObjects.Name DESC;"
   Else
    Me.cboReports.RowSource = "SELECT MsysObjects.Name FROM MsysObjects WHERE (((Left([Name],5))<>'Admin') AND ((MsysObjects.Type)=-32764)) ORDER BY MsysObjects.Name DESC;"
   End If
End Sub

Private Sub cmdGenerateReport_Click()

    On Error GoTo Error
    If Me.cboReports & "" <> "" Then
        DoCmd.OpenReport cboReports, acViewReport, , IIf(IsNull(Me.cboFacility), "", "Facility='" & Me.cboFacility & "'")
    Else
     MsgBox ("You Must First Select a Report To Print!")
     cboReports.SetFocus
   End If

   cboReports = ""
   Exit Sub
Error:
   
End Sub

but that didn't work either.

 

[Minty]

This would normally need to go into the form load event surely?

Set once at the form load based on the user.

 

[psyc0tic1]

This would normally need to go into the form load event surely?

Set once at the form load based on the user.

Ok that makes sense.

I tried putting it in the form load like this but it didn't show the "Admin" prefixed reports whether you were logged in AccessLvl = 1 or not

Private Sub Form_Load()
   If Credentials.AccessLvlID = 1 Then
    Me.cboReports.RowSource = "SELECT MsysObjects.Name FROM MsysObjects WHERE ((MsysObjects.Type)=-32764) ORDER BY MsysObjects.Name DESC;"
   Else
    Me.cboReports.RowSource = "SELECT MsysObjects.Name FROM MsysObjects WHERE (((Left([Name],5))<>'Admin') AND ((MsysObjects.Type)=-32764)) ORDER BY MsysObjects.Name DESC;"
   End If
End Sub

so I tried this:

Private Sub Form_Load()
   [COLOR="Red"]If Credentials.AccessLvlID = 1 Then
    Me.cboReports.RowSource = "SELECT MsysObjects.Name FROM MsysObjects WHERE (((Left([Name],5))<>'~') AND ((MsysObjects.Type)=-32764) ORDER BY MsysObjects.Name DESC;"[/color]
   Else
    Me.cboReports.RowSource = "SELECT MsysObjects.Name FROM MsysObjects WHERE (((Left([Name],5))<>'Admin') AND ((MsysObjects.Type)=-32764)) ORDER BY MsysObjects.Name DESC;"
   End If
End Sub

but I get a missing operator error in the IF code which should have displayed all reports without the tilde in the title.