Button to add new record to another table

[Steindi]

Hi there,

I have a form wich users input information into and it goes in one table. Several buttons on my form send a report from that table to people.

What I need to to is that every time one of these buttons are pressed and a report is sent, a timestamp and wich button was pressed needs to be recorded in another table in the same database.

This is because I need to record to whom each report was sent and when.

Can anybody help? I mainly use VBA and try to stay away from macros.

Steindi

 

[Frothingslosh]

One way would probably be to create a recordset based on the tracking table, add a new record, update the fields you need, and save it.

Add that code to each button whose use you want to track.

 

[Steindi]

Ok, how would the code look like?

I'm not much of a code builder you see...

 

[Frothingslosh]

This is just untested air code, as I have no idea how your database is set up, but it would be something like this:

Public Sub UpdateActivityLog(ByVal ReportSent As String, _
                             ByVal Recipient As String)
 
[COLOR=seagreen]'Variable declaration
[/COLOR]Dim rs As DAO.Recordset
 
On Error GoTo UpdateActivityLog_Err
 
[COLOR=seagreen]    'Open the table "ActivityLog" in data entry mode.
[/COLOR]    Set rs = CurrentDb.OpenRecordset("[COLOR=red]ActivityLog[/COLOR]", dbOpenDynaset, dbAppendOnly)
    
    With rs
[COLOR=seagreen]        'Create a new record.
[/COLOR]        .AddNew
[COLOR=seagreen]        'Update TimeStamp field with the current date/time.
[/COLOR]        ![COLOR=red]TimeStamp[/COLOR] = Now()
[COLOR=seagreen]        'Update ReportSent field with the name of the report that was generated.
[/COLOR]        ![COLOR=red]ReportSent[/COLOR] = ReportSent
[COLOR=seagreen]        'Update Recipient field with the name of the person receiving the report.
[/COLOR]        ![COLOR=red]Recipient[/COLOR] = Recipient
[COLOR=seagreen]        'Save the new record.
[/COLOR]        .Update
    End With
 
UpdateActivityLog_Exit:
    If Not rs Is Nothing Then Set rs = Nothing
    Exit Sub
 
UpdateActivityLog_Err:
    MsgBox "Error found in UpdateActivityLog!" & vbCrLf & vbCrLf & _
           "Error Code:" & vbTab & Err.Number & vbCrLf & _
           "Error Desc:" & vbTab & Err.Description
    Resume UpdateActivityLog_Exit
    
End Sub

If you have multiple forms that can create the reports, then you need to put the above code in a regular module. If you only have one form that does it, then you can make it a private sub and place it in the form's module.

Please be aware that in order to use this code, the table names and field names MUST be changed to match the names in your database. I have highlighted those in red so that they will stand out for you.

To call this subroutine, for each button that generates a report, after the report is created, you would put something like this:

UpdateActivityLog [COLOR=blue]"ReportName"[/COLOR], [COLOR=blue]"RecipientName"[/COLOR]

Again, a note: the parameters (listed in blue) need to be supplied by you, either as text (as in the example above) or as field references (Me.txtFieldName).

 

[Steindi]

I had actually started something similar but you solved my problem.

Thank you so much...

Regards,
Steindi