Automate Drop-Down Selections in Form

[ces11]

Hi all,

I'm working in a form that has several dropdown menus. Your selection from the first dropdown will impact what is shown in subsequent dropdowns (e.g., there are event procedures that are run after each update). Once you have made all of your selections there is a button at the bottom to "make report" which runs VBA code that, based on your dropdown selections, runs the appropriate macro to generate the appropriate report.

My question is this: For each organization (of which there are dozens) I have to generate 60 reports to create a packet -- and that means a lot of manual drop down selections. Is there any way to automate the dropdown selections such that it would be exactly as if I were making those selections manually, including running all of the event procedures after the update? Is that something that can be done in VBA? Going through the same tedious steps for each organization seems ridiculous but I'm not familiar with any way to automate this.

I hope that makes sense, and thanks so much for your help!

 

[GohDiamond]

Yes it is possible, "practical" is another issue.

If the procedure is simply repetitive with only the Organization changing then it's a bit simpler, but if there are various considerations for each organization and type of report or special criteria by reporting period etc. then it gets quite complex.

You'd need to create a test environment of the existing database

Back up your Production dB before you try to make your Test dB
Always back up your Test dB before you make any changes and/or after you commit changes

a) you'd have to map out your work flow for each of the reports
b) you'd have to determine whether your selections are based on the ComboBox(DropDown) Text or another value and note that for coding
c) you'd have to rewrite code in the existing process to select those values or write code to simply bypass the ComboBox selection entirely
d) you'd have to test all 60 of the reports against your new code to make sure nothing got screwed up

Does this sound like something you are prepared to do? Do you have time to make the changes? If so, we're here to help with any questions that may come up. However, if it doesn't sound practical now then you might need to reconsider.

Let us know what you decide.

Cheers
Goh

 

[spikepl]

To input to a combo using VBA:

1. Me.MyComboBox.SetFocus ' necessary to enable use of .Text
2. Me.MyCOmboBox.Text=MyValue ' whatever value you'd normally select/type
3. Me.MyNextControl.SetFocus ' the AfterUpdate of the combo is triggered when focus is moved to the next control