subform error #2455: invalid reference...

[kabaka]

Hi,

I hope that someone will be able to help me with this - I can't figure it out.

I have an unbound form with a subform (named Test_adj). On the form is a
button which displays the subform with current data. Because I'm using
access97 and don't have conditional formating, I have code that attempts to
fake it (it's not flawless since every row has the same formatting, but it's
not bad). The code that follows WORKED at one time, but doesn't now. It
fails on the first line of the "if" checking the "allocation_date" field. It
says that the "property may not exist or apply to the object specified". The
code follows.

Any help would be greatly appreciated.

Set dbs = CurrentDb
sql = "SELECT * from [current pace position total]"
Set rst = dbs.OpenRecordset(sql)
If Me.Test_Adj.Controls("Allocation_Date").Value = "" Or _
IsNull(Me.Test_Adj.Controls("Allocation_Date").Value) Then
Me.Test_Adj.Controls("Message").Visible = True
Else
Me.Test_Adj.Controls("Message").Visible = False
For i = 0 To Me.Test_Adj.Controls.count - 1
If Me.Test_Adj.Controls.Item(i).ControlType = acTextBox Then
Select Case Me.Test_Adj.Controls.Item(i).Name
Case "Hedge_val"
If rst.Fields("Hedge_val").Value = 0 Then
Me.Test_Adj.Controls.Item(i).BackColor = 16777215
Else
Me.Test_Adj.Controls.Item(i).BackColor = 65535
End If
Case "Debt_val"
If rst.Fields("Debt_val").Value = 0 Then
Me.Test_Adj.Controls.Item(i).BackColor = 16777215
Else
Me.Test_Adj.Controls.Item(i).BackColor = 65535
End If
Case "Equity_val"
If rst.Fields("Equity_val").Value = 0 Then
Me.Test_Adj.Controls.Item(i).BackColor = 16777215
Else
Me.Test_Adj.Controls.Item(i).BackColor = 65535
End If
Case "YTD_Val"
If rst.Fields("YTD_val").Value = 0 Then
Me.Test_Adj.Controls.Item(i).BackColor = 16777215
Else
Me.Test_Adj.Controls.Item(i).BackColor = 65535
End If
Case "Liability_BV_val"
If rst.Fields("Liability_BV_val").Value = 0 Then
Me.Test_Adj.Controls.Item(i).BackColor = 16777215
Else
Me.Test_Adj.Controls.Item(i).BackColor = 65535
End If
Case "Current_val"
If rst.Fields("Hedge_val").Value +
rst.Fields("Debt_val").Value + _
rst.Fields("Equity_val").Value -
rst.Fields("YTD_val").Value - _
rst.Fields("Liability_BV_val").Value = 0 Then
Me.Test_Adj.Controls.Item(i).BackColor = 16777215
Else
Me.Test_Adj.Controls.Item(i).BackColor = 65535
End If
Case "Target_val"
If rst.Fields("Target_val").Value = 0 Then
Me.Test_Adj.Controls.Item(i).BackColor = 16777215
Else
Me.Test_Adj.Controls.Item(i).BackColor = 65535
End If
Case "Pace_val"
If (rst.Fields("Hedge_val").Value +
rst.Fields("Debt_val").Value + _
rst.Fields("Equity_val").Value -
rst.Fields("YTD_val").Value - _
rst.Fields("Liability_BV_val").Value) - _
rst.Fields("Target_val").Value = 0 Then
Me.Test_Adj.Controls.Item(i).BackColor = 16777215
Else
Me.Test_Adj.Controls.Item(i).BackColor = 65535
End If
Case Else
Me.Test_Adj.Controls.Item(i).BackColor = 16777215
End Select
End If
Next i
rst.Close
Set dbs = Nothing