Determining ActiveForm

Hi,
I have written a VBA audit that records the OLDVALUE and NEW VALUE of a field.

It uses Set MyForm = Screen.ActiveForm in order to determine the fields on the screen.
It then loops through the data checking for changes.

PROBLEM:
I have a sub form too.
However, when I call my AUDIT function from the subform, the AUDIT VBA check the parent form and NOT the subform.

How can I get my VBA to check the subform rather than the parent form.

If this is not clear then I can add the code to the query.
Patrick O'DeaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Try using Screen.ActiveControl within the subform

mx
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I find it better to pass a reference to your AUDIT function.
0
Patrick O'DeaAuthor Commented:
Thanks mx,

I have attached my code below.

Does your suggestion not mean that I will only be auditting one single field.
Surely I need to loop through the entire form??

(Perhaps, I am missing something??)

Function Audit()
On Error GoTo Audit_err
Dim UserNoApostrophe As String
UserNoApostrophe = Replace(Environ("Username"), "'", "''")
    
    Dim MyForm As Form, ccont As Control, Addstr As String, Auditstr As String, BuildStr As String, BuildStr2 As String
    
    
'********************    SEE NEXT LINE
Set MyForm = Screen.ActiveForm
For Each ccont In MyForm.Controls

If (TypeName(ccont) = "Textbox" Or TypeName(ccont) = "CheckBox" Or TypeName(ccont) = "combobox") And (Left(ccont.Name, 2) <> "XX") Then
MsgBox "Ccont.name -> " & ccont.Name

    If (ccont.OldValue <> ccont.Value And ccont.Name <> "AuditMemo") Then
        BuildStr = "** " & ccont.Name & " Old: " & ccont.OldValue & "    New: " & ccont.Value & Chr(13) & Chr(10)
        BuildStr2 = Replace(BuildStr, "'", "''")
        CreateAudit (BuildStr2) ' There are two type of audit!
        Addstr = Addstr & BuildStr2
    End If
End If

Next ccont
If Len(Addstr) > 0 Then

Open in new window

0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well ... kind of ignore my first comment.  The code above, where is that located ?

mx
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I would use this:

Function Audit(Optional frmRef As Form)
On Error GoTo Audit_err
Dim UserNoApostrophe As String
UserNoApostrophe = Replace(Environ("Username"), "'", "''")
    
    Dim MyForm As Form, ccont As Control, Addstr As String, Auditstr As String, BuildStr As String, BuildStr2 As String
    
    
'********************    SEE NEXT LINE
' Set MyForm = Screen.ActiveForm

' use the passed form reference is used
   On Error Resume Next
   If frmRef Is Nothing Then
        Set MyForm = Screen.ActiveForm
 
   Else
   
        Set MyForm  = frmRef
   End If


For Each ccont In MyForm.Controls

If (TypeName(ccont) = "Textbox" Or TypeName(ccont) = "CheckBox" Or TypeName(ccont) = "combobox") And (Left(ccont.Name, 2) <> "XX") Then
MsgBox "Ccont.name -> " & ccont.Name

    If (ccont.OldValue <> ccont.Value And ccont.Name <> "AuditMemo") Then
        BuildStr = "** " & ccont.Name & " Old: " & ccont.OldValue & "    New: " & ccont.Value & Chr(13) & Chr(10)
        BuildStr2 = Replace(BuildStr, "'", "''")
        CreateAudit (BuildStr2) ' There are two type of audit!
        Addstr = Addstr & BuildStr2
    End If
End If

Next ccont
If Len(Addstr) > 0 Then

Open in new window


To call from a sub form use:
Audit(Me)

Open in new window


0
Patrick O'DeaAuthor Commented:
To HiTechCoach (all responses welcome too!)

Thanks for your code (which I fully understand).

However, perhaps you could advise on the "call".

Audit(me)

What exactly do I me here - specifically for the "me" bit.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Me is a VBA shorthand for a reference to object where the VBA code is executing.  It can be used in forms and reports.

From a Sub Form you would use exactly this notation:   Audit(Me)

Access will automatically replace Me VBA shorthand with the referent to the actual  form or sub form.

The issue with Screen.ActiveForm is that only the parent form is recognized as a From.  A sub form in actually a container control not a form object.








0
Patrick O'DeaAuthor Commented:
Thanks again HiTech,
Your explanation is very helpful.


However, If I use
Audit(me)
it causes error 13 - Type mismatch.

How would access know that the "me" referred to a form and not something else.
Do I need more than just me??


0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
You will need to call Audit(me)  from within the sub form.

Did you modify your function to have a parameter like this:

Function Audit(Optional frmRef As Form)

Open in new window


What line get the error: it causes error 13 - Type mismatch.

Will you post your new code?
0
Patrick O'DeaAuthor Commented:
See Code.
In the interests of simplicity I created a button on the subform and attached the code to it.
(I put the "me" in lower case and it was converted to "Me". )


Private Sub Command43_Click()
Audit (Me)
End Sub

_______________________________________
Function Audit(Optional frmRef As Form)

On Error GoTo Audit_err
Dim UserNoApostrophe As String
UserNoApostrophe = Replace(Environ("Username"), "'", "''")
    
    Dim MyForm As Form, ccont As Control, Addstr As String, Auditstr As String, BuildStr As String, BuildStr2 As String
    
    Dim crap As String
    
' Set MyForm = Screen.ActiveForm

' use the passed form reference is used
   On Error Resume Next
   If frmRef Is Nothing Then
        Set MyForm = Screen.ActiveForm
    Else
           Set MyForm = frmRef
   End If



For Each ccont In MyForm.Controls

If (TypeName(ccont) = "Textbox" Or TypeName(ccont) = "CheckBox" Or TypeName(ccont) = "combobox") And (Left(ccont.Name, 2) <> "XX") Then
'MsgBox "Ccont.name -> " & ccont.Name

    If (ccont.OldValue <> ccont.Value And ccont.Name <> "AuditMemo") Then
        BuildStr = "** " & ccont.Name & " Old: " & ccont.OldValue & "    New: " & ccont.Value & Chr(13) & Chr(10)
        BuildStr2 = Replace(BuildStr, "'", "''")
        CreateAudit (BuildStr2) ' There are two type of audit!
        Addstr = Addstr & BuildStr2
    End If
End If

Next ccont
If Len(Addstr) > 0 Then

    MyForm.AuditMemo = UserNoApostrophe & "  " & Now() & Chr(13) & Chr(10) _
                        & Addstr & Chr(13) & Chr(10) & MyForm.AuditMemo & Chr(13) & Chr(10)
End If
Exit Function

Audit_err:

Auditstr = "Error : " & Err.Number & "   desc: " & Err.Description
MsgBox Auditstr
CreateAudit (Auditstr)
Resume Next




End Function

Open in new window

0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Let me try your code in a test database.  Note: I will have to make a stb function for CreateAudit().




 
0
Patrick O'DeaAuthor Commented:
Thanks HiTech,

I have been experimenting in the interim but with no great results.

I considered a bit more hardcoding but ... not a great way to go..
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I took an MS Access Template and added teh code. It worked for me.

note: You loginc has some issue usnles the fields are always required. You are not handling a Nulls correctly.

Look at the Video Tapes form in the attached example VideoCollection-Audit.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick O'DeaAuthor Commented:
Thanks again HiTech,

I looked at the video database and all seemed well there.

I then re-visited my own database and the problem seemed to have vanished - without making any change!

... when things start magically fixing themselves it is perhaps a sign of bedtime!  It's after 1am here ..

I appreciate your comments on the "null" etc.

Thanks again, you have been most helpful.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.