change color of text field if record exists in another table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to change the color of a text field on a form to alert the
user if there is a record associated with this field (record can be accessed
by double clicking the field)? What would that code look like?
 
Is there a way to change the color of a text field on a form to alert the
user if there is a record associated with this field (record can be accessed
by double clicking the field)? What would that code look like?
Click to expand...

You could use code like this in the Form's Current event:

Private Sub Form_Current()
If IsNull(DLookUp("ID", "OtherTable", "ID = " & Me!txtID) Then
Me!txtThisTextbox.BackColor = vbWhite
Else
Me!txtThisTextbox.BackColor = vbPink
End If
End Sub

with appropriate field and table name changes of course.

John W. Vinson[MVP]
 
Thanks, I got it to work EXCEPT for if there is no record at all in the
subform that holds the text box I want to change. When that occurs, I get
the debug screen and the following line is highlighted in yellow

If IsNull(DLookup("CompleteID", "tblPam", "CompleteID = " & Me!CompleteID))
Then
blah blah blah

Is there a way to handle the problem when no record exists in the subform?
 
The error message in the debug window says
Syntax error (missing operator) in query expression 'completeID='
 
Thanks, I got it to work EXCEPT for if there is no record at all in the
subform that holds the text box I want to change. When that occurs, I get
the debug screen and the following line is highlighted in yellow

If IsNull(DLookup("CompleteID", "tblPam", "CompleteID = " & Me!CompleteID))
Then
blah blah blah

Is there a way to handle the problem when no record exists in the subform?
Click to expand...

Try

If IsNull(DLookup("CompleteID", "tblPam", "CompleteID = " &
NZ(Me!CompleteID)))

assuming that there are no records in tblPam with 0 as the CompleteID.

John W. Vinson[MVP]
 
I still get the same error message - the "CompleteID" text box on the subform
actually has "Autonumber" in it when there is record entered yet. Is that the
problem?
 
I still get the same error message - the "CompleteID" text box on the subform
actually has "Autonumber" in it when there is record entered yet. Is that the
problem?
Click to expand...

Probably: there IS no value to look up!

Try:

Private Sub Form_Current()
If Me.NewRecord Then
Me!txtThisTextBox.BackColor = vbWhite
Else
<the code you had before>
End If

John W. Vinson[MVP]
 
Back
Top