Dynamic Month Roll-Over

[sguan]

I have three fields on a form of months in the correct sequence. i.e Jan, Feb and Mar.

I want these fields to automatically update into the correct order based on the current date/month. I.e today is 29th of August, I want the fields to show Aug, Sept, Oct.

Is there a code that I can input somewhere to create this function?


Your help is much appreciated. I am a newbie in Access so I apologize if there is any confusion in what I have written above. Please let me know and I will clarify.

Thanks!

Suzanne

 

[Galaxiom]

It is possible but I suspect it isn't really what needs to be done.

Are these Controls on the form bound to fields in a table with Month names.
If so you data structure is not done the right way.

 

[sguan]

These are controls on a form bound to a query with a number for each month. 1 for Jan, 2 for Feb 3 for Mar and so on.

I would appreciate any suggestions or alternative solutions as long as it can solve the problem.

Thanks a lot!

 

[Galaxiom]

Normally the results for each month would be held in a table as individual records rather than as multiple fields in a single record.

It is important your table is structured like this or it will make querying complex. Perhaps you already have this and have made a crosstab query to display the three months. That is the hard way.

They can easily be displayed in Continuous Forms so that it appears that they are separate controls on one form even though there really is just one set of controls.

Another control can have its RecordSource set to show the month name based on the date of the record.

The number of records displayed can be changed by simply adjusting the range of dates chosen in the RecordSource query. The number of months and their names will appear on the form automatically.

 

[sguan]

I tried and it doesn't seem to be the way I want it to work.

let's say the control field for Current Date says 29/8/2011.

Then the first of those three controls should show August, then the second and third show September and October respectively.

if the Current date changes to 1/9/2011, then the three controls should be automaticall changed to Sept, Oct, Nov.

I thought a code linked from the current date to the three control was needed in order to create this dynamic month roll-over...

Thanks again and welcome any suggestions

 

[Galaxiom]

I don't think you understand what I am saying. There is only one control and it is repeated each time for the records on the Continuous Form.

The current date control is used in the RecordSource query to define the range of months returned by the query. \

I strongly suspect your underlying data structure is the real problem.

Can you post a cut down database with just a few dummy records? Compact it and zip after removing the non-essential stuff.