VBA and Other stuff

[Guest]

So I don't have to post several messages until all my questions are answered
I'm gonna post em all at once.

1. I have user level security enabled. I am trying to hide one of my
switchboard buttons on the main switchboard so only members of the admins
group can see the button Database Administration. I have the following code
entered into the switchboard form but its giving me an error when I open the
database. (Compile error: Sub or Function not defined) The following If
statement is currently under the Sub Form_Open(Cancel As Integer). What will
stop this error?

If faq_IsUserInGroup("Admins", CurrentUser()) Then
Me.cmdDatabase Administration.Visible = True
Else
Me.cmdDatabase Administration.Visible = False
End If

2. Is there a way to determine what users are currently logged into the
database?

3. Is there a way to dump all current users out of the database?

4. How do I change the error message when a user enters the wrong password
or login ID?

5. I currently have the f11 function key password protected. Can I make an
error box pop up when the password is typed incorrectly which would display a
hint to the user? (Is there a way to make the hint only show up for people
in the admins group)

6. Is there a way to have a password hint pop up for a specific user when
the password is typed incorrectly.

Really the first 2 questions are most important I definetly need those. The
others are just "nice to haves" that my boss wants.

Thanks in advance,

 

[Roger Carlson]

Actually, it is much preferred if you ask each question in a separate
posting with a descriptive Subject line. That way each question can develop
it's own discussion thread and other people searching Google and other
engines can find the answers too.

I don't have answers for everything, but:

2) Look here: http://www.mvps.org/access/modules/mdl0055.htm

3) Look here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=LogUsersOff.mdb

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Well I guess I will continue on with my second question and repost the others
then. So I have a module set up to show the users that are logged in. Which
runs using the immediate window after typing in the command
ShowUserRosterMultipleUsers. How can I automate this process so I can press
one button from the switchboard and receive this information and would it be
possible to dispay it in a form?

 

[Dirk Goldgar]

So I don't have to post several messages until all my questions are
answered I'm gonna post em all at once.

1. I have user level security enabled. I am trying to hide one of my
switchboard buttons on the main switchboard so only members of the
admins group can see the button Database Administration. I have the
following code entered into the switchboard form but its giving me an
error when I open the database. (Compile error: Sub or Function not
defined) The following If statement is currently under the Sub
Form_Open(Cancel As Integer). What will stop this error?

If faq_IsUserInGroup("Admins", CurrentUser()) Then
Me.cmdDatabase Administration.Visible = True
Else
Me.cmdDatabase Administration.Visible = False
End If

So the first question is: is there in fact a function named
"faq_IsUserInGroup" defined anywhere in your database? That's not a
built-in Access or VBA function. I have a similar function of my own,
but if you cribbed this code from somewhere, you may as well copy the
code for the function from the same location.

Another possibility is that you've just got the name wrong. The "faq_"
prefix is an uncommon prefix for a function name.

 

[Guest]

My knowledge in VBA is limited to copy and pasting code into a module or a
spot on a form based on the instructions I read. So I have no idea how to
define the function. I copied this code that was a response in another
thread on how to hide a switchboard button. You said you have something
similar? I just need to know what to do so I can hide the Database
Administration button on the default switchboard for everyone who is not in
the Admins group. I'm willing to do it whatever way it will work.

 

[Dirk Goldgar]

My knowledge in VBA is limited to copy and pasting code into a module
or a spot on a form based on the instructions I read. So I have no
idea how to define the function. I copied this code that was a
response in another thread on how to hide a switchboard button. You
said you have something similar? I just need to know what to do so I
can hide the Database Administration button on the default
switchboard for everyone who is not in the Admins group. I'm willing
to do it whatever way it will work.

I did a little Googling, and that function is defined in the Access
Security FAQ document, which can be downloaded from here:

http://support.microsoft.com/?id=207793

If you download it, unzip it, and search it for "faq_IsUserInGroup",
you'll locate the function definition. You'll need to copy that
function and paste it into a standard module in your database. Standard
modules are defined on the Modules tab of the database container window.
If you don't already have a standard module that is suitable for the
purpose, you can create a new one -- just make sure you don't give the
module the same name as the function (or any other public object in your
database, for that matter).

If you have trouble with this, let me know.

 

[Guest]

That helped some. It is no longer highlighting the faq line when it errors
out. Now it errors out on the second line.
Me.cmdDatabaseAdministration.Visible = True

It doesn't seem to like the name of the button I am trying to make visible
for admin users only. Any ideas on this one? I tried putting a space
between the command button name, and cmd I've tried putting the command
button name in quotes but neither of those worked.

Thanks,

 

[Dirk Goldgar]

That helped some. It is no longer highlighting the faq line when it
errors out. Now it errors out on the second line.
Me.cmdDatabaseAdministration.Visible = True

It would help if you gave the error message (and error number, if
available). However, I wonder if you've got the name of the button
right. With the form open in design view, double-click on the button
(or use any of the other possible methods) to bring up its property
sheet. On the "Other" tab of the property sheet, what is listed in the
Name property? That's the name you have to use in your code. If the
name contains spaces or other nonstandard characters, you have to
enclose it in square brackets when you refer to it in code.

 

[Guest]

Sorry about that. Its a switchboard button and the button opens another
switchboard called DatabaseAdministration. It had a space in the middle
before but I changed the name after it error'd the first time and it didn't
seem to help. The Error says Compile Error: Method or Data Member not found.
Does the switchboard save the names different than what I set it up as? The
cmd button will open another switchboard when pressed and that switchboard is
named DatabaseAdministration. How would I find the name for a switchboard
button? In designview its option 7 but I have several switchboards linked
off the default switchboard so I do not want every option 7 to not be visible
for people not in the admins group.

Thanks,

 

[Dirk Goldgar]

Sorry about that. Its a switchboard button

*Now* you tell me!

and the button opens
another switchboard called DatabaseAdministration. It had a space in
the middle before but I changed the name after it error'd the first
time and it didn't seem to help. The Error says Compile Error:
Method or Data Member not found. Does the switchboard save the names
different than what I set it up as? The cmd button will open another
switchboard when pressed and that switchboard is named
DatabaseAdministration. How would I find the name for a switchboard
button? In designview its option 7 but I have several switchboards
linked off the default switchboard so I do not want every option 7 to
not be visible for people not in the admins group.

The built-in switchboard is a rather elaborate and cumbersome beast,
using its own code to show and hide buttons on the fly depending on the
contents of the [Switchboard Items] table. The simple solution you've
been attempting, just hiding the button in the form's Open event, won't
work. I don't use the built-in switchboard, preferring to just create a
simple form with my own, static buttons on it.

However, if you're going to use the built-in switchboard, here's what
you have to do. First, restore the original name to the button, and
take the code you added out of the Open event, leaving what was
originally place there when Access created the form for you. Then
replace the FillOptions() sub in the form's module with the following:

'----- start of replacement code -----
Private Sub FillOptions()
' Fill in the options for this switchboard page.

' The number of buttons on the form.
Const conNumButtons = 8

Dim con As Object
Dim rs As Object
Dim stSql As String
Dim intOption As Integer

Dim blnShowButton As Boolean

' Set the focus to the first button on the form,
' and then hide all of the buttons on the form
' but the first. You can't hide the field with the focus.
Me![Option1].SetFocus
For intOption = 2 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).Visible = False
Next intOption

' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM [Switchboard Items]"
stSql = stSql & _
" WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & _
Me![SwitchboardID]
stSql = stSql & " ORDER BY [ItemNumber];"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

' If there are no options for this Switchboard Page,
' display a message. Otherwise, fill the page with the items.
If (rs.EOF) Then
Me![OptionLabel1].Caption = _
"There are no items for this switchboard page"
Else
While (Not (rs.EOF))

blnShowButton = True ' default is to show all buttons

If rs!ItemText = "Database Administration" Then
blnShowButton = _
faq_IsUserInGroup("Admins", CurrentUser())
End If

Me("Option" & rs![ItemNumber]).Visible = blnShowButton
Me("OptionLabel" & rs![ItemNumber]).Visible = blnShowButton
Me("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]
rs.MoveNext
Wend
End If

' Close the recordset and the database.
rs.Close
Set rs = Nothing
Set con = Nothing

End Sub
'----- end of replacement code -----

If I'm right in my understanding about what you named the switchboard
item, that ought to work.

 

[Guest]

Wonderful! I got it working now. Thank you very much!
--
Denise

Sorry about that. Its a switchboard button

*Now* you tell me!

and the button opens
another switchboard called DatabaseAdministration. It had a space in
the middle before but I changed the name after it error'd the first
time and it didn't seem to help. The Error says Compile Error:
Method or Data Member not found. Does the switchboard save the names
different than what I set it up as? The cmd button will open another
switchboard when pressed and that switchboard is named
DatabaseAdministration. How would I find the name for a switchboard
button? In designview its option 7 but I have several switchboards
linked off the default switchboard so I do not want every option 7 to
not be visible for people not in the admins group.

The built-in switchboard is a rather elaborate and cumbersome beast,
using its own code to show and hide buttons on the fly depending on the
contents of the [Switchboard Items] table. The simple solution you've
been attempting, just hiding the button in the form's Open event, won't
work. I don't use the built-in switchboard, preferring to just create a
simple form with my own, static buttons on it.

However, if you're going to use the built-in switchboard, here's what
you have to do. First, restore the original name to the button, and
take the code you added out of the Open event, leaving what was
originally place there when Access created the form for you. Then
replace the FillOptions() sub in the form's module with the following:

'----- start of replacement code -----
Private Sub FillOptions()
' Fill in the options for this switchboard page.

' The number of buttons on the form.
Const conNumButtons = 8

Dim con As Object
Dim rs As Object
Dim stSql As String
Dim intOption As Integer

Dim blnShowButton As Boolean

' Set the focus to the first button on the form,
' and then hide all of the buttons on the form
' but the first. You can't hide the field with the focus.
Me![Option1].SetFocus
For intOption = 2 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).Visible = False
Next intOption

' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM [Switchboard Items]"
stSql = stSql & _
" WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & _
Me![SwitchboardID]
stSql = stSql & " ORDER BY [ItemNumber];"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

' If there are no options for this Switchboard Page,
' display a message. Otherwise, fill the page with the items.
If (rs.EOF) Then
Me![OptionLabel1].Caption = _
"There are no items for this switchboard page"
Else
While (Not (rs.EOF))

blnShowButton = True ' default is to show all buttons

If rs!ItemText = "Database Administration" Then
blnShowButton = _
faq_IsUserInGroup("Admins", CurrentUser())
End If

Me("Option" & rs![ItemNumber]).Visible = blnShowButton
Me("OptionLabel" & rs![ItemNumber]).Visible = blnShowButton
Me("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]
rs.MoveNext
Wend
End If

' Close the recordset and the database.
rs.Close
Set rs = Nothing
Set con = Nothing

End Sub
'----- end of replacement code -----

If I'm right in my understanding about what you named the switchboard
item, that ought to work.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Wonderful! I got it working now. Thank you very much!

Excellent! You're welcome.

 

[aaron.kempf]

user level security is a joke; move your database to SQL Server; and
use Access Data Projects.

MDB is a security THREAT.
It shouldn't be used for a single record and a single user.

-Aaron Kempf

 

[aaron.kempf]

way to dump users in MDB ??

YEAH REBOOT YOUR FILESERVER

ROFL

what a piece of shit database format

-Aaron