[framarco]

I am using Access 2003. I have a form, 'Protocollo', with a subform 'sfrm Prot' that is linked to the form on the field IDProt. The record source for the subform is a subset of the table [Prot_detail], and it contains only two records. The first record is displayed when btnIn on the subform is pushed and the second is displayed when btnOut is pushed. I want to change the font on the buttons to bold if the underlying record has any text in the 'Oggetto' field. I wrote a routine that is attached to the Current event of the subform, but it is not working. Here's what I wrote:

Dim rst As Recordset

set rst = Me.Recordsetclone

Me.btnIn.FontBold = IsNull(Me.Oggetto)
rst.MoveLast
Me.btnOut.FontBold = IsNull(Me.Oggetto)

rst.Close

Any ideas why this doesn't work? Can someone suggest something that would work, hopefully without using DLookup?

[orangeCat1]

I think you need an IF statement along this line:::

If IsNull(Me.Oggetto) Then
Me.btnIn.FontBold = True
.....

[framarco]

Actually, I originally wrote the routine using IF...THEN...ELSE statements. It still didn't work. Since IsNull() returns either True or False, the statements I have work. That is not where my problem is. The problem is that Me.Oggetto is only picking up the value from the record that is currently displayed. After the line rst.Movelast, I thought Me.Oggetto should get the value from the next record, but it is not doing that.

[OBP]

After the rst.Movelast add
msgbox rst.recordcount
which will tell you if the Subform has 1 or 2 more records.
You could also use
rst.movenext
to move to the second record.

[framarco]

Originally Posted by OBP

After the rst.Movelast add
msgbox rst.recordcount
which will tell you if the Subform has 1 or 2 more records.
You could also use
rst.movenext
to move to the second record.

rst.recordcount shows 2.

I have made some progress on this, but it is still not working as I want. I now have:

Set rst = Me.RecordsetClone

rst.MoveFirst
Me.btnEntrata.FontBold = Not IsNull(Oggetto)
rst.MoveLast
Me.btnUscita.FontBold = Not IsNull(Oggetto)
Me.Refresh

rst.Close

What happens is that if the 'Oggetto' field in the currently displayed record is blank, both btnIn and btnOut will display normal text. If, on the other hand, the 'Oggetto' field has something in it, both buttons will display bold text. I want to be able to display one button with bold text and one with normal text, based on whether the Oggetto field (not the form control) has text in it or not.

My gut tells me that the problem lies with the 'IsNull(Oggetto)' statement. If I set a breakpoint at the beginning of the routine and check the value of Oggetto while the routine runs, the value does not change after the rst.MoveLast statement runs.

[OBP]

Try
Set rst = Me.RecordsetClone

rst.MoveFirst
Me.btnEntrata.FontBold = Not IsNull(rst.Oggetto)
rst.MoveLast
Me.btnUscita.FontBold = Not IsNull(rst.Oggetto)
Me.Refresh

rst.Close

[framarco]

OBP: That caused a "Compilation error. Method or data member not found."

But it gave me the thought that solved the problem. Here is the winning code:

Set rst = Me.RecordsetClone
rst.MoveFirst
Me.btnEntrata.FontBold = Not IsNull(rst.Fields("Oggetto"))
rst.MoveLast
Me.btnUscita.FontBold = Not IsNull(rst.Fields("Oggetto"))
Me.Refresh
rst.Close

Why this works and your solution did not is beyond me.

[OBP]

Access VBA is full of idiosyncrasies like that.