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
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.
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.
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
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
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.
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
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.
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.
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.