REPOST: Highlight New Record

  • Thread starter Thread starter Kel
  • Start date Start date
K

Kel

Hi,

I have reposted this query (originally on the modulesdaovbs newsgroup) as I
still can't find a suitable solution -
despite some helpful responses. I would really appreciate any suggestions:

I am currently developing database in which I have used the method found on
Lebans.com for highlighting the background of
the current record in a continuous form. I am using the solution which uses
conditional formatting on a dummy textbox
which covers the background of the section (using the
ClsConditionalFormatting class - if anyone is familiar with the code). This
works well, however the one thing it does not do is to highlight the record
if it is a new record - this is pretty important to the functionality (ie
there's not much point in me highlighting the current record if it can't
highlight a new one!) and I have
spent a while trying to figure out a way round it.

Currently the code I am using checks if this is a New Record or if this
control's value is Null and returns False if either conditions are true -
this then stops the conditional formatting for the current record - so I was
recomended to comment out this If/End if block and this would allow
conditional formatting for the new record.

However this only works if the new record is the first record in the
continuous view and I have discovered this is for the following reason (if
you aren't familiar with the code from Lebans.com then this may not make any
sense to you!):

When you select a new record, the absoluteposition is returned as 0,
regardless of which position the new record is in, so therefore if the new
record is the fourth record to be created, then the piece of code....

If rs.AbsolutePosition + 1 = frm.CurrentRecord Then
fCurrentRow = True
Else
fCurrentRow = False
End If

....which decides whether the row should be formatted or not will return
false because frm.currentrecord = 4, but the absoluteposition is still 0.

Using the method suggested (removing the If/End If block) also causes a
problem when it is the first record you select. AbsolutePosition for both
the first record and the 'placeholder' for a new record is returned as 0,
therefore if you select the first record, both the first record and the new
record placeholder both evaluate to TRUE in the above code and are therefore
both highlighted.

The nearest I have got is to create a variable artificialAbsPosn as long,
force a value onto it if it is a new record and use that for comparison:
e.g.
If IsNull(ctl.Value) Then
rs.MoveLast
artificialAbsPosn = rs.AbsolutePosition + 1 'assume next record
after last record is new record
Else
artificialAbsPosn = rs.AbsolutePosition
End If
' See if we have a match
If artificialAbsPosn + 1 = frm.CurrentRecord Then
fCurrentRow = True
Else
fCurrentRow = False
End If

This works fine until you start to enter data into the new record, at which
point the next new record 'placeholder' is created and this is highlighted
instead of the current new record.

I am also unsure about assuming that the new record will have a null value
(as you can't use frm.newrecord to evaluate as this would evaluate true for
all records looped through) and I am also unsure about assuming the next
record after last record is new record.

Does anyone have any ideas? - I would be indebted if you do ;)

Kel
 
Since you've been having so much trouble with this issue, I
thought I would have a play with it. I found it to be every
bit as tough a problem as you said it is.

I might have gotten a little closer with this code:

Public Function IsCurrentRecord(Key)
With Me.RecordsetClone
.FindFirst "ID = " & Key
If .NoMatch Then ' new record
IsCurrentRecord = (Me.CurrentRecord > .RecordCount)
Else
IsCurrentRecord = (.AbsolutePosition + 1 =
Me.CurrentRecord)
End If
End With
End Function

but it still won't highlight a new record until you actually
type a character into one of its controls.

I tried doing it the old fashined way with variations of:
http://www.mvps.org/access/forms/frm0024.htm
along with:
http://www.mvps.org/access/forms/frm0055.htm
and had similar problems until I used each controls Change
event (ugh) to set the header text box to the current
record's key.

I usually try to avoid these techniques and just settle for
highlighting the control that has the focus instead of the
entire row. This is easy to do by placing a rectangle
control with transparent border exactly behind each text
box. Set the rectangles' fill color to the color you want
each text box to have when it does not have the focus. Now,
set the text boxes' BackColor to your highlight color and,
after that, set the text boxes' BackStyle to Transparent. A
transparent text box will show its back color only when it
has the focus.
 
Thanks so much for taking a look! I'll have a good look at your code and
also consider what you said about just highlighting the control that has the
focus - I really appreciate you looking at this for me.

However - I am about to go on holiday and forget about it all for a whole
week :) ). I will consider all when I get back!!

Thanks again

Kel


Marshall Barton said:
Since you've been having so much trouble with this issue, I
thought I would have a play with it. I found it to be every
bit as tough a problem as you said it is.

I might have gotten a little closer with this code:

Public Function IsCurrentRecord(Key)
With Me.RecordsetClone
.FindFirst "ID = " & Key
If .NoMatch Then ' new record
IsCurrentRecord = (Me.CurrentRecord > .RecordCount)
Else
IsCurrentRecord = (.AbsolutePosition + 1 =
Me.CurrentRecord)
End If
End With
End Function

but it still won't highlight a new record until you actually
type a character into one of its controls.

I tried doing it the old fashined way with variations of:
http://www.mvps.org/access/forms/frm0024.htm
along with:
http://www.mvps.org/access/forms/frm0055.htm
and had similar problems until I used each controls Change
event (ugh) to set the header text box to the current
record's key.

I usually try to avoid these techniques and just settle for
highlighting the control that has the focus instead of the
entire row. This is easy to do by placing a rectangle
control with transparent border exactly behind each text
box. Set the rectangles' fill color to the color you want
each text box to have when it does not have the focus. Now,
set the text boxes' BackColor to your highlight color and,
after that, set the text boxes' BackStyle to Transparent. A
transparent text box will show its back color only when it
has the focus.
--
Marsh
MVP [MS Access]



I have reposted this query (originally on the modulesdaovbs newsgroup) as I
still can't find a suitable solution -
despite some helpful responses. I would really appreciate any suggestions:

I am currently developing database in which I have used the method found on
Lebans.com for highlighting the background of
the current record in a continuous form. I am using the solution which uses
conditional formatting on a dummy textbox
which covers the background of the section (using the
ClsConditionalFormatting class - if anyone is familiar with the code). This
works well, however the one thing it does not do is to highlight the record
if it is a new record - this is pretty important to the functionality (ie
there's not much point in me highlighting the current record if it can't
highlight a new one!) and I have
spent a while trying to figure out a way round it.

Currently the code I am using checks if this is a New Record or if this
control's value is Null and returns False if either conditions are true -
this then stops the conditional formatting for the current record - so I was
recomended to comment out this If/End if block and this would allow
conditional formatting for the new record.

However this only works if the new record is the first record in the
continuous view and I have discovered this is for the following reason (if
you aren't familiar with the code from Lebans.com then this may not make any
sense to you!):

When you select a new record, the absoluteposition is returned as 0,
regardless of which position the new record is in, so therefore if the new
record is the fourth record to be created, then the piece of code....

If rs.AbsolutePosition + 1 = frm.CurrentRecord Then
fCurrentRow = True
Else
fCurrentRow = False
End If

...which decides whether the row should be formatted or not will return
false because frm.currentrecord = 4, but the absoluteposition is still 0.

Using the method suggested (removing the If/End If block) also causes a
problem when it is the first record you select. AbsolutePosition for both
the first record and the 'placeholder' for a new record is returned as 0,
therefore if you select the first record, both the first record and the new
record placeholder both evaluate to TRUE in the above code and are therefore
both highlighted.

The nearest I have got is to create a variable artificialAbsPosn as long,
force a value onto it if it is a new record and use that for comparison:
e.g.
If IsNull(ctl.Value) Then
rs.MoveLast
artificialAbsPosn = rs.AbsolutePosition + 1 'assume next record
after last record is new record
Else
artificialAbsPosn = rs.AbsolutePosition
End If
' See if we have a match
If artificialAbsPosn + 1 = frm.CurrentRecord Then
fCurrentRow = True
Else
fCurrentRow = False
End If

This works fine until you start to enter data into the new record, at which
point the next new record 'placeholder' is created and this is highlighted
instead of the current new record.

I am also unsure about assuming that the new record will have a null value
(as you can't use frm.newrecord to evaluate as this would evaluate true for
all records looped through) and I am also unsure about assuming the next
record after last record is new record.

Does anyone have any ideas? - I would be indebted if you do ;)
Click to expand...
Click to expand...
 
Marsh....You are an absolute STAR!!!!! I've just tried your code and it
seems to do exactly what I want....

Thanks Loads!!!

Kel


Marshall Barton said:
Since you've been having so much trouble with this issue, I
thought I would have a play with it. I found it to be every
bit as tough a problem as you said it is.

I might have gotten a little closer with this code:

Public Function IsCurrentRecord(Key)
With Me.RecordsetClone
.FindFirst "ID = " & Key
If .NoMatch Then ' new record
IsCurrentRecord = (Me.CurrentRecord > .RecordCount)
Else
IsCurrentRecord = (.AbsolutePosition + 1 =
Me.CurrentRecord)
End If
End With
End Function

but it still won't highlight a new record until you actually
type a character into one of its controls.

I tried doing it the old fashined way with variations of:
http://www.mvps.org/access/forms/frm0024.htm
along with:
http://www.mvps.org/access/forms/frm0055.htm
and had similar problems until I used each controls Change
event (ugh) to set the header text box to the current
record's key.

I usually try to avoid these techniques and just settle for
highlighting the control that has the focus instead of the
entire row. This is easy to do by placing a rectangle
control with transparent border exactly behind each text
box. Set the rectangles' fill color to the color you want
each text box to have when it does not have the focus. Now,
set the text boxes' BackColor to your highlight color and,
after that, set the text boxes' BackStyle to Transparent. A
transparent text box will show its back color only when it
has the focus.
--
Marsh
MVP [MS Access]



I have reposted this query (originally on the modulesdaovbs newsgroup) as I
still can't find a suitable solution -
despite some helpful responses. I would really appreciate any suggestions:

I am currently developing database in which I have used the method found on
Lebans.com for highlighting the background of
the current record in a continuous form. I am using the solution which uses
conditional formatting on a dummy textbox
which covers the background of the section (using the
ClsConditionalFormatting class - if anyone is familiar with the code). This
works well, however the one thing it does not do is to highlight the record
if it is a new record - this is pretty important to the functionality (ie
there's not much point in me highlighting the current record if it can't
highlight a new one!) and I have
spent a while trying to figure out a way round it.

Currently the code I am using checks if this is a New Record or if this
control's value is Null and returns False if either conditions are true -
this then stops the conditional formatting for the current record - so I was
recomended to comment out this If/End if block and this would allow
conditional formatting for the new record.

However this only works if the new record is the first record in the
continuous view and I have discovered this is for the following reason (if
you aren't familiar with the code from Lebans.com then this may not make any
sense to you!):

When you select a new record, the absoluteposition is returned as 0,
regardless of which position the new record is in, so therefore if the new
record is the fourth record to be created, then the piece of code....

If rs.AbsolutePosition + 1 = frm.CurrentRecord Then
fCurrentRow = True
Else
fCurrentRow = False
End If

...which decides whether the row should be formatted or not will return
false because frm.currentrecord = 4, but the absoluteposition is still 0.

Using the method suggested (removing the If/End If block) also causes a
problem when it is the first record you select. AbsolutePosition for both
the first record and the 'placeholder' for a new record is returned as 0,
therefore if you select the first record, both the first record and the new
record placeholder both evaluate to TRUE in the above code and are therefore
both highlighted.

The nearest I have got is to create a variable artificialAbsPosn as long,
force a value onto it if it is a new record and use that for comparison:
e.g.
If IsNull(ctl.Value) Then
rs.MoveLast
artificialAbsPosn = rs.AbsolutePosition + 1 'assume next record
after last record is new record
Else
artificialAbsPosn = rs.AbsolutePosition
End If
' See if we have a match
If artificialAbsPosn + 1 = frm.CurrentRecord Then
fCurrentRow = True
Else
fCurrentRow = False
End If

This works fine until you start to enter data into the new record, at which
point the next new record 'placeholder' is created and this is highlighted
instead of the current new record.

I am also unsure about assuming that the new record will have a null value
(as you can't use frm.newrecord to evaluate as this would evaluate true for
all records looped through) and I am also unsure about assuming the next
record after last record is new record.

Does anyone have any ideas? - I would be indebted if you do ;)
Click to expand...
Click to expand...
 
Actually .... I was a bit premature.... It has the same problem as the code
I wrote in that it highlights the new record, but as soon as you start
entering data and the next new record placeholder appears, both the current
new record and the new record placeholder are highlighted at the same time!!

I still appreciate your help - Thanks alot!!!

Kel

Kel said:
Marsh....You are an absolute STAR!!!!! I've just tried your code and it
seems to do exactly what I want....

Thanks Loads!!!

Kel


Marshall Barton said:
Since you've been having so much trouble with this issue, I
thought I would have a play with it. I found it to be every
bit as tough a problem as you said it is.

I might have gotten a little closer with this code:

Public Function IsCurrentRecord(Key)
With Me.RecordsetClone
.FindFirst "ID = " & Key
If .NoMatch Then ' new record
IsCurrentRecord = (Me.CurrentRecord > .RecordCount)
Else
IsCurrentRecord = (.AbsolutePosition + 1 =
Me.CurrentRecord)
End If
End With
End Function

but it still won't highlight a new record until you actually
type a character into one of its controls.

I tried doing it the old fashined way with variations of:
http://www.mvps.org/access/forms/frm0024.htm
along with:
http://www.mvps.org/access/forms/frm0055.htm
and had similar problems until I used each controls Change
event (ugh) to set the header text box to the current
record's key.

I usually try to avoid these techniques and just settle for
highlighting the control that has the focus instead of the
entire row. This is easy to do by placing a rectangle
control with transparent border exactly behind each text
box. Set the rectangles' fill color to the color you want
each text box to have when it does not have the focus. Now,
set the text boxes' BackColor to your highlight color and,
after that, set the text boxes' BackStyle to Transparent. A
transparent text box will show its back color only when it
has the focus.
--
Marsh
MVP [MS Access]



I have reposted this query (originally on the modulesdaovbs newsgroup)
Click to expand...
Click to expand...
as
I
Click to expand...
found
on
Click to expand...
I
was
Click to expand...
make
any
Click to expand...
Click to expand...
 
Actually - ignore me!! I have now got the code working in the same way that
you obviously did and doesn't make the error that I thought - it was me
incorrectly tweaking!!!

Sorry to slur you good name!!!

Thanks
Kel

Kel said:
Actually .... I was a bit premature.... It has the same problem as the code
I wrote in that it highlights the new record, but as soon as you start
entering data and the next new record placeholder appears, both the current
new record and the new record placeholder are highlighted at the same time!!

I still appreciate your help - Thanks alot!!!

Kel

Kel said:
Marsh....You are an absolute STAR!!!!! I've just tried your code and it
seems to do exactly what I want....

Thanks Loads!!!

Kel


Marshall Barton said:
Since you've been having so much trouble with this issue, I
thought I would have a play with it. I found it to be every
bit as tough a problem as you said it is.

I might have gotten a little closer with this code:

Public Function IsCurrentRecord(Key)
With Me.RecordsetClone
.FindFirst "ID = " & Key
If .NoMatch Then ' new record
IsCurrentRecord = (Me.CurrentRecord > .RecordCount)
Else
IsCurrentRecord = (.AbsolutePosition + 1 =
Me.CurrentRecord)
End If
End With
End Function

but it still won't highlight a new record until you actually
type a character into one of its controls.

I tried doing it the old fashined way with variations of:
http://www.mvps.org/access/forms/frm0024.htm
along with:
http://www.mvps.org/access/forms/frm0055.htm
and had similar problems until I used each controls Change
event (ugh) to set the header text box to the current
record's key.

I usually try to avoid these techniques and just settle for
highlighting the control that has the focus instead of the
entire row. This is easy to do by placing a rectangle
control with transparent border exactly behind each text
box. Set the rectangles' fill color to the color you want
each text box to have when it does not have the focus. Now,
set the text boxes' BackColor to your highlight color and,
after that, set the text boxes' BackStyle to Transparent. A
transparent text box will show its back color only when it
has the focus.
--
Marsh
MVP [MS Access]




Kel wrote:
I have reposted this query (originally on the modulesdaovbs
Click to expand...
Click to expand...
newsgroup)
as found which
uses code).
This
Click to expand...
so
I reason
(if make the
new still
0.
Click to expand...
as
0, the
new true
for
Click to expand...
Click to expand...
 
Gee, I go out of town for five days, and didn't have to post
anything to get the problem (sort of) under control ;-)

Nice to know you made some progress, keep on truck'n Kel.
 
Thanks for all your help - the way your code worked is atcually how I wanted
it to work - I only wanted the new record highlighted when the user starts
to edit the record - so I reiterate - you're a STAR!!!

And thanks so much for taking the time to help me - I think my laptop may
have been chucked out the window by now if it wasn't for you ;)

Kel


Marshall Barton said:
Gee, I go out of town for five days, and didn't have to post
anything to get the problem (sort of) under control ;-)

Nice to know you made some progress, keep on truck'n Kel.
--
Marsh
MVP [MS Access]

Actually - ignore me!! I have now got the code working in the same way that
you obviously did and doesn't make the error that I thought - it was me
incorrectly tweaking!!!

Sorry to slur you good name!!!

Thanks


which record -
so causes
a returned
as and
the
Click to expand...
Click to expand...
 
Back
Top