Password to open a form- previous AceMan post

[Dophia]

Aceman: I found your response to set a password to open a form in a previous post, but I am having trouble with it. What does Set prp = db.TableDefs("tblType").Properties("PassAns")refer to? Also, are you suggesting a table to hold Pass and PassAns or the db's custom properties?Does the password form's Ok button open the form? This is a little over my head. Any help would be appreciated.

Thanks Sophia

You start br creating two custom table properties, Pass & PassAns (it helps if the password can be saved somewhere not out in the open, even if its VBA). These properties hold the password and password answer from the popup. You can use any table to hold the properties. I used a table to hold the properties instead of the db because db properties call for redistribution of the front end if the db is split.
In the Click event of the button on your switchboard, copy/paste the following:

CODE
On Error GoTo GotErr
DoCmd.OpenForm "FormToOpenName"
GotErr:
On Error simply traps the following error due to canceling opening of the form thru DoCmd.OpenForm:
Run-Time Error '2501':
The Open Form action was canceled.
The form starts to open triggering its On Open event which has the following code for each form you wish to test password before opening:

CODE
Dim db As DAO.Database, prp As Property

Set db = CurrentDb
Set prp = db.TableDefs("tblType").Properties("PassAns")

prp = "~" 'Reset PassAns
If Not PasswordOK() Then Cancel = True 'Test PassWord!
prp = "~" 'Reset PassAns

Set prp = Nothing
Set db = Nothing
PassAns is reset then the popup is opened in the function PasswordOK to get user input. Code stops running here in the form (due to acDialog in opening the popup) and continues when the popup is closed (after the function updates PassAns with user input 1st . . . of course!). If Pass and PassAns match you know what happens . . .
Here's the code for the popup:

CODE
Private Sub Form_Unload(Cancel As Integer)
Dim Ans As String

If Trim(Me!TextboxName & "") = "" Then
Ans = "@"
Else
Ans = Me!TextboxName
End If

CurrentDb.TableDefs("tblType").Properties("PassAns") = Ans

End Sub

Private Sub PopupTextboxName_AfterUpdate()
DoCmd.Close acForm, "PopupFormName", acSaveNo

End Sub
Finally, in a module in the modules window, copy/paste the following:

CODE
Public Function PasswordOK() As Boolean
Dim db As DAO.Database, tdf As TableDef
Dim PW As Property, PWA As Property

Set db = CurrentDb
Set tdf = db.TableDefs("tblType")
Set PW = tdf.Properties("Pass")
Set PWA = tdf.Properties("PassAns")

DoCmd.OpenForm "purple]PopupFormName[/purple]", , , , , acDialog
If StrComp(PW, PWA, vbBinaryCompare) = 0 Then
PasswordOK = True
Else
MsgBox "Wrong PASSWORD!"
End If

Set PWA = Nothing
Set PW = Nothing
Set tdf = Nothing
Set db = Nothing

End Function

 

[TheAceMan1]

Howdy Dophia . . .

For reference the thread is thread702-1235844

[blue]What does Set prp = db.TableDefs("tblType").Properties("PassAns")refer to?[/blue]

[ol][li][blue]db.TableDefs[/blue] refers to the collection of table objects in the Db (all your tables plus hidden system tables).[/li]
[li]db.TableDefs[blue]("tblType")[/blue] refers to the specific table [blue]tblType[/blue] in the tables collection.[/li]
[li]db.TableDefs("tblType").[blue]Properties[/blue] refers to the [blue]properties collection[/blue] (all current properties) of tblType.[/li]
[li]db.TableDefs("tblType").Properties[blue]("PassAns")[/blue] refers to the specific property [blue]PassAns[/blue].[/li]
[li][blue]Set prp[/blue] = db.TableDefs("tblType").Properties("PassAns"), sets an object [blue]referencing the property[/blue]. The main reason I set an object here is so I don't have to type that long line whenever I have to reference the property. Over the years I've learned to format my code for easy reading & debugging. A few more lines are added due to this, but its turned out to be an impressive aid.[/li][/ol]

[blue]I used a table to hold the properties [purple]instead of the db because db properties[/purple] call for redistribution of the front end if the db is split.[/blue]

I.ve already shown table properties are used versus Db properties. However in the referenced thread I do say in 5.:

[blue]Why did I choose Table over the DB? . . . In a split DB configuration the [purple]DB properties become part of the FrontEnd[/purple] and this would require [purple]redistribution (espcially if on a network!)[/purple]. The tables being centrally located would not.[/blue]

In totality, what I posted is what I came up with at the time. I have to admit it is a bit of over-kill. If I could go back, I'd open the password form first, relieving a good part of the code.

Also as the referenced thread shows, there are many other ways to password protect a form.

[blue]Is there any other cooperation you require on this subject?[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997