Code problem where it should be working (1 Viewer)

[Ironis]

Hey there

I have a form, which is hidden. The form starts up using the AutoExec macro. I want on other forms, to deactivate certain buttons with certain users. I do this using the following:

In the macro, the form opens, with a table in it with user names en workgroups. The where condition when the form opens is:
[username]=CurrentUser()

This way, the value on the form is always the person who is logged on to the DB.
Now, on another form I have the following piece of code:

Private_Sub Form_Open(Cancel As Integer)
     If [Forms]![frmSecure]![workgroup] = "shopuser"
          cmdOpenFormSettings.Enabled = False
     End If
End Sub

This was just to test things, but now I get the error that the form doen not exist or can't be found..

Every name is right, no typos...

What do I do wrong??

 

[chenn]

Ironis, I think it has something to do with your hidden form. May I make a suggestion on a different path?

I would create a public function in a module that would return the user's group. Such as:

Public Function GET_USER_GROUP ()as string
Dim rstGroups as recordset
'set the initial value to not found
GET_USER_GROUP = "GroupNotFound"
'open a recordset for the groups/users table
Set rstGroups = currentdb.openrecordset("MyTable", dbopensnapshot)

'make sure there are records in the recordset
If not rstGroups.eof then
'look for the current user
rstGroups.findfirst ("[User] = ' " & currentUser() & " ' ")
if rstgroups.nomatch = false then
'found user, assign the group field name to function
GET_USER_GROUP = rstGroups!MyGroupFieldName
end if
end if
'close and destroy your object
rstGroup.close
set rstGroup = nothing
end function

The above example will work for Access 97 but not for Access 2000, access 2000 will mostly use ado recordsets.

Now replace the on-open code to read:

Private_Sub Form_Open(Cancel As Integer)
If GET_USER_GROUP = "shopuser"
cmdOpenFormSettings.Enabled = False
End If
End Sub

Doing it this way will eliminate your problem and will no longer require you to use that hidden form. Let me know if you have any questions.

 

[Ironis]

The next problem is: Where do I put the Public Function?
and another one: I'm working with Access 2002

Ironis

 

[chenn]

Put the code in a new module. Goto modules - new and paste it there. In not sure if 2002 uses ADO although its a pretty good bet. Here is some sample code using ado from the code librarian:


Public Sub OpenMdb()

' Public Sub OpenMdb
' Purpose:
' This code sample demonstrates the following:
' 1. Opening a Jet *.mdb using ADO and the Jet 3.51 OLE DB provider
' 2. Creating a recordset against the database itself.

Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim varx As Variant

'Set Provider
Set conn = New ADODB.Connection
conn.Provider = "Microsoft.JET.OLEDB.3.51"

'Open Northwinds
conn.Open "C:\northwind.mdb"

'Load the recordset with data in Categories Table
Set rst = New ADODB.Recordset

rst.Open "Select * from Categories", conn, adOpenDynamic, _
adLockBatchOptimistic

'Loop through the recordset
Do Until rst.EOF
Debug.Print rst(1)
rst.MoveNext
Loop

End Sub

Or you could try to add a reference: check out this thread for how:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=39624

"Still looking for help writing to a table "

 

[Ironis]

Chenn,

Thanx alot for helping me out. The first code works fine. I only had to remove the ADO 2.1 reference, and add the ADO 2.7 and ADO Recordset 2.7, and it works fine now.

Thanks again for helping me out

Ironis

 

[Ironis]

Oke now it goes wrong again. I've installed my system, and I am building the final security (wizard asnd this code etc), and now the code gives up. I still have the same references, but now the code just won't work. It won't find a user..

Here's the code I am using:

Public Function GET_USER_GROUP() As String
Dim rstGroups As Recordset

'set the initial value to not found
    GET_USER_GROUP = "GroupNotFound"

'open a recordset for the groups/users table
    Set rstGroups = CurrentDb.OpenRecordset("tblSecure", dbOpenSnapshot)

'make sure there are records in the recordset
    If Not rstGroups.EOF Then

'look for the current user
        rstGroups.MoveFirst
        rstGroups.FindFirst ("[Users] = ' " & CurrentUser() & " ' ")
        If rstGroups.NoMatch = False Then

'found user, assign the group field name to function
            GET_USER_GROUP = rstGroups!WorkGroup
        Else
            MsgBox "No User Found!"
        End If
    End If

'close and destroy your object
rstGroups.Close
Set rstGroups = Nothing

End Function

And:

Private Sub Form_Open(Cancel As Integer)
    If GET_USER_GROUP = "fulldata" Then
        cmdOpenfrmMenuStats.Enabled = True
    ElseIf GET_USER_GROUP = "admin" Then
        cmdOpenfrmMenuStats.Enabled = True
    Else
        cmdOpenfrmMenuStats.Enabled = False
    End If
End Sub

But I only get a msgbox: No User Found!

I log on as admin user or as fulldata user, but nothing works.. The code seems to be ok.. I added .MoveFirst tho, thought that might help.. but no difference....

The table is hidden, buit I don't think that has anuything to do with the problem..

Plz help me out,

Dennis

 

[Ironis]

Hmm, never mind.. I dd the following:

changed
rstGroups.FindFirst ("[UserName] = ' " & CurrentUser() & " ' ")

Into:
rstGroups.FindFirst ("[UserName] = CurrentUser()")

And Now it works

Dennis