On Current Code Help . .

[Evagrius]

Hi - I am not certain what I am doing wrong here. I am testing this code to find if there is a value in a field - if not - I would like to change a certain control to a red color - but I am paused below because I keep getting "YES". Thank you for any help!

Private Sub Form_Current()
Dim MyDB As DAO.Database
Dim MyDR As DAO.Recordset
Set MyDB = CurrentDb()
Set MyDR = MyDB.OpenRecordset("QueryName", 2)
If IsNull(MyDR.Fields("FieldName")) Then
MsgBox "YES"
Else
MsgBox "NO"
End If

 

[boblarson]

What is the actual field name you are looking for? The example you show has "FieldName" instead of an actual name.

 

[boblarson]

Also, what are you really checking? Is it the same record source as the form? Are you wanting to change the control bound to the field if it has YES in it? If so, put away the DAO code and just use Conditional Formatting.

 

[Evagrius]

Hi Bob - thanks for the reply.

The control that I would like to change is a label on the form - the actual fieldName is "FinRatio". The record source for the entire form is the Query referenced in the code "QueryName".

All I am trying to do is as the user scrolls throught the form, if the Field FinRatio, which is not visible, is blank, then I am going to change the color of a label.

I know the problem with the code . . it keeps returning the value of only the first record, not the current one. Thanks for any help!

 

[boblarson]

My suggestion to you is to

1. Use a text box formatted to LOOK like a label instead. (You can set its special effect to be FLAT and then Border to TRANSPARENT and BACKSTYLE to NORMAL (if necessary set the backcolor to be the same as the form's back color).

2. Then just use CONDITIONAL FORMATTING and set the condition like this:

EXPRESSION IS Len([FinRatio] & "") = 0

And then set the fill to red.

 

[Evagrius]

Thank you Bob! That is a great idea and I will use it. But just so I can grow and develop, would you be willing to provide any guidance on how I could have done this programatically? I am curious as how one would have resolved this problem. Thank You!!

 

[boblarson]

Thank you Bob! That is a great idea and I will use it. But just so I can grow and develop, would you be willing to provide any guidance on how I could have done this programatically? I am curious as how one would have resolved this problem. Thank You!!

Okay, your code opens a recordset with a query titled QueryName (in your example). Instead it should open it up based on the current ID field.

So,

Set rst = CurrentDb.OpenRecordset("SELECT * FROM QueryName WHERE [IDFieldNameHere] = " & Me!IDFieldOnForm)

It should only return a recordset of 1 record.

 

[Evagrius]

I understand - thank you Bob! As always your help is deeply appreciated!

Also Bob, the below code seems to work. Do you see any potential for problem with it since it does not reference any recordset? Thanks you again!

If IsNull([FinRatio])Then
MsgBox "YES"
Else
MsgBox "NO"
End If