Wulfen's Profile User Rating: -----

Reputation: 0 Apprentice
Group:
New Members
Active Posts:
11 (0.01 per day)
Joined:
16-December 08
Profile Views:
1,176
Last Active:
User is offline Oct 14 2012 01:48 PM
Currently:
Offline

Previous Fields

Country:
US
OS Preference:
Windows
Favorite Browser:
FireFox
Favorite Processor:
Who Cares
Favorite Gaming Platform:
PC
Your Car:
Pontiac
Dream Kudos:
0

Latest Visitors

Icon   Wulfen has not set their status

Posts I've Made

  1. In Topic: Access 2010 - Two Questions

    Posted 13 Oct 2012

    Okay, I managed to figure out how to get the peoples names taken from the list separated by the semicolon, but now I need to know how to get this bit of code to work correctly. It takes information from the temp table (a copy of the main table that is deleted once the user saves the document however it currently only copies the information from the first record to all the records in the table. I got it to stop doing that by looking for the ID number first then copying the information, however I can't figure out how to get it to do that for every record in the temp table.

    Private Sub Command17_Click()
    On Error GoTo Error_Handler
        Dim db          As DAO.Database
        Dim rs          As DAO.Recordset
        Dim iCount      As Integer
     
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("Table1", dbOpenDynaset) 'open the recordset for use (table, Query, SQL Statement)
    DoCmd.SetWarnings False 'Turn off warnings
    
        With rs
            If .RecordCount <> 0 Then 'Ensure that there are actually records to work with
                'The next 2 line will determine the number of returned records
                rs.MoveLast 'This is required otherwise you may not get the right count
                iCount = rs.RecordCount 'Determine the number of returned records
                
                Do While Not .BOF
    If rs![ID] = Me.ID Then  'stops to check if the number matches
    rs.Edit
    rs![Test] = Me.Test 'Just test data, but currently copies to the correct field
    rs![Ton] = Me.Ton   'Just test data  but currently copies to the correct field
    rs.Update
    End If
    
    '-----------------------------------------------------------------------
    'This is Looping to all the records, but is not correctly updating them
    'currently this is only updating the records with the current record ID
    'the form is set to looking at.
    '-----------------------------------------------------------------------
                                  
                                  
                                  
                                  
                   .MovePrevious
                  
                                   
                Loop
                
                
            End If
            
            
        End With
       
        rs.Close 'Close the recordset
    
    Error_Handler_Exit:
        On Error Resume Next
        'Cleanup after ourselves
        Set rs = Nothing
        Set db = Nothing
        Exit Sub
     
    Error_Handler:
        MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
        Err.Number & vbCrLf & "Error Source: LoopRecExample" & vbCrLf & "Error Description: " & _
        Err.Description, vbCritical, "An Error has Occured!"
        Resume Error_Handler_Exit
        DoCmd.SetWarnings True 'Turn off warnings
    End Sub
    
    
    
    
  2. In Topic: Help with a Append Update action in Access 2010

    Posted 26 Sep 2012

    I have given up on an offical audit trail. At this point as ugly as it will be I am going to load each box in the form to a variable and then load those variables into a table to store them. I just need to find out how to compare the variables so if they are in the table it won't copy them.
  3. In Topic: Help with a Append Update action in Access 2010

    Posted 25 Sep 2012

    Believe me, I would rather have them all come from the same table, but my supervisor insists that some of the data come from other tables using drop down menus.


    At this point though I really need pointing in the right direction for VBA coding and storing strings into variables to write to other tables.


    The way he wants it to work He wants the form to have two parts, an upper area that has the static data and a lower area that can be edited that is basically a copy of the upper half of the form. Once the user inputs his changes and presses save the VBA code dumps the data to a new table (changes) and to the main table that is refreshed at the top of the form showing the new changes.

    :(

    I keep telling him that is way out of my league, but he won't listen to my ideas for it.


    Long and short, I need pointing at some good VBA tutorials so I can learn how to write variables that can store a string of data from, Text fields, check boxes, combo boxes and what ever else they want. Once i can figure that part out I can figure a way to have it write to a new table and use an append query for the main table.
  4. In Topic: Help with a Append Update action in Access 2010

    Posted 24 Sep 2012

    View PostJune7, on 15 September 2012 - 08:00 PM, said:

    That is a bit advanced for a beginner. Maybe these threads will give you some ideas:
    http://www.accessfor...form-21405.html
    http://www.accessfor...ates-20419.html

    Code for earlier versions should work for 2010.

    Google: Access database audit trail
    http://www.techrepub...ss-data/6166807

    IS there any way to add support for the multi box fields in this code? I have a few fields that have to have some data pulled from another table and this works fine if I don't have those, but my boss wants those drop down boxes.


    Thanks again!

    Option Compare Database
    
    ' Author:   Allen Browne, allen@allenbrowne.com, 2006.
    
    ' Purpose:  Audit trail, to track Deletes, Edits, and Inserts.
    '           Does not audit any Cascading Updates/Deletes.
    
    ' Requirements: The table to be audited must have an AutoNumber primary key.
    '               Data entry must be through a form.
    
    ' Method:   Makes a copy of the record in a temp table, and logs the
    '           change when it is guaranteed. The temp table copes with:
    '               - multiple deletes at once (continuous/datasheet view)
    '               - cancelled deletes or failed updates.
    '               - requirement for sequential numbering in the audit table.
    '           On a multi-user split (front-end/back-end) database, the
    '           temp table may reside in the front end, and the audit log
    '           in the back-end.
    
    ' Result:   The audit table will contain one record for each deletion or
    '           insertion, and two records for each edit (before and after).
    '               Delete Copy of the deleted record, marked "Delete".
    '               Insert Copy of the new record, marked "Insert".
    '               Change: Copy of the record before change, marked "EditFrom".
    '               Copy of the record after change, marked "EditTo".
    '           This approach, together with the sequential numbering of the
    '           AutoNumber in the audit table makes tampering with the audit
    '           log more detectable.
    
    'Note:      Record confirmations need to be on. When opening the database:
    '               If Not Application.GetOption("Confirm Record Changes") Then
    '                   Application.SetOption ("Confirm Record Changes"), True
    '               End If
    
    'Option Compare Database
    Option Explicit
    
    Private Const conMod As String = "ajbAudit"
    Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
        "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    
    
    Function NetworkUserName() As String
    'On Error GoTo Err_Handler
        'Purpose:    Returns the network login name
        Dim lngLen As Long
        Dim lngX As Long
        Dim strUserName As String
    
        NetworkUserName = "Unknown"
    
        strUserName = String$(254, 0)
        lngLen = 255&
        lngX = apiGetUserName(strUserName, lngLen)
        If (lngX > 0&) Then
            NetworkUserName = Left$(strUserName, lngLen - 1&)
        End If
    
    Exit_Handler:
        Exit Function
    
    Err_Handler:
       ' Call LogError(Err.Number, Err.Description, conMod & ".NetworkUserName", , False)
        Resume Exit_Handler
    End Function
    
    
    Function AuditDelBegin(sTable As String, sAudTmpTable As String, sKeyField As String, lngKeyValue As Long) As Boolean
    'On Error GoTo Err_AuditDelBegin
        'Purpose:    Write a copy of the record to a tmp audit table.
        '            Copy to be written to real audit table in AfterDelConfirm.
        'Arguments:  sTable = name of table to be audited.
        '            sAudTmpTable = the name of the temp audit table.
        '            sKeyField = name of AutoNumber field in table.
        '            lngKeyValue = number in the AutoNumber field.
        'Return:     True if successful.
        'Usage:      Call from a form's Delete event. Example:
        '                Call AuditDelBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Me.InvoiceID)
        'Note:       Must also call AuditDelEnd in the form's AfterDelConfirm event.
        Dim db As DAO.Database           ' Current database
        Dim sSQL As String               ' Append query.
    
        ' Append record to the temp audit table.
        Set db = DBEngine(0)(0)
        sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
            "SELECT 'Delete' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
            "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
        db.Execute sSQL, dbFailonerror
    
    Exit_AuditDelBegin:
        Set db = Nothing
        Exit Function
    
    Err_AuditDelBegin:
       ' Call LogError(Err.Number, Err.Description, conMod & ".AuditDelBegin()", , False)
        Resume Exit_AuditDelBegin
    End Function
    
    
    Function AuditDelEnd(sAudTmpTable As String, sAudTable As String, Status As Integer) As Boolean
    'On Error GoTo Err_AuditDelEnd
        'Purpose:    If the deletion was completed, copy the data from the
        '                temp table to the autit table. Empty temp table.
        'Arguments:  sAudTmpTable = name of temp audit table
        '            sAudTable = name of audit table
        '            Status = Status from the form's AfterDelConfirm event.
        'Return:     True if successful.
        'Usage:      Call from form's AfterDelConfirm event. Example:
        '                Call AuditDelEnd("audTmpInvoice", "audInvoice", Status)
        Dim db As DAO.Database           ' Currrent database
        Dim sSQL As String               ' Append query.
    
        ' If the Delete proceeded, copy the record(s) from temp table to delete table.
        ' Note: Only "Delete" types are copied: cancelled Edits may be there as well.
        Set db = DBEngine(0)(0)
        If Status = acDeleteOK Then
            sSQL = "INSERT INTO " & sAudTable & " SELECT " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
                " WHERE (" & sAudTmpTable & ".audType = 'Delete');"
            db.Execute sSQL, dbFailonerror
        End If
    
        'Remove the temp record(s).
        sSQL = "DELETE FROM " & sAudTmpTable & ";"
        db.Execute sSQL, dbFailonerror
        AuditDelEnd = True
    
    Exit_AuditDelEnd:
        Set db = Nothing
        Exit Function
    
    Err_AuditDelEnd:
        'Call LogError(Err.Number, Err.Description, conMod & ".AuditDelEnd()", False)
        Resume Exit_AuditDelEnd
    End Function
    
    
    Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
        lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
    'On Error GoTo Err_AuditEditBegin
        'Purpose:    Write a copy of the old values to temp table.
        '            It is then copied to the true audit table in AuditEditEnd.
        'Arugments:  sTable = name of table being audited.
        '            sAudTmpTable = name of the temp audit table.
        '            sKeyField = name of the AutoNumber field.
        '            lngKeyValue = Value of the AutoNumber field.
        '            bWasNewRecord = True if this was a new insert.
        'Return:     True if successful
        'Usage:      Called in form's BeforeUpdate event. Example:
        '                bWasNewRecord = Me.NewRecord
        '                Call AuditEditBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
        Dim db As DAO.Database           ' Current database
        Dim sSQL As String
    
        'Remove any cancelled update still in the tmp table.
        Set db = DBEngine(0)(0)
        sSQL = "DELETE FROM " & sAudTmpTable & ";"
        db.Execute sSQL
    
        ' If this was not a new record, save the old values.
        If Not bWasNewRecord Then
            sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
                "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
                "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
            db.Execute sSQL, dbFailonerror
        End If
        AuditEditBegin = True
    
    Exit_AuditEditBegin:
        Set db = Nothing
        Exit Function
    
    Err_AuditEditBegin:
       ' Call LogError(Err.Number, Err.Description, conMod & ".AuditEditBegin()", , False)
        Resume Exit_AuditEditBegin
    End Function
    
    
    Function AuditEditEnd(sTable As String, sAudTmpTable As String, sAudTable As String, _
        sKeyField As String, lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
    'On Error GoTo Err_AuditEditEnd
        'Purpose:    Write the audit trail to the audit table.
        'Arguments:  sTable = name of table being audited.
        '            sAudTmpTable = name of the temp audit table.
        '            sAudTable = name of the audit table.
        '            sKeyField = name of the AutoNumber field.
        '            lngKeyValue = Value of the AutoNumber field.
        '            bWasNewRecord = True if this was a new insert.
        'Return:     True if successful
        'Usage:      Called in form's AfterUpdate event. Example:
        '                Call AuditEditEnd("tblInvoice", "audTmpInvoice", "audInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
        Dim db As DAO.Database
        Dim sSQL As String
        Set db = DBEngine(0)(0)
    
        If bWasNewRecord Then
            ' Copy the new values as "Insert".
            sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
                "SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
                "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
            db.Execute sSQL, dbFailonerror
        Else
            ' Copy the latest edit from temp table as "EditFrom".
            sSQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
                " WHERE (" & sAudTmpTable & ".audType = 'EditFrom') ORDER BY " & sAudTmpTable & ".audDate DESC;"
            db.Execute sSQL
            ' Copy the new values as "EditTo"
            sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
                "SELECT 'EditTo' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
                "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
            db.Execute sSQL
            ' Empty the temp table.
            sSQL = "DELETE FROM " & sAudTmpTable & ";"
            db.Execute sSQL, dbFailonerror
        End If
        AuditEditEnd = True
    
    Exit_AuditEditEnd:
        Set db = Nothing
        Exit Function
    
    Err_AuditEditEnd:
        'Call LogError(Err.Number, Err.Description, conMod & ".AuditEditEnd()", , False)
        Resume Exit_AuditEditEnd
    End Function
    
    
    

My Information

Member Title:
New D.I.C Head
Age:
36 years old
Birthday:
September 25, 1977
Gender:

Contact Information

E-mail:
Click here to e-mail me
Website URL:
Website URL  http://

Friends

Wulfen hasn't added any friends yet.

Comments

Wulfen has no profile comments yet. Why not say hello?