Why can't I think of a simpler way? (1 Viewer)

[guinness]

Hi Guys

Happy New Year

I have a resourcing database which has a form created from a crosstab query with the dates of every working date between January 2014 and December 2020 as its left hand column. By default the form opens at todays date and I have created a refresh (actually requery) button with the same functionality. I would also like to create a form jump function where if a user picks a year and a month the form can go directly to that point.

The code I have written is this

Private Sub Command5_Click()
If [Cmb_Year] = "2014" And [Cmb_Month] = "January" And Environ("username") = "mo096560" Then
 With [Forms]![frmOperationalTrainers]
    .Requery
    .Recordset.FindFirst "Start_Date =" & SQLDate(#1/31/2014#)
    End With
 
End If
End Sub

The code does exactly what I want but I don't much fancy copying it line by line for every month between now and December 2020. Am I missing a trick? I am sure I am but I need prodded in the right direction.

Any help is, as always, much appreciated.

 

[RainLover]

Set it up as a class Module and call it each month as you want it.

Or place a couple of text boxes on it and call any date period that you need.

 

[guinness]

Thanks RainLover.

If you really do love rain you should try relocating to Scotland

I wanted it to be a popup form where you could jump on demand to any month. I don't want to filter out other dates just save the user a bit of scrolling. As I said the code I've posted does exactly what I need it would just take ages to write it. It's probably just too early in January but I'm trying to think of something along the lines of match month and year rather than saying match January and 2014 if that makes sense.

 

[jdraw]

Have you considered a user defined function? You may need a few controls to select parameter/date values before executing the UDF.

 

[Simon_MT]

You try setting a Flag = CurrentMonth and StartDate in your table. That leaves identifying the user.

Simon

 

[BigHappyDaddy]

Can you change [Cmb_Month] to store the numeric equivlient of the months? ie [Cmb_Month] = 1 instead of January?

If so, then you could use a function like DATEVALUE to help indentify your start date.

 

[guinness]

Thanks for looking guys

Bighappydaddy seems to be closest to doing what I want to do but I'd need to have a think about how I achieve it.

I don't want to establish the present date as I have code that jumps to todays date whenever a user either opens the form or updates data. The issue is that the user might want to check the program for July next year. At the moment that means scrolling down from January 2015 to July 2016. What I want is a simple form where you pick the Month and the year and it jumps down the main form to that point without hiding the data before or after.

As I said the code in my first post does work I would just need to write a lot of it. I suppose I just have to start copying and pasting.

Thanks guys

 

[RainLover]

Perhaps Date Serial might be of some help.

Look up Access help as a start.