• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

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.
0
Patrick O'Dea
Asked:
Patrick O'Dea
  • 6
  • 6
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try using Screen.ActiveControl within the subform

mx
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

mx
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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 MVPCommented:
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 MVPCommented:
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 MVPCommented:
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 MVPCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now