Macro to pass values from summary to detail levels

[#1]

I have an application that contains both aggregated data (for speed) and detail data. They are contained on different sheets and have no fields in common (and therefore cannot be joined). I have a macro that sets a variable to hide the summary sheets and show the detail sheets. I would like to pass the list box values selected in the summary sheet to the corresponding list box values in the detail sheets. Any ideas?

[disqr_rm]

One option would be using "OnLeaveSheet" you create a bookmark for the details sheet fields and values and on "OnActivateSheet" of the detail sheet, you call that bookmark. Ofcourse, using Macros.

Just one idea.

[#3]

How do I create a bookmark using a macro?

[disqr_rm]

Select the fields on the details shhet based on the header sheet when leaving header sheet using macro:

ActiveDocument.Fields("Month").Select "September"
ActiveDocument.Fields("Month").Select "J*"
ActiveDocument.Fields("Sales").Select ">1500"

Then create the bookmark

ActiveDocument.CreateDocBookmark false, "MyBookmark"

Then when activating details sheet recall that bookmark using

ActiveDocument.RecallDocBookmark "MyBookmark"

Once done, you can also remove the bookmark programatically.

For all these commands, check APIGuide.

Hope this helps.

[#5]

According to the API Guide: ActiveDocument.CreateDocBookmark false, "My Bookmark"

[#6]

How do I get the value selected in the list box instead of hardcoding "September"?

[#7]

The values selected in your list box will be applied to the bookmark when you create it. Rakesh's example makes some selections and then creates a bookmark. If you already have the items selected, just create the bookmark.

For other uses, GetFieldSelections(Field) will get you a list of selected values for a particular field.

[#8]

The problem is that the field names are different for the summary and detail sheets even though the data is the same. Is there a way to manipulate the field names in the bookmark?

[#9]

Oh, I kind of forgot about the multiple datasets problem.

You could try: ActiveDocument.Fields("Month").Select GetFieldSelections("Other_Month")

The GetFieldSelections will get you a comma separated list of the selected values. I'm not sure if you can use that string to make your selection in the other field.

[#10]

I get a Type mismatch: GetFieldSelection error