Report List Query

[psyc0tic1]

I changed it to this and now it works

Private Sub Form_Load()
   If Credentials.AccessLvlID = 1 Then
     Me.cboReports.RowSource = "SELECT [MSysObjects].[Name] FROM MsysObjects WHERE (Left$([Name],1)<>'~') And ([MSysObjects].[Type])=-32764 ORDER BY [MSysObjects].[Name];"
   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

 

[Pat Hartman]

but that didn't work either.

Does that mean you got a compile error, a run time error, or the wrong result?

What is Credentials.AccessLvlID referring to? You can't just reference a table/query this way. Do you have an object named "Credentials" that has methods and properties?

 

[psyc0tic1]

Does that mean you got a compile error, a run time error, or the wrong result?

I got a blank combobox

What is Credentials.AccessLvlID referring to? You can't just reference a table/query this way. Do you have an object named "Credentials" that has methods and properties?

Credentials is a module that deals with the AccessLvlID of the users who log in and is the basis of what they can and cannot see in the database like forms, fields, labels etc and now reports. In my database when you want to restrict or allow access to areas or items the code will start with Credentials.UserName or Credentials.AccessLvlID

For reference this is my Credentials module

Option Compare Database
Option Explicit

Public UserName As String
Public UserId As Integer
Public AccessLvlID As Integer
Public Function GetCurrentUser() As Integer
    GetCurrentUser = UserId
End Function

Public Function GetCurrentUserFacility() As String
    GetCurrentUserFacility = DLookup("FacilityNumber", "tbl_users", "ID = " & Credentials.UserId)
End Function

Public Function GetCurrentUserFacility2() As String
    On Error GoTo NoFacility
    GetCurrentUserFacility2 = DLookup("FacilityNumber2", "tbl_users", "ID = " & Credentials.UserId)
    Exit Function
NoFacility:
    GetCurrentUserFacility2 = ""
End Function

As an example on my main form this code exists and uses Credentials to perform various functions when the form opens

Private Sub Form_Open(Cancel As Integer)
    Dim dbs As DAO.Database
    Dim FEVersion As Variant

    Set dbs = CurrentDb
    If Credentials.AccessLvlID = 0 Then
        DoCmd.OpenForm "frm_loginform"
        Cancel = 1
    End If
    
    If Credentials.AccessLvlID = 1 Then
        If Weekday(Now) = vbMonday Then
            WaitVis
            WaitLab
            SendEMail
        End If
    End If
    
    If Credentials.UserId = 2 Then
        dbs.Execute "AppendNewPONumbers", dbFailOnError
        dbs.Execute "AppendNewPoNumbers_WNK", dbFailOnError
    End If

        FEVersion = DLookup("fe_version_number", "tbl-fe_version")
        DoCmd.SetWarnings False
        DoCmd.RunSQL "UPDATE tbl_users SET Version = '" & FEVersion & "' WHERE ID = " & Credentials.UserId
    
    If Credentials.AccessLvlID = 6 Then
        MsgBox "Your Account Has Been Deactivated. Please Contact the Administrator."
        DoCmd.Quit
    End If
End Sub

 

[isladogs]

Richard
Three things

1. I agree with Pat.
The If line should either use a function (which is what I assumed originally) or a DLookup expression

2. I omitted the part excluding reports that begin with a tilde ~ from my code as clearly you have no reports with names like that. But if you do have any, they would also need to be excluded from the second statement

3. Form load is the correct event for this code

 

[psyc0tic1]

1. I agree with Pat.
The If line should either use a function (which is what I assumed originally) or a DLookup expression

It is using the function in the credentials module. I knew it would need that part.

2. I omitted the part excluding reports that begin with a tilde ~ from my code as clearly you have no reports with names like that. But if you do have any, they would also need to be excluded from the second statement

I understand a little but without the code looking to show all reports, the only way I was able to accomplish that was to have the originally posted query code (from post #1) put in the IF and keep yours in the Else. I am sure there is a correct way but in my case this actually worked.

3. Form load is the correct event for this code

That is where I put it and all is working now.

 

[Minty]

Where are you getting Credentials.AccessLvlID from?

Debug.Print Credentials.AccessLvlID to see what it's set to in the VBA immediate window. I'd guess its not set to 1.


EDIT - That's weird none of the post on page 2 of this thread showed up when I replied before. Ignore me I'm going mad.

 

[isladogs]

If the function is in a standard module, you don't need the module name as a prefix UNLESS you have more than one function with that name (not a good idea)

To try and illustrate my point, make a report and name it ~test or similar.
Login in as a non admin user. That report will appear in your list.

Next create a report and delete it. Don't compact or close the database.
View the report list again as non admin user. You should see an item starting with ~TMPCLP which is your deleted report. It should disappear when you close or compact the db.

Hope that helps

 

[psyc0tic1]

Where are you getting Credentials.AccessLvlID from?

Debug.Print Credentials.AccessLvlID to see what it's set to in the VBA immediate window. I'd guess its not set to 1.

When I log in... as an Admin (found in the tbl_accesslevel, field name AccessLvl, field value is Admin and the field value ID is 1)

 

[Gasman]

I would be walking through the code line by line by now using F8 and a breakpoint in the first relevant form.?

 

[psyc0tic1]

Well Colin,

I put the code back the way you originally posted it in post #15 and it is working now. I can only assume it was not working for me while I was in design mode I was testing it without actually logging into the database so it was not knowing what AccessLvlID I had.

I don't know what I was thinking :banghead:

Thank you everyone for your helpful information.

 

[isladogs]

Excellent.it seems that Deja vu has repeated itself again...

If you haven't tried doing the two test reports I mentioned in post #27, I suggest you do so whilst the subject is fresh in your mind.

 

[psyc0tic1]

Excellent.it seems that Deja vu has repeated itself again...

If you haven't tried doing the two test reports I mentioned in post #27, I suggest you do so whilst the subject is fresh in your mind.

I did the tests with the current code in place and it did as you said it would other than when I deleted the report it showed in the list as #Deleted and I closed the form and re-opened it and it was gone from the list but it all did as you said.

 

[isladogs]

Perhaps the deleted report needs a code module in order to appear as ~TMPCLP....

Anyway, the reason for suggesting the tests was to help you understand what the SQL statements were doing.

Good luck with the next stage in your journey...!