programming a continuous form?

[Ferret]

hi everyone,
i'm fairly new to access forms as i normally work/view in the tables (raw data).what i'm trying to do is create a form that shows the month at the top followed by the records for that month. i would then like the next months records to follow on from the last record of the previous month using the same fromat. (The records will vary in number from month to month).
see example:


Month
record data ----------------------data--------------------------
record data ----------------------data--------------------------

Month
record data -----------------------data-------------------------

Month
record data------------------------data------------------------

i don't really want to use sub forms as they would break up the readability of the form.

many thanks in advance

Ferret

[Jim Doherty]

Hi Ferret,

Working directly in tables is not a good idea if you expect some assemblance of order. Tables should really ever only be seen as big buckets where you put the data. Everything else comes using queries. That is how you work with data ie: not directly in tha tables themselves.

So how do you achieve what it is you are trying to do well one assumes you have a logical sort sequence for your data records let us say a field called rec_datetime (datatype datetime) So for each record (in addition to any primary key you might have) you have a logical sort order on that date and time. Now you have that datatime for each row you can format it using a query however you wish ie:

"Give me the YEAR only for that date" will be YEAR(rec_datetime) in any query.
"Give me the MONTH for that entry" will be MONTH(rec_datetime)
"Give me the DAY for that entry" will be DAY(rec_datetime)
"Give me the year and month for that entry" will be Format([rec_datetime],"yyyy mm")

There are many ways to do..... this these are pure examples. You really need to understand querying.

Your form should be based on a query and any records FILTERED or restricted using the WHERE clause of the underlying query SQL to reflect the year and month you are interested in.

Once you get a handle on this you will realise that is entirely feasible for you to have an arrangement of years and months horizontally left to right in the header of your form with all records showing for the relevant year and month filtered on click of a button or something else.

Any new records you might insert for a given month year could have the default set in code for that datetime field to reflect any retrospective insert you might want or whatever else.

[Ferret]

hi Jim,
thanks for your swift reply i shall give the query a go as your reply has made me look at it from a different angle not just the query it's self but as to how to query the data.

many thanks

ferret

[Jim Doherty]

You,re welcome