changing control props in datasheet view for 'active' record

[Guest]

hi,

i don't know whether my subject line's heading fairly represents what i'm
trying to learn but here goes anyway...

i have a form (that uses a query as its record source) in datasheet view. i
would like to be able to change the colors (fore and/or background) of the
controls on it subject to the condition the record pointer is on a particular
record. if you make a change to a controls color properties, when the data is
viewed in this form, it applies to everything the user sees not just the
record the pointer's on. i don't know if it makes a difference, but records
on the form are non-editable, non-deletable and records may not be added
using this particular datasheet view.

i'd be willing to bet there's some vba type property setting that needs to
be tested for but i haven't a clue what it'd be called.

thanks for any help in advance.

ted

 

[Marshall Barton]

i have a form (that uses a query as its record source) in datasheet view. i
would like to be able to change the colors (fore and/or background) of the
controls on it subject to the condition the record pointer is on a particular
record. if you make a change to a controls color properties, when the data is
viewed in this form, it applies to everything the user sees not just the
record the pointer's on. i don't know if it makes a difference, but records
on the form are non-editable, non-deletable and records may not be added
using this particular datasheet view.

The only way I know of that can do this in datasheet view is
Conditional Formatting (Format menu).

To highlight the current record, use the form's Current
event to set the record's PK field to a text box in the
form's header section. Then use this text box in the
condition for each bound control in the detail section.

 

[Guest]

marshall,

my pk consists of lastname, firstname, mi, medrecord, irb_number. can you
flesh out a little more what you're suggestion would look like. i don't know
if this really matters all that much, but my datasheet form is a sub-form of
a main form. the main one's called "DataSheet View of Screen" and the
subFrm's called "Screening Log DS View". the form header of the subForm's
holds nothing but labels with the controls' names.

with regards,

ted

 

[Marshall Barton]

my pk consists of lastname, firstname, mi, medrecord, irb_number. can you
flesh out a little more what you're suggestion would look like. i don't know
if this really matters all that much, but my datasheet form is a sub-form of
a main form. the main one's called "DataSheet View of Screen" and the
subFrm's called "Screening Log DS View". the form header of the subForm's
holds nothing but labels with the controls' names.

Look at the subform's Form object in design view. Make sure
the Form Header section exists (View menu). Create a text
box named txtCurKey in the header section.

Display the Form's property sheet, select the OnCurrent
property and click on the [...] button at the right side of
the property. When the code window appears, add a line of
code at the cursor:

Me.txtCurKey = Me.nameofPKfield

Now, go back to the form design screen and select the
lastname text box. Then use the Format - Conditional
Formatting menu item to open the CF window. Select the
Expression Is option in the drop down box and enter the
expression:
[nameofPKfield] = [txtCurKey]
and select your special fore and back ground colors.

Test it to make sure the last name text box works the way
you want it. When it does, repeat the CF settings for the
other text boxes.

 

[Guest]

hi marshall,

that was somewhat easier to follow (relative newbie over here) but here's a
snag thhat i found; although the field txtCurkey is recognized by VBA when i
add it to the VBA code i'm writing in On Current event section, the name (i
guess that) my PK's called "PrimaryKey" (to judge from its appearance in the
Index name column of the table's) is not and VBA chokes on it. what to do?

with regards,

ted

my pk consists of lastname, firstname, mi, medrecord, irb_number. can you
flesh out a little more what you're suggestion would look like. i don't know
if this really matters all that much, but my datasheet form is a sub-form of
a main form. the main one's called "DataSheet View of Screen" and the
subFrm's called "Screening Log DS View". the form header of the subForm's
holds nothing but labels with the controls' names.

Look at the subform's Form object in design view. Make sure
the Form Header section exists (View menu). Create a text
box named txtCurKey in the header section.

Display the Form's property sheet, select the OnCurrent
property and click on the [...] button at the right side of
the property. When the code window appears, add a line of
code at the cursor:

Me.txtCurKey = Me.nameofPKfield

Now, go back to the form design screen and select the
lastname text box. Then use the Format - Conditional
Formatting menu item to open the CF window. Select the
Expression Is option in the drop down box and enter the
expression:
[nameofPKfield] = [txtCurKey]
and select your special fore and back ground colors.

Test it to make sure the last name text box works the way
you want it. When it does, repeat the CF settings for the
other text boxes.

 

[Guest]

.....

does your explanation only hold for the case when there's just one field
that's been designated a PK; in my case there are 5 fields!!??

my pk consists of lastname, firstname, mi, medrecord, irb_number. can you
flesh out a little more what you're suggestion would look like. i don't know
if this really matters all that much, but my datasheet form is a sub-form of
a main form. the main one's called "DataSheet View of Screen" and the
subFrm's called "Screening Log DS View". the form header of the subForm's
holds nothing but labels with the controls' names.

Look at the subform's Form object in design view. Make sure
the Form Header section exists (View menu). Create a text
box named txtCurKey in the header section.

Display the Form's property sheet, select the OnCurrent
property and click on the [...] button at the right side of
the property. When the code window appears, add a line of
code at the cursor:

Me.txtCurKey = Me.nameofPKfield

Now, go back to the form design screen and select the
lastname text box. Then use the Format - Conditional
Formatting menu item to open the CF window. Select the
Expression Is option in the drop down box and enter the
expression:
[nameofPKfield] = [txtCurKey]
and select your special fore and back ground colors.

Test it to make sure the last name text box works the way
you want it. When it does, repeat the CF settings for the
other text boxes.

 

[Marshall Barton]

that was somewhat easier to follow (relative newbie over here) but here's a
snag thhat i found; although the field txtCurkey is recognized by VBA when i
add it to the VBA code i'm writing in On Current event section, the name (i
guess that) my PK's called "PrimaryKey" (to judge from its appearance in the
Index name column of the table's) is not and VBA chokes on it. what to do?

The field name is the name of the **field** in the table's
design view, not the name of an index. You may or may not
have a field named PrimaryKey and you may or may not have an
index named PrimaryKey, but they are different things.

Are you sure you need so many fields in the primary key
index? Wouldn't the irb_number field be sufficient? At
least you should be aware that the lastname, firstname and
mi fields will **NOT** help identify a unique record, any
number of people can have the same name. So, at least you
should remove those from the PK index's field list. Then
rethink your idea of primary key to make sure you aren't
going to run into a wall somewhere down the road.

does your explanation only hold for the case when there's
just one field that's been designated a PK; in my case there
are 5 fields!!??

What we're looking for here is the field or fields that
uniquely identify a record. We need this so that the
conditional formatting will only be applied to one record.
If there are multiple fields required to uniquely identify a
record, then you will need a text box in the header section
for each field and a line of code to assign each value to
the text boxes.

Me.txtCurKey1 = Me.nameofPKfield1
Me.txtCurKey2 = Me.nameofPKfield2
. . .

The CF expression will then be more like this:

[nameofPKfield1] = [txtCurKey1] And [nameofPKfield2] =
[txtCurKey2] And . . .
--
Marsh
MVP [MS Access]

Look at the subform's Form object in design view. Make sure
the Form Header section exists (View menu). Create a text
box named txtCurKey in the header section.

Display the Form's property sheet, select the OnCurrent
property and click on the [...] button at the right side of
the property. When the code window appears, add a line of
code at the cursor:

Me.txtCurKey = Me.nameofPKfield

Now, go back to the form design screen and select the
lastname text box. Then use the Format - Conditional
Formatting menu item to open the CF window. Select the
Expression Is option in the drop down box and enter the
expression:
[nameofPKfield] = [txtCurKey]
and select your special fore and back ground colors.

Test it to make sure the last name text box works the way
you want it. When it does, repeat the CF settings for the
other text boxes.

Ted wrote:
i have a form (that uses a query as its record source) in datasheet view. i
would like to be able to change the colors (fore and/or background) of the
controls on it subject to the condition the record pointer is on a particular
record. if you make a change to a controls color properties, when the data is
viewed in this form, it applies to everything the user sees not just the
record the pointer's on. i don't know if it makes a difference, but records
on the form are non-editable, non-deletable and records may not be added
using this particular datasheet view.


:
The only way I know of that can do this in datasheet view is
Conditional Formatting (Format menu).

To highlight the current record, use the form's Current
event to set the record's PK field to a text box in the
form's header section. Then use this text box in the
condition for each bound control in the detail section.

 

[Guest]

hi marshall,

i will intersperse my responses/reactions below, but let me add that i've
worked on this a bit since getting your response and found what i think is
just the solution that my users'll appreciate. essentially, i took your
suggestion to heart and figured that i could decide to use the [Last Name]
field as my primary key and went on with the CF-ing to create a ds view that
illuminates (turns to red) the black foreground characters --- i think the
advantage of using just this as my determining factor of 'uniqueness' rather
than (as i had previously anticipated) the entire PK comes from the fact that
for every n-tuple of same consecutive LNs it illuminates the lot of them
giving the user the opportunity to really assure themselves that they're
picking/looking at the one with the correct litany of other relevant (dates,
etc. etc) information.

The field name is the name of the **field** in the table's
design view, not the name of an index. You may or may not
have a field named PrimaryKey and you may or may not have an
index named PrimaryKey, but they are different things.

yes, as i've already proven to myself before receiving your reply -- but it
is appreciated nevertheless; live 'n learn.

Are you sure you need so many fields in the primary key
index? Wouldn't the irb_number field be sufficient? At
least you should be aware that the lastname, firstname and
mi fields will **NOT** help identify a unique record, any
number of people can have the same name. So, at least you
should remove those from the PK index's field list. Then
rethink your idea of primary key to make sure you aren't
going to run into a wall somewhere down the road.

this is kind of a patient screening database, each record recording the LN,
FN, MI, MR# (which are patient specific) and lastly the IRB# which represents
the clinical study for which the patient was screened for possible
participation; this last bit is what must be unique for the prior combination
of fields that comprise the composite PK. in other words, a patent may only
be screened for the an IRB# once but IRB# numbers may and do appear multiple
numbers of times in the table because there are more than one patient's worth
of information being tracked.

does your explanation only hold for the case when there's
just one field that's been designated a PK; in my case there
are 5 fields!!??

What we're looking for here is the field or fields that
uniquely identify a record. We need this so that the
conditional formatting will only be applied to one record.
If there are multiple fields required to uniquely identify a
record, then you will need a text box in the header section
for each field and a line of code to assign each value to
the text boxes.

Me.txtCurKey1 = Me.nameofPKfield1
Me.txtCurKey2 = Me.nameofPKfield2
. . .

The CF expression will then be more like this:

[nameofPKfield1] = [txtCurKey1] And [nameofPKfield2] =
[txtCurKey2] And . . .
--

i get it, i get it.... very nice, this is exactly what i needed and will
come back to it if warranted.

Marsh
MVP [MS Access]

with every best wish and thanks,

ted

Ted wrote:
my pk consists of lastname, firstname, mi, medrecord, irb_number. can you
flesh out a little more what you're suggestion would look like. i don't know
if this really matters all that much, but my datasheet form is a sub-form of
a main form. the main one's called "DataSheet View of Screen" and the
subFrm's called "Screening Log DS View". the form header of the subForm's
holds nothing but labels with the controls' names.

Look at the subform's Form object in design view. Make sure
the Form Header section exists (View menu). Create a text
box named txtCurKey in the header section.

Display the Form's property sheet, select the OnCurrent
property and click on the [...] button at the right side of
the property. When the code window appears, add a line of
code at the cursor:

Me.txtCurKey = Me.nameofPKfield

Now, go back to the form design screen and select the
lastname text box. Then use the Format - Conditional
Formatting menu item to open the CF window. Select the
Expression Is option in the drop down box and enter the
expression:
[nameofPKfield] = [txtCurKey]
and select your special fore and back ground colors.

Test it to make sure the last name text box works the way
you want it. When it does, repeat the CF settings for the
other text boxes.


Ted wrote:
i have a form (that uses a query as its record source) in datasheet view. i
would like to be able to change the colors (fore and/or background) of the
controls on it subject to the condition the record pointer is on a particular
record. if you make a change to a controls color properties, when the data is
viewed in this form, it applies to everything the user sees not just the
record the pointer's on. i don't know if it makes a difference, but records
on the form are non-editable, non-deletable and records may not be added
using this particular datasheet view.


:
The only way I know of that can do this in datasheet view is
Conditional Formatting (Format menu).

To highlight the current record, use the form's Current
event to set the record's PK field to a text box in the
form's header section. Then use this text box in the
condition for each bound control in the detail section.

 

[Guest]

.....just as an afterthought: what's the theory behind this? why do the
txtCurKey textboxes 'go' in the header part of the form (and not, e.g. the
details)?

that was somewhat easier to follow (relative newbie over here) but here's a
snag thhat i found; although the field txtCurkey is recognized by VBA when i
add it to the VBA code i'm writing in On Current event section, the name (i
guess that) my PK's called "PrimaryKey" (to judge from its appearance in the
Index name column of the table's) is not and VBA chokes on it. what to do?

The field name is the name of the **field** in the table's
design view, not the name of an index. You may or may not
have a field named PrimaryKey and you may or may not have an
index named PrimaryKey, but they are different things.

Are you sure you need so many fields in the primary key
index? Wouldn't the irb_number field be sufficient? At
least you should be aware that the lastname, firstname and
mi fields will **NOT** help identify a unique record, any
number of people can have the same name. So, at least you
should remove those from the PK index's field list. Then
rethink your idea of primary key to make sure you aren't
going to run into a wall somewhere down the road.

does your explanation only hold for the case when there's
just one field that's been designated a PK; in my case there
are 5 fields!!??

What we're looking for here is the field or fields that
uniquely identify a record. We need this so that the
conditional formatting will only be applied to one record.
If there are multiple fields required to uniquely identify a
record, then you will need a text box in the header section
for each field and a line of code to assign each value to
the text boxes.

Me.txtCurKey1 = Me.nameofPKfield1
Me.txtCurKey2 = Me.nameofPKfield2
. . .

The CF expression will then be more like this:

[nameofPKfield1] = [txtCurKey1] And [nameofPKfield2] =
[txtCurKey2] And . . .
--
Marsh
MVP [MS Access]

Ted wrote:
my pk consists of lastname, firstname, mi, medrecord, irb_number. can you
flesh out a little more what you're suggestion would look like. i don't know
if this really matters all that much, but my datasheet form is a sub-form of
a main form. the main one's called "DataSheet View of Screen" and the
subFrm's called "Screening Log DS View". the form header of the subForm's
holds nothing but labels with the controls' names.

Look at the subform's Form object in design view. Make sure
the Form Header section exists (View menu). Create a text
box named txtCurKey in the header section.

Display the Form's property sheet, select the OnCurrent
property and click on the [...] button at the right side of
the property. When the code window appears, add a line of
code at the cursor:

Me.txtCurKey = Me.nameofPKfield

Now, go back to the form design screen and select the
lastname text box. Then use the Format - Conditional
Formatting menu item to open the CF window. Select the
Expression Is option in the drop down box and enter the
expression:
[nameofPKfield] = [txtCurKey]
and select your special fore and back ground colors.

Test it to make sure the last name text box works the way
you want it. When it does, repeat the CF settings for the
other text boxes.


Ted wrote:
i have a form (that uses a query as its record source) in datasheet view. i
would like to be able to change the colors (fore and/or background) of the
controls on it subject to the condition the record pointer is on a particular
record. if you make a change to a controls color properties, when the data is
viewed in this form, it applies to everything the user sees not just the
record the pointer's on. i don't know if it makes a difference, but records
on the form are non-editable, non-deletable and records may not be added
using this particular datasheet view.


:
The only way I know of that can do this in datasheet view is
Conditional Formatting (Format menu).

To highlight the current record, use the form's Current
event to set the record's PK field to a text box in the
form's header section. Then use this text box in the
condition for each bound control in the detail section.

 

[Marshall Barton]

....just as an afterthought: what's the theory behind this? why do the
txtCurKey textboxes 'go' in the header part of the form (and not, e.g. the
details)?

It doesn't really matter all that much, but the detail
section is where you're displaying the real data and it gets
processed multiple times. So, I think it's better to park
these utility controls in an out of the way place,
especially in datasheet view where the header/footer are not
displayed anyway.
--
Marsh
MVP [MS Access]

The field name is the name of the **field** in the table's
design view, not the name of an index. You may or may not
have a field named PrimaryKey and you may or may not have an
index named PrimaryKey, but they are different things.

Are you sure you need so many fields in the primary key
index? Wouldn't the irb_number field be sufficient? At
least you should be aware that the lastname, firstname and
mi fields will **NOT** help identify a unique record, any
number of people can have the same name. So, at least you
should remove those from the PK index's field list. Then
rethink your idea of primary key to make sure you aren't
going to run into a wall somewhere down the road.

What we're looking for here is the field or fields that
uniquely identify a record. We need this so that the
conditional formatting will only be applied to one record.
If there are multiple fields required to uniquely identify a
record, then you will need a text box in the header section
for each field and a line of code to assign each value to
the text boxes.

Me.txtCurKey1 = Me.nameofPKfield1
Me.txtCurKey2 = Me.nameofPKfield2
. . .

The CF expression will then be more like this:

[nameofPKfield1] = [txtCurKey1] And [nameofPKfield2] =
[txtCurKey2] And . . .

Ted wrote:
my pk consists of lastname, firstname, mi, medrecord, irb_number. can you
flesh out a little more what you're suggestion would look like. i don't know
if this really matters all that much, but my datasheet form is a sub-form of
a main form. the main one's called "DataSheet View of Screen" and the
subFrm's called "Screening Log DS View". the form header of the subForm's
holds nothing but labels with the controls' names.


:
Look at the subform's Form object in design view. Make sure
the Form Header section exists (View menu). Create a text
box named txtCurKey in the header section.

Display the Form's property sheet, select the OnCurrent
property and click on the [...] button at the right side of
the property. When the code window appears, add a line of
code at the cursor:

Me.txtCurKey = Me.nameofPKfield

Now, go back to the form design screen and select the
lastname text box. Then use the Format - Conditional
Formatting menu item to open the CF window. Select the
Expression Is option in the drop down box and enter the
expression:
[nameofPKfield] = [txtCurKey]
and select your special fore and back ground colors.

Test it to make sure the last name text box works the way
you want it. When it does, repeat the CF settings for the
other text boxes.


Ted wrote:
i have a form (that uses a query as its record source) in datasheet view. i
would like to be able to change the colors (fore and/or background) of the
controls on it subject to the condition the record pointer is on a particular
record. if you make a change to a controls color properties, when the data is
viewed in this form, it applies to everything the user sees not just the
record the pointer's on. i don't know if it makes a difference, but records
on the form are non-editable, non-deletable and records may not be added
using this particular datasheet view.


:
The only way I know of that can do this in datasheet view is
Conditional Formatting (Format menu).

To highlight the current record, use the form's Current
event to set the record's PK field to a text box in the
form's header section. Then use this text box in the
condition for each bound control in the detail section.

 

[Guest]

i wanted to say thanks again for all the bandwidth. it's been really really
helpful. now i have more than one approach to implement in this datasheet.
now that i know that there's nothing 'mysterious' about plonking them in the
header, it kind of makes me feel better about a2k as well (in terms of the
way it hangs together).

ted

....just as an afterthought: what's the theory behind this? why do the
txtCurKey textboxes 'go' in the header part of the form (and not, e.g. the
details)?

It doesn't really matter all that much, but the detail
section is where you're displaying the real data and it gets
processed multiple times. So, I think it's better to park
these utility controls in an out of the way place,
especially in datasheet view where the header/footer are not
displayed anyway.
--
Marsh
MVP [MS Access]

Ted wrote:
that was somewhat easier to follow (relative newbie over here) but here's a
snag thhat i found; although the field txtCurkey is recognized by VBA when i
add it to the VBA code i'm writing in On Current event section, the name (i
guess that) my PK's called "PrimaryKey" (to judge from its appearance in the
Index name column of the table's) is not and VBA chokes on it. what to do?

The field name is the name of the **field** in the table's
design view, not the name of an index. You may or may not
have a field named PrimaryKey and you may or may not have an
index named PrimaryKey, but they are different things.

Are you sure you need so many fields in the primary key
index? Wouldn't the irb_number field be sufficient? At
least you should be aware that the lastname, firstname and
mi fields will **NOT** help identify a unique record, any
number of people can have the same name. So, at least you
should remove those from the PK index's field list. Then
rethink your idea of primary key to make sure you aren't
going to run into a wall somewhere down the road.


does your explanation only hold for the case when there's
just one field that's been designated a PK; in my case there
are 5 fields!!??

What we're looking for here is the field or fields that
uniquely identify a record. We need this so that the
conditional formatting will only be applied to one record.
If there are multiple fields required to uniquely identify a
record, then you will need a text box in the header section
for each field and a line of code to assign each value to
the text boxes.

Me.txtCurKey1 = Me.nameofPKfield1
Me.txtCurKey2 = Me.nameofPKfield2
. . .

The CF expression will then be more like this:

[nameofPKfield1] = [txtCurKey1] And [nameofPKfield2] =
[txtCurKey2] And . . .


Ted wrote:
my pk consists of lastname, firstname, mi, medrecord, irb_number. can you
flesh out a little more what you're suggestion would look like. i don't know
if this really matters all that much, but my datasheet form is a sub-form of
a main form. the main one's called "DataSheet View of Screen" and the
subFrm's called "Screening Log DS View". the form header of the subForm's
holds nothing but labels with the controls' names.


:
Look at the subform's Form object in design view. Make sure
the Form Header section exists (View menu). Create a text
box named txtCurKey in the header section.

Display the Form's property sheet, select the OnCurrent
property and click on the [...] button at the right side of
the property. When the code window appears, add a line of
code at the cursor:

Me.txtCurKey = Me.nameofPKfield

Now, go back to the form design screen and select the
lastname text box. Then use the Format - Conditional
Formatting menu item to open the CF window. Select the
Expression Is option in the drop down box and enter the
expression:
[nameofPKfield] = [txtCurKey]
and select your special fore and back ground colors.

Test it to make sure the last name text box works the way
you want it. When it does, repeat the CF settings for the
other text boxes.


Ted wrote:
i have a form (that uses a query as its record source) in datasheet view. i
would like to be able to change the colors (fore and/or background) of the
controls on it subject to the condition the record pointer is on a particular
record. if you make a change to a controls color properties, when the data is
viewed in this form, it applies to everything the user sees not just the
record the pointer's on. i don't know if it makes a difference, but records
on the form are non-editable, non-deletable and records may not be added
using this particular datasheet view.


:
The only way I know of that can do this in datasheet view is
Conditional Formatting (Format menu).

To highlight the current record, use the form's Current
event to set the record's PK field to a text box in the
form's header section. Then use this text box in the
condition for each bound control in the detail section.

 

[Marshall Barton]

Glad to hear that you're making progress on your application
and expanding your knowledge along the way. A goal we all
strive for.
--
Marsh
MVP [MS Access]

i wanted to say thanks again for all the bandwidth. it's been really really
helpful. now i have more than one approach to implement in this datasheet.
now that i know that there's nothing 'mysterious' about plonking them in the
header, it kind of makes me feel better about a2k as well (in terms of the
way it hangs together).

It doesn't really matter all that much, but the detail
section is where you're displaying the real data and it gets
processed multiple times. So, I think it's better to park
these utility controls in an out of the way place,
especially in datasheet view where the header/footer are not
displayed anyway.

Ted wrote:
that was somewhat easier to follow (relative newbie over here) but here's a
snag thhat i found; although the field txtCurkey is recognized by VBA when i
add it to the VBA code i'm writing in On Current event section, the name (i
guess that) my PK's called "PrimaryKey" (to judge from its appearance in the
Index name column of the table's) is not and VBA chokes on it. what to do?

The field name is the name of the **field** in the table's
design view, not the name of an index. You may or may not
have a field named PrimaryKey and you may or may not have an
index named PrimaryKey, but they are different things.

Are you sure you need so many fields in the primary key
index? Wouldn't the irb_number field be sufficient? At
least you should be aware that the lastname, firstname and
mi fields will **NOT** help identify a unique record, any
number of people can have the same name. So, at least you
should remove those from the PK index's field list. Then
rethink your idea of primary key to make sure you aren't
going to run into a wall somewhere down the road.


does your explanation only hold for the case when there's
just one field that's been designated a PK; in my case there
are 5 fields!!??

:
What we're looking for here is the field or fields that
uniquely identify a record. We need this so that the
conditional formatting will only be applied to one record.
If there are multiple fields required to uniquely identify a
record, then you will need a text box in the header section
for each field and a line of code to assign each value to
the text boxes.

Me.txtCurKey1 = Me.nameofPKfield1
Me.txtCurKey2 = Me.nameofPKfield2
. . .

The CF expression will then be more like this:

[nameofPKfield1] = [txtCurKey1] And [nameofPKfield2] =
[txtCurKey2] And . . .


Ted wrote:
my pk consists of lastname, firstname, mi, medrecord, irb_number. can you
flesh out a little more what you're suggestion would look like. i don't know
if this really matters all that much, but my datasheet form is a sub-form of
a main form. the main one's called "DataSheet View of Screen" and the
subFrm's called "Screening Log DS View". the form header of the subForm's
holds nothing but labels with the controls' names.


:
Look at the subform's Form object in design view. Make sure
the Form Header section exists (View menu). Create a text
box named txtCurKey in the header section.

Display the Form's property sheet, select the OnCurrent
property and click on the [...] button at the right side of
the property. When the code window appears, add a line of
code at the cursor:

Me.txtCurKey = Me.nameofPKfield

Now, go back to the form design screen and select the
lastname text box. Then use the Format - Conditional
Formatting menu item to open the CF window. Select the
Expression Is option in the drop down box and enter the
expression:
[nameofPKfield] = [txtCurKey]
and select your special fore and back ground colors.

Test it to make sure the last name text box works the way
you want it. When it does, repeat the CF settings for the
other text boxes.


Ted wrote:
i have a form (that uses a query as its record source) in datasheet view. i
would like to be able to change the colors (fore and/or background) of the
controls on it subject to the condition the record pointer is on a particular
record. if you make a change to a controls color properties, when the data is
viewed in this form, it applies to everything the user sees not just the
record the pointer's on. i don't know if it makes a difference, but records
on the form are non-editable, non-deletable and records may not be added
using this particular datasheet view.


:
The only way I know of that can do this in datasheet view is
Conditional Formatting (Format menu).

To highlight the current record, use the form's Current
event to set the record's PK field to a text box in the
form's header section. Then use this text box in the
condition for each bound control in the detail section.

 

[Guest]

hi marshall,

just fyi, i have a similar goal which bears on a continuous sub-form and the
ability to enable/disable a command button appearing on it that i posted
today with the heading: changing cntrl props in continuous view for 'active'
record in A2K

ted

Glad to hear that you're making progress on your application
and expanding your knowledge along the way. A goal we all
strive for.
--
Marsh
MVP [MS Access]

i wanted to say thanks again for all the bandwidth. it's been really really
helpful. now i have more than one approach to implement in this datasheet.
now that i know that there's nothing 'mysterious' about plonking them in the
header, it kind of makes me feel better about a2k as well (in terms of the
way it hangs together).

Ted wrote:
....just as an afterthought: what's the theory behind this? why do the
txtCurKey textboxes 'go' in the header part of the form (and not, e.g. the
details)?

It doesn't really matter all that much, but the detail
section is where you're displaying the real data and it gets
processed multiple times. So, I think it's better to park
these utility controls in an out of the way place,
especially in datasheet view where the header/footer are not
displayed anyway.


Ted wrote:
that was somewhat easier to follow (relative newbie over here) but here's a
snag thhat i found; although the field txtCurkey is recognized by VBA when i
add it to the VBA code i'm writing in On Current event section, the name (i
guess that) my PK's called "PrimaryKey" (to judge from its appearance in the
Index name column of the table's) is not and VBA chokes on it. what to do?

The field name is the name of the **field** in the table's
design view, not the name of an index. You may or may not
have a field named PrimaryKey and you may or may not have an
index named PrimaryKey, but they are different things.

Are you sure you need so many fields in the primary key
index? Wouldn't the irb_number field be sufficient? At
least you should be aware that the lastname, firstname and
mi fields will **NOT** help identify a unique record, any
number of people can have the same name. So, at least you
should remove those from the PK index's field list. Then
rethink your idea of primary key to make sure you aren't
going to run into a wall somewhere down the road.


does your explanation only hold for the case when there's
just one field that's been designated a PK; in my case there
are 5 fields!!??

:
What we're looking for here is the field or fields that
uniquely identify a record. We need this so that the
conditional formatting will only be applied to one record.
If there are multiple fields required to uniquely identify a
record, then you will need a text box in the header section
for each field and a line of code to assign each value to
the text boxes.

Me.txtCurKey1 = Me.nameofPKfield1
Me.txtCurKey2 = Me.nameofPKfield2
. . .

The CF expression will then be more like this:

[nameofPKfield1] = [txtCurKey1] And [nameofPKfield2] =
[txtCurKey2] And . . .


Ted wrote:
my pk consists of lastname, firstname, mi, medrecord, irb_number. can you
flesh out a little more what you're suggestion would look like. i don't know
if this really matters all that much, but my datasheet form is a sub-form of
a main form. the main one's called "DataSheet View of Screen" and the
subFrm's called "Screening Log DS View". the form header of the subForm's
holds nothing but labels with the controls' names.


:
Look at the subform's Form object in design view. Make sure
the Form Header section exists (View menu). Create a text
box named txtCurKey in the header section.

Display the Form's property sheet, select the OnCurrent
property and click on the [...] button at the right side of
the property. When the code window appears, add a line of
code at the cursor:

Me.txtCurKey = Me.nameofPKfield

Now, go back to the form design screen and select the
lastname text box. Then use the Format - Conditional
Formatting menu item to open the CF window. Select the
Expression Is option in the drop down box and enter the
expression:
[nameofPKfield] = [txtCurKey]
and select your special fore and back ground colors.

Test it to make sure the last name text box works the way
you want it. When it does, repeat the CF settings for the
other text boxes.


Ted wrote:
i have a form (that uses a query as its record source) in datasheet view. i
would like to be able to change the colors (fore and/or background) of the
controls on it subject to the condition the record pointer is on a particular
record. if you make a change to a controls color properties, when the data is
viewed in this form, it applies to everything the user sees not just the
record the pointer's on. i don't know if it makes a difference, but records
on the form are non-editable, non-deletable and records may not be added
using this particular datasheet view.


:
The only way I know of that can do this in datasheet view is
Conditional Formatting (Format menu).

To highlight the current record, use the form's Current
event to set the record's PK field to a text box in the
form's header section. Then use this text box in the
condition for each bound control in the detail section.

 

[Guest]

hey marsh,

it's been a month of sundays since we confabulated on this thread and i'm
finding an opp'y to utilize the approach in another database.

the particulars are almost the same, there are 6 components to the PK so i
created 6 txtCurKey# type controlsl that are in the header.

here's the VBA

Private Sub Form_Current()
Me.txtCurKey1 = Me.Last_Name
Me.txtCurKey2 = Me.First_Name
Me.txtCurKey3 = Me.MI
Me.txtCurKey4 = Me.MR_Number
Me.txtCurKey5 = Me.IRB_Number
Me.txtCurKey6 = Me.RecordNumber
End Sub

there's another piece i'm adding immediately below because i suspect it
might have to do with the reason for this posting (which i'll get to in a
minute), i.e.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then
Me.Updated_by = LAS_GetUserName()
Me.Last_edited = Now()
End If
End Sub

i have done the CF thing on the pair of fields used in the above (Updated_by
and Last_edited) and all's well and good until i attempt to change the
active/current record and the cursor enters either of the pair of above said
fields. what happens next is that when you attempt to move the cursor
away/out from the active/current record to another, the field goes blank and
you get a message telling me that "Field 'DaysView.Updated_by' cannot be a
zero length string".

my gut tells me that if these were in otherways not dependent upon the VBA
code that auto-completes them i wouldn't be getting into this, so what's the
matter w/ all of this? is there some workaround? i'd love for the user to be
able to see these controls change color per the same CF rule i'm applying to
the bulk of the datasheet's fields, but if i have to bite the old bullet,
then so be it!

all the best,

-ted

Glad to hear that you're making progress on your application
and expanding your knowledge along the way. A goal we all
strive for.
--
Marsh
MVP [MS Access]

i wanted to say thanks again for all the bandwidth. it's been really really
helpful. now i have more than one approach to implement in this datasheet.
now that i know that there's nothing 'mysterious' about plonking them in the
header, it kind of makes me feel better about a2k as well (in terms of the
way it hangs together).

Ted wrote:
....just as an afterthought: what's the theory behind this? why do the
txtCurKey textboxes 'go' in the header part of the form (and not, e.g. the
details)?

It doesn't really matter all that much, but the detail
section is where you're displaying the real data and it gets
processed multiple times. So, I think it's better to park
these utility controls in an out of the way place,
especially in datasheet view where the header/footer are not
displayed anyway.


Ted wrote:
that was somewhat easier to follow (relative newbie over here) but here's a
snag thhat i found; although the field txtCurkey is recognized by VBA when i
add it to the VBA code i'm writing in On Current event section, the name (i
guess that) my PK's called "PrimaryKey" (to judge from its appearance in the
Index name column of the table's) is not and VBA chokes on it. what to do?

The field name is the name of the **field** in the table's
design view, not the name of an index. You may or may not
have a field named PrimaryKey and you may or may not have an
index named PrimaryKey, but they are different things.

Are you sure you need so many fields in the primary key
index? Wouldn't the irb_number field be sufficient? At
least you should be aware that the lastname, firstname and
mi fields will **NOT** help identify a unique record, any
number of people can have the same name. So, at least you
should remove those from the PK index's field list. Then
rethink your idea of primary key to make sure you aren't
going to run into a wall somewhere down the road.


does your explanation only hold for the case when there's
just one field that's been designated a PK; in my case there
are 5 fields!!??

:
What we're looking for here is the field or fields that
uniquely identify a record. We need this so that the
conditional formatting will only be applied to one record.
If there are multiple fields required to uniquely identify a
record, then you will need a text box in the header section
for each field and a line of code to assign each value to
the text boxes.

Me.txtCurKey1 = Me.nameofPKfield1
Me.txtCurKey2 = Me.nameofPKfield2
. . .

The CF expression will then be more like this:

[nameofPKfield1] = [txtCurKey1] And [nameofPKfield2] =
[txtCurKey2] And . . .


Ted wrote:
my pk consists of lastname, firstname, mi, medrecord, irb_number. can you
flesh out a little more what you're suggestion would look like. i don't know
if this really matters all that much, but my datasheet form is a sub-form of
a main form. the main one's called "DataSheet View of Screen" and the
subFrm's called "Screening Log DS View". the form header of the subForm's
holds nothing but labels with the controls' names.


:
Look at the subform's Form object in design view. Make sure
the Form Header section exists (View menu). Create a text
box named txtCurKey in the header section.

Display the Form's property sheet, select the OnCurrent
property and click on the [...] button at the right side of
the property. When the code window appears, add a line of
code at the cursor:

Me.txtCurKey = Me.nameofPKfield

Now, go back to the form design screen and select the
lastname text box. Then use the Format - Conditional
Formatting menu item to open the CF window. Select the
Expression Is option in the drop down box and enter the
expression:
[nameofPKfield] = [txtCurKey]
and select your special fore and back ground colors.

Test it to make sure the last name text box works the way
you want it. When it does, repeat the CF settings for the
other text boxes.


Ted wrote:
i have a form (that uses a query as its record source) in datasheet view. i
would like to be able to change the colors (fore and/or background) of the
controls on it subject to the condition the record pointer is on a particular
record. if you make a change to a controls color properties, when the data is
viewed in this form, it applies to everything the user sees not just the
record the pointer's on. i don't know if it makes a difference, but records
on the form are non-editable, non-deletable and records may not be added
using this particular datasheet view.


:
The only way I know of that can do this in datasheet view is
Conditional Formatting (Format menu).

To highlight the current record, use the form's Current
event to set the record's PK field to a text box in the
form's header section. Then use this text box in the
condition for each bound control in the detail section.