Change label if record exists

[Tara]

I have a continuous form (frmContacts) that displays all contacts between a
client and their mentor by date. Users have the ability to amend individual
records while keeping the original records intact by clicking a command
button (cmdAmd) to open up another form to enter alternate data into
tblAmend. The records are related via the RecordID. The original record
continues to show up on frmContacts but users have the ability to run reports
based either on original or amended data. Now my question...I'm looking for
an easy way for users to visually recognize the records that have been
amended when they look at them on frmContacts. My thought was to place a
border around the Amend button and change the color if there was an amended
record attached to the original record. For instance, the border is
generally blue, but would change to red if the record had been amended. Can
I do this? If so, how?

Thanks!

 

[Tara]

I just re-read my title. It'a a bit misleading since it's not the label I
want to change...sorry for any confusion!

 

[Marshall Barton]

I have a continuous form (frmContacts) that displays all contacts between a
client and their mentor by date. Users have the ability to amend individual
records while keeping the original records intact by clicking a command
button (cmdAmd) to open up another form to enter alternate data into
tblAmend. The records are related via the RecordID. The original record
continues to show up on frmContacts but users have the ability to run reports
based either on original or amended data. Now my question...I'm looking for
an easy way for users to visually recognize the records that have been
amended when they look at them on frmContacts. My thought was to place a
border around the Amend button and change the color if there was an amended
record attached to the original record. For instance, the border is
generally blue, but would change to red if the record had been amended.

On a continuous (or datasheet) form, you need to use
Conditional Formatting (Format menu) to get different
effects on different records. To do that the "border"
around the command button would have to be a text box a
little bigger and in back of the button. Set the text box's
BackColor to blue, Enabled to No and Locked to Yes.

Then use CF with the Expression Is: option to test if the
red back color should be used. I don't know how the form is
supposed to know if the record has been ammended or not so I
can't specify the expression at this time.

 

[Tara]

Thanks Marshall for the feedback. Currently the "border" is just a rectangle
and as you said is slightly bigger than and set behind the command button.
So, I guess the first thing would be to change that to a text box...which I
will do.

As for the form knowing whether or not a record has been amended, I was
thinking perhaps a query of some sort to see what recordID numbers are
present in each table. If the number is in both, then the background would
change. Would something like that work in this situation?

 

[Marshall Barton]

Well, using a DLookup might work, but ot would be pretty
slow. A better way is to modify the form record source
query to outer join the two tables and include the
ammendment key field from the ammendments table.

The query would be something along these lines:

SELECT Contacts.*, Ammendments.AmmendmentKey
FROM Contacts LEFT JOIN Ammendments
ON Contacts.RecordID = Ammendments.RecordID
WHERE whatever

Then the CF expression would simply be:
[AmmendmentKey] Is Not Null