Re: Cond. Formatting w/ DLookup

croy wrote:

On Mon, 22 Oct 2007 12:13:42 -0500, Marshall Barton wrote:

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?


I finally figured out what the problem was/is: on each row
of this subform, there are actually *two* of these "open
form" buttons (or mock buttons), each for a different popup
form.

I can add the table for one of these to the subform query,
using an outer join, and all is well. But when I add the
second table to the query with an outer join, then the query
produces a non-updatable recordset. I'll experiment with
stacked (or nested?) queries to see if I can get around
that.


It is not normal to edit data from multiple tables through a
single query/form. Access is more liberal than most
database systems in that it sometimes does allow it, but I
am pretty sure that editing two tables is as far as you can
push it. If there is to be any hope of doing that, you must
include the primary key from each table, but it's not a good
practice. I have avoided form designs that attempt to do it
and I strongly suggest that you use a separate subform for
each table.

--
Marsh
MVP [MS Access]
.