Cond. Formatting w/ DLookup

[croy]

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 I try to inch this along toward actual code, I get to,

frmIv_OnCurrent
If IsNotNull([tblFishCount]![IvId] where
[IvId]=Me![txtIvId])

....but I doubt that is very close to working code.

Is it even in the right direction?

 

[Marshall Barton]

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 I try to inch this along toward actual code, I get to,

frmIv_OnCurrent
If IsNotNull([tblFishCount]![IvId] where
[IvId]=Me![txtIvId])

If your form is displayed in single view, you could make
that sort of thing work:

Me.commandbutton.Enabled = _
DCount("IvId","tblFishCount","IvId=" & Me!txtIvId)=0

but you can not set the BackColor of cammand buttons so my
example used Enabled instead.

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

 

[croy]

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 I try to inch this along toward actual code, I get to,

frmIv_OnCurrent
If IsNotNull([tblFishCount]![IvId] where
[IvId]=Me![txtIvId])

If your form is displayed in single view, you could make
that sort of thing work:

Me.commandbutton.Enabled = _
DCount("IvId","tblFishCount","IvId=" & Me!txtIvId)=0

but you can not set the BackColor of cammand buttons so my
example used Enabled instead.

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

Thanks Marsh.

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. When a main-form record comes up, for which any of
the associated subform records would change the button text
color, they all turn!

Any ideas on how to make this be more selective? (each row
on the subform show its conditional colors)

 

[Marshall Barton]

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 I try to inch this along toward actual code, I get to,

frmIv_OnCurrent
If IsNotNull([tblFishCount]![IvId] where
[IvId]=Me![txtIvId])

If your form is displayed in single view, you could make
that sort of thing work:

Me.commandbutton.Enabled = _
DCount("IvId","tblFishCount","IvId=" & Me!txtIvId)=0

but you can not set the BackColor of cammand buttons so my
example used Enabled instead.

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

Thanks Marsh.

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. When a main-form record comes up, for which any of
the associated subform records would change the button text
color, they all turn!

Any ideas on how to make this be more selective? (each row
on the subform show its conditional colors)

Ooohh, it's a continuous form. Well that changes just about
everything ;-)

You can not use VBA code to set control properties in a
continuous (or datasheet) form because there is only one
control that is displayed multiple times. So, it natuarally
follows that setting a property sets it for all rows.

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.

 

[croy]

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 I try to inch this along toward actual code, I get to,

frmIv_OnCurrent
If IsNotNull([tblFishCount]![IvId] where
[IvId]=Me![txtIvId])


If your form is displayed in single view, you could make
that sort of thing work:

Me.commandbutton.Enabled = _
DCount("IvId","tblFishCount","IvId=" & Me!txtIvId)=0

but you can not set the BackColor of cammand buttons so my
example used Enabled instead.

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

Thanks Marsh.

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. When a main-form record comes up, for which any of
the associated subform records would change the button text
color, they all turn!

Any ideas on how to make this be more selective? (each row
on the subform show its conditional colors)

Ooohh, it's a continuous form. Well that changes just about
everything ;-)

You can not use VBA code to set control properties in a
continuous (or datasheet) form because there is only one
control that is displayed multiple times. So, it natuarally
follows that setting a property sets it for all rows.

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.

All smiles here!

Thank you Marshall.

 

[Marshall Barton]

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 I try to inch this along toward actual code, I get to,

frmIv_OnCurrent
If IsNotNull([tblFishCount]![IvId] where
[IvId]=Me![txtIvId])


If your form is displayed in single view, you could make
that sort of thing work:

Me.commandbutton.Enabled = _
DCount("IvId","tblFishCount","IvId=" & Me!txtIvId)=0

but you can not set the BackColor of cammand buttons so my
example used Enabled instead.

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. When a main-form record comes up, for which any of
the associated subform records would change the button text
color, they all turn!

Any ideas on how to make this be more selective? (each row
on the subform show its conditional colors)

Ooohh, it's a continuous form. Well that changes just about
everything ;-)

You can not use VBA code to set control properties in a
continuous (or datasheet) form because there is only one
control that is displayed multiple times. So, it natuarally
follows that setting a property sets it for all rows.

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.

All smiles here!

Thank you Marshall.

That's terrific, smiles are good, even if they're not my new
granddaughter's ;-)

 

[croy]

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 I try to inch this along toward actual code, I get to,

frmIv_OnCurrent
If IsNotNull([tblFishCount]![IvId] where
[IvId]=Me![txtIvId])


If your form is displayed in single view, you could make
that sort of thing work:

Me.commandbutton.Enabled = _
DCount("IvId","tblFishCount","IvId=" & Me!txtIvId)=0

but you can not set the BackColor of cammand buttons so my
example used Enabled instead.

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

Thanks Marsh.

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. When a main-form record comes up, for which any of
the associated subform records would change the button text
color, they all turn!

Any ideas on how to make this be more selective? (each row
on the subform show its conditional colors)

Ooohh, it's a continuous form. Well that changes just about
everything ;-)

You can not use VBA code to set control properties in a
continuous (or datasheet) form because there is only one
control that is displayed multiple times. So, it natuarally
follows that setting a property sets it for all rows.

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?

 

[Marshall Barton]

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?

 

[croy]

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. Meanwhile, if anyone else has a solution... ;-).

 

[Marshall Barton]

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.

 

[croy]

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.

I'm not trying to edit data from multiple tables. The only
reason for more than one table in the query behind the
subform is to make conditional formatting of my mock form
open buttons work, and your suggestion for getting there was
to add a table to the subform's underlying query and make
the join outer. It worked fine for one button, but not for
two.

 

[Marshall Barton]

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.

I'm not trying to edit data from multiple tables. The only
reason for more than one table in the query behind the
subform is to make conditional formatting of my mock form
open buttons work, and your suggestion for getting there was
to add a table to the subform's underlying query and make
the join outer. It worked fine for one button, but not for
two.

I misunderstood the problem, sorry.

The data from the main table should be updatable so I still
don't understand what's wrong.

One thing to double check is to open the query by itself
(without using the form) and see if the data is updatable.
If it's not updatable, then the form is eliminated as part
of the problem. If it is updatable, then the problem is not
in the query and we need to concentrate on the form.

Post back with whatever you learn from that along with the
query's SQL and see if I can figure something out.

 

[croy]

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.

I'm not trying to edit data from multiple tables. The only
reason for more than one table in the query behind the
subform is to make conditional formatting of my mock form
open buttons work, and your suggestion for getting there was
to add a table to the subform's underlying query and make
the join outer. It worked fine for one button, but not for
two.

I misunderstood the problem, sorry.

The data from the main table should be updatable so I still
don't understand what's wrong.

One thing to double check is to open the query by itself
(without using the form) and see if the data is updatable.
If it's not updatable, then the form is eliminated as part
of the problem. If it is updatable, then the problem is not
in the query and we need to concentrate on the form.
Post back with whatever you learn from that along with the
query's SQL and see if I can figure something out.

That's the routine I've settled into... As soon as I see
the query won't allow new records, I know I'm stuck.

Here's the SQL for the subform's query:

SELECT tblIvDetail.*, IIf(IsNull([FishCountId]),"","Fish!")
AS Fish
FROM tblIvDetail LEFT JOIN tblFishCount ON
tblIvDetail.IvDetId = tblFishCount.IvDetId
ORDER BY tblIvDetail.IvPage, tblIvDetail.IvLine
WITH OWNERACCESS OPTION;


I've been experimenting with new queries, where I simply add
the "real" table, grab all the fields, and that is
updatable. Then I add the second table, not grabbing any
fields, and that is updatable. But as soon as I add the
third table, no fields grabbed, no more updatable.
I then try setting the last two tables to outer joins, but
the results are still not updatable.

I'm wondering if using a domain lookup wouldn't serve better
for getting the conditional formatting on these two
"buttons".

 

[Marshall Barton]

That's the routine I've settled into... As soon as I see
the query won't allow new records, I know I'm stuck.

Here's the SQL for the subform's query:

SELECT tblIvDetail.*, IIf(IsNull([FishCountId]),"","Fish!")
AS Fish
FROM tblIvDetail LEFT JOIN tblFishCount ON
tblIvDetail.IvDetId = tblFishCount.IvDetId
ORDER BY tblIvDetail.IvPage, tblIvDetail.IvLine
WITH OWNERACCESS OPTION;


I've been experimenting with new queries, where I simply add
the "real" table, grab all the fields, and that is
updatable. Then I add the second table, not grabbing any
fields, and that is updatable. But as soon as I add the
third table, no fields grabbed, no more updatable.
I then try setting the last two tables to outer joins, but
the results are still not updatable.

I'm wondering if using a domain lookup wouldn't serve better
for getting the conditional formatting on these two
"buttons".

Are you saying that query is not updatable? It certainly
looks good to me.

I just created a test query that I think would look like
this with your tables and everything worked fine:

SELECT tblIvDetail.*,
Nz(FishCountId, "Fish") AS Fish,
Nz(otherCountId, "other") AS Other
FROM (tblIvDetail
LEFT JOIN tblFishCount
ON tblIvDetail.IvDetId = tblFishCount.IvDetId)
LEFT JOIN tblother
ON tblIvDetail.IvDetId = tblother.IvDetId

 

[croy]

That's the routine I've settled into... As soon as I see
the query won't allow new records, I know I'm stuck.

Here's the SQL for the subform's query:

SELECT tblIvDetail.*, IIf(IsNull([FishCountId]),"","Fish!")
AS Fish
FROM tblIvDetail LEFT JOIN tblFishCount ON
tblIvDetail.IvDetId = tblFishCount.IvDetId
ORDER BY tblIvDetail.IvPage, tblIvDetail.IvLine
WITH OWNERACCESS OPTION;


I've been experimenting with new queries, where I simply add
the "real" table, grab all the fields, and that is
updatable. Then I add the second table, not grabbing any
fields, and that is updatable. But as soon as I add the
third table, no fields grabbed, no more updatable.
I then try setting the last two tables to outer joins, but
the results are still not updatable.

I'm wondering if using a domain lookup wouldn't serve better
for getting the conditional formatting on these two
"buttons".

Are you saying that query is not updatable? It certainly
looks good to me.

I'm not able to add records to it, or alter values.

I just created a test query that I think would look like
this with your tables and everything worked fine:

SELECT tblIvDetail.*,
Nz(FishCountId, "Fish") AS Fish,
Nz(otherCountId, "other") AS Other
FROM (tblIvDetail
LEFT JOIN tblFishCount
ON tblIvDetail.IvDetId = tblFishCount.IvDetId)
LEFT JOIN tblother
ON tblIvDetail.IvDetId = tblother.IvDetId

Were you able to add records?

I plugged that into my mdb (fixing the table name for the
"other" table to "tblFishMeas"), got the query to run, but
still not updatable.

Does it matter that there is a "natural" one-to-many
relationship between tblIvDetail and tblFishCount, and
between tblIvDetail and tblFishMeas? Another thought:
tblFishCount holds values for Species, NumberKept, and
NumberReleased, whereas tblFishMeas holds values for each
fish measured. Each of these "fish" tables also has a
natural relationship to tblSpecies. Maybe the hangup is
here (Species conflict--struggling to get my head around
this...)?

 

[Marshall Barton]

That's the routine I've settled into... As soon as I see
the query won't allow new records, I know I'm stuck.

Here's the SQL for the subform's query:

SELECT tblIvDetail.*, IIf(IsNull([FishCountId]),"","Fish!")
AS Fish
FROM tblIvDetail LEFT JOIN tblFishCount ON
tblIvDetail.IvDetId = tblFishCount.IvDetId
ORDER BY tblIvDetail.IvPage, tblIvDetail.IvLine
WITH OWNERACCESS OPTION;


I've been experimenting with new queries, where I simply add
the "real" table, grab all the fields, and that is
updatable. Then I add the second table, not grabbing any
fields, and that is updatable. But as soon as I add the
third table, no fields grabbed, no more updatable.
I then try setting the last two tables to outer joins, but
the results are still not updatable.

I'm wondering if using a domain lookup wouldn't serve better
for getting the conditional formatting on these two
"buttons".

Are you saying that query is not updatable? It certainly
looks good to me.

I'm not able to add records to it, or alter values.

I just created a test query that I think would look like
this with your tables and everything worked fine:

SELECT tblIvDetail.*,
Nz(FishCountId, "Fish") AS Fish,
Nz(otherCountId, "other") AS Other
FROM (tblIvDetail
LEFT JOIN tblFishCount
ON tblIvDetail.IvDetId = tblFishCount.IvDetId)
LEFT JOIN tblother
ON tblIvDetail.IvDetId = tblother.IvDetId

Were you able to add records?

I plugged that into my mdb (fixing the table name for the
"other" table to "tblFishMeas"), got the query to run, but
still not updatable.

Does it matter that there is a "natural" one-to-many
relationship between tblIvDetail and tblFishCount, and
between tblIvDetail and tblFishMeas? Another thought:
tblFishCount holds values for Species, NumberKept, and
NumberReleased, whereas tblFishMeas holds values for each
fish measured. Each of these "fish" tables also has a
natural relationship to tblSpecies. Maybe the hangup is
here (Species conflict--struggling to get my head around
this...)?

Yes, I could edit existing records and add new records.

Since the Species table is not involved in the query, it can
not interfere beyond its referential integrity constraints.

I am not sure what you mean by "natural" relationship.
Technically, I think that means that you have a natural
primary key in tbllvlDetail instead of a surrogate primary
key. If that's what you're saying, then I don't see how it
can cause a problem. Not have a primary key in tbllvlDetail
might be a problem though.

 

[croy]

Yes, I could edit existing records and add new records.

Hmmm.


Since the Species table is not involved in the query, it can
not interfere beyond its referential integrity constraints.

I am not sure what you mean by "natural" relationship.
Technically, I think that means that you have a natural
primary key in tbllvlDetail instead of a surrogate primary
key. If that's what you're saying, then I don't see how it
can cause a problem. Not have a primary key in tbllvlDetail
might be a problem though.

Oops. I'm stepping on technical words with generic intent!

What I meant by natural was that the relationship is set up
in the relationships window, database-wide.

All my tables have Primary Keys.

Could it be that this is a version-specific specific
problem? I'm running Access 2002, with 2000 set as the
default format.

 

[Marshall Barton]

Oops. I'm stepping on technical words with generic intent!

What I meant by natural was that the relationship is set up
in the relationships window, database-wide.

All my tables have Primary Keys.

Could it be that this is a version-specific specific
problem? I'm running Access 2002, with 2000 set as the
default format.

I don't think the version of Access/Jet makes a difference.
I have done this kind of thing in every version.

I am afraid I am at a loss as to what the problem is and out
of ideas on what else to try.

I guess we could try to get the DCount approach working, if
for no ither reason than to find out if the perfermance is
acceptable or not. I think(?) the CF expression would be:

DCount("IvId", "tblFishCount, "IvId=" & Form.IvId) > 0

 

[croy]

I don't think the version of Access/Jet makes a difference.
I have done this kind of thing in every version.

I am afraid I am at a loss as to what the problem is and out
of ideas on what else to try.

I guess we could try to get the DCount approach working, if
for no ither reason than to find out if the perfermance is
acceptable or not. I think(?) the CF expression would be:

DCount("IvId", "tblFishCount, "IvId=" & Form.IvId) > 0

Well, you sure gave it one helluva effort! I cerainly
appreciate the help.

On the good side, one of the buttons is working, and that
makes life better for the users.

I'll work on getting the other one tuned up as time permits.

This job is getting to be an excersize in distractions--I
have no idea what I started out to do several weeks ago, nor
do I have any recollection of how it led me here!

"Hey--where are we going and what am I doing in this
handbasket?" Hehe.

Thanks again Marshall.