Further help with code please

[JohnB]

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 sorting the
records selected by this code? 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))"

 

[SteveS]

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 sorting the
records selected by this code? 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))"

Hi John,

What is the record source for the form "frmMentorsAndSBTs"? It sure sounds
like the record source is a table.

The code above opens the form and the last part that begins ""MentorID IN ...
limits the records returned to those that meet the subquery parameters. It
is like a WHERE clause without the word WHERE.

Nothing in the code above would cause the records to be unsorted; the record
source would have to be a table or a query without any sorting set.

HTH

 

[JohnB]

Thanks for this Steve. The record source for frmMentorsAndSBTs is a query
which sorts the records by Mentor surname. But the code Ive posted seems to
take its recordsource as the Mentors table, which is unsorted. Youve given me
an idea. I wonder if its possible to change the record source for the code to
be the same query that feeds frmMentorsAndSBTs. Ill play around with it and
get back to you. Thanks again, JohnB

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 sorting the
records selected by this code? 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))"

Hi John,

What is the record source for the form "frmMentorsAndSBTs"? It sure sounds
like the record source is a table.

The code above opens the form and the last part that begins ""MentorID IN ...
limits the records returned to those that meet the subquery parameters. It
is like a WHERE clause without the word WHERE.

Nothing in the code above would cause the records to be unsorted; the record
source would have to be a table or a query without any sorting set.

HTH

 

[JohnB]

Hi again Steve.

Ive now changed the code so that it selects records from a sorted query, the
same query that frmMentorsAndSBTs pulls its records from when it opens
normally. Still getting the records opening unsorted. Wierd. Anyhoo, I
actually reposted this on 1st June and I will spend some time figuring out
how to apply some of the suggestions made in that thread. Thanks again for
the help. Cheers, JohnB

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 sorting the
records selected by this code? 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))"

Hi John,

What is the record source for the form "frmMentorsAndSBTs"? It sure sounds
like the record source is a table.

The code above opens the form and the last part that begins ""MentorID IN ...
limits the records returned to those that meet the subquery parameters. It
is like a WHERE clause without the word WHERE.

Nothing in the code above would cause the records to be unsorted; the record
source would have to be a table or a query without any sorting set.

HTH

 

[SteveS]

What version of Access are you using?

If you want, delete any confidential data, compact, then zip the DB and send
it to me. I'll take a look at it.... like Graham Mandeno said... it is very
strange.

I have a hotmail account, user ID is > limbim53 at hotmail dot com

Good luck

 

[JohnB]

Hi Steve.

Im using Access 2002 on XP. Thanks for the offer to look at the db. Leave it
with me for a while please. I wont be able to get at it until Monday around
10.30 am GMT and then Id like to build a small sample db that illustrates
the problem, rather than sending the whole db.

Ill get back to you as soon as I can. Cheers, JohnB

 

[JohnB]

Hi again Steve.

After all this messing about, I tried Grahams 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 again for the offer to look at the db, I appreciate that.
Thanks for your help. Cheers, JohnB