Keeping forms in order they are entered?

[WhitneyL]

I am working with a database that was designed for us and have pretty much
just used without having to change much in but have now run into a problem
and hope someone can help. We track and number complaints we recieve by
number using last digits of year recieved then next number, i.e. a complaint
recived 12/31/09 might be 9234 and first one received this year would be
10001 and the next would be 10002. The problem is now when I open my forms
and go to the last one it is the 9234 instead of the 10002 which I need. How
do I keep that last form I entered last the next time I open the database?
Thanks so much for any help!

 

[Jeff Boyce]

Whitney

Please give us a bit more to go on ...

How is that number being generated? How is it being displayed? Where?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[WhitneyL]

Hi Jeff, Thanks for responding.
We enter that number based on the entry before it, it is not something that
is generated or an autonumber, just our own numbering system. It is just
displayed in a text box on the form, but for some reason that is the number
that the form is sorting itself by. More then one person enters the data so
that is why we need the last entry to be there when we go into the forms so
the numbers stay consectutive. I'm sorry if this does not help, I really do
not know much about setting these things up and the person that did is long
gone. Thanks again.

Whitney

Please give us a bit more to go on ...

How is that number being generated? How is it being displayed? Where?

Regards

Jeff Boyce
Microsoft Access MVP

I am working with a database that was designed for us and have pretty much
just used without having to change much in but have now run into a problem

[quoted text clipped - 7 lines]

do I keep that last form I entered last the next time I open the database?
Thanks so much for any help!

 

[John W. Vinson]

Hi Jeff, Thanks for responding.
We enter that number based on the entry before it, it is not something that
is generated or an autonumber, just our own numbering system. It is just
displayed in a text box on the form, but for some reason that is the number
that the form is sorting itself by. More then one person enters the data so
that is why we need the last entry to be there when we go into the forms so
the numbers stay consectutive. I'm sorry if this does not help, I really do
not know much about setting these things up and the person that did is long
gone. Thanks again.

Access data is not stored in Forms - the form is just a window, and the data
is stored in a Table. The Form has a "recordsource" property which might be
the name of a table or of a query.

Data in tables *has no order* - it's just a heap of records. If you want a
specific order you must use a Query sorting the records; it's possible that
the person who set this database up did so incorrectly.

Open the Form in design view, and look at its Properties. Find the
Recordsource property (first on the data tab) - what's in it? Click the ...
icon by the property to open a Query Design window; if Access lets you do so,
select View... SQL on the menu and copy and paste the SQL text to a message
here (it may look like Sanskrit to you at this point but there are gurus
here...)

 

[KARL DEWEY]

You probably have a text field and it is doing a text sort. The number nine
being larger that a one that you have as first character in the field.
Change the datatype of the field to Number - Long Integer and it should sort
correctly.

--
Build a little, test a little.

Hi Jeff, Thanks for responding.
We enter that number based on the entry before it, it is not something that
is generated or an autonumber, just our own numbering system. It is just
displayed in a text box on the form, but for some reason that is the number
that the form is sorting itself by. More then one person enters the data so
that is why we need the last entry to be there when we go into the forms so
the numbers stay consectutive. I'm sorry if this does not help, I really do
not know much about setting these things up and the person that did is long
gone. Thanks again.

Whitney

Please give us a bit more to go on ...

How is that number being generated? How is it being displayed? Where?

Regards

Jeff Boyce
Microsoft Access MVP

I am working with a database that was designed for us and have pretty much
just used without having to change much in but have now run into a problem

[quoted text clipped - 7 lines]

do I keep that last form I entered last the next time I open the database?
Thanks so much for any help!

.

 

[WhitneyL]

Well we could be getting to the problem, in the recordsource it says
TblComplaints, which would be the table for our complaints, then when I click
Query Design window thing it says "You invoked the query builder on a table.
Do you want to create a query based on the table?" Does this help? Thanks so
much!

Hi Jeff, Thanks for responding.
We enter that number based on the entry before it, it is not something that

[quoted text clipped - 5 lines]

not know much about setting these things up and the person that did is long
gone. Thanks again.

Access data is not stored in Forms - the form is just a window, and the data
is stored in a Table. The Form has a "recordsource" property which might be
the name of a table or of a query.

Data in tables *has no order* - it's just a heap of records. If you want a
specific order you must use a Query sorting the records; it's possible that
the person who set this database up did so incorrectly.

Open the Form in design view, and look at its Properties. Find the
Recordsource property (first on the data tab) - what's in it? Click the ...
icon by the property to open a Query Design window; if Access lets you do so,
select View... SQL on the menu and copy and paste the SQL text to a message
here (it may look like Sanskrit to you at this point but there are gurus
here...)

 

[John W. Vinson]

Well we could be getting to the problem, in the recordsource it says
TblComplaints, which would be the table for our complaints, then when I click
Query Design window thing it says "You invoked the query builder on a table.
Do you want to create a query based on the table?" Does this help? Thanks so
much!

Yep! Since the Form is based on a Table, the records will be presented in
whatever order Access finds convenient (which may not be what YOU would find
convenient).

Accept Access' offer. You will then be put into a query design grid. Add all
the fields from the table (all the fields that you want on the form, to be
precise) to the query grid, and sort by the ID field. One question though:
what is the datatype of the field? If it's Number then 9234 will sort before
10002, but if it is Text then "9234" will sort after "10002" (because the text
string "92..." is greater, alphabetically, than the text string "10...").