Password Protect a Form

[TriniGal]

Hello,

I'm not sure this can be done, but I will give it a try. We have 10 regions in our company. I a form that lists all the systems in all the regions. What I would like to do is, when the user click on the button to open the systems listings from the main switchboard, where they click on a system to report on, I want them to asked to enter the region and password. Once the region is entered, I was only the systems within that region to be listed. That way, they can't enter/view other region's informaton.

I have searched on here and on other sites, and I can't seem to find anyway of doing this. The information on here is usually how to password protect a form.

Please be as descriptive as possible. Any help is greatly appreciated.

Thanks

 

[wshs]

seems like you just need to filter the lists.

easiest way would be using DLookUp function.

 

[TriniGal]

wshs,

I thank you for your reponse. Do you have any code that would help me with this or maybe a website that you could point me in the direction of?

Thanks

 

[wshs]

sure. it would make it easier if you can list me your tables and its columns

 

[TriniGal]

Hello,

I think I have somewhat got this figured out with a lot of help for a website I found. Can anyone please help me figure out why I'm getting the following error on the code listed below? I hightlighted the spot which according to VB, is creating the error.

"Run-time error '2501': The openForm action was canceled."

Private Sub cmdLogin_Click()

Dim strDocName As String
Dim strWhere As String

    strDocName = "frmENTER_LEAK_FORM"
    strWhere = "[Region]=" & Me!cboREGION
    
'Check to see if data is entered into the UserName combo box

If IsNull(Me.cboREGION) Or Me.cboREGION = "" Then
MsgBox "You must enter a Region.", vbOKOnly, "Required Data"
Me.cboREGION.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If

'Check value of password in tblRegions to see if this matches value chose in combo box

If Me.txtPassword.Value = DLookup("strRegPassword", "tblRegions", "[lngRegID]=" & Me.cboREGION.Value) Then
lngMyRegID = Me.cboREGION.Value

'Close logon form and open enter leak form

DoCmd.Close acForm, "frmSELECT_REGION", acSaveNo
[highlight]DoCmd.OpenForm strDocName, , , strWhere[/highlight]

Else
MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
End If

'If User Enters incorrect password 3 times the database will shut down

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database.Please contact the Databas Administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If


End Sub

 

[PHV]

I have searched on here
Really ? even in the FAQ area ?
faq702-4289, faq702-4640, ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[VicRauch]

TriniGal,

The code that you posted, I assume is executed from the form named "frmSELECT_REGION" If so, then just before you get your error message, you CLOSE the form that is running the code. Best to put the close of the form that is running this code into the code that opened this form, unless it is the last thing you want the code with this form to execute.

If you want the form "frmSELECT_REGION" to no longer show on the screen, then set it's visibility property to False.
Me.Visibility = False

HTH
Vic

 

[TriniGal]

Vic,

Thanks for your response. You are right, the code in excuted from "frmSELECT_REGION". I'm not understanding what you are telling me that I should do. Where do I put all this coding that you are talking about? I'm sorry, this is a little new to me.

Thanks

 

[jebry]

Hi!

If this is being implemented on a network then all you need to do is keep a table of user's network id and the region(s) they are allowed to view. Then you can just check the network id using Environ("username") and limit the form automatically by the region(s) listed in the table. You can also use the network id to limit what command buttons can be used or fields updated as well.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[TriniGal]

My knowledge of VB is very limited. Do you know how I would do this, or maybe there is a website with the code?

Thanks

 

[jebry]

Hi again!

You would need a table:

tblUsers

fldNetworkName
fldRegion

Then you can use a switchboard form that opens automatically when the database is opened. In the form open event of the switchboard put:

Dim rs As DAO.Recordset

Set rs = CurrentDB.OpenRecordset("Select * From tblUsers Where fldNetworkName = '" & Environ("username") & "'", dbOpendynaset)

If rs.EOF = True And rs.BOF = True Then
MsgBox "Your network id is not valid for using this database. Please contact the system administrator to be given access."
DoCmd.Quit
End If

Set rs = Noting


Then, in the buttons that open the forms use the following code.

Dim rs As DAO.Recordset

Set rs = CurrentDB.OpenRecordset("Select * From tblUsers Where fldNetworkName = '" & Environ("username") & "'", dbOpendynaset)

DoCmd.OpenForm "YourForm", , , "Region = '" & rs.fldRegion & "'"

Set rs = Nothing

This will verify that the user is allowed in the database and then will limit the view only to the region they are allowed to see. There are other security measures you would need to take so that the users can't just open the database window and access the tables directly. There are many FAQs on this site that can help you with security.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[VicRauch]

TriniGal,

To answer your question about a little more detail as to where to put the code I suggested.

It appears that you are trying to pass control from one form to another to control the process of checking the user password against the selected region (form "frmSELECT_REGION"), then have this form open the next form in the control flow (form "frmENTER_LEAK_FORM"). This passing of logic control from one form to another is best done from a completly separate form.

Rather than
DoCmd.OpenForm "Form1"
Then within Form1, DoCmd.Close acForm, "Form1" and DoCmd.Open "Form2"
It is much better to have your code, where you currently open Form1 (frmSELECT_REGION):

DoCmd.OpenForm "frmSELECT_REGION",,,,,acDialog
DoCmd.OpenForm "frmENTER_LEAK_FORM",,,,,acDialog
...
DoCmd.Close acForm, "frmSELECT_REGION"
DoCmd.Close acForm, "frmENTER_LEAK_FORM"
End Sub

The "Dialog" causes the form to be just that , a dialog box that keeps the user on that form, and can do nothing else until that form is closed or hidden (set to Visible=False)

So, if you need some information from the form itself, like which region was selected, then you would hide the form, such as "frmSELECT_REGION" so your code could reference that form later when dealing with the selected region. If all you are going to do is check a password, without needed any other information later, then rather than hidding the form, you can just close it.

I do realize that this will be confusing on the first read. Please re-read it a few times and I think you will get the idea. But still, let me give you a quick summary of what I have tried to explain.

Rather than open form1, and then have form1 open form2 and close itself (form1), then have form2 open form3 and close itself (form2), do all of this from a separate form (form0?)

So in form0 you would have this type of code:
DoCmd.Open "form1",,,,,Dialog
DoCmd.Open "form2",,,,,Dialog
DoCmd.Open "form3",,,,,Dialog
...
...
DoCmd.Close acForm, "form1"
DoCmd.Close acForm, "form2"
DoCmd.Close acForm, "form3"

HTH, and ask more questions where you need to.
Vic