Results 1 to 10 of 10

On button click write data into table

  1. #1
    fletcjas is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    13

    On button click write data into table

    Hello,
    I am trying to create a database with some security features.
    I am at a point where i need to add a way of logging if someone hits 'Print' on a form/report.

    I am looking for a way where the following would happen:
    1) Person opens form/report


    2) User hits 'Print' (the following would happen)
    a) record is created in a table called 'AuditDB' with the following: DateTime, UserName, FormName, Action.
    The following is the data inputs i would use:

    Date = datTimeCheck
    UserName = StrLoginName
    formname = Screen.ActiveForm.Name
    Action = "Print"

    I already have a log of changes made in the database hence the form names.

    I am using Office 2010 x86 (Due to the ability to publish the database)

    I am happy to take advice, can anyone help please?

    Thanks

  2. #2
    kd2017 is online now Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    331
    Have you worked with VBA yet? Inserting a record into your table would be simple:

    Code:
    CurrentDb.Execute "INSERT INTO AuditDB (Date, UserName, formname, Action) VALUES (#" & datTimeCheck & "#, '" & StrLoginName & "', '" & Screen.ActiveForm.Name & "','Print');", dbFailOnError
    Note that you certainly shouldn't be using [Date] or [Action] as a field name in your table. You don't want to use key words as Access uses them for other things and can cause errors when you do.
    https://support.office.com/en-ie/art...7-da237c63eabe

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,292
    FWIW, there's a much bigger list of reserved words here, I'd say.
    http://allenbrowne.com/AppIssueBadWord.html

    For your scheme to work from a button click, are you going to disable all other means of printing a report, such as the ribbon and context menus (what you get from a right click)? Your button code won't run when those methods are used. You could try one of the report section print events such as for the header, but I believe you would have to either send the report directly to the printer or open only in report view. IIRC, the print events don't fire when a report is printed from print preview.

    Curious: what's so important about the printing of a document? I probably didn't work in QS or ISO certified programs as long as you might have, but I'm sure there was no concern about printing a report or any other document. The concern was about what happened to it after that.
    Last edited by Micron; 09-09-2018 at 08:58 PM. Reason: clarification
    - "doesn't work" is no help. What's happening? Error messages? Where??
    - Use code tags for code/sql. Implement changes in copies of your database.
    Irregardless, ain't no such word as "reoccur".

  4. #4
    fletcjas is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    13
    Hey both,
    Thank you for the replies.
    Code:
    CurrentDb.Execute "INSERT INTO AuditDB (Date, UserName, formname, Action) VALUES (#" & datTimeCheck & "#, '" & StrLoginName & "', '" & Screen.ActiveForm.Name & "','Print');", dbFailOnError
    I have added the code above to the VBA as follows:
    Code:
    '------------------------------------------------------------' CMDPrint_Click
    '
    '------------------------------------------------------------
    Private Sub cmdPrint_Click()
    On Error GoTo cmdPrint_Click_Err
    DoCmd.RunCommand acCmdPrint
    
    
    CurrentDb.Execute "INSERT INTO AuditDB (Date, UserName, formname, Action) VALUES (#" & datTimeCheck & "#, '" & StrLoginName & "', '" & Screen.ActiveForm.Name & "','Print');", dbFailOnError
    
    
    
    
    cmdPrint_Click_Exit:
        Exit Sub
    
    
    cmdPrint_Click_Err:
        
        Resume cmdPrint_Click_Exit
    
    
    End Sub
    But when i click 'Print, nothing is added to the table AuditDB.

    I have the application (if you want to call it that) running as a 'program' so only what i have as the forms are displayed, there is no right click functionality and therefore would only be able to print via the 'print' button. There is of course the other way which would be to print screen.

    The reason for the log is that the database will hold personal information that has been encrypted at both ends, so i wouldn't want people printing the info without it being logged.. Something for the GDPR rules!

    Thank you, both, again. This has pointed me in the right direction, also will take a look at the likes for 'bad words'

    Jase

  5. #5
    kd2017 is online now Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    331
    Did it throw an error and if so what was it?

    I'd suggest you have
    Code:
    Option Explicit
    at the very top of your code. https://www.excel-easy.com/vba/examp...-explicit.html

    You'll need to modify the sql string to whatever fits for your situation. I was only guessing at field names and values but without more details of your DB I can't know what exactly was wrong with that statement. The code given assumed variables based off post #1 but then they aren't defined in the code shown in post #4.

  6. #6
    fletcjas is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    13
    Click image for larger version. 

Name:	cc.PNG 
Views:	14 
Size:	19.9 KB 
ID:	35438

    Before i added the 'Option Explicit' t did nothing, but now gives me the error in the picture. I think it is because it can't do the date and time check..?

  7. #7
    kd2017 is online now Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    331
    It's because your variable is not defined.

    Code:
    '------------------------------------------------------------' CMDPrint_Click
    '
    '------------------------------------------------------------
    Private Sub cmdPrint_Click()
    On Error GoTo cmdPrint_Click_Err
    DoCmd.RunCommand acCmdPrint
    
    Dim datTimeCheck as Date
    Dim StrLoginName as String
    Dim StrFormName as String
    Dim StrAction as String
    
    datTimeCheck = Now()
    StrLoginName = "John Doe"
    StrFormName = Screen.ActiveForm.Name
    StrAction = "Print"
    
    CurrentDb.Execute "INSERT INTO AuditDB (Date, UserName, formname, Action) VALUES (#" & datTimeCheck & "#, '" & StrLoginName & "', '" & StrFormName & "',' & StrAction & ');", dbFailOnError
    
    
    
    
    cmdPrint_Click_Exit:
        Exit Sub
    
    
    cmdPrint_Click_Err:
        
        Resume cmdPrint_Click_Exit
    
    
    End Sub
    And make sure that the field names are correct and reflect what they actually are for your tables.

  8. #8
    fletcjas is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    13
    I have tried to code above, I get no errors, but no data is inserted to the AuditDB table.

    Will this work for 'Reports' or just for forms?

    Thanks

    Jase

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,292
    Put debug.print "INSERT INTO AuditDB (Date, UserName, formname, Action) VALUES (#" & datTimeCheck & "#, '" & StrLoginName & "', '" & StrFormName & "',' & StrAction & ');"

    right before CurrentDb line and put a break point on the CurrentDb line. When code stops at the break point, look at the print output in the immediate window and see if the sql statement makes sense. If you cannot see the immediate window, activate it from the View menu.

    Perhaps all the variables have nothing in them? Rather than post "I tried but..." post your code so we have a better chance of spotting the problem if it's there. Also, if you have that table open, run the code and look at the table again, you must refresh it (from the Ribbon) otherwise it will look like nothing was added even if it was. You can also try pasting the print output into a new query and see if a datasheet view (a preview) indicates what, if anything, will be appended. It might also raise an error. You have not turned off warnings in code prior to this and left them turned off by any chance?
    - "doesn't work" is no help. What's happening? Error messages? Where??
    - Use code tags for code/sql. Implement changes in copies of your database.
    Irregardless, ain't no such word as "reoccur".

  10. #10
    fletcjas is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    13
    I have cracked it, Thank you all! - Turns out that the error wasn't showing as i had 'On Error GoTo cmdPrint_Click_Err' removed this temporary and up popped the error! - Turns out i was running the code in a report and it was looking for a form name.

    This is now working a treat!

    Thanks again!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-08-2018, 01:09 AM
  2. Replies: 7
    Last Post: 03-07-2017, 01:10 PM
  3. Replies: 12
    Last Post: 11-11-2014, 01:10 PM
  4. Replies: 5
    Last Post: 05-20-2014, 10:51 AM
  5. Replies: 9
    Last Post: 05-30-2011, 04:43 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums