Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37

    Unanswered: Assistance in creating a fuction

    I need help creating a custom function to write data to an Access Table and do not know where to start.

    I am looking to create a function with a string option that then writes data to a table using a SQL Statement. This is for an activity log within the database to track users activity within the database.

    I have a 4 field table named LocalUser that is written every time a user logs in, storing the Computer network name, the Network User, The Database User’s login and the department that the user is assigned to. I then have a 8 field table that records the Date and time of the log entry, the network user name, the network computer name, the database user name, the department of the user and a text string of what the user did which is static to every menu option the user enters.

    What I am trying to do is store the static activity in a string:

    LogActivity = “User went into menu A”

    And then call a function:

    WriteActivityLog( LogActivity )

    Which then runs a SQL statement to write the data into a table stored within the database. This will save me many hours of entering the SQL statement over and over and keep my VBA coding down to a more manageable size. I have never written a function before and am wondering how this can be done.

    Any help would be much appreciated.

    -Tony

  2. #2
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37
    Figured this out on my own, please disregard.

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by gorf8083
    Figured this out on my own, please disregard.

    Hi Tony,

    For the sake of others that happen upon this site with the same situation as you, would you please POST on here your solution to that others may benefit from it. It's sort of like passing on your knowledge and paying it forward.

    thanks and have a nice one,
    BUD

  4. #4
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37

    Talking

    Okay, lets start with the beginning. I have a database that I need to create an activity log for. I have 2 tables that I use to create the activity log.

    TBL_LocalLogin: This table Stores Information on the user when they log into the access database.
    EmpName - text - Stores the name of the user logged into the Database
    EmpDepartment - text - Stores the department the logged in user
    EmpComputer - text - Stores the name of the computer that is currently being used
    EmpLogin - text - Stores the Windows Network Login of the computer being used

    TBL_ActivityLog: This table actually stores all of the activity of the user when they are useinf the database
    LogKey - Primary Key, auto number - used to reference the activity log, also will let you know how many entries are in the log
    LogDate - Date/Time, Format=Short Date, Default value = "=now()" - This stores the Date of the activity that is being stored.
    LogTime - Date/Time, Format=Long Time, Default value = "=now()" - This stores the Time of the activity that is being stored.
    LogComputer - Text - Stores the PC that the Activity Log entry was created on.
    LogLogin - Text - Stores the Windows Network Login of the user logged into the machine that the Activity Log entry was created on.
    LogUser - Text - Stores the Username of the user logged into the access database.
    LogDepartment - Text - Stores the Department of the User logged into the access database.
    LogActivity - Text - Stores the Activity that the user logged into the database has done.
    I know that the time and date field could have been combined into one field; it just is easier for my users to read with them in separate fields. Also the LogActivity can be set to memo but I am not storing all that much information in LogActivity field so text was sufficient.

    Ok down to business, I was looking for a way to enter information from the TBL_LocalLogin into the TBL_ActivityLog when a user does things such as: Login to the database, change passwords, edit inventory, edit vendors... the list is endless of what you can have the activity log record. I did not want to have to write a SQL statement for each form and action the user is taking so I figured that if I created a function to call the same SQL statement over and over and just change the activity going into the function that would work much easier as well as save me a lot of typing and space in my database.

    This is what I have come up with.

    I created a Module Named ActivityLogString and within that I created a function:

    Code:
    Function ActivityLog(Activity As String)
    
            Dim LDP As String
            Dim LUS As String
            Dim LPC As String
            Dim LNN As String
            Dim strSQL As String
            
            LPC = DLookup("EmpComputer", "TBL_LocalLogin")
            LNN = DLookup("EmpLogin", "TBL_LocalLogin")
            LUS = DLookup("EmpName", "TBL_LocalLogin")
            LDP = DLookup("EmpDepartment", "TBL_LocalLogin")
    
            strSQL = "INSERT INTO TBL_ActivityLog  " & vbCrLf & _
            "( LogComputer, LogLogin, LogUser, LogDepartment, LogActivity ) " & vbCrLf & _
            "SELECT  " & vbCrLf & _
            "'" & [LPC] & "' AS LogComputer,  " & vbCrLf & _
            "'" & [LNN] & "' AS LogLogin,  " & vbCrLf & _
            "'" & [LUS] & "' AS LogUser,  " & vbCrLf & _
            "'" & [LDP] & "' AS LogDepartment,  " & vbCrLf & _
            "'" & [Activity] & "' AS LogActivity;"
    
            CurrentDb.Execute strSQL
    
    End Function
    Let me explain the coding here just a little bit. Function ActivityLog (Activity As String) is the start of the function and is declares the input variable of Activity as a String (text) value.

    The next 5 lines are setting up 5 variables also as strings so I can use them within the function.

    The next 4 lines are using the DLookup function to look at the information originally stored in the TBL_LocalLogin table.

    On now for the fun stuff, the next 8 lines I am creating a SQL string (which another user in the forum taught me how to do) that is storing the 4 strings we looked up in the previous 4 lines of code into the TBL_ActivityLog Table. If you notice I am not even trying to enter data into the LogKey, LogDate and LogTime fields. When we enter data into the other fields it will autopopulate the information because of how the table is set up. The 4 variables that we set up above are stored into the fields they should be and then the 5th item is the Activity that was inputted into the function.

    Next line is just telling the Access Database to execute the SQL Command stored in the strSQL on the current database. This will actually write the data to the TBL_ActivityLog.

    The End Function just cleans up the function and tells the Visual Basic that we are done with it.

    Ok, how we use this. Actually it's quite simple even though it looks quite hard. When ever you create a button to do an action or any Event you want recorded, edit the event procedure and add ActivityLog ("Activity you want stored in the activity log table") to your lines of code. What I have done is actually go into the Menu Forms I have created, in the properties for the form it's self, created an event procedure in the On Open Event. Click the ... to the right of the On Open Event and then choose the code builder and enter the function and the string you want store.

    An example of this when a user logs into the Database I use ActivityLog ("User Logged into System") and when the user logs out of the system I use ActivityLog ("User Logged out of System") and so on.
    Last edited by gorf8083; 10-27-08 at 13:57.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I use something similar (with a few tweaks and shorter code.) I like mine in that it grabs the actual LoggedIn user, computer, and department right from the system. Here is what the ADO code looks like:

    Function ActivityLog(Activity as variant) 'Variant here so some values (ie. nulls) don't crash the function
    if not isnull(Activity) then
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from dbo_tblActivityLog"
    rs.open strSQL,currentproject.connaction,adopendynamic,adl ockoptimistic
    rs.addnew
    rs!LogActivity = Activity
    rs!LogUser = GetUser() - (note: from the Getuser function which is posted in the code bank. It returns the actual system user who logged onto the machine. Download example and import GetUser module to utilize - the function only has 5 lines of code and works very well (I've never had a problem with it not being able to return the LoginID of the user.)
    rs!LogComputer = GetComputer() - (same module and gets user's computer name from the system.
    rs!LogDepartment = GetDepartment() - (same as above. Or Active Directory lookup)
    rs!TimeOfActivity = Now()
    rs.update
    rs.close
    set rs = nothing
    else
    msgbox "Error - Null value passed to ActivityLog Function!"
    end if
    End Function

    Gorf8083-

    The function you posted seems like it could be a problem if multiple users were to log onto the same machine and use the mdb or multiple users from any machine were to use the mdb at the same time (unless you have an mdb (frontend I'm assuming) for each user.) But still, if multiple users are in the tbl_LocalLogin, how would this code: LNN = DLookup("EmpLogin", "TBL_LocalLogin") know the correct login to grab? Or is it always assumed that the last record in this table will always match the correct user, computer, etc.?
    Last edited by pkstormy; 10-29-08 at 03:24.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37

    Talking

    pkstormy -

    I agree that this would cause a problem if more than one person is logged onto the system at one time. This is why I use front end/back end databases. The Front End Database stores key tables that are important to the user (TBL_UserLogin and TBL_CurrentDepeartmentItems). The back end Database stores things such at the Incoming Inventory and Outgoing Inventory, Departments, Vendors, Item Numbers and Names, Ect. Actually I have the Database Split into 3 Databases and link them using the Get External Data -> Linked Data tool built in Access, so its a Front end, Inventory and activity log databases.

    I also have the same Functions that grab the Network Username and Network Computername and it stored it in the TBL_localLogin right after the user makes a successful login to the system. I figured it would be easier to run the function one time instead of running the same function over and over and within another function.

    I would love to know how your String works though. I am still very new to the VBA and I know the basics of how to program some things but any other tricks I can learn would be great!

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Gorf8083,

    Here's what I use:
    1. I have a vb script I use to clone the frontend which works (EXTREMELY) well for multi-users in an mdb/mde file and I think it would be ideal for your situation where only 1 specific user record needs to be in the TBL_UserLogin. Using this vb script resolves a number of problems that are faced with multiple users using an mdb. Here's the link: http://www.dbforums.com/showpost.php...6&postcount=19
    I might strongly encourage you to take a look at this vb script as I think it will really help (I've been using it for over 10 years now at every company I've designed programs for without any problems). It has literally resolved countless, countless problems and is ideal for a split front-end/back-end system.
    2. Here's the GetUser routine. The 2nd link beow is using the GetUser routine with a security table enabled.
    Link 1: http://www.dbforums.com/showpost.php...0&postcount=20
    Link 2 (with security table): http://www.dbforums.com/showpost.php...9&postcount=68

    If you have any questions on these examples, feel free to ask.
    Last edited by pkstormy; 10-29-08 at 11:43.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •