?
Solved

Access 2007 Question

Posted on 2011-02-22
5
Medium Priority
?
429 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

764 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