Repost - Help with code

[JohnB]

Hi. I posted this two days ago with no response. Thought Id give it one more
try. Thanks.

Hi. Ive already had help with this code, from Rick Brandt and I now see that
another mod is needed. This code is part of the On Click code for a Schools
forms command button and opens a form called frmMentorsAndSBTs and in it,
shows records where the MentorID is linked to the SchoolID via
tblMentorSBTSchool. Ive now realised that, although frmMentorsAndSBTs is set
to show records alphabetically ascending by Mentor surname, this code
overwrites this and jumbles up the records. Is there a way of changing this
code so that it selects the required records then sorts them prior to
opening the form? The field to sort on is MentorSecondName and is in table
Mentors, where the MentorID is the same as that selected by the code. Can
anyone help please? Thanks, JohnB

DoCmd.OpenForm "frmMentorsAndSBTs", , , "MentorID IN (SELECT MentorID FROM
tblMentorSBTSchool WHERE (tblMentorSBTSchool.SchoolID=" & SchoolID & " And
[MovedToSchoolID] Is Null))"

 

[Graham Mandeno]

Hi John

When you say "frmMentorsAndSBTs is set to show records alphabetically
ascending by Mentor surname", do you mean that the form's recordsource query
has an ORDER BY clause?

If so, then this could be another instance of a problem I've noticed
recently but have not yet had time to research properly. It is that the
form sometimes stops honouring the ORDER BY clause in the recordsource when
a filter is applied.

The workaround is to use the OrderBy property of the form. In your form's
Form_Load event procedure, add this code:
Me.OrderBy = "MentorSecondName"
Me.OrderByOn = True

 

[JohnB]

Thank you Graham.

Yes, the form's recordsource query does have an ORDER BY clause and the
records are sorted correctly when the form is opened normally. Its only when
the form is opened using this command button that the sort is being ignored.

I will try your suggestion tomorrow around 11.30 GMT and will get back to
you.

Cheers, JohnB

Hi John

When you say "frmMentorsAndSBTs is set to show records alphabetically
ascending by Mentor surname", do you mean that the form's recordsource query
has an ORDER BY clause?

If so, then this could be another instance of a problem I've noticed
recently but have not yet had time to research properly. It is that the
form sometimes stops honouring the ORDER BY clause in the recordsource when
a filter is applied.

The workaround is to use the OrderBy property of the form. In your form's
Form_Load event procedure, add this code:
Me.OrderBy = "MentorSecondName"
Me.OrderByOn = True

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi. I posted this two days ago with no response. Thought Id give it one
more
try. Thanks.

Hi. Ive already had help with this code, from Rick Brandt and I now see
that
another mod is needed. This code is part of the On Click code for a
Schools
forms command button and opens a form called frmMentorsAndSBTs and in it,
shows records where the MentorID is linked to the SchoolID via
tblMentorSBTSchool. Ive now realised that, although frmMentorsAndSBTs is
set
to show records alphabetically ascending by Mentor surname, this code
overwrites this and jumbles up the records. Is there a way of changing
this
code so that it selects the required records then sorts them prior to
opening the form? The field to sort on is MentorSecondName and is in table
Mentors, where the MentorID is the same as that selected by the code. Can
anyone help please? Thanks, JohnB

DoCmd.OpenForm "frmMentorsAndSBTs", , , "MentorID IN (SELECT MentorID FROM
tblMentorSBTSchool WHERE (tblMentorSBTSchool.SchoolID=" & SchoolID & " And
[MovedToSchoolID] Is Null))"

 

[Support7556]

Hi John B. (B isn't for Beard in NC, is it?)

Does your current (WhereCondition) work? (in the DoCmd.OpenForm)
I'm dubious of a nested Where clause in an OpenForm's WhereCondition.
If your compound Where clause works, I would still dim a string with your
criteria
Dim stLinkCriteria As String
stLinkCriteria = "WhereCondition"
DoCmd.OpenForm stDocName, , , stLinkCriteria

If you’re mostly bothered by sorting, then create a (sort) query and
reference it in your DoCmd.OpenForm's ,,(Filter) section. Thus
DoCmd.OpenForm stDocName, ,qSortMentorSecondName , stLinkCriteria

*See the documentation for OpenForm "Filter"

You could also use the OpenArg to pass additional criteria, I've had to use
it for additional constraints, in the OnOpen event of the Form had it check
for OpenArg and apply, for example: ~something like~
IF NOT OpenArg is null THEN
OpenArg.OrderBy
ELSE
MentorSecondName.OrderBy
Make sure you sent it from your click event:
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , stOpenArg

I don't know the form's data source but if "MentorSecondName" is part of it
the just set the Forms OrderBy arg to that! You can even

I've even had to pass multiple items in OpenArgs and then parse them with
Left, Mid, Right, substring etc...

You’re going to have to play around with these different techniques until
you get the desired result.

One last idea is to change your Form's Data Source with references

 

[JohnB]

Hi again Graham. Im afraid your suggestion doesnt work. The records still
open up in form frmMentorsAndSBTs all jumbled up. Unless you have any further
thoughts, I ll have a play around with Support7556s suggestion - although I
think I might find that more difficult. Thanks again for your help. JohnB

Hi John

When you say "frmMentorsAndSBTs is set to show records alphabetically
ascending by Mentor surname", do you mean that the form's recordsource query
has an ORDER BY clause?

If so, then this could be another instance of a problem I've noticed
recently but have not yet had time to research properly. It is that the
form sometimes stops honouring the ORDER BY clause in the recordsource when
a filter is applied.

The workaround is to use the OrderBy property of the form. In your form's
Form_Load event procedure, add this code:
Me.OrderBy = "MentorSecondName"
Me.OrderByOn = True

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi. I posted this two days ago with no response. Thought Id give it one
more
try. Thanks.

Hi. Ive already had help with this code, from Rick Brandt and I now see
that
another mod is needed. This code is part of the On Click code for a
Schools
forms command button and opens a form called frmMentorsAndSBTs and in it,
shows records where the MentorID is linked to the SchoolID via
tblMentorSBTSchool. Ive now realised that, although frmMentorsAndSBTs is
set
to show records alphabetically ascending by Mentor surname, this code
overwrites this and jumbles up the records. Is there a way of changing
this
code so that it selects the required records then sorts them prior to
opening the form? The field to sort on is MentorSecondName and is in table
Mentors, where the MentorID is the same as that selected by the code. Can
anyone help please? Thanks, JohnB

DoCmd.OpenForm "frmMentorsAndSBTs", , , "MentorID IN (SELECT MentorID FROM
tblMentorSBTSchool WHERE (tblMentorSBTSchool.SchoolID=" & SchoolID & " And
[MovedToSchoolID] Is Null))"

 

[JohnB]

Hi. I posted a reply to this earlier today but it seems to have vanished. No,
Im not B for Beard, sorry.

Yes, the current Where clause works OK. Its only the sort order thats being
ignored when form frmMentorsAndSBTs is opened using the command button. Thats
why I though it would be best to somehow sort the records at the same time as
selecting them in the code.

Ill have to take some time to look at your suggestion - Im not too good at
this code stuff. If I get stuck Ill perhaps repost with your name in the
header. OK?

Thanks again for the help. JohnB

Hi John B. (B isn't for Beard in NC, is it?)

Does your current (WhereCondition) work? (in the DoCmd.OpenForm)
I'm dubious of a nested Where clause in an OpenForm's WhereCondition.
If your compound Where clause works, I would still dim a string with your
criteria
Dim stLinkCriteria As String
stLinkCriteria = "WhereCondition"
DoCmd.OpenForm stDocName, , , stLinkCriteria

If you’re mostly bothered by sorting, then create a (sort) query and
reference it in your DoCmd.OpenForm's ,,(Filter) section. Thus
DoCmd.OpenForm stDocName, ,qSortMentorSecondName , stLinkCriteria

*See the documentation for OpenForm "Filter"

You could also use the OpenArg to pass additional criteria, I've had to use
it for additional constraints, in the OnOpen event of the Form had it check
for OpenArg and apply, for example: ~something like~
IF NOT OpenArg is null THEN
OpenArg.OrderBy
ELSE
MentorSecondName.OrderBy
Make sure you sent it from your click event:
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , stOpenArg

I don't know the form's data source but if "MentorSecondName" is part of it
the just set the Forms OrderBy arg to that! You can even

I've even had to pass multiple items in OpenArgs and then parse them with
Left, Mid, Right, substring etc...

You’re going to have to play around with these different techniques until
you get the desired result.

One last idea is to change your Form's Data Source with references

Hi. I posted this two days ago with no response. Thought Id give it one more
try. Thanks.

Hi. Ive already had help with this code, from Rick Brandt and I now see that
another mod is needed. This code is part of the On Click code for a Schools
forms command button and opens a form called frmMentorsAndSBTs and in it,
shows records where the MentorID is linked to the SchoolID via
tblMentorSBTSchool. Ive now realised that, although frmMentorsAndSBTs is set
to show records alphabetically ascending by Mentor surname, this code
overwrites this and jumbles up the records. Is there a way of changing this
code so that it selects the required records then sorts them prior to
opening the form? The field to sort on is MentorSecondName and is in table
Mentors, where the MentorID is the same as that selected by the code. Can
anyone help please? Thanks, JohnB

DoCmd.OpenForm "frmMentorsAndSBTs", , , "MentorID IN (SELECT MentorID FROM
tblMentorSBTSchool WHERE (tblMentorSBTSchool.SchoolID=" & SchoolID & " And
[MovedToSchoolID] Is Null))"

 

[Graham Mandeno]

Hi John

Well, that IS strange!

Are you sure that the two Me.OrderBy... lines are being executed?

What version of Access are you using?

Is "MentorSecondName" a field in your recordsource?

What happens if you create a temporary command button and put:
Me.OrderBy = "MentorSecondName"
Me.OrderByOn = True
in its Click event?

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi again Graham. Im afraid your suggestion doesnt work. The records still
open up in form frmMentorsAndSBTs all jumbled up. Unless you have any
further
thoughts, I ll have a play around with Support7556s suggestion - although
I
think I might find that more difficult. Thanks again for your help. JohnB

Hi John

When you say "frmMentorsAndSBTs is set to show records alphabetically
ascending by Mentor surname", do you mean that the form's recordsource
query
has an ORDER BY clause?

If so, then this could be another instance of a problem I've noticed
recently but have not yet had time to research properly. It is that the
form sometimes stops honouring the ORDER BY clause in the recordsource
when
a filter is applied.

The workaround is to use the OrderBy property of the form. In your
form's
Form_Load event procedure, add this code:
Me.OrderBy = "MentorSecondName"
Me.OrderByOn = True

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi. I posted this two days ago with no response. Thought Id give it one
more
try. Thanks.

Hi. Ive already had help with this code, from Rick Brandt and I now see
that
another mod is needed. This code is part of the On Click code for a
Schools
forms command button and opens a form called frmMentorsAndSBTs and in
it,
shows records where the MentorID is linked to the SchoolID via
tblMentorSBTSchool. Ive now realised that, although frmMentorsAndSBTs
is
set
to show records alphabetically ascending by Mentor surname, this code
overwrites this and jumbles up the records. Is there a way of changing
this
code so that it selects the required records then sorts them prior to
opening the form? The field to sort on is MentorSecondName and is in
table
Mentors, where the MentorID is the same as that selected by the code.
Can
anyone help please? Thanks, JohnB

DoCmd.OpenForm "frmMentorsAndSBTs", , , "MentorID IN (SELECT MentorID
FROM
tblMentorSBTSchool WHERE (tblMentorSBTSchool.SchoolID=" & SchoolID & "
And
[MovedToSchoolID] Is Null))"

 

[Support7556]

The simplest way to sort the FORM is put "Mentors.MentorSecondName" in the
forms Order By property. Thus:
If you will always want the FORM: "frmMentorsAndSBTs" to be sorted:
"ascending by Mentor surname" as you suggested. And "MentorSecondName" is a
field/column (View > Field List) in the record set. Then in Access open
"frmMentorsAndSBTs" in design view, then menu View > Properties > Data Tab in
the property "Order By" put "Mentors.MentorSecondName " (without quotes).
May need brackets [Mentors].[MentorSecondName], but access usually decides
and formats the property for you.
"MentorSecondName" has to be spelled exactly as it is in the "Field List"
(View > Field List)

I noticed a bug in Access 2003, the Order By property isn't being applied
automatically. The form must be opened into Form view or Datasheet and then
the menu item Records > Apply Filter/Sort. Then Close and Save the Form.

Hi. I posted a reply to this earlier today but it seems to have vanished. No,
Im not B for Beard, sorry.

Yes, the current Where clause works OK. Its only the sort order thats being
ignored when form frmMentorsAndSBTs is opened using the command button. Thats
why I though it would be best to somehow sort the records at the same time as
selecting them in the code.

Ill have to take some time to look at your suggestion - Im not too good at
this code stuff. If I get stuck Ill perhaps repost with your name in the
header. OK?

Thanks again for the help. JohnB

Hi John B. (B isn't for Beard in NC, is it?)

Does your current (WhereCondition) work? (in the DoCmd.OpenForm)
I'm dubious of a nested Where clause in an OpenForm's WhereCondition.
If your compound Where clause works, I would still dim a string with your
criteria
Dim stLinkCriteria As String
stLinkCriteria = "WhereCondition"
DoCmd.OpenForm stDocName, , , stLinkCriteria

If you’re mostly bothered by sorting, then create a (sort) query and
reference it in your DoCmd.OpenForm's ,,(Filter) section. Thus
DoCmd.OpenForm stDocName, ,qSortMentorSecondName , stLinkCriteria

*See the documentation for OpenForm "Filter"

You could also use the OpenArg to pass additional criteria, I've had to use
it for additional constraints, in the OnOpen event of the Form had it check
for OpenArg and apply, for example: ~something like~
IF NOT OpenArg is null THEN
OpenArg.OrderBy
ELSE
MentorSecondName.OrderBy
Make sure you sent it from your click event:
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , stOpenArg

I don't know the form's data source but if "MentorSecondName" is part of it
the just set the Forms OrderBy arg to that! You can even

I've even had to pass multiple items in OpenArgs and then parse them with
Left, Mid, Right, substring etc...

You’re going to have to play around with these different techniques until
you get the desired result.

One last idea is to change your Form's Data Source with references

Hi. I posted this two days ago with no response. Thought Id give it one more
try. Thanks.

Hi. Ive already had help with this code, from Rick Brandt and I now see that
another mod is needed. This code is part of the On Click code for a Schools
forms command button and opens a form called frmMentorsAndSBTs and in it,
shows records where the MentorID is linked to the SchoolID via
tblMentorSBTSchool. Ive now realised that, although frmMentorsAndSBTs is set
to show records alphabetically ascending by Mentor surname, this code
overwrites this and jumbles up the records. Is there a way of changing this
code so that it selects the required records then sorts them prior to
opening the form? The field to sort on is MentorSecondName and is in table
Mentors, where the MentorID is the same as that selected by the code. Can
anyone help please? Thanks, JohnB

DoCmd.OpenForm "frmMentorsAndSBTs", , , "MentorID IN (SELECT MentorID FROM
tblMentorSBTSchool WHERE (tblMentorSBTSchool.SchoolID=" & SchoolID & " And
[MovedToSchoolID] Is Null))"

 

[JohnB]

Thanks for this.

I havent had a chance to look at your earlier suggestions yet. Although it
might interest you that I had a reply to my original post and it made me
realise that my code is selecting records from an unsorted table. So I
changed the code so that it selected the records from a sorted query but it
didnt do any good - the records were still appearing unsorted when the form
frmMentorsAndSBTs opens.

Ill try doing as you suggest in this post, although I think my other methods
should have been doing the same. Im using Access 2002 but Ill Apply
Filter/Sort as you suggest anyway.

Itll be sometime early next week before I can try this. Please keep an eye
on this thread for my next reply.

Thanks again for the help. JohnB

The simplest way to sort the FORM is put "Mentors.MentorSecondName" in the
forms Order By property. Thus:
If you will always want the FORM: "frmMentorsAndSBTs" to be sorted:
"ascending by Mentor surname" as you suggested. And "MentorSecondName" is a
field/column (View > Field List) in the record set. Then in Access open
"frmMentorsAndSBTs" in design view, then menu View > Properties > Data Tab in
the property "Order By" put "Mentors.MentorSecondName " (without quotes).
May need brackets [Mentors].[MentorSecondName], but access usually decides
and formats the property for you.
"MentorSecondName" has to be spelled exactly as it is in the "Field List"
(View > Field List)

I noticed a bug in Access 2003, the Order By property isn't being applied
automatically. The form must be opened into Form view or Datasheet and then
the menu item Records > Apply Filter/Sort. Then Close and Save the Form.

Hi. I posted a reply to this earlier today but it seems to have vanished. No,
Im not B for Beard, sorry.

Yes, the current Where clause works OK. Its only the sort order thats being
ignored when form frmMentorsAndSBTs is opened using the command button. Thats
why I though it would be best to somehow sort the records at the same time as
selecting them in the code.

Ill have to take some time to look at your suggestion - Im not too good at
this code stuff. If I get stuck Ill perhaps repost with your name in the
header. OK?

Thanks again for the help. JohnB

Hi John B. (B isn't for Beard in NC, is it?)

Does your current (WhereCondition) work? (in the DoCmd.OpenForm)
I'm dubious of a nested Where clause in an OpenForm's WhereCondition.
If your compound Where clause works, I would still dim a string with your
criteria
Dim stLinkCriteria As String
stLinkCriteria = "WhereCondition"
DoCmd.OpenForm stDocName, , , stLinkCriteria

If you're mostly bothered by sorting, then create a (sort) query and
reference it in your DoCmd.OpenForm's ,,(Filter) section. Thus
DoCmd.OpenForm stDocName, ,qSortMentorSecondName , stLinkCriteria

*See the documentation for OpenForm "Filter"

You could also use the OpenArg to pass additional criteria, I've had to use
it for additional constraints, in the OnOpen event of the Form had it check
for OpenArg and apply, for example: ~something like~
IF NOT OpenArg is null THEN
OpenArg.OrderBy
ELSE
MentorSecondName.OrderBy
Make sure you sent it from your click event:
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , stOpenArg

I don't know the form's data source but if "MentorSecondName" is part of it
the just set the Forms OrderBy arg to that! You can even

I've even had to pass multiple items in OpenArgs and then parse them with
Left, Mid, Right, substring etc...

You're going to have to play around with these different techniques until
you get the desired result.

One last idea is to change your Form's Data Source with references

:

Hi. I posted this two days ago with no response. Thought Id give it one more
try. Thanks.

Hi. Ive already had help with this code, from Rick Brandt and I now see that
another mod is needed. This code is part of the On Click code for a Schools
forms command button and opens a form called frmMentorsAndSBTs and in it,
shows records where the MentorID is linked to the SchoolID via
tblMentorSBTSchool. Ive now realised that, although frmMentorsAndSBTs is set
to show records alphabetically ascending by Mentor surname, this code
overwrites this and jumbles up the records. Is there a way of changing this
code so that it selects the required records then sorts them prior to
opening the form? The field to sort on is MentorSecondName and is in table
Mentors, where the MentorID is the same as that selected by the code. Can
anyone help please? Thanks, JohnB

DoCmd.OpenForm "frmMentorsAndSBTs", , , "MentorID IN (SELECT MentorID FROM
tblMentorSBTSchool WHERE (tblMentorSBTSchool.SchoolID=" & SchoolID & " And
[MovedToSchoolID] Is Null))"

 

[JohnB]

Hi again.

After all this messing about, I tried your last suggestion - to put
"Mentors.MentorSecondName" in the forms Order By property and it works.
Amazing. Why it wouldnt automatically work when the records supplied to the
form are sorted and the form itself is set to sort, I dont know. Anyway, Im
happy now.

Thanks for your help. Cheers, JohnB

The simplest way to sort the FORM is put "Mentors.MentorSecondName" in the
forms Order By property. Thus:
If you will always want the FORM: "frmMentorsAndSBTs" to be sorted:
"ascending by Mentor surname" as you suggested. And "MentorSecondName" is a
field/column (View > Field List) in the record set. Then in Access open
"frmMentorsAndSBTs" in design view, then menu View > Properties > Data Tab in
the property "Order By" put "Mentors.MentorSecondName " (without quotes).
May need brackets [Mentors].[MentorSecondName], but access usually decides
and formats the property for you.
"MentorSecondName" has to be spelled exactly as it is in the "Field List"
(View > Field List)

I noticed a bug in Access 2003, the Order By property isn't being applied
automatically. The form must be opened into Form view or Datasheet and then
the menu item Records > Apply Filter/Sort. Then Close and Save the Form.

Hi. I posted a reply to this earlier today but it seems to have vanished. No,
Im not B for Beard, sorry.

Yes, the current Where clause works OK. Its only the sort order thats being
ignored when form frmMentorsAndSBTs is opened using the command button. Thats
why I though it would be best to somehow sort the records at the same time as
selecting them in the code.

Ill have to take some time to look at your suggestion - Im not too good at
this code stuff. If I get stuck Ill perhaps repost with your name in the
header. OK?

Thanks again for the help. JohnB

Hi John B. (B isn't for Beard in NC, is it?)

Does your current (WhereCondition) work? (in the DoCmd.OpenForm)
I'm dubious of a nested Where clause in an OpenForm's WhereCondition.
If your compound Where clause works, I would still dim a string with your
criteria
Dim stLinkCriteria As String
stLinkCriteria = "WhereCondition"
DoCmd.OpenForm stDocName, , , stLinkCriteria

If you're mostly bothered by sorting, then create a (sort) query and
reference it in your DoCmd.OpenForm's ,,(Filter) section. Thus
DoCmd.OpenForm stDocName, ,qSortMentorSecondName , stLinkCriteria

*See the documentation for OpenForm "Filter"

You could also use the OpenArg to pass additional criteria, I've had to use
it for additional constraints, in the OnOpen event of the Form had it check
for OpenArg and apply, for example: ~something like~
IF NOT OpenArg is null THEN
OpenArg.OrderBy
ELSE
MentorSecondName.OrderBy
Make sure you sent it from your click event:
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , stOpenArg

I don't know the form's data source but if "MentorSecondName" is part of it
the just set the Forms OrderBy arg to that! You can even

I've even had to pass multiple items in OpenArgs and then parse them with
Left, Mid, Right, substring etc...

You're going to have to play around with these different techniques until
you get the desired result.

One last idea is to change your Form's Data Source with references

:

Hi. I posted this two days ago with no response. Thought Id give it one more
try. Thanks.

Hi. Ive already had help with this code, from Rick Brandt and I now see that
another mod is needed. This code is part of the On Click code for a Schools
forms command button and opens a form called frmMentorsAndSBTs and in it,
shows records where the MentorID is linked to the SchoolID via
tblMentorSBTSchool. Ive now realised that, although frmMentorsAndSBTs is set
to show records alphabetically ascending by Mentor surname, this code
overwrites this and jumbles up the records. Is there a way of changing this
code so that it selects the required records then sorts them prior to
opening the form? The field to sort on is MentorSecondName and is in table
Mentors, where the MentorID is the same as that selected by the code. Can
anyone help please? Thanks, JohnB

DoCmd.OpenForm "frmMentorsAndSBTs", , , "MentorID IN (SELECT MentorID FROM
tblMentorSBTSchool WHERE (tblMentorSBTSchool.SchoolID=" & SchoolID & " And
[MovedToSchoolID] Is Null))"