Hi, I wonder if someone is able to help me as i am completely stuck . I have built an access database which is used at work by a number of people within my team. I want to add an authorise button to the database, which when clicked will stamp the record with the date, time and also the users windows logon username. Is this possible? The reason i need this is so that we can clearly see who has authorised and this cannot be changed by anyone. Any ideas?
Create another new Table and name it tblRecordAuthorization. This table would contain the Fields:
Code:
RecordAuthID (AutoNumber - PK) - Caption: Authorisation ID
RecordID (Number - Long Integer - Indexed (Yes Duplicates OK)) - Caption: Record ID
AuthorizationDate (Date - General Date Format) - Caption: Authorization Date
AuthorizedBy (Text - 50) - Caption: Authorized By
FromComputer (Text - 50) - Caption: From Computer
Now in the Form's code module under your Authorizaton Button's OnClick event:
Code:
Private Sub AuthorizationButton_Click()
[COLOR="DarkGreen"]'Trap Errors...[/COLOR]
On Error GoTo Error_Authorize
[COLOR="DarkGreen"]'Has the Form had any data entry..[/COLOR].
If Me.Dirty = False Then
[COLOR="DarkGreen"]'Nope. Inform User and get outta here.[/COLOR]
MsgBox "There has been no data entry for this Record.", _
vbInformation, "Nothing To Authorize"
Exit Sub
End If
[COLOR="DarkGreen"]'--------------------------------------------------------------
'If you do not want a Password to access the Authorization
'button then get rid of his section of code. To get the button
'to work, the User must enter: 1234[/COLOR]
Dim AuthPassword As String, RetStrg As String
[COLOR="DarkGreen"]'Provide a Password. Normally this would be a Public Variable
'declared within a Database Code Module and the password pulled
'from a Table. Simple Encryption/Decryption would also normally
'be done on the Password. For the sake of this example we keep
'it simple.[/COLOR]
AuthPassword = "1234"
RetStrg = InputBox("Please supply the Authorization Password.", _
"Authorization Password Reqired", "")
If RetStrg = "" Then
Exit Sub
ElseIf RetStrg <> AuthPassword Then
MsgBox "Incorrect Password. Please try again.", vbExclamation, _
"Incorrect Password"
Exit Sub
End If
[COLOR="DarkGreen"]'--------------------------------------------------------------
'Yes. There has been Data Entry so...
'Declare a String Variable to hold our INSERT Query[/COLOR]
Dim StrgSQL As String
[COLOR="DarkGreen"]'Create the INSERT Query[/COLOR]
StrgSQL = "INSERT INTO tblRecordAuthorization (RecordID,AuthorizationDate,AuthorizedBy,FromComputer) " & _
"VALUES (" & Me.[I][COLOR="Red"]myFormsRecordIDTextBoxName[/COLOR][/I] & ",#" & Now() & "#,'" & Environ("USERNAME") & "','" & _
Environ("COMPUTERNAME") & "');"
[COLOR="DarkGreen"]'Disable MS-Access Warings like: "You are about to append a record to Table blah blah"[/COLOR]
DoCmd.SetWarnings False
[COLOR="DarkGreen"]'Run the Query String.[/COLOR]
DoCmd.RunSQL StrgSQL
[COLOR="DarkGreen"]'Enable MS-Access Warnings again.[/COLOR]
DoCmd.SetWarnings True
[COLOR="DarkGreen"]'Save the Form Record so as to Reset the Dirty property.[/COLOR]
DoCmd.RunCommand acCmdSaveRecord
Exit_Authorize:
Exit Sub
Error_Authorize:
[COLOR="DarkGreen"]'Display the Error if any.[/COLOR]
MsgBox "Error In Authorization Button Code:" & vbCr & vbCr & _
Err.Number & " -- " & Err.Description, vbExclamation, _
"Database Code Error"
Resume Exit_Authorize
End Sub
Looks like a lot but it really isn't. Just lot of Comments.
Now you can set up a one to many relationship between the Table bound to Form and the tblRecordAuthorization Table.
- Open the Relationships window (Tools | Relationships..);
- Right Click your mouse pointer over a blank spot in the window and select Show Tables;
- From the Tables list displayed select the Table bound to your Form then select Add;
- Again from the Table List select the tblRecordAuthorization Table and select Add;
- Close the Tables List window;
- Select and hold the left mouse button down on the RecordID Field from the Table field list which is bound to your Form and drag then drop it onto the RecordID field within the tblRecordAuthorization Table field list (this field should be named the same in both Tables). The Edit Relationships dialog box should appear;
- Select the Join Type.. button located within the Edit Relationships dialog box. The Join Properties window will appear;
- Select the number 3 option then click the OK button. Also select the OK button within the Edit Relationships dialog box;
- Close the Relationships Window and accept to Save it. You're done.
Use the Authorization button a few times on different Records then from the Database Objects Window, open the Table that is bound to your Form. It will display in Datasheet view, notice the little plus symbol (+) just to the right of the Record Selector button in the DataSheet. Click on the little + symbol, the Authorizations for that particular record are displayed.
I road it completely but not understand, my problem is quit similar but I could not achieve the goal. I want to just put windows login ID / Windows user name to my table’s field automatically, like date stamp.
. I have built an access database which is used at work by a number of people within my team. I want to add an authorise button to the database, which when clicked will stamp the record with the date, time and also the users windows logon username. Is this possible? The reason i need this is so that we can clearly see who has authorised and this cannot be changed by anyone. Any ideas?
