Records jumbled up!

[CW]

Our mdb has been fine for over 2 years but suddenly the following is happening:

1. I have a button on the switchboard (inbuilt standard switchboard) that
takes us to Update an Order and opens the main form. Although I never set it
up to do this, it always used to open up on the very first record in the main
table, ref 70001. Now it always opens up on another ref (70053).

2. After opening that initial record (70001), if you inadvertently scrolled
the mouse button - I have never got round to installing Stephen Lebans's fix
for this!) - it would open up the very next one, 70002, and next 70003 and so
on. Now, it seems to select a next record at random.

I guess something that controlled the sorting/ordering of records has taken
a knock - any ideas, please?

Many thanks
CW

 

[strive4peace]

Hi CW,

you can solve this one of two ways:

1. in the form RecordSource

SELECT * FROM [Tablename] ORDER BY [fieldname]

OR

2. sort the record won the form Open or the form Load event:

'~~~~~~~~~~~~
me.OrderBy = "[fieldname]"
me.OrderByOn = true
'~~~~~~~~~~~~

it could be, that your form had these properties set -- but if you
changed the RecordSource, the settings were probably lost

Without giving records a specific order, they will appear in a random
order...


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[CW]

Crystal -
I'm very grateful for your quick response. I tried solution 1 on a locally
saved copy of the mdb and that worked perfectly so I will do that on the
server as soon as the users have gone home.
Question: I'm going to tell it to order by [Ref]. However we have about a
dozen stray records in the main table where there is no ref (I'm not sure how
and they probably don't serve any real purpose but I have been reluctant to
delete them in case that upsets something!). Using this new ordering, of
course the blank refs come up first so the page opens with no apparent record
showing. This isn't the end of the world except that one of the users might
think they were in Add mode and would start entering a new order, whereas in
fact they are already in one (an empty one). Is it possible to exclude the
blank Refs from the selection and ordering, so that the first "real" order
comes up, i.e. ref 70001 - and if so, how would I modify the record source to
achieve that?
Many thanks
CW

Hi CW,

you can solve this one of two ways:

1. in the form RecordSource

SELECT * FROM [Tablename] ORDER BY [fieldname]

OR

2. sort the record won the form Open or the form Load event:

'~~~~~~~~~~~~
me.OrderBy = "[fieldname]"
me.OrderByOn = true
'~~~~~~~~~~~~

it could be, that your form had these properties set -- but if you
changed the RecordSource, the settings were probably lost

Without giving records a specific order, they will appear in a random
order...


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Our mdb has been fine for over 2 years but suddenly the following is happening:

1. I have a button on the switchboard (inbuilt standard switchboard) that
takes us to Update an Order and opens the main form. Although I never set it
up to do this, it always used to open up on the very first record in the main
table, ref 70001. Now it always opens up on another ref (70053).

2. After opening that initial record (70001), if you inadvertently scrolled
the mouse button - I have never got round to installing Stephen Lebans's fix
for this!) - it would open up the very next one, 70002, and next 70003 and so
on. Now, it seems to select a next record at random.

I guess something that controlled the sorting/ordering of records has taken
a knock - any ideas, please?

Many thanks
CW

 

[strive4peace]

Hi CW,

if you want to exclude the records, the only way to do that is to use a
WHERE clause and not show them at all

.... but what you can do is

if number:
ORDER BY nz([Ref],9999999)

if text:
ORDER BY nz([Ref],"zzzzz")

then, they will be at the end...

NZ converts NULL to the value specified in the optional second
parameter. If nothing is specified, Access uses zero for numbers and an
empty string for text


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Crystal -
I'm very grateful for your quick response. I tried solution 1 on a locally
saved copy of the mdb and that worked perfectly so I will do that on the
server as soon as the users have gone home.
Question: I'm going to tell it to order by [Ref]. However we have about a
dozen stray records in the main table where there is no ref (I'm not sure how
and they probably don't serve any real purpose but I have been reluctant to
delete them in case that upsets something!). Using this new ordering, of
course the blank refs come up first so the page opens with no apparent record
showing. This isn't the end of the world except that one of the users might
think they were in Add mode and would start entering a new order, whereas in
fact they are already in one (an empty one). Is it possible to exclude the
blank Refs from the selection and ordering, so that the first "real" order
comes up, i.e. ref 70001 - and if so, how would I modify the record source to
achieve that?
Many thanks
CW

Hi CW,

you can solve this one of two ways:

1. in the form RecordSource

SELECT * FROM [Tablename] ORDER BY [fieldname]

OR

2. sort the record won the form Open or the form Load event:

'~~~~~~~~~~~~
me.OrderBy = "[fieldname]"
me.OrderByOn = true
'~~~~~~~~~~~~

it could be, that your form had these properties set -- but if you
changed the RecordSource, the settings were probably lost

Without giving records a specific order, they will appear in a random
order...


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Our mdb has been fine for over 2 years but suddenly the following is happening:

1. I have a button on the switchboard (inbuilt standard switchboard) that
takes us to Update an Order and opens the main form. Although I never set it
up to do this, it always used to open up on the very first record in the main
table, ref 70001. Now it always opens up on another ref (70053).

2. After opening that initial record (70001), if you inadvertently scrolled
the mouse button - I have never got round to installing Stephen Lebans's fix
for this!) - it would open up the very next one, 70002, and next 70003 and so
on. Now, it seems to select a next record at random.

I guess something that controlled the sorting/ordering of records has taken
a knock - any ideas, please?

Many thanks
CW

 

[CW]

Brilliant - just what I needed!
Thanks again
CW

Hi CW,

if you want to exclude the records, the only way to do that is to use a
WHERE clause and not show them at all

.... but what you can do is

if number:
ORDER BY nz([Ref],9999999)

if text:
ORDER BY nz([Ref],"zzzzz")

then, they will be at the end...

NZ converts NULL to the value specified in the optional second
parameter. If nothing is specified, Access uses zero for numbers and an
empty string for text


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Crystal -
I'm very grateful for your quick response. I tried solution 1 on a locally
saved copy of the mdb and that worked perfectly so I will do that on the
server as soon as the users have gone home.
Question: I'm going to tell it to order by [Ref]. However we have about a
dozen stray records in the main table where there is no ref (I'm not sure how
and they probably don't serve any real purpose but I have been reluctant to
delete them in case that upsets something!). Using this new ordering, of
course the blank refs come up first so the page opens with no apparent record
showing. This isn't the end of the world except that one of the users might
think they were in Add mode and would start entering a new order, whereas in
fact they are already in one (an empty one). Is it possible to exclude the
blank Refs from the selection and ordering, so that the first "real" order
comes up, i.e. ref 70001 - and if so, how would I modify the record source to
achieve that?
Many thanks
CW

Hi CW,

you can solve this one of two ways:

1. in the form RecordSource

SELECT * FROM [Tablename] ORDER BY [fieldname]

OR

2. sort the record won the form Open or the form Load event:

'~~~~~~~~~~~~
me.OrderBy = "[fieldname]"
me.OrderByOn = true
'~~~~~~~~~~~~

it could be, that your form had these properties set -- but if you
changed the RecordSource, the settings were probably lost

Without giving records a specific order, they will appear in a random
order...


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*





CW wrote:
Our mdb has been fine for over 2 years but suddenly the following is happening:

1. I have a button on the switchboard (inbuilt standard switchboard) that
takes us to Update an Order and opens the main form. Although I never set it
up to do this, it always used to open up on the very first record in the main
table, ref 70001. Now it always opens up on another ref (70053).

2. After opening that initial record (70001), if you inadvertently scrolled
the mouse button - I have never got round to installing Stephen Lebans's fix
for this!) - it would open up the very next one, 70002, and next 70003 and so
on. Now, it seems to select a next record at random.

I guess something that controlled the sorting/ordering of records has taken
a knock - any ideas, please?

Many thanks
CW

 

[strive4peace]

you're welcome, CW happy to help

Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Brilliant - just what I needed!
Thanks again
CW

Hi CW,

if you want to exclude the records, the only way to do that is to use a
WHERE clause and not show them at all

.... but what you can do is

if number:
ORDER BY nz([Ref],9999999)

if text:
ORDER BY nz([Ref],"zzzzz")

then, they will be at the end...

NZ converts NULL to the value specified in the optional second
parameter. If nothing is specified, Access uses zero for numbers and an
empty string for text


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Crystal -
I'm very grateful for your quick response. I tried solution 1 on a locally
saved copy of the mdb and that worked perfectly so I will do that on the
server as soon as the users have gone home.
Question: I'm going to tell it to order by [Ref]. However we have about a
dozen stray records in the main table where there is no ref (I'm not sure how
and they probably don't serve any real purpose but I have been reluctant to
delete them in case that upsets something!). Using this new ordering, of
course the blank refs come up first so the page opens with no apparent record
showing. This isn't the end of the world except that one of the users might
think they were in Add mode and would start entering a new order, whereas in
fact they are already in one (an empty one). Is it possible to exclude the
blank Refs from the selection and ordering, so that the first "real" order
comes up, i.e. ref 70001 - and if so, how would I modify the record source to
achieve that?
Many thanks
CW

:

Hi CW,

you can solve this one of two ways:

1. in the form RecordSource

SELECT * FROM [Tablename] ORDER BY [fieldname]

OR

2. sort the record won the form Open or the form Load event:

'~~~~~~~~~~~~
me.OrderBy = "[fieldname]"
me.OrderByOn = true
'~~~~~~~~~~~~

it could be, that your form had these properties set -- but if you
changed the RecordSource, the settings were probably lost

Without giving records a specific order, they will appear in a random
order...


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*





CW wrote:
Our mdb has been fine for over 2 years but suddenly the following is happening:

1. I have a button on the switchboard (inbuilt standard switchboard) that
takes us to Update an Order and opens the main form. Although I never set it
up to do this, it always used to open up on the very first record in the main
table, ref 70001. Now it always opens up on another ref (70053).

2. After opening that initial record (70001), if you inadvertently scrolled
the mouse button - I have never got round to installing Stephen Lebans's fix
for this!) - it would open up the very next one, 70002, and next 70003 and so
on. Now, it seems to select a next record at random.

I guess something that controlled the sorting/ordering of records has taken
a knock - any ideas, please?

Many thanks
CW