MS Access - Capturing the name of the form or report 1

[chippyles]

I have an Append query that currently captures data when a user OPENS a form or a report. The name of the user (USER: Environ("username")), the timestamp (DATE: Now()), and the Host (HOST: Environ("COMPUTERNAME")) are what I am currently capturing. I am looking to add one more field (Task) and I want the name of the form or the report the person uses.

So when person X on computer xyz opens form ABC at 12 am it should capture the data and do the append query.

USER = X
HOST = xyz
DATE = 01/01/2001 12:00:00 AM
TASK = ABC

 

[PWise]

try me.name

 

[chippyles]

Tried it and it doesn't work.

any other suggestions??

 

[PWise]

how are you capturing the data and how are you inserting it into the log

 

[chippyles]

The data is captured by an append query.

In the name I have three columns

USER: Environ("username")
DATE: Now()
HOST: Environ("COMPUTERNAME")

I want a field called TASK to capture the form or report name.

 

[PWise]

are you doing this in the the form load form open or whereever
and what is the sql of the append query ect

 

[chippyles]

Form Open or Report open activates the query. The three fields in the query work perfectly fine.

The SQL behind the Append is

INSERT INTO LOG_REPORT ( [DATE], [USER], HOST )
SELECT Now() AS [DATE], Environ("username") AS [USER], Environ("COMPUTERNAME") AS HOST;


Hope this helps!!

 

[PWise]

try this
docmd.runsql " INSERT INTO LOG_REPORT ( [DATE], [USER], HOST ,task )
SELECT Now() AS [DATE], Environ("username") AS [USER], Environ("COMPUTERNAME") AS HOST ," & me.name

 

[chippyles]

I am getting a syntax error

Private Sub Form_Open(Cancel As Integer)
DoCmd.RunSQL " INSERT INTO LOG_REPORT ( [DATE], [USER], HOST ,TASK )"
SELECT Now() AS [DATE], Environ("username") AS [USER], Environ("COMPUTERNAME") AS HOST ," & me.name
End Sub


Private...line1
DoCmd...line 2
SELECT...line 3
End Sub...line4

 

[PWise]

DoCmd.RunSQL " INSERT INTO LOG_REPORT ( [DATE], [USER], HOST ,TASK )" & _
"SELECT Now()......

 

[chippyles]

I am still getting a syntax error


Private Sub Form_Open(Cancel As Integer)
DoCmd.RunSQL " INSERT INTO LOG_REPORT ( [DATE], [USER], HOST ,TASK )" & _
"SELECT Now() AS [DATE], user_name() AS [USER], host_name() AS HOST ," & Me.Name
End Sub

I had to change the Environ to the SQL interpretations. In the VBA I can take my mouse over the Me.Name and it tells me the name of the form, but there is a syntax error somewhere.

 

[chippyles]

I tested this in the query analyzer of SQL. the INSERT TO works fine, but I put in the VBA code and there is a syntax erro at & Me.Name.

I do not know if this helps at all.

 

[PWise]

sorry
host_name() AS HOST ,'" & Me.Name & "'"

 

[PWise]

that is a single quote enclosed in double quotes

 

[chippyles]

Is this how it is supposed to look because it doesn't work for me? Can you modify the code and send me the whole code back? I am not understanding how you want me to end the code. I appreciate it very much!!

Private Sub Form_Open(Cancel As Integer)
DoCmd.RunSQL " INSERT INTO LOG_REPORT ( [DATE], [USER], HOST ,TASK )" & _
"SELECT getdate() AS [DATE], user_name() AS [USER], host_name() AS HOST ," '"& Me.Name&"'"
End Sub

 

[PWise]

host_name() AS HOST ,'" & Me.Name & "'"

 

[PWise]

host_name() AS HOST ,'" & Me.Name & "'"

 

[chippyles]

ok now it works!!

One last question to complete this project.

How do I set the warnings off or on in the VBA?

RunCommand?? or what?

 

[PWise]

with docmd
.SetWarnings off
.RunSQL " INSERT INTO LOG_REPORT ( [DATE], [USER], HOST ,TASK )" & _
"SELECT Now() AS [DATE], user_name() AS [USER], host_name() AS HOST ,'" & Me.Name
& "'"
.SetWarnings on
end with

 

[chippyles]

One last part to my question.

How can I make the attached code a Global code (module) so I can have every single form use this code and do the insert. this would save me some time in configuring every single form.