Re: Cond. Formatting w/ DLookup

croy wrote:

On Tue, 16 Oct 2007 14:28:02 -0500, Marshall Barton wrote:

croy wrote:

On Mon, 15 Oct 2007 19:14:02 -0500, Marshall Barton wrote:

croy wrote:

A form has a command button to launch a popup form for
records related to the first form.

I'd like to be able to indicate on the first form, whether
or not there are any records on the related (popup) form.
Something conversationally like, "if there is a related
record in the table associated with the popup form, make
this field's background color blue".
[]
If table tblFishCount is related to the form's table by the
IvId field, then a better way would be to change the first
form's record source to be a query with an outer join of the
two tables and includes the on the IvId field from
tblFishCount:

SELECT T.*, R.IvId Is Not Null As DataExists
FROM yourtable As T LEFT JOIN tblFishCount As R
ON T.IvId = R.IvId

Then the code could be much simpler and faster:

Me.commandbutton.Enabled = Me.DataExists


This form is continuous (actually a subform), with command
buttons to open the popups on each line.

Using your excellent idea of the outer join, I put some code
in the OnCurrent event to change the color of the button
text (ForeColor). That works, but not quite like I had
hoped.
[]

[]
The Conditional Formatting feature was added (in A2K) just
for this situation. Unfortunately, you can only use CF on
text boxes, so we'll have to play some tricks.

Use a text box instead of a button. To prevent users from
typing in the text box, set its Locked property to Yes. You
can make the text box look a lot like a button by setting
its SpecialEffect property to Raised.

With all that taken care of, use Conditional Formatting
(Format menu) to change the text box's appearance. Select
the Expression Is option and enter the expression:
[DataExists]
and then pick your favorite colors.


This works good on a read-only form, but the outer join
prevents adding or deleting data. Any ideas for doing this
kind of thing on a data-entry/edit form?


Naturally the calculated DataExists field can not be edited,
but the fields in the query from your main table should be
editable.

Did you change something else in the query?

Is there some other things going on that might be locking
the records?

Are you sure that the form has its AllowEdits property set
to Yes and that the OpenForm method's DataMode argument
isn't overriding it?

--
Marsh
MVP [MS Access]
.

Loading