How to create an "email form"?

[ittechguy]

I've got a database I've been working on that keeps track of communication equipment and the room/cabinets they're installed in. We're required to send out an email every time a change to the network has been made. For an example, if one piece of equipment was removed from the network we would write an email saying

Removed ABC equipment from Building A, Room 1 on 6 Nov 2015 at 2:30PM.

Equipment Type: Switch
Model #: Cisco 3750
Host Name: SwitchABC
IP Address: 192.168.0.1

Another example would be if one or more pieces of equipment was removed, and then one or more was installed. Then the format would be similar.

Uninstalled ABC Equipment (same format as above.

Installed XYZ equipment (same format as above).

Another example would be if the host name and/or IP address was changed.


Our current process is to perform the work, then send out an email so that everyone is aware of the changes. Now the process is to make changes in the database, and then send out the email. I would much prefer if I could send an email either directly from Access, or if it would open up outlook mail with the required data in the description and with the TO box and the subject already filled out. Is this possible?

Thanks!

[orange]

See these samples for ideas:
http://www.blueclaw-db.com/download/...il_example.htm
http://www.blueclaw-db.com/access_em...nd_outlook.htm
http://www.blueclaw-db.com/read_emai...ss_outlook.htm

[ittechguy]

Thanks for the links Orange!

I think the first (and probably hardest) challenge is to create the form.

An idea I had was to create a form which allowed me to make changes to the network. Basically, add and/or remove all in the same form. I could have a set of text/combo boxes used to remove a record. And then have an identical set of text/combo boxes used to add a record.

The issue I'm running into is I'm not sure how to handle if I need to remove or add more than 2 records at a time. Typically if in one day we go to a building and uninstall 2 switches, we will send out the email saying that we've uninstalled 2 switches and include all the information. I would prefer if the database could do it the same way, rather than having to remove/add one at a time.

[orange]

Here is an approach --adjust as needed.

Identify the logic involved(overview first then add details as you get to the nitty gritty..)
Build some functions.(these do will do all the steps for a single add/delete action)
Then call the functions with appropriate parameters(recordId..) according to your logic.
You'll probably save these as transactions to an operations Log.

Suppose you had a routine to identify what you were going to do.

Let's say it is to replace 2 switches.
So you need to identify the Switches to Remove (switchId, Location...etc) and
you need to identify the Switches to Add.
Your will need all the info about each action (SwitchId, Location..) to in your email.
You will also have to identify who should receive the email(if it's targeted to specific users/managers)

So you're going to remove SwitchX and switchY; and add Switch23 and Switch37 respectively. So your to do list is:
SwitchX Delete, Switch23 ADD
SwitchY Delete, Switch37 ADD and
These are typical transactions
1-get details for SwitchX(manufacturer, model, serial,Bldg, Room,network,manager??) identify as Delete
2-get details for SwitchY(manufacturer, model, serial,Bldg, Room,network,manager??) identify as Delete
3-get details for Switch23(manufacturer, model, serial,Bldg, Room,network,manager??) identify as Add
4-get details for Switch37(manufacturer, model, serial,Bldg, Room,network,manager??) identify as Add

Call functions in a processing loop passing appropriate values and check responses
Function SwitchDELETE(D_SwitchID,...) returns value for successful/failed
Function SwitchADD(D_SwitchID,...) returns value for successful/failed

After Switch actions have been completed, assemble the details for the email(s).
You can query the Operations Log and your manager/bldg/etc files to get any/all details.
Send email(s).

If you have 10 or 20 switches to replace, you identify the switches involved, then use a Loop structure to process them all.

My view is you will need to add such transactions to an Operations/Audit Log.

Good luck.

[ittechguy]

Thanks for the help.

It seems perhaps I am going to need to build an audit log before I can figure out how to email the changes. I'm doing research on that now.

I've wanted an audit log anyways, but I've been putting it off because I know nothing about it.

[orange]

See this thread https://www.accessforums.net/forms/h...orm-24504.html

I posted a database in post14. You can download it and see how it works. Get some ideas and use as a starting point to build your own.

You'll see what others were thinking as you read through the various posts.

As for audit log info see allen browne AuditLog

[ittechguy]

Thanks for the sample database! Definitely does help.

It seems to me like I have two options for an audit log:

1. Create a separate table which logs changes one or more tables.
2. Use only one equipment table and have a field for "DateInstalled", a field for "DateUninstalled", and a field for the status (operational, in storage, discontinued, etc). Then information is changed as necessary, but records are never deleted.

What is the "preferred" method? I'm more inclined to use the second option.

[orange]

My recommendation would be a separate log table. Also, it would be a good exercise (analysis and design), to identify what types of transactions are relevant to your work/database:

For example:

Adding/deleting a Bldg
Adding/deleting a POC/FMgr
Adding/deleting a Room
Adding/deleting a Switch
Adding/deleting a User
...

Then you might have a function/routine to add a record to your LogTable


And some search/print routines with options to see the history of Bldg, Room, Network.....etc.

The LogTable approach can be a big help if you experience some issues and can review exactly what was done to X,Y or Z during a time period (Jun1--Jun 15) etc. It is a good practice

Years back I needed a simple Log -basically a table in which I could write a status/text record with a timestamp.
It was more of a memory jogger -who has been working on what and when. It was useful for many purposes including --filling out time sheets and work progress (after the fact).

Here is that code:

'---------------------------------------------------------------------------------------
' Procedure : Logger
' Author    : jack
' Date      : 1/21/2009
' Purpose   : To write records to a LOG file using FileSystemObject.
'
'Parameters
' sLogName As String -- full path and file name of the log file
' sLogRec As String  -- record to be written to the log
'
' NOTE: Each log record has a timestamp appended
'
' Special Note/restriction:
'***** Must set a reference to MICROSOFT SCRIPTING RUNTIME ***
'---------------------------------------------------------------------------------------
'
Sub Logger(sLogName As String, sLogRec As String)
      Dim tslog As TextStream
      Dim fileLog As File
      Dim i As Integer
      Dim fso As FileSystemObject
10       On Error GoTo Logger_Error

20    Set fso = New FileSystemObject
30    Set fileLog = fso.GetFile(sLogName) '"I:\wordtest\output\Results.log")
40    Set tslog = fileLog.OpenAsTextStream(ForAppending)
50    tslog.WriteLine Now() & vbTab & sLogRec
60    tslog.Close

70       On Error GoTo 0
80       Exit Sub

Logger_Error:

90        MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure Logger of Module ADO_Etc"
End Sub

And a test routine

' Procedure : testLogger
' Author    : Jack
' Created   : 4/18/2011
' Purpose   : Sample procedure to use the Logger function
' Log file must exist before using Logger
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Sub testLogger()
      Dim mlog As String
      Dim res As String
10       On Error GoTo testLogger_Error

20    res = "*Pass "
30    mlog = "I:/wordtest/output/result_apr2011.txt"
      Dim i As Integer
40    For i = 1 To 20
50     Call Logger(mlog, res & i)
60    Next i

70       On Error GoTo 0
80       Exit Sub

testLogger_Error:

90        MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure testLogger of Module ADO_Etc"

End Sub

[ittechguy]

Ok, I am going to use your advice and create a separate log table. I've been working off the link you gave me from Allen Browne. I'm trying to create the module per his instruction, and its failing to compile here:

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

It says that the code must be modified to work with 64 bit systems. Do you know how I would edit that so it would work?

[orange]

Since you are using 64 bit Office/Access, you'll have to do a little googling.
I'll pass a couple of links that should help.
I use 32bit Access on win 8.1 64 bit. Even M$oft recommends using 32 Access, and 64 bit Excel if your requirements need it.

https://msdn.microsoft.com/en-us/lib.../gg264421.aspx
https://msdn.microsoft.com/en-us/lib.../gg278832.aspx

Good luck.

[orange]

I found these references that may be helpful to you.
http://www.jkp-ads.com/articles/apideclarations.asp
http://www.utteraccess.com/forum/index.php?showtopic=2032773&hl=
http://www.utteraccess.com/forum/index.php?showtopic=2032783&hl=

Good luck.

[ittechguy]

I'm making progress.

I'm using the link for Allen Browne's Audit Logger. I'm trying to make it log changes to a subform, but its not working. I'm using the BeforeUpdate and AfterUpdate events of the subform, from the main form, like this:

Option Compare Database
Dim bWasNewRecord As Boolean


Private Sub frmSubAddEquipment_BeforeUpdate(Cancel As Integer)
    bWasNewRecord = Me.NewRecord
    Call AuditEditBegin("tblEquipment", "audTmpEquipment", "audTmpEquipmentID", Nz(Me!frmSubAddEquipment.Form!txtEquipmentPK, 0), bWasNewRecord)
End Sub
Private Sub frmSubAddEquipment_AfterUpdate()
    Call AuditEditEnd("tblEquipment", "audTmpEquipment", "audEquipment", "audEuipmentPK", Nz(Me!frmSubAddEquipment.Form!txtEquipmentPK, 0), bWasNewRecord)
End Sub

It doesn't appear to be firing at all. It gives me no error messages and nothing is in the audit tables. I have tried placing this code directly in the subform (frmSubAddEquipment), but I get the same result.

[ittechguy]

I figured it out! Took a lot of troubleshooting, but I found out I had some issues with my field names.

Now the audit logger works perfectly. I need to figure out how to query the audit log and send an email...

[orange]

Good stuff.

Build a little proof of concept prototype ---first just be able to select some entries from the log. You don't need fancy formatting for this. It could just be select xyz from log where TransType is HH.
The look at the email links I provided previously. Create just a very simple sMessage --can be as simple as
AB ="This is my test message for email development". Then using the code examples send an email message to yourself.
Just get a simple email working, then add a little more detail. Do it in steps, you will gain experience and confidence --you'll surprise yourself.
Good luck.