Capture a form's Open instance to a table

[DubCap01]

Easy to explain, is it easy to capture via VBA? "FrmOpenMe" opens, then magically capture form name "FrmOpenMe" to write "FrmOpenMe" to a table, with date/time stamp and Coordinator's payroll number...



I'd like to capture the form name on the Event Open occurrence of any form, and insert that into a table. Just not sure how to create this universally, to capture the form name when run from a function (module). I currently have a function called FormLoad which executes some basic stuff when fired. I'd like to add a bit more to capture the form name of the form being opened, and update a table with the latest instance of the user's 'form' activity.

thanks in anticipation everyone

Pete

[pbaldy]

I'd create a public function that accepted the form name as a parameter, then call it from each form's open event:

FunctionName Me.Name

[DubCap01]

Thanks Paul, great idea, I just have no clue how to get the function to accept the parameter! Me.Novice at that bit!

[pbaldy]

This should get you started:

http://www.baldyweb.com/Function.htm

[DubCap01]

Thanks for the reply Paul, but that doesn't answer or help (as I am a little bit less novice that that stage)

I was hoping for a line of code that does what you suggest and be able to modify myself to insert into the Function 'FormLoad' looking something like this (bad) example:

Public Function FormLoad()

'do my first bits first
If.. then
do this
End if

CODE The Bit I Need! The code to capture the name of the form I just opened, use it as a reference, and then go to the next part

'next part

Run SQL statement to write that Form name to a table 'I can do this bit as long s I know what the Form Name is captured as

End Function

I appreciate that you are approaching this in a way to help educate people like me to learn (and get better) from your instructions, but it's baffling me right now

Sorry Paul, but if that explains what I need satisfactorily, can you help with the intermediate code?

cheers
Pete

[evander]

As pbaldy explained, you need to create a public subprocedure or function and refer to this function in the Form_Open event of an Access form. Creating subprocedures and functions is considered a separate skill itself, so you need to read books on this topic to further improve your programming skills.

Public Sub RecordForm (ByVal FormName as String)
     DoCmd.SetWarnings False
     DoCmd.RunSQL "INSERT INTO tblForms (FormName) VALUES (" & ''" & FormName & "')"
     DoCmd.SetWarnings True
End Sub



Private Sub Form_Open()
     Call RecordForm("frmPrices")
End Sub

[pbaldy]

As I showed in my first reply, this will return the name of the form it's in:

Me.Name

[DubCap01]

Hi everyone, and thanks for your combined inputs. I found another route for the same answer, so this thread now becomes redundant, as my solution didn't answer the question I asked originally (if that makes sense)

I appreciate all your help, once again, thank you all