Passing a fieldname to a function

[Guest]

Hello,

I am trying to create a function that will do a date comparison and change
the background color of a field. As I need to use this on several fields, I
would like to be able to create a universal function that I can supply with
the field name and then simply call it as required. I am however having
trouble passing along the fieldname.

My function looks something very similar to this


Function Compdt(FieldName as String)
Dim leaddt as date, otherdt as date
Dim lngRed as Long

lngRed = RBG(255,0,0)

leaddt = Forms![FormName]![SubFromName].Form.Lead_Date
otherdt = Forms![FormName]![SubFromName].Form(FieldName)

If IsNull(otherdt)=False Then
If otherdt < leaddt Then otherdt.backcolor=lngRed
End If

End Function

Then I call this function is a sub like
Call Compdt(Stress_Date)
But is is giving me an error #2465 "...can't find the field '6/15/2006'"
which is the value in the field in question???

Thanking you in advance,

Daniel

 

[Granny Spitz via AccessMonster.com]

Then I call this function is a sub like
Call Compdt(Stress_Date)
But is is giving me an error #2465 "...can't find the field '6/15/2006'"
which is the value in the field in question???

That's what you passed to the function, the value, not the name of the column.
Stress_Date is a property of the form and the default member of a form's
property is *value.* Instead, use the name of the control like this:


Call Compdt(Me!Stress_Date.Name)

 

[Guest]

I've got it partially functional but can't seem to change the background color?

Hear is the code thus far

***
Dim leaddt As Date, otherdt As Date
Dim lngRed As Long
lngRed = RGB(255, 0, 0)

If IsNull(Forms![Drawing Management Frm]![Drawing Management Frm - Issue
Subfrm]![Drawing History Frm sous-formulaire]![Drawing Status Tbl
subform]![Lead Date].Value) = False Then
leaddt = Forms![Drawing Management Frm]![Drawing Management Frm -
Issue Subfrm]![Drawing History Frm sous-formulaire]![Drawing Status Tbl
subform]![Lead Date].Value
End If
If IsNull(Eval("Forms![Drawing Management Frm]![Drawing Management Frm
- Issue Subfrm]![Drawing History Frm sous-formulaire]![Drawing Status Tbl
subform]![" & strFormName & "]")) = False Then
otherdt = Eval("Forms![Drawing Management Frm]![Drawing Management
Frm - Issue Subfrm]![Drawing History Frm sous-formulaire]![Drawing Status Tbl
subform]![" & strFormName & "]")
End If

If IsNull(otherdt) = False And IsNull(leaddt) = False Then
If otherdt < leaddt Then
Forms![Drawing Management Frm].[Drawing Management Frm - Issue
Subfrm].[Drawing History Frm sous-formulaire].[Drawing Status Tbl
subform].Controls!(strFormName).BackColor = lngRed
End If
End If
***
It sucessfully retrieve the dates from the specified fields and compares
them... The logical comparison works but the final part of the code does not
work. Can someone please point out what is wrong with the line

Forms![Drawing Management Frm].[Drawing Management Frm - Issue
Subfrm].[Drawing History Frm sous-formulaire].[Drawing Status Tbl
subform].Controls!(strFormName).BackColor = lngRed


I've tried so many variation trying to trouble shoot the problem I don't
know what's left to try and I know the solution is going to quite simple.

Thank you,

Daniel

 

[Granny Spitz via AccessMonster.com]

I've got it partially functional but can't seem to change the background color?

Daniel, I don't know how to tell you this without being unkind. Your code is
such a mess I can't read it. Can you put the logic to get and compare the
dates in a public event procedure (or even a public procedure) in the Drawing
Status Tbl subform so that you can use simple code like this to change the
control's background color?

Me!ControlName.BackColor = vbRed

You could trigger the event procedure or call the public procedure in the
subform from code elsewhere.

If you can't, then maybe someone else in the newsgroup can help you, but I
don't think I can. Sorry.

 

[Granny Spitz via AccessMonster.com]

Actually, rereading your first post, the simpler code in a universal function
to change the backcolor of a control on the same form should be:

Me.Controls(ControlName).BackColor = vbRed

with ControlName as the variable (string data type) passed to the function.

 

[Guest]

That's brutal!

How then does one properly handle dealing with a control that is 3 or 4
subforms deep to be able to make date comparisons and change background
colors?

Daniel

 

[David F Cox]

I am guessing. Maybe it does work, but you have not redrawn the form?

I've got it partially functional but can't seem to change the background
color?

Hear is the code thus far

***
Dim leaddt As Date, otherdt As Date
Dim lngRed As Long
lngRed = RGB(255, 0, 0)

If IsNull(Forms![Drawing Management Frm]![Drawing Management Frm -
Issue
Subfrm]![Drawing History Frm sous-formulaire]![Drawing Status Tbl
subform]![Lead Date].Value) = False Then
leaddt = Forms![Drawing Management Frm]![Drawing Management Frm -
Issue Subfrm]![Drawing History Frm sous-formulaire]![Drawing Status Tbl
subform]![Lead Date].Value
End If
If IsNull(Eval("Forms![Drawing Management Frm]![Drawing Management Frm
- Issue Subfrm]![Drawing History Frm sous-formulaire]![Drawing Status Tbl
subform]![" & strFormName & "]")) = False Then
otherdt = Eval("Forms![Drawing Management Frm]![Drawing Management
Frm - Issue Subfrm]![Drawing History Frm sous-formulaire]![Drawing Status
Tbl
subform]![" & strFormName & "]")
End If

If IsNull(otherdt) = False And IsNull(leaddt) = False Then
If otherdt < leaddt Then
Forms![Drawing Management Frm].[Drawing Management Frm - Issue
Subfrm].[Drawing History Frm sous-formulaire].[Drawing Status Tbl
subform].Controls!(strFormName).BackColor = lngRed
End If
End If
***
It sucessfully retrieve the dates from the specified fields and compares
them... The logical comparison works but the final part of the code does
not
work. Can someone please point out what is wrong with the line

Forms![Drawing Management Frm].[Drawing Management Frm - Issue
Subfrm].[Drawing History Frm sous-formulaire].[Drawing Status Tbl
subform].Controls!(strFormName).BackColor = lngRed


I've tried so many variation trying to trouble shoot the problem I don't
know what's left to try and I know the solution is going to quite simple.

Thank you,

Daniel

That's what you passed to the function, the value, not the name of the
column.
Stress_Date is a property of the form and the default member of a form's
property is *value.* Instead, use the name of the control like this:


Call Compdt(Me!Stress_Date.Name)

 

[Granny Spitz via AccessMonster.com]

How then does one properly handle dealing with a control that is 3 or 4
subforms deep to be able to make date comparisons and change background
colors?

In this example, most of the code is in the subform's module, which is 3
levels deep. The subform contains three text boxes, txtLeadDate,
txtStartDate and txtCompletionDate. The UpdateAllBkgnds function uses "Date"
as part of the text box name to select *which* text boxes on the subform to
compare to the value in txtLeadDate. The text box's background color will
turn red if the date is before txtLeadDate, otherwise it will turn white.

I've also supplied the syntax used to assign a value to one of the text boxes
(which won't trigger an after update event) and then call the UpdateAllBkgnds
function to reset the background colors of the text boxes as necessary.

'*****************************
' Code in subform's module:

Private Sub Form_Current()
Call UpdateAllBkgnds
End Sub

Private Sub txtCompletionDate_AfterUpdate()
Call CompDt(Me!txtCompletionDate.Name)
End Sub

Private Sub txtLeadDate_AfterUpdate()
Call UpdateAllBkgnds
End Sub

Private Sub txtStartDate_AfterUpdate()
Call CompDt(Me!txtStartDate.Name)
End Sub

Public Sub CompDt(sControlName As String)
If (Not (IsNull(Me.Controls(sControlName).Value))) Then
If (Me.Controls(sControlName).Value < Me!txtLeadDate.Value) Then
Me.Controls(sControlName).BackColor = vbRed
Else
Me.Controls(sControlName).BackColor = vbWhite
End If
Else
Me.Controls(sControlName).BackColor = vbWhite
End If
End Sub

Public Sub UpdateAllBkgnds()
Dim ctl As Control

For Each ctl In Me.Controls
If ((ctl.ControlType = acTextBox) And _
(ctl.Name <> Me!txtLeadDate.Name) And _
(InStr(1, ctl.Name, "Date") > 0)) Then
Call CompDt(ctl.Name)
End If
Next ctl
End Sub
'*****************************

'*****************************
' Code in main form to update background colors in
' 3rd level subform (beware of wraparound as this is
' 2 lines of code):

Me.Controls("subFrmCtlName1").Form.Controls("subFrmCtlName2").Form.
Controls("subFrmCtlName3").Form.Controls("txtStartDate").Value = #4/1/2006#

Call Me.Controls("subFrmCtlName1").Form.Controls("subFrmCtlName2").Form.
Controls("subFrmCtlName3").Form.UpdateAllBkgnds

'*****************************

'*****************************
' Code in standard module or elsewhere to update
' background colors in 3rd level subform (beware of
' wraparound as this is 2 lines of code):

Forms!frmFormName.Controls("subFrmCtlName1").Form.Controls("subFrmCtlName2").
Form.Controls("subFrmCtlName3").Form.Controls("txtStartDate").Value =
#4/1/2006#

Forms!frmFormName.Controls("subFrmCtlName1").Form.Controls("subFrmCtlName2").
Form.Controls("subFrmCtlName3").Form.UpdateAllBkgnds

'*****************************

 

[bbn]

Should have come here an hour ago..."TableName.Controls(variable).property"
is exactly what I needed!!