Default Value in ComboBox Problem 1

[slocat]

Most of our reports are based on a fiscal year and we have a number of forms which require the user to select the fiscal year from a dropdown list. The contents of the list come from the following code in the "Row Source" field of the Properties dialog box:

SELECT FiscalYears.FiscYear FROM FiscalYears ORDER BY FiscalYears.FiscYrKey DESC;

The "Default Value" property is hardcoded to the current fiscal year value ("03-04&quot. This means I have to go in and change the default value every year. I am trying to automate this process. I have a query (qryFiscalYear) that compares the current date to the StartDate and EndDate fields in the Fiscal Year table and produces the current fiscal year as a result. My problem is getting this result into the Default Value property. I have tried several approaches and either get no devault value, or a #Name error.
I have tried everything I could find in my books, as well as this forum and nothing I've found has worked. I would really appreciate any suggestions you might have!

Thanks for your time!

 

[TonyJollans]

Hi slocat,

I've had similar problems in the past and never fully understood why but it should work if you set the default value to something like this:

=DLookup(&quot;FiscYear&quot;,&quot;FiscalYears&quot;,&quot;StartDate < Today And EndDate > Today&quot;)

You'll have to supply your own proper criteria. You shouldn't need to set up a query just for this but if you have it anyway you can use it instead in the DLookup.

Enjoy,
Tony

 

[slocat]

Hi Tony,

Your code did the trick! And it's much simpler than all the stuff I tried that didn't work.

Many thanks,
slocat