Within a macroed SQL insert call the form name

[pbcharlie]

I am trying to created an audit trial. So far things are going well, But what I need to know is.
Can you call the form name in an SQL statement which has ran the macro?


Regards
Charlie
Thanks in advance
Charlie

 

[vbajock]

I don't know if I quite get your question, but you could set up a public function and put it in the form load or activate event, depending on how you want to use it:

Function TrackForm()

Dim frmCurrentForm As Form

dim mydb as DAO.database

set mydb=Currentdb

Set frmCurrentForm = Screen.ActiveForm

sql="INSERT INTO SomeAuditTable ( [Form Name] )
sql=sql &" SELECT '"& frmCurrentForm.Name & "' AS Expr1;
mydb.execute sql

End function

 

[JeremyNYC]

True, thought that will bomb out if there's not an active form at the time it's run. A lot will depend on how you're assembling the audit trail. If you give us a bit of info on that, you'll get some decent ideas. But as a wide open question, there are so many possible ways of doing it that what you get stands little chance of applying to your model.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

See the Developers' section

Please post in the appropriate forum and include 1) a descriptive subject 2) code and SQL, if referenced,
and 3) expected results. See thread181-473997

 

[pbcharlie]

I have numerous forms. on these forms in the on delete, after insert ect I have a macro running which inserts a line of data, namely the user, time and action.
Thr macro runs the sql for an insert
Insert Into [Audit_Log] (Ref, Dateofentry,Action) Select fOSUserName() AS Ref, Now() AS Dateofentry, '"add"' AS Action
And
Insert Into [Audit_Log] (Ref, Dateofentry,Action) Select fOSUserName() AS Ref, Now() AS Dateofentry, '"delete"' AS Action

For a delete command, however I would need to do this for every form to identify which form was used.
What I was trying to find out is can the SQL query identify the form?
Thanks in advance
Charlie

 

[JeremyNYC]

Charlie,

Hmm. Maybe I'm not understanding. I don't know why a delete would be different from an insert...but in any case, if this is the only place you'll be using the macro, VBAJock's method should work. On the other hand, I would guess that you would be able include screen.activeform.name in the sql statement to grab the name of that form without having to code anything.

If I were to do this it would be in code instead of a macro (I never use 'em), and I would simply call the function and pass a parameter of the form's name. I guess I'd add other details as parameters, too, to make it as generic as possible [this is completely untested aircode. Test it carefully]:

Function AddAuditRecord(strFormName as String, strActionType as String)
dim db as dao.database
dim strSql as string

set db = currentdb
strsql = "Insert Into [Audit_Log] (Ref, Dateofentry, Action, FormName) VALUES (fOSUserName() AS Ref, Now() AS Dateofentry, '" & strActionType & "', '" & strFormName & "')"
DB.execute(strsql, dbfailonerror)
db.close
set db = nothing
end function

Hope that helps some.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

See the Developers' section

Please post in the appropriate forum and include 1) a descriptive subject 2) code and SQL, if referenced,
and 3) expected results. See thread181-473997

 

[pbcharlie]

Ok I will give this a try, The macros are not as good as code.


Thanks again
Thanks in advance
Charlie

 

[vbajock]

JeremyNYC-
Dude: Of course it can "bomb out". I am not going to write the whole program for him, error handlers and all. That's a whole 'nother subject.

pbcharlie -
If I was going to do something like this I would set up functions that return the value I needed, then I could plug the functions into SQL's, Querys, or assign the value to variables or controls, or I could use it in code loops.

Here's an example, and I even included the error handler:

Public Function TrackForm() as string

On error got err_h

Dim frmCurrentForm As Form

dim mydb as DAO.database

set mydb=Currentdb

Set frmCurrentForm = Screen.ActiveForm

Trackform = frmCurrentForm.Name
exit function

err_h:
Trackform="Error stuff"

End function

Then you could use
sql="INSERT INTO SomeAuditTable ( [Form Name] )
sql=sql &" SELECT '"& trackform() & "' AS Expr1;
mydb.execute sql

 

[pbcharlie]

I have now been able to do this with your help.

Thanks VBAJock
Thanks JeremyNYC Thanks in advance
Charlie