OnOpen Select ListBox value

[#1]

I have 2 list boxes that are loaded with data from an excel spreadsheet. 1 listbox uses field FiscalYear and The other uses FiscalPeriod. I also have written a query returning 2 fields from a database ReportYear and ReportMonth. 1 listbox contains all of the years from the spreadsheet and the other contains the months. What I want to do is have the default value in each listbox set to whatever value my query returns.

I have tried adding an OnOpen trigger in the document properties, but could not get that to work. Not sure if I am doing it wrong or if this is the wrong place to do this.

Here is what I have done Settings->Document Properties->Triggers

Added Select in Field action OnOpen

Choose field FiscalYear

added expression = Fields("ReportYear")

[#2]

Hello Ryan, I think the trigger would only allow you to select one value in of the list boxes; to do that only change your expression to:

=ReportYear

If you want to have a value selected by default, you can click a single value in your list boxes and then go to the general tab and mark "always one selected value" option.

Regards

[#3]

Still could not get it to work...see screenshot below

[#4]

Also Is it possible to debug the document? That way I can step through it to see what is happenning?

[#5]

hello ryan, I dont think it is possible to debug triggers, or at least, I haven't seen that yet. What I suggest is trying to hardcode a Year in you trigger value, for example 2009, to see if it works. Remeber that the expression:

= ReportYear

would only work if only one ReportYear is selected, probably you can try changing it to max(ReportYear), so it always return a value even if nothing is selected.

Regards

[#6]

Thanks for helping me out on this, but maybe I didn't explain what I am trying to do well enough.

I have a listbox containing a list of Years. The field used in this listbox is FiscalYear and the data is loaded from an excel spreadsheet. I am also running a query against my database that gets the current open year for our company. The field I am loading in from the query is RepoirtYear. Now when the document loads I want to set FiscalYear to ReportYear(Select the particular year returned from the query). I have tried putting =ReportYear in the expression and that did not work, but I think you have to actually specify somewhere that I want the FiscalYear field to be changed to whatever value ReportYear is. The max function will not work because the year that I want to return may not always be the max year. I need to be able to set the selected value of the listbox(FiscalYear) to whatever is returned from my query(ReportYear). See my screenshot below maybe that will help.

[#7]

Anyone?

[#8]

I have also tried editing the module

Tools->Edit Module

Tried all of the following and none work

ActiveDocument.Fields("FiscalYear").Select ReportYear

ActiveDocument.Fields("FiscalYear").Select =ReportYear

ActiveDocument.Fields("FiscalYear").Select "ReportYear"

ActiveDocument.Fields("FiscalYear").Select "=ReportYear"

ActiveDocument.Fields("FiscalYear").Select ActiveDocument.Fields("ReportYear")

Now this works

ActiveDocument.Fields("FiscalYear").Select "2009"

Also tried setting a variable at the top of the module, but I could not even declare a variable without getting an object required error.

See code Below

Set

("Test")

vTest = "Test"

msgbox

ActiveDocument.Fields(

"FiscalYear"). Select vTest

[#9]

Ryan,

This looks like the right approach. The fact that your text entry for 'Search String' is displayed in Red implies that maybe you mistyped the exact name of the ReportYear field? If you've correctly typed the fieldname it should display in green text.

The other possibility is that one or both of the "FiscalYear" and 'ReportYear' fields are full date datatypes and the comparison is not working because of the other elements of the date datatype. If so, you can use the YEAR function to simplify the data.

regards,

Paul

[#10]

Thanks everyone for your help. The problem was very simple. I needed to reload the data and then the field names showed in green and everything worked fine. It is always the small things. The clue about the field being red clued me in that it wasn't loading correctly. Thanks again.