Refreshing Form After Sorting Records - NOT WORKING

Greetings!

I am getting the following error after hitting a command button that I
have on a form to sort information on the screen:
"The expression you entered has a field, control, or propertyname that
Microsoft Access can't find. Error Number 2424"

Background:
MSAccess2003 -
The form is displaying SCORE records. These records contain scores
earned by dogs entered in Obedience Competitions.

Table: tblScores
Fields:
scoreID (pk)
dogregnbr (fk)
trialclassID (fk)
score
qualified (true/false)
note (some text stuff)
processed (true/false) ... meaning have we counted this score toward
earning a title, yet
processeddt (date)
validscore (true/false) ... meaning has the dog earned the prerequisite
title enabling the dog to actually enter the class that they did.

I just added the field 'validscore' to the record and modified the
display screen (WHICH WAS WORKING JUST FINE BEFORE I ADDED validscore).

I updated the form to have the validscore checkbox NOT VISIBLE and added
a TEXTBOX to have three values (Y,N,or U). The CONTROLSOURCE of the
txtValidScore (in the form definitions) is
=IIf(Me!processed = True, IIf(Me!validscore = True, "Y", "N"), "U")

Y=yes, N=no, U=unknown (we don't know if a score iS valid until the
record is processed. Therefore, if the record is not processed the
value of txtValidScore is "U" for UNKNOWN)

The form is defined to display the records (for a particular dog) in
'continuous form'. So I can scroll down thru the list of scores. Each
detail line contains a combobox containing information like date, city,
state of the competition, the class of the competition (novice, open,
utility). It also contains the score field and three new fields just
added: processed ckbox, validscore ckbox, txtValidScore textbox. The
validscore ckbox is NOT VISIBLE.

When the form opens up the txtValidScore field in each record is
populated just fine.

HOWEVER, when I hit the command button to sort the scores by 'class'
(which was working just fine before I added validscore and
txtValidScore) it fails with the error mentioned above.

Initially, I didn't modify the sortbutton routine and the txtValidScore
field did not get repopulated. It looked like it needed to be
'refreshed'. So I added:

Me!txtValidScore = IIf(Me!processed = True, IIf(Me!validscore = True,
"Y", "N"), "U")

I TRACED THE ROUTINE in the module behind the command button (which I
wrote) and found that the particular field that it is crapping out on is
me!validscore in the statement:

Me!txtValidScore = IIf(Me!processed = True, IIf(Me!validscore = True,
"Y", "N"), "U")


By the way the routine looks like:

Me.RecordSource = blahblahblah (here is where I have the lenghty
SELECT statement to resort the reocrds .. it works)
Me.Requery
Me!cboEventInfo.Requery
Me.Refresh
Me!txtValidScore = IIf(Me!processed = True, IIf(Me!validscore =
True, "Y", "N"), "U")


I HAVE NOT A CLUE! Does anybody else? I really need help. Thanks, so
much.


Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

Re: Refreshing Form After Sorting Records - NOT WORKING

Susan Bricker wrote in
news:4788ec65$0$512$815e3792@news.qwest.net:

> Greetings!
>
> I am getting the following error after hitting a command button
> that I have on a form to sort information on the screen:
> "The expression you entered has a field, control, or propertyname
> that Microsoft Access can't find. Error Number 2424"
>
> Background:
> MSAccess2003 -
> The form is displaying SCORE records. These records contain
> scores earned by dogs entered in Obedience Competitions.
>
> Table: tblScores
> Fields:
> scoreID (pk)
> dogregnbr (fk)
> trialclassID (fk)
> score
> qualified (true/false)
> note (some text stuff)
> processed (true/false) ... meaning have we counted this score
> toward earning a title, yet
> processeddt (date)
> validscore (true/false) ... meaning has the dog earned the
> prerequisite title enabling the dog to actually enter the class
> that they did.
>
> I just added the field 'validscore' to the record and modified the
> display screen (WHICH WAS WORKING JUST FINE BEFORE I ADDED
> validscore).
>
> I updated the form to have the validscore checkbox NOT VISIBLE and
> added a TEXTBOX to have three values (Y,N,or U). The
> CONTROLSOURCE of the txtValidScore (in the form definitions) is
> =IIf(Me!processed = True, IIf(Me!validscore = True, "Y", "N"),
> "U")
>
> Y=yes, N=no, U=unknown (we don't know if a score iS valid until
> the record is processed. Therefore, if the record is not
> processed the value of txtValidScore is "U" for UNKNOWN)
>
> The form is defined to display the records (for a particular dog)
> in 'continuous form'. So I can scroll down thru the list of
> scores. Each detail line contains a combobox containing
> information like date, city, state of the competition, the class
> of the competition (novice, open, utility). It also contains the
> score field and three new fields just added: processed ckbox,
> validscore ckbox, txtValidScore textbox. The validscore ckbox is
> NOT VISIBLE.
>
> When the form opens up the txtValidScore field in each record is
> populated just fine.
>
> HOWEVER, when I hit the command button to sort the scores by
> 'class' (which was working just fine before I added validscore and
> txtValidScore) it fails with the error mentioned above.
>
> Initially, I didn't modify the sortbutton routine and the
> txtValidScore field did not get repopulated. It looked like it
> needed to be 'refreshed'. So I added:
>
> Me!txtValidScore = IIf(Me!processed = True, IIf(Me!validscore =
> True, "Y", "N"), "U")
>
> I TRACED THE ROUTINE in the module behind the command button
> (which I wrote) and found that the particular field that it is
> crapping out on is me!validscore in the statement:
>
> Me!txtValidScore = IIf(Me!processed = True, IIf(Me!validscore =
> True, "Y", "N"), "U")
>
>
> By the way the routine looks like:
>
> Me.RecordSource = blahblahblah (here is where I have the
> lenghty
> SELECT statement to resort the reocrds .. it works)
> Me.Requery
> Me!cboEventInfo.Requery
> Me.Refresh
> Me!txtValidScore = IIf(Me!processed = True, IIf(Me!validscore
> =
> True, "Y", "N"), "U")
>
>
> I HAVE NOT A CLUE! Does anybody else? I really need help.
> Thanks, so much.
>
>
> Regards,
> SueB
>
> *** Sent via Developersdex http://www.developersdex.com ***

Did you remember to add the validscore field to the Me.RecordSource
= blahblahblah statement?

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Re: Refreshing Form After Sorting Records - NOT WORKING

Additional information that I forgot to supply:

[processed] ckbox and [txtValidScore] txtbox on the form are BOTH
defined as enabled=NO, lockd=YES (to prevent the user from modifying
those fields).

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

Re: Refreshing Form After Sorting Records - NOT WORKING

Susan Bricker wrote:

> Greetings!
>
> I am getting the following error after hitting a command button that I
> have on a form to sort information on the screen:
> "The expression you entered has a field, control, or propertyname that
> Microsoft Access can't find. Error Number 2424"
>
> Background:
> MSAccess2003 -
> The form is displaying SCORE records. These records contain scores
> earned by dogs entered in Obedience Competitions.
>
> Table: tblScores
> Fields:
> scoreID (pk)
> dogregnbr (fk)
> trialclassID (fk)
> score
> qualified (true/false)
> note (some text stuff)
> processed (true/false) ... meaning have we counted this score toward
> earning a title, yet
> processeddt (date)
> validscore (true/false) ... meaning has the dog earned the prerequisite
> title enabling the dog to actually enter the class that they did.
>
> I just added the field 'validscore' to the record and modified the
> display screen (WHICH WAS WORKING JUST FINE BEFORE I ADDED validscore).
>
> I updated the form to have the validscore checkbox NOT VISIBLE and added
> a TEXTBOX to have three values (Y,N,or U). The CONTROLSOURCE of the
> txtValidScore (in the form definitions) is
> =IIf(Me!processed = True, IIf(Me!validscore = True, "Y", "N"), "U")

> Y=yes, N=no, U=unknown (we don't know if a score iS valid until the
> record is processed. Therefore, if the record is not processed the
> value of txtValidScore is "U" for UNKNOWN)
>
> The form is defined to display the records (for a particular dog) in
> 'continuous form'. So I can scroll down thru the list of scores. Each
> detail line contains a combobox containing information like date, city,
> state of the competition, the class of the competition (novice, open,
> utility). It also contains the score field and three new fields just
> added: processed ckbox, validscore ckbox, txtValidScore textbox. The
> validscore ckbox is NOT VISIBLE.
>
> When the form opens up the txtValidScore field in each record is
> populated just fine.
>
> HOWEVER, when I hit the command button to sort the scores by 'class'
> (which was working just fine before I added validscore and
> txtValidScore) it fails with the error mentioned above.
>
> Initially, I didn't modify the sortbutton routine and the txtValidScore
> field did not get repopulated. It looked like it needed to be
> 'refreshed'. So I added:
>
> Me!txtValidScore = IIf(Me!processed = True, IIf(Me!validscore = True,
> "Y", "N"), "U")
>
> I TRACED THE ROUTINE in the module behind the command button (which I
> wrote) and found that the particular field that it is crapping out on is
> me!validscore in the statement:
>
> Me!txtValidScore = IIf(Me!processed = True, IIf(Me!validscore = True,
> "Y", "N"), "U")
>
>
> By the way the routine looks like:
>
> Me.RecordSource = blahblahblah (here is where I have the lenghty
> SELECT statement to resort the reocrds .. it works)
> Me.Requery
> Me!cboEventInfo.Requery
> Me.Refresh
> Me!txtValidScore = IIf(Me!processed = True, IIf(Me!validscore =
> True, "Y", "N"), "U")
>
You are attempting to override the control source with this statement.
YOu could remove the control source and do something like
If not me.newrecord then
Me!txtValidScore = IIf(Me!processed = True, IIf(Me!validscore =
True, "Y", "N"), "U")
Else
Me.txtvalidscore = "U"
ENdif
You could make the above a routine and call it in OnCurrent and other
places where you need to refresh it.

Or leave the control source and enter
Me.TxtValidScore.Requery

>
> I HAVE NOT A CLUE! Does anybody else? I really need help. Thanks, so
> much.
>
>
> Regards,
> SueB
>
> *** Sent via Developersdex http://www.developersdex.com ***

You are attempting to override the control source

Re: Refreshing Form After Sorting Records - NOT WORKING

On Jan 12, 8:35=A0am, Susan Bricker wrote:
> Greetings!
>
> I am getting the following error after hitting a command button that I
> have on a form to sort information on the screen:
> "The expression you entered has a field, control, or propertyname that
> Microsoft Access can't find. =A0Error Number 2424"
>
> Background:
> MSAccess2003 -
> The form is displaying SCORE records. =A0These records contain scores
> earned by dogs entered in Obedience Competitions.
>
> Table: tblScores
> Fields:
> scoreID (pk)
> dogregnbr (fk)
> trialclassID (fk)
> score
> qualified (true/false)
> note (some text stuff)
> processed (true/false) ... meaning have we counted this score toward
> earning a title, yet
> processeddt (date)
> validscore (true/false) ... meaning has the dog earned the prerequisite
> title enabling the dog to actually enter the class that they did.
>
> I just added the field 'validscore' to the record and modified the
> display screen (WHICH WAS WORKING JUST FINE BEFORE I ADDED validscore).
>
> I updated the form to have the validscore checkbox NOT VISIBLE and added
> a TEXTBOX to have three values (Y,N,or U). =A0The CONTROLSOURCE of the
> txtValidScore (in the form definitions) is =A0
>  =IIf(Me!processed =3D True, IIf(Me!validscore =3D True, "Y", "N"), "U=
")
>
> Y=3Dyes, N=3Dno, U=3Dunknown (we don't know if a score iS valid until the
> record is processed. =A0Therefore, if the record is not processed the
> value of txtValidScore is "U" for UNKNOWN)
>
> The form is defined to display the records (for a particular dog) in
> 'continuous form'. =A0So I can scroll down thru the list of scores. =A0Eac=
h
> detail line contains a combobox containing information like date, city,
> state of the competition, the class of the competition (novice, open,
> utility). =A0It also contains the score field and three new fields just
> added: processed ckbox, validscore ckbox, txtValidScore textbox. =A0The
> validscore ckbox is NOT VISIBLE.
>
> When the form opens up the txtValidScore field in each record is
> populated just fine.
>
> HOWEVER, when I hit the command button to sort the scores by 'class'
> (which was working just fine before I added validscore and
> txtValidScore) it fails with the error mentioned above.
>
> Initially, I didn't modify the sortbutton routine and the txtValidScore
> field did not get repopulated. =A0It looked like it needed to be
> 'refreshed'. =A0So I added: =A0
>
> Me!txtValidScore =3D IIf(Me!processed =3D True, IIf(Me!validscore =3D True=
,
> "Y", "N"), "U")
>
> I TRACED THE ROUTINE in the module behind the command button (which I
> wrote) and found that the particular field that it is crapping out on is
> me!validscore in the statement:
>
> Me!txtValidScore =3D IIf(Me!processed =3D True, IIf(Me!validscore =3D True=
,
> "Y", "N"), "U")
>
> By the way the routine looks like:
>
> =A0 =A0 Me.RecordSource =3D blahblahblah (here is where I have the lenghty=

> SELECT statement to resort the reocrds .. it works) =A0 =A0
> =A0 =A0 Me.Requery
> =A0 =A0 Me!cboEventInfo.Requery
> =A0 =A0 Me.Refresh
> =A0 =A0 Me!txtValidScore =3D IIf(Me!processed =3D True, IIf(Me!validscore =
=3D
> True, "Y", "N"), "U")
>
> I HAVE NOT A CLUE! =A0Does anybody else? =A0I really need help. =A0Thanks,=
so
> much.
>
> Regards,
> SueB
>
> *** Sent via Developersdexhttp://www.developersdex.com***

Hi Sue,
You might want to consider putting ValidScoreType:IIf(processed =3D
True, IIf(validscore =3D True, "Y", "N"), "U") as a field in the record
source query for the form. Then you can sort by ValidScoreType. As a
note, Me! is something that will work only code. The fact you have it
in your control source may be your issue. In the control source you
need to refer to the control value =3DIIf(txtprocessed =3D True,
IIf(txtvalidscore =3D True, "Y", "N"), "U") In code the ! referres to
the underlying recordset value, in an sql statemtent it thinks !
refers to field a table {tablename!fieldname} - it thinks {Me} is a
table name.
HTH
P

Re: Refreshing Form After Sorting Records - NOT WORKING

Thank you for the helpful hints.

BTW ... I don't havethe me! in the controlsource ... I was just cut and
pasting into the Post text and guess I copied the wrong thing or put it
in the wrong place in the post.

I'll take your suggestions and run with them (along with the others that
I have received). Thanks, so much.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

Re: Refreshing Form After Sorting Records - NOT WORKING

Bob,

Well, I'll bet that is the problem. Because I DID forget to update that
blahblahblah statement. Thanks, so much.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

Re: Refreshing Form After Sorting Records - NOT WORKING

Salad,

Thanks, so much for your suggestions. I'll go make my updates now.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

Re: Refreshing Form After Sorting Records - NOT WORKING

Just to update you all ...

I updated the lengthy SELECT statement for the form RECORDSOURCE to make
sure that it included the new field, validscore. Then after requering
the form (existing statement), instead of resetting the controlsource of
the txtValidScore field, I simply requeried the control.

VOILA ... the SORT BUTTON is working again.

You folks are simply the best.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***