PW Help

[Stew]

I'm trying to PW protect a button based on two inputs. However the code worked with one input, then with two. Once I started expanding it to all the options it stopped working. It is the onclick event on frmMorningReport.

[Charlotte]

I only see the two tests in code behind the frmMorningReport. What else were you trying?

[Charlotte]

And BTW, you aren't actually password protecting anything that I see except for access to the report. You're simply determining whether a user is allowed to see a particular report based on the two criteria. Minor quibble, I realize, but it's important to not confuse others who might be trying the same idea.

[Stew]

I only expanded it to two sections in HQ Company. With the two combo boxes being the critera. When the button is pressed it should requery the qryMorningReport, if the correct PW was inputed, with the selected company and section. The first line of cade for the inputbox isn't even coming up. Well just found the error as I was typing this. Had an extra ' in one of the commens and messed it up. However it is still doesn't work.

[HansV]

In the first place, you don't have Option Explicit, so a declaration error is not flagged: you have

Dim strPsswd

strPasswd = InputBox("Enter Password", "Restricted Information")

Notice the difference in spelling? Please read The importance of 'Option Explicit'.

And you missed an End If. I'd use Select Case ... End Select here, it's easier to maintain:

Code: Select all

Private Sub Search_Click()
  Dim strPasswd As String
  strPasswd = InputBox("Enter Password", "Restricted Information")
  'Check to see if entry was made'
  If strPasswd = "" Or strPasswd = Empty Then
    MsgBox "NoInput Provided", vbInformation, "Required Data"
    Exit Sub
  End If
  'Check for correct PW
  'Check for HQ Company
  Select Case cmbCompany
  Case "HQ"
    'Check for HQ Office
    Select Case cmbSection
    Case "HQ Office"
      If strPasswd = "a" Then
        Me.subfrmMorningReport.Requery
      Else
        MsgBox "Sorry, you do not have access to HQ Office Morning Report", vbOKOnly, "Important Information"
        Exit Sub
      End If
    'Check for S-1
    Case "S-1"
      If strPasswd = "b" Then
        Me.subfrmMorningReport.Requery
      Else
        MsgBox "Sorry, you do not have access to S-1 Morning Report", vbOKOnly, "Important Information"
        Exit Sub
      End If
    Case Else
      MsgBox "Sorry, you did not choose a set up", vbOKOnly, "Important Information"
      Exit Sub
    End Select
  Case Else
    MsgBox "Sorry, you didn't pick HQ", vbOKOnly, "Important Information"
    Exit Sub
  End Select
End Sub

Keep in mind that this is not very secure - experienced users can easily break it.

[Stew]

I understand this is not that secured, they told me not to worry about expeienced users. An experienced user can get around just about every security lock that I know of in Access. Once I get my whole database together I am locking out the menu bars, right click, shortcuts (such as shift on open), and the navigation bar. I am going to use a admin button for a password to unlock the shift on open shortcut. Any other advice would be useful. Also since I have to do multiple cases inside the cmbCompany Case, what is the best way to do that?

[HansV]

I used Select Case because that makes it easy to add new options for cmbCompany, just like there currently are for cmbSection.

I'd consider using a table to store the permissions and passwords, that is easier to maintain than modifying the code each time you add, remove or change a company or section.

[Charlotte]

I concur with Hans suggestion. A select case is definitely easier to manage than If ... End If blocks.

[Stew]

Thank you, I think I am going to switch it to Case. Would either one of you have an example of the PW stored in a seperate table. I'd like to take a look at one and see if I want to switch it. I like the idea that it would be easier to update and change. I have to make a turnover binder for this database explaining all the code. I think the table with PW would be the best.

[HansV]

Take a look at the attached sample database.
Look at the tables first, you can see the passwords in the table tblSections. In the "production" version, you could set the Input Mask property of the Password field to Password.
Then open the form.
The "Action" button only displays a message box in this version, it doesn't do anything useful.

PWTest.zip

[Stew]

I like the idea behind this alot more then how I was doing it with cases. I think I am going to set it up like that instead. So all I would have to do for my example is set a PW field in my validation table. Then change my form to include a txtbox for the PW. That seems alot more dynamic then how I was hard coding it. That way if I add more sections and company the form will automatically account for them. Thanks for the help on this. Your a life save Hans. You too Charlotte.

[Stew]

I tried to implement it with the table. However when I did that my requery for my subform is messing up. It won't retrieve the data that fits the critera now. Could you take a look at it. I'm pretty sure it is something small. The PW part of it works perfectly, thanks for that.

[HansV]

Could you post a stripped down, compacted and zipped copy of the current version of the database?

[Stew]

Well I feel dumb meant to attach it to that post, here it is sorry.

[HansV]

The table tblMain doesn't need a Company field - the Section uniquely defines the Company to which the record belongs, so a company field would be superfluous.
The Section field is currently a text field holding the name of the section. It should be a number field holding the Section ID of the section. You can have it display the name of the section by using a combo box to look up the name in tblSections.
This is also necessary because the cmbSection combo box on the main form has Section ID as (hidden) first column.

See the attached version (I compacted the database before zipping, hence the considerably smaller file size)

PW Problem.zip

[Stew]

I like having it set up like that, however I'm forced to reformat two things and I can get them to work now if you wouldn't mind taking a look at it. My master report you helped me set up with field names, however we used the company field in the table main to sort it. I want that to still be workable sorting by company however I can't get the code to work. Then my edit form works, however I can't get it to show the section name not the number. Any help would be fantastic. Thanks alot for all your help you have given me in this project so far. You have expanded my knowledge greatly over the past two weeks.

[HansV]

I'll try to get back to you a bit later.

[Stew]

Thanks alot, I appreciate it.

[HansV]

The answers can be found in the database that I attached yesterday.

1) Create a query based on tblMain, tblSections and tblCompany. Add the fields from tblMain, plus the Company field from tblCompany. Sort the query on the latter field.
Save this query as (for example) qryMain.

Set the Row Source of lstFieldList in frmReportMaster to qryMain instead of tblMain.

Also use qryMain instead of tblMain in the SQL string assembled by btnExport_Click.

2) Change the Section text box on subfrmMorningReport to a combo box, with the following properties:
Column Count = 2
Column Widths = 0";1"
Row Source = tblSections (or a query based on tblSections that sorts the records by Section)

[Stew]

Ok I see, it just wasn't clicking in my head. It took a little messing around with but I understand how to do that alot better now. Thanks.