Solved

Access 2007 Question

Posted on 2011-02-22
5
419 Views
Last Modified: 2012-05-11
Good afternoon,

quick question.


Working on a general PO system. Lookin to add an approve button. What i want this button to do is when click it would pop up a box and ask for a password. When the password is inputed correctly, the date would automatically poulate a unbound datafeild. Anything you can help me with?

thanks!
0
Comment
Question by:hcp27
  • 2
  • 2
5 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34956298
Supposing you don't need extremely rigorous security, you could save the password to a custom database property (they can only be examined in code), and put up a dialog form asking for the password; if it is entered correctly, then an Approve button would be enabled, which the user could click to put today's date into the unbound date control.  Here is sample code for the password textbox's AfterUpdate event, plus some code samples illustrating the usage of custom database properties.  Select the Password input mask for txtPassword, so what is typed won't appear on the screen.
Private Sub txtPassword_AfterUpdate()

   Dim strPassword As String
   
   strPassword = GetProperty("DatePassword", "")
   If Me.ActiveControl.Value = strPassword Then
      Me![cmdOK].Enabled = True
   Else
      Me![cmdOK].Enabled = False
   End If
   
End Sub

==========================

Option Compare Database
Option Explicit

Private dbs As DAO.Database
Private prp As DAO.Property
Private prps As DAO.Properties
Private strPropertyName As String
Private strPropertyValue As String
Private lngDataType as Long
Private varPropertyValue As Variant


Public Sub SetProperty(strName As String, lngType As Long, _
   varValue As Variant)
'Created by Helen Feddema 2-Oct-2006
'Modified by Helen Feddema 2-Oct-2006
'Called from various procedures

On Error GoTo ErrorHandler

   'Attempt to set the specified property
   Set dbs = CurrentDb
   Set prps = dbs.Properties
   prps(strName) = varValue

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
    If Err.Number = 3270 Then
      'The property was not found; create it
      Set prp = dbs.CreateProperty(Name:=strName, _
         Type:=lngType, Value:=varValue)
      dbs.Properties.Append prp
      Resume Next
   Else
   MsgBox "Error No: " & Err.Number _
      & " in SetProperty procedure; " _
      & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Sub

Public Function GetProperty(strName As String, strDefault As String) _
   As Variant
'Created by Helen Feddema 2-Oct-2006
'Modified by Helen Feddema 2-Oct-2006
'Called from various procedures

On Error GoTo ErrorHandler
   
   'Attempt to get the value of the specified property
   Set dbs = CurrentDb
   GetProperty = dbs.Properties(strName).Value

ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 3270 Then
      'The property was not found; use default value
      GetProperty = strDefault
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number _
         & " in GetProperty procedure; " _
         & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Function

Public Function ListCustomProps()
'Created by Helen Feddema 3-Oct-2006
'Modified by Helen Feddema 3-Oct-2006
'Lists DB properties created in code (as well as built-in properties)

On Error Resume Next
   
   Set dbs = CurrentDb
   Debug.Print "Database properties:"
   
   For Each prp In dbs.Properties
      Debug.Print vbTab & prp.Name & ": " & prp.Value
   Next prp

End Function

==================================
Usage examples:

Private dbs As DAO.Database
Private prp As DAO.Property
Private prps As DAO.Properties
Private lngDataType As Long
Private strPropertyName As String
Private strPropertyValue as String
Private varPropertyValue As Variant

Date
====
    strPropertyName = "PropName"
    strPropertyValue = CStr(dteStart)
    lngDataType = dbDate
    Call SetProperty(strPropertyName, lngDataType, _
       strPropertyValue )

   GetStartDate = CDate(GetProperty("PropName", ""))

Text
====
   strPropertyName = "PropName"
   strPropertyValue = CStr(cbo.Value)
   lngDataType = dbText
   Call SetProperty(strPropertyName, lngDataType, _
      strPropertyValue )

   strDocsPath = GetProperty("PropName", "")

Long
====
   strPropertyName = "PropName"
   strPropertyValue = CStr(lngID)
   lngDataType = dbLong
   Call SetProperty(strPropertyName, lngDataType, _
     strPropertyValue )

   lngID = CLng(GetProperty("PropName", ""))

Integer
=======
   strPropertyName = "PropName"
   strPropertyValue = CStr(lngID)
   lngDataType = dbInteger
   Call SetProperty(strPropertyName, lngDataType, _
     strPropertyValue )

   intID = CInt(GetProperty("PropName", ""))

Saving to a custom property from a control's AfterUpdate event
==============================================================
Private Sub txtDate_AfterUpdate()
'Created by Helen Feddema 2-Sep-2009
'Last modified 2-Sep-2009

On Error GoTo ErrorHandler
   
   If IsDate(Me![txtDate].Value) = True Then
      dteSingle = CDate(Me![txtDate].Value)
      strPropertyName = "SingleDate"
      Call SetProperty(strName:=strPropertyName, _
         lngType:=dbDate, varValue:=dteSingle)
   End If
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & _
      Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 

Author Comment

by:hcp27
ID: 34956848
Thanks for the responce...


As i am not that clear on the whole VB code thing...is there any publications you would suggest that is an easy read for me? I could sure use some direction when decoding all this code you just put one me. I wouldnt even know where to put this code much less of how to use it.


Please let me know if you can suggest ANYTHING for me.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 125 total points
ID: 34964825
<As i am not that clear on the whole VB code thing...is there any publications you would suggest that is an easy read for me? I could sure use some direction when decoding all this code you just put one me. I wouldnt even know where to put this code much less of how to use it.>
...Then it would seem that your first question should be:
"How do I learn VBA"
;-)

Almost any question you ask here will require at least a basic understanding of VBA coding.
However, this is a very broad topic.
For the purposes of this specific Q, I will explain the code and how it applies here.


"When the password..."
"The" password?
Where are you storing/setting this password?


As Helen stated, this can open a whole other can of worms if you need anything more that the most basic system here.
Your skill level with VBA is also a concern here and contributes to the reduction in complexity.

You can do something like this:
In a public Module, Create a Public constant to store the Password

    Public Const strconstPassword As String = "Password"


Then on the OnClick even of your "Approval" button use code like this:

Private Sub cmdApprove_Click()
    'If the text the user enters in the Inputbox matches the password stored in the Public costant
    If InputBox("Please enter Password.:") = strconstPassword Then
        'Insert today's date in the ApprovalDate Field
        Me.ApprovalDate = Date
        'Save this record
        DoCmd.RunCommand acCmdSaveRecord
    'Or else
    Else
        'Display a message that they typed the worng password
        MsgBox "Incorrect Password.", vbInformation
        'Exit this code
        Exit Sub
    End If
End Sub


Working sample attached.

;-)

JeffCoachman
Access-EEQ26840035ApprovalDatePa.mdb
0
 

Author Comment

by:hcp27
ID: 34965823

Jeff!

Can you let me know how you suggest uping my VB knowledge!?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34967879
Again, VBA is a Huge topic.

You can spend an entire school yer "learning" it.

For the purposes of using it here, you just need to understand how to insert/modify the code to work in your DB.

If you view enough posts here, you will see that the code is not all that hard to understand.
Jut writing it from scratch is the difficult part.
;-)

But if you want a basic reference book on Access in general and VBA, I recommend the"Access Bible" series;
http://www.amazon.com/Access-2007-Bible-Michael-Groh/dp/0470046732
General begginners Access VBA:
http://www.amazon.com/Microsoft-Access-Programming-Absolute-Beginner/dp/1598633937/ref=sr_1_1?s=books&ie=UTF8&qid=1298533891&sr=1-1
More advanced Access VBA:
http://www.amazon.com/Access-2007-Programmers-Reference-Programmer/dp/0470047038/ref=sr_1_2?s=books&ie=UTF8&qid=1298533891&sr=1-2
...et al

;-)

JeffCoachman
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now