• I am creating a new home page for this site, where the focus will be on directing people to the forums. If you would like to provide a testimonial, I would be most grateful. The thread where this is discussed can be found here: Seeking Testimonials Alternatively, just private message me.

Solved Code syntax help (1 Viewer)

Yianni

Registered User.
Local time
Today, 13:03
Joined
May 15, 2017
Messages
38
I am attempting to create a Function that receives 2 values -- myformname and mymainformname. I am not sure of the proper syntax with the "
Can anyone help?

In the next line of code I want to replace CustomerF with the passed myformname value
For Each c In Forms("CustomerF").Controls



In this next line I want to replace MainMenuF with the passed mymainformname value
TempVars!UserID.Value = Forms!MainMenuF.txtUserID.Value

Your help will be appreciated.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:03
Joined
Oct 29, 2018
Messages
11,077
Hi. The syntax might look something like:

Forms(VariableName)
 

The_Doc_Man

Happy Retired Curmudgeon and Occasional Moderator
Staff member
Local time
Today, 12:03
Joined
Feb 28, 2001
Messages
17,271
To expand (and avoid use of names...):

Code:
Public Function DiddleForms( ByRef MainF as Access.Form, ByRef SubF as Access.Form )
...
    FOR EACH c in MainF ....
Then you can pass form references like this:

Code:
    DiddleForms( Me, Me.subformcontrolname.Form )

...  (or)

    DiddleForms( Forms("FormA"), Forms("FormB") )
That way you have a direct reference to the actual form and don't have to do Forms(formname) in the sub. Oh, I'm somewhat pedantic so I included ByRef to emphasize it, but in fact I believe ByRef is usually the default, particularly for objects like that.
 

Micron

AWF VIP
Local time
Today, 13:03
Joined
Oct 20, 2018
Messages
3,377
Agree, the function is needed or more explanation. You're saying you want to pass 2 form names, yet in one example line you want to use a form reference yet in the other line, a form or property value. You can pass the form names to the function and use one or the other form reference (why use both methods?) or you can pass the form as an object, which would require different syntax.
 

arnelgp

error reading drive A:
Local time
Tomorrow, 01:03
Joined
May 7, 2009
Messages
9,835
what are you trying to accomplish.
if you want to know the Form where the subForm is, pass the subform to this function, eg:

dim mainForm as Form
set mainForm = fncGetParent(Me)
'check if there is Parent form
if not (mainForm is nothing) then
'mainform exists
end if
Code:
Public Function fncGetParent(ByRef frm As Object) As Object
    On Error Resume Next
    Set fncGetParent = frm.Parent.Form
End Function
if the subform is embedded, 2 or more deep from the Main form, and you want
to access the Main form:

dim mainForm as Form
set mainForm = fncGetRoot(Me)
'check if there is Parent form
if not (mainForm is nothing) then
'mainform exists
end if
Code:
Public Function fncGetRoot(ByRef frm As Object) As Object
    Static f As Object
    Dim o As Object
    On Error Resume Next
    If Not (frm Is Nothing) Then
        Set f = frm
    End If
    Set o = fncGetRoot(frm.Parent.Form)
    If Not (f Is Nothing) Then
        If Not (f Is frm) Then
            Set fncGetRoot = f
        End If
    End If
End Function
 
Last edited:

Yianni

Registered User.
Local time
Today, 13:03
Joined
May 15, 2017
Messages
38
Can you post your function?
Hi, Thanks for responding.

Code Tags Added by UG
Please use Code Tags when posting VBA Code

Please read this for further information:-
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/
Please feel free to Remove this Comment

Code:
Public Function GetLog()
'Public Function GetLog(myMainForm as string,myForms string)
   Dim UserID As Long
    Dim c As Variant
    Dim strSQL As String
   '------ Need to change code below

     For Each c In Forms("CustomerF").Controls
    'For Each c In Me.Form.Controls '--------------------------------reference ME.Form here
    'Me.Form.Controls is collection native to Access
          If c.ControlType = acTextBox And c.Section = acDetail Then
            'if the control is a text box and is in the detail section
            TempVars!NV = c.Name & " = '" & c & "'"
            TempVars!OV = c.Name & " = '" & c.OldValue & "'"
            TempVars!UserID.Value = Forms!MainMenuF.txtUserID.Value '---- specific form and control
            If TempVars!OV <> TempVars!NV Then
               '--------There is a reference to a specific form below
               DoCmd.RunSQL "INSERT INTO MyLog ( ID, DT, OldData, NewData, UserID )SELECT [Forms]![CustomerF]![CustomerID] AS ID, Now() AS DT, [TempVars]![OV] AS OldValue, [TempVars]![NV] AS NewValue, [TempVars]![txtUserId] AS [User];"
            End If
        TempVars!OV = ""
        TempVars!NV = ""
        End If
    Next

TempVars!UserID = ""
TempVars!NV = ""
TempVars!OV = ""

End Function
' This code works but as you can see, I am not passing anything yet. I want to eliminate references to specific forms and pass in what I need. I appreciate your help. john
 
Last edited by a moderator:

Yianni

Registered User.
Local time
Today, 13:03
Joined
May 15, 2017
Messages
38
Agree, the function is needed or more explanation. You're saying you want to pass 2 form names, yet in one example line you want to use a form reference yet in the other line, a form or property value. You can pass the form names to the function and use one or the other form reference (why use both methods?) or you can pass the form as an object, which would require different syntax.
Thanks for responding

Hi, Thanks for responding.

EDITED BY THE_DOC_MAN: ADDED CODE TAGS - NO OTHER CHANGES MADE

Code:
Public Function GetLog()
'Public Function GetLog(myMainForm as string,myForms string)
   Dim UserID As Long
    Dim c As Variant
    Dim strSQL As String
   '------ Need to change code below

     For Each c In Forms("CustomerF").Controls
    'For Each c In Me.Form.Controls '--------------------------------reference ME.Form here
    'Me.Form.Controls is collection native to Access
          If c.ControlType = acTextBox And c.Section = acDetail Then
            'if the control is a text box and is in the detail section
            TempVars!NV = c.Name & " = '" & c & "'"
            TempVars!OV = c.Name & " = '" & c.OldValue & "'"
            TempVars!UserID.Value = Forms!MainMenuF.txtUserID.Value '---- specific form and control
            If TempVars!OV <> TempVars!NV Then
               '--------There is a reference to a specific form below
               DoCmd.RunSQL "INSERT INTO MyLog ( ID, DT, OldData, NewData, UserID )SELECT [Forms]![CustomerF]![CustomerID] AS ID, Now() AS DT, [TempVars]![OV] AS OldValue, [TempVars]![NV] AS NewValue, [TempVars]![txtUserId] AS [User];"
            End If
        TempVars!OV = ""
        TempVars!NV = ""
        End If
    Next

TempVars!UserID = ""
TempVars!NV = ""
TempVars!OV = ""

End Function
' This code works but as you can see, I am not passing anything yet. I want to eliminate references to specific forms and pass in what I need. I appreciate your help. john
 
Last edited by a moderator:

Micron

AWF VIP
Local time
Today, 13:03
Joined
Oct 20, 2018
Messages
3,377
First, please use code tags (</> on menubar) and indentation when posting code.
That code is quite confusing; bizarre actually. It looks like you're getting the value of a control, setting a tempVar to it then using the tempVar in an append sql statement. Why not just use the control reference in the sql? Suggest also that you do not use variants as variables for objects. I also don't see why you need to worry about whether or not a control is in the detail section. Normally, you loop over form controls and refer to the .Tag property so as to distinguish between those you want to include (have a tag property value) and those you don't. Having said all that, I can't see how or why anyone would create a record based on one control, then move onto the next control and create another record until you've looped over all the controls.

Perhaps you should explain what the operation is, or just go with the 2 or 3 versions that we've already provided with respect to how to pass an object or its name as a parameter. Or you could research functions and subs and how to include parameters and apply that knowledge to your situation, which isn't clear.
Your procedure could just as well be a sub as it doesn't return anything anyway, but that's not critical.
 

The_Doc_Man

Happy Retired Curmudgeon and Occasional Moderator
Staff member
Local time
Today, 12:03
Joined
Feb 28, 2001
Messages
17,271
OK, after reformatting what you posted, here are a few comments.

Depending on how you pass in the form information, your "FOR EACH" loop would either be
FOR EACH c IN FORMS(name).CONTROLS (if you pass in the form's name)
or
FOR EACH c IN MainForm.CONTROLS (if you pass in the form as an object.)
At no time would you use "ME" in this context. AND you could make Intellisense work better if you declared c as
DIM C AS ACCESS.CONTROL rather than calling it a variant.

Then, you are going all around the mulberry bush to define the SQL string to do the insert. You have a string called strSQL that would work just fine and MAYBE simplify what you are doing.

Code:
        IF c <> c.OldValue THEN
            strSQL = "INSERT INTO MyLOG ( ID, DT, OldData, NewData, UserID )"
            strSQL = strSQL & "SELECT " & [FORMS]![CUSTOMERF]![CUSTOMERID}
            strSQL = strSQL & ", Now(), "& "'" & CStr( C.OldValue ) & "', '" & CStr( C ) & "','" 
            strSQL = strSQL & CStr( FORMS!MainMenuF!txtUserID ) & "' ;"
            DoCmd.RunSQL strSQL, dbFailOnError
        END IF
Obviously, this is some sort of audit log. You could search this forum for "Auditing" or "Audit Log" to see a lot of other posts on the topic.

You don't need the "AS" in the "SELECT" half of an INSERT INTO/SELECT sequence because insertion is positional at that point, not by name.

All that stuff with TempVars doesn't matter because those values are immutable during execution of the INSERT so you might as well make them constants in that string. This should run faster since you would have the constants in the string so no lookup (by the query) is involved.

I didn't use the forms as input but strongly suspect that your two form references would also be simplified by using either the form as an object or taking the form name. I'll do one example, you can figure out the other one. Besides, I'm not sure which one is which anyway.

strSQL = strSQL & "SELECT " & FORMS(name)![CUSTOMERID} (if you pass in the form name)
strSQL = strSQL & "SELECT " & Mainform![CUSTOMERID} (if you pass in a form object)

This should give you at least some ideas on how to proceed.
 

Yianni

Registered User.
Local time
Today, 13:03
Joined
May 15, 2017
Messages
38
First, please use code tags (</> on menubar) and indentation when posting code.
That code is quite confusing; bizarre actually. It looks like you're getting the value of a control, setting a tempVar to it then using the tempVar in an append sql statement. Why not just use the control reference in the sql? Suggest also that you do not use variants as variables for objects. I also don't see why you need to worry about whether or not a control is in the detail section. Normally, you loop over form controls and refer to the .Tag property so as to distinguish between those you want to include (have a tag property value) and those you don't. Having said all that, I can't see how or why anyone would create a record based on one control, then move onto the next control and create another record until you've looped over all the controls.

Perhaps you should explain what the operation is, or just go with the 2 or 3 versions that we've already provided with respect to how to pass an object or its name as a parameter. Or you could research functions and subs and how to include parameters and apply that knowledge to your situation, which isn't clear.
Your procedure could just as well be a sub as it doesn't return anything anyway, but that's not critical.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom