We help IT Professionals succeed at work.

Open Form (delay it) - wait for data input

pdvsa
pdvsa asked
on
Experts,

I am working on putting  a password on a form
      frmLCAmendHistory
I open this frmLCAmendHistory from another form called
      frmLetterOfCredit_Cont
frmLCAmendHistory has ONOPEN code to open
      frmPassword

I need the frmLCAmendHistory to delay in opening until only AFTER entry of the Password on frmPassword.

The pw system I have setup works, however, right now,when I am on frmLetterOfCredit_Cont and want to go to frmLCAmendHistory (the protected form with a pw), I press the button on frmfrmLetterOfCredit_Cont and frmPassword opens (due to the ONOPEN event of frmLCAmendHistory) but in the background behind frmPassword the frmLCAmendHistory opens as well.

How can I prevent the frmLCAmendHistory from opening until only after the pw is entered on frmPassword?

I hope that makes sense.  Let me know what you think...

thank you (will post the code below)

Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2013

Commented:
Use a command button on your password form with this click vent:

If Me.txtPassword <> "CorrectPassword then
   Msgbox "incorrect"
Else
   Docmd.OpenForm "OtherForm"
End if

Open in new window

pdvsaProject finance

Author

Commented:
==========================================================
frmLetterOfCredit_Cont :
==========================================================

(the form I am on and wanting to go to frmLCAmendHistory (the locked down form)

Private Sub cmdAmendment_Click()

   If Me.Dirty Then
        Me.Dirty = False
   
    End If
 
 DoCmd.RunCommand acCmdSaveRecord

        DoCmd.OpenForm "FrmLCAmendedHistory", , , , , , OpenArgs:=Me.ID & ";" & Me.cboEndUserID & ";" & Me.txtLCNo


End Sub


==========================================================
frmLCAmendHistory
==========================================================
Private Sub Form_Open(Cancel As Integer)

    DoCmd.Maximize
   
   Dim Hold As Variant
   Dim tmpKey As Long
   Dim I As Integer
   Dim rs As DAO.Recordset
   Dim db As DAO.Database

   On Error GoTo Error_Handler
   ' Prompt the user for the Password.
   DoCmd.OpenForm "frmPassword", acNormal, , , , acDialog
            Hold = MyPassword
' Open the table that contains the password.
   Set db = CurrentDb
   Set rs = db.OpenRecordset("tblPassword", dbOpenTable)
   rs.Index = "PrimaryKey"
   rs.Seek "=", Me.Name
   If rs.NoMatch Then
      MsgBox "Sorry cannot find password information. Try Again"
      Cancel = -1
   Else
      ' Test to see if the key generated matches the key in
      ' the table; if there is not a match, stop the form
      ' from opening.
      If Not (rs![KeyCode] = KeyCode(CStr(Hold))) Then
         MsgBox "Sorry you entered the wrong password." & _
            "Try again.", vbOKOnly, "Incorrect Password"
         Cancel = -1
      End If
   End If
   rs.Close
   db.Close
   Exit Sub

Error_Handler:
   MsgBox Err.Description, vbOKOnly, "Error #" & Err.Number
   Exit Sub
End Sub


remember that I just want to delay the opening of frmLCAmendHistory if after entereing a correct pw.

==========================================================
frmPassword
==========================================================:
Private Sub CheckPassword_Click()

    If IsNull(Forms!frmPassword!Text0.Value) Then
                 MsgBox "You cannot enter a blank Password. Try again."
                 Me!Text0.SetFocus
            Else
                 MyPassword = Me!Text0.Value
                 DoCmd.Close acForm, "frmPassword"
    End If
           
End Sub
 
pdvsaProject finance

Author

Commented:
frmLCAmendedHistory.  (I forgot the "ed" I think)
pdvsaProject finance

Author

Commented:
oh I already have a response.  Pretty quick mbizup.

let me know if after reading my code you have a different answer....thx!
Most Valuable Expert 2012
Top Expert 2013

Commented:
Okay - looking at your code - acDialog suspends code execution until the popup form closes.

Why not just use an input box for the password, like this:

Private Sub Form_Open(Cancel As Integer)
    dim sPassword as string

    ' the dlookup finds the password you have stored in your table
    sPassword = "" & dlookup("Password","YourTable", "{some criteria}")
 
    ' This checks it against the user's input:
    If "" & InputBox("Enter Password:") <> sPassword then
         msgbox "Incorrect"
         Cancel = true
   end if
End Sub

Open in new window

and error handling here to handle the error generated by cancelling the oppen event:

Private Sub cmdAmendment_Click()
   On error goto EH
   If Me.Dirty Then
        Me.Dirty = False
    
    End If
  
 DoCmd.RunCommand acCmdSaveRecord

        DoCmd.OpenForm "FrmLCAmendedHistory", , , , , , OpenArgs:=Me.ID & ";" & Me.cboEndUserID & ";" & Me.txtLCNo
Exit sub
EH:
   if error = 2501 then 
     resume next
   else
      Msgbox err.description

End Sub

Open in new window

pdvsaProject finance

Author

Commented:
Mbizup:  
Where should I place that dim s password code?  
on the frmLetterOfCredit_Cont or on frmPassword?
Most Valuable Expert 2012
Top Expert 2013

Commented:
It goes in the open event of frmletterofcredit_cont.

It does not use a separate password form.  Just an inputbox.
Most Valuable Expert 2012
Top Expert 2013

Commented:
Sorry! It goes in the open even of the form the user is trying to open. I think that is frmlcammendedhistory.
pdvsaProject finance

Author

Commented:
the "some criteria" part is messing me up.

I have a tblPasswords
with
ObjectName
KeyCode

this is where the pw is stored for frmLCAmendedHistory
I need to Dlookup the pw for frmLCAmendedHistory

I think it is something like:
sPassword = "" & dlookup("KeyCode","tblPassword", "{DONT KNOW THIS PART}")

thank you

Most Valuable Expert 2012
Top Expert 2013
Commented:
Okay -

I think my posts may be confusing things - largely because I'm confused about how far along you are with the approach you are taking.

Lets start from the top again and scratch all of my previous posts.

Change your open event code to this and see if it does what you need:

==========================================================
frmLCAmendHistory
==========================================================
Private Sub Form_Open(Cancel As Integer)

   me.visible = false  '<--- Hide the form    
   Dim Hold As Variant
   Dim tmpKey As Long
   Dim I As Integer
   Dim rs As DAO.Recordset
   Dim db As DAO.Database

   On Error GoTo Error_Handler
   ' Prompt the user for the Password.
   DoCmd.OpenForm "frmPassword", acNormal, , , , acDialog
            Hold = MyPassword
' Open the table that contains the password.
   Set db = CurrentDb
   Set rs = db.OpenRecordset("tblPassword", dbOpenTable)
   rs.Index = "PrimaryKey"
   rs.Seek "=", Me.Name
   If rs.NoMatch Then
      MsgBox "Sorry cannot find password information. Try Again"
      Cancel = -1
   Else
      ' Test to see if the key generated matches the key in
      ' the table; if there is not a match, stop the form
      ' from opening.
      If Not (rs![KeyCode] = KeyCode(CStr(Hold))) Then
         MsgBox "Sorry you entered the wrong password." & _
            "Try again.", vbOKOnly, "Incorrect Password"
          Me.Visible = True '<-------*** Unhide it before the cancel = -1
         Cancel = -1
         Goto Cleanup
      End If
   End If
   ' Unhide it and maximize it here at the end.
   me.visible = true
   docmd.Maximize
Cleanup:
      rs.Close
   db.Close

   Exit Sub

Error_Handler:
   MsgBox Err.Description, vbOKOnly, "Error #" & Err.Number
   Exit Sub
End Sub

Open in new window


And use error handling here:

Private Sub cmdAmendment_Click()
   On error goto EH
   If Me.Dirty Then
        Me.Dirty = False
    
    End If
  
 DoCmd.RunCommand acCmdSaveRecord

        DoCmd.OpenForm "FrmLCAmendedHistory", , , , , , OpenArgs:=Me.ID & ";" & Me.cboEndUserID & ";" & Me.txtLCNo
Exit sub
EH:
   if error = 2501 then 
     resume next
   else
      Msgbox err.description

End Sub 

Open in new window



pdvsaProject finance

Author

Commented:
dang you are good.  that worked.  I dont get it but it worked.  
I think what you added was the visible part.  

I can rest now.  I am so glad to get this out of the way.  thank you dearly...

Most Valuable Expert 2012
Top Expert 2013

Commented:
Glad to help out.

Just explaining -

You caught the added code to hide/unhide the form.

I also moved your doCmd.Maximize code to run it after the password validation.

That error handling in Form1 is also critical.

Just for fun, try taking it out temporarily to see what it actually does  :-)
pdvsaProject finance

Author

Commented:
MBIZUP:  thank you again.  

One slight thing I noticed was that if I simply close the frmPassword then the frmLCAmendedHistory does open which I would not really wnat that that happen.  How do you suggest I get around a user just closing the form and prevent the frmLCAmendedHistory from opening?

thank you for the help!  Maybe that is in the error handler.
Most Valuable Expert 2012
Top Expert 2013

Commented:
It looks like your password form already handles null password and disallows them. That's good.

Additionally you should get rid of the close button / control box through the forms property sheet.

The user should be forced to use the custom button.
Most Valuable Expert 2012
Top Expert 2013

Commented:
Let me know if that works out okay.

There are other ways to handle the issue you described, but that is the simplest.
pdvsaProject finance

Author

Commented:
ok i disabled the close button.  It works.  

I do see that the form does try to open in the background but it is only a sorta flicker.  I seem to remember some fix for that flickering?  

thank you.  just whenever you get a sec.

here is the code:
let me know if you see some simple soltuion to that flicker


Private Sub Form_Open(Cancel As Integer)

   Me.Visible = False  '<--- Hide the form
   Dim Hold As Variant
   Dim tmpKey As Long
   Dim I As Integer
   Dim rs As DAO.Recordset
   Dim db As DAO.Database

   On Error GoTo Error_Handler
   ' Prompt the user for the Password.
   DoCmd.OpenForm "frmPassword", acNormal, , , , acDialog
            Hold = MyPassword
' Open the table that contains the password.
   Set db = CurrentDb
   Set rs = db.OpenRecordset("tblPassword", dbOpenTable)
   rs.Index = "PrimaryKey"
   rs.Seek "=", Me.Name
   If rs.NoMatch Then
      MsgBox "Sorry cannot find password information. Try Again"
      Cancel = -1
   Else
      ' Test to see if the key generated matches the key in
      ' the table; if there is not a match, stop the form
      ' from opening.
      If Not (rs![KeyCode] = KeyCode(CStr(Hold))) Then
         MsgBox "Sorry you entered the wrong password." & _
            "Try again.", vbOKOnly, "Incorrect Password"
          Me.Visible = True '<-------*** Unhide it before the cancel = -1
         Cancel = -1
         GoTo Cleanup
      End If
   End If
   ' Unhide it and maximize it here at the end.
   Me.Visible = True
   DoCmd.Maximize
Cleanup:
      rs.Close
   db.Close

   Exit Sub

Error_Handler:
   MsgBox Err.Description, vbOKOnly, "Error #" & Err.Number
   Exit Sub

End Sub

Open in new window

Most Valuable Expert 2012
Top Expert 2013

Commented:
Interesting - I'm not sure if it is speed specific to your machine, or maybe the popup password form contributing to that flicker.  I have never seen it using similar code on systems at work.

Try revising your OpenForm command like this:

  DoCmd.OpenForm "FrmLCAmendedHistory", WindowMode:= acHidden, OpenArgs:=Me.ID & ";" & Me.cboEndUserID & ";" & Me.txtLCNo

Open in new window


And your Open Event like this:

Private Sub Form_Open(Cancel As Integer)
   Dim Hold As Variant
   Dim tmpKey As Long
   Dim I As Integer
   Dim rs As DAO.Recordset
   Dim db As DAO.Database

   On Error GoTo Error_Handler
   ' Prompt the user for the Password.
   DoCmd.OpenForm "frmPassword", acNormal, , , , acDialog
            Hold = MyPassword
' Open the table that contains the password.
   Set db = CurrentDb
   Set rs = db.OpenRecordset("tblPassword", dbOpenTable)
   rs.Index = "PrimaryKey"
   rs.Seek "=", Me.Name
   If rs.NoMatch Then
      MsgBox "Sorry cannot find password information. Try Again"
      Cancel = -1
   Else
      ' Test to see if the key generated matches the key in
      ' the table; if there is not a match, stop the form
      ' from opening.
      If Not (rs![KeyCode] = KeyCode(CStr(Hold))) Then
         MsgBox "Sorry you entered the wrong password." & _
            "Try again.", vbOKOnly, "Incorrect Password"
         Cancel = -1
         GoTo Cleanup
      End If
   End If

   ' Unhide it and maximize it here at the end.
   Me.Visible = True
   DoCmd.Maximize
Cleanup:
      rs.Close
   db.Close

   Exit Sub

Error_Handler:
   MsgBox Err.Description, vbOKOnly, "Error #" & Err.Number
   Exit Sub

End Sub 

Open in new window

pdvsaProject finance

Author

Commented:
mbizup:  not sure if you are still following.  I dont see that I ever did respond in regards to the "flicker"...anyways the last 2 posts did solve the issue.  I am exploring setting up pw on forms again.  

Are you still monitoring this question?