Automatic date increment through forms

[thouston]

I am developing an Access front end for people to enter meteorological data.

The user goes into an introductory screen in which they type in the date of the assessment. They then go into the data entry screen.

What I want to do is to make a check in the data; if that day's data already exists then it should be displayed on the screen; if not, a new record is created for them to fill in.

After that, I have a couple of extra buttons saying "next day" and "previous day". When pressed, if data for next or previous day already exists, it should be displayed, otherwise new record created with appropriate data in the Date field (without user having to retype it or go back to introductory screen).

Data (date + meteorological variables) all stored in a single underlying table, date as PK.

Why am I doing it this way? This is based on an old system (not written in Access) that people like and know how to use, but it won't run on any OS later than NT. The guy who wrote that is no longer around and nobody else knows how he did it...

Not sure if this is a forms, macros, or VBA problem (or combination); any ideas welcomed!

 

[smercer]

You may need to start working it out in Visual basic for this with plenty of IF statements

Good Luck

 

[thouston]

Does anybody have some more ideas on this - I'm not a VBA expert (only use databases for the storage and querying facilities normally).

 

[smercer]

I beleive I can help you, but I can't take the credit as it is pat hartman's. I got this at the sample databases forum so you will find the same thing there. I will attach this one ( UsefulDateFunctions.zip) here and you can have a look at it and ask pat about it if you like

also There is a useful data base about calendars in access from same place by Kevin_S enjoy

hope this helps

 

[Mile-O]

Data (date + meteorological variables) all stored in a single underlying table, date as PK.

What sort of meteorological data are you collecting? Air pressure, precipitation, temperature, and oktas. Anything else?

Personally, I think you'd be better rethinking the whole structure as one table, to me, seems illogical for gathering meteorological information.

Is it for one weather centre or do you collate information for multiple sites?

 

[thouston]

It's a long, long story and I would not be doing it this way if I were starting out fresh...

In the old days people from a variety of outstations filled in a daily record and submitted a monthly paper form. The data entry system was then written so they could enter the info daily and submit it electronically. It also did some useful real-time checks (eg is max temp higher than min) which saved us finding this out ages later when we checked the monthly returns.

The data is stored in the outstation in a single Access table (created by the system). Other local programs access this DB to display the data etc. The people then send us an electronic monthly return which we collate and put into our "full" database (properly designed & normalised etc etc).

So I am not worried about the database, so much as the data entry system for the guys at the outstations, who are not computer experts and who like the current system.

I would also prefer not to alter the underlying structure of the local databases, as we now have this set of legacy Access DBs all over the country, and if I radically alter them I can see myself rewriting a whole lot of the other software that currently accesses them.

If that's not possible then I'll probably start again from scratch but I was hoping this might be the easier way out!?

PS Ironically the whole thing could become redundant in a few years as we gradually move over to automatic weather stations...sigh...
PPS Thanks for the examples smercer, I shall ponder them over the next few days...

 

[smercer]

This is what i would do:
because you only type in the date field one date which is for present day and only one date is allowed to exist I would do it like this:

in your table design view, type in the default value:
=Now()

this will always give the todays date and time of entry in the field or

=today()
this will always give the todays date without the time (If I remember correctly)

now because you only have the one date the same you can either make this field the primary key (this would be my recomendation) to do this just click on the key on the toolbar when the date field is selected

or

when the date field is selected go down to indexed and click on the field next to this and choose "Yes, No duplicates".

the only problem with both these solutions is that access will give the user a criptic message if they enter a duplicate date.

 

[Mile-O]

=today()
this will always give the todays date without the time

TODAY() is an Excel function. The correct one is Date()

 

[Rich]

TODAY() is an Excel function. The correct one is Date()

Today also works in Access, oddly enough, certainly on the calCntl

 

[smercer]

I can't understand why microsoft likes to have it more confusing by having different functions for both programs.

A nice uniform functions like they do in excel with a uniform function expession builder in access would be nice or the same expression builder in excel from access

 

[thouston]

"because you only type in the date field one date which is for present day and only one date is allowed to exist I would do it like this:" (smercer)

Unfortunately the date entered would not necessarily be for the present day (eg they would fill in the paper log book over the weekend but not get to the PC till Monday), so although that sounds like a really neat solution I need to allow for any date to be entered. That's why I then have to check for whether the date already exists and display the appropriate record if it does.

This gives them a chance to determine whether they've made an error (didn't intend to enter that date, or forgot it had already been done) or if they genuinely want to bring up that day to check/correct something.

I still feel the dreaded VBA if clauses looming...

 

[smercer]

Unfortunately the date entered would not necessarily be for the present day (eg they would fill in the paper log book over the weekend but not get to the PC till Monday), so although that sounds like a really neat solution I need to allow for any date to be entered. That's why I then have to check for whether the date already exists and display the appropriate record if it does.

Thouston, the default value is only a default value, not Permanent, so if it is monday then you would change the day in the date to suit the day you are doing the record for.

I had overlooked something too; do not use the "=now()" function because when the user enters a record and two minites later someone else can enter another record (because it enters the time and therefore not unquie)

instead use the

=Date()

as mile suggested