Solved Set default value of a combo box using current date as a parameter

[SurreyNick]

On opening my form I’d like to set the default value of a combo box using the current date as a parameter

The following SQL gives me the value I want but I don’t know how to apply this to the default value for the combo box.

SELECT tblAcademicYears.AcademicYearID
FROM tblAcademicYears
WHERE (((Date() Between [tblAcademicYears]![StartDate] And [tblAcademicYears]![EndDate])=-1));

I have tried creating an expression in the default value box and I have tried using a select statement in the On_Load event of the form but I am not having any success. Can someone point me in the right direction?

Thank you

Nick.

 

[bob fitz]

Can you not just set the Default Value in its property sheet to Date()

 

[SurreyNick]

Alas no Bob. The combo box filters the records in the underlying query by the AcademicYearID which is an integer that represents an academic year.

 

[bob fitz]

On opening my form I’d like to set the default value of a combo box using the current date as a parameter

So what would you want that to be today.

 

[theDBguy]

Alas no Bob. The combo box filters the records in the underlying query by the AcademicYearID which is an integer that represents an academic year.

Hi. Maybe you can use DLookup() in the DefaultValue.

 

[SurreyNick]

Ah, that was the function I tried in the expression builder of the combo box default value but I just couldn't get the syntax right for the criteria part of the function and I couldn't find a similar example on the web.

 

[bob fitz]

Ah, that was the function I tried in the expression builder of the combo box default value but I just couldn't get the syntax right for the criteria part of the function and I couldn't find a similar example on the web.

I don't suppose anyone here will either if you don't give us more details of what you want to lookup and from where you want to get it.

 

[SurreyNick]

Fair enough. My apologies for being obscure. And apologies again if I now go OTT.

The table from which I want to get the value is called tblAcademicYears. It has 4 fields:
[AcademicYearID] = integer
[AcademicYear] = string
[StartDate] = date
[EndDate] = date

The table currently has 13 records

The default value I want in the combo box is the AcademicYearID for the current AcademicYear, which is currently '3'. I could simply leave the user to select the academic year but as the most likely selection will be the current academic year I thought it would be helpful if I can set the default value to whatever that is.

Nick

 

[isladogs]

One way to do this would be to create a function GetCurrentAcademicYear which would extract the value using either a query as in post #1 or using a DLookup on the table. Save the function in a standard module

Then set your combo default value to =GetCurrentAcademicYear()

Personally I wouldn't bother with the autonumber ID field. The AcademicYear field would be fine as your PK field.

 

[bob fitz]

Could you also show us the Row Source property of the combo box please

 

[SurreyNick]

Thank you all for your help. Problem is solved

Following your guidance I created a query "qryCurrentAcademicYear" and then used =DLookUp("AcademicYearID","qryCurrentAcademicYear") in the default value of the combo box. Elegantly simple but I couldn't get there and was going around and around becoming more and more frustrated trying to do it without creating a query.

Oh for reference the Row Source property of the combo box is SELECT [tblAcademicYears].[AcademicYearID], [tblAcademicYears].[AcademicYear] FROM tblAcademicYears ORDER BY [AcademicYear];

Nick

 

[theDBguy]

Thank you. Problem solved
Nick

Congratulations!

 

[bob fitz]

If you save the the Select statement in post #1 as say, "qryAcYrID" then perhaps use: the following as the default for your combo box:

DLookup("AcademicYearID", "qryAcYrID")

 

[SurreyNick]

Thank you Bob that is exactly what I did, except that saved it as "qryCurrentAcademicYear"