Form buttons to control report output

[Keiath]

Hi Guys

Wonder if you can help as I am not even sure where to start with this small problem that will make a big difference.

I have a database that produces yearend accounts, its highly specialized to my industry.

The year end accounts have schedules these can be 1 to 3

On my form I have it set up for 3 schedules, but can be used for 1 and 2 schedules accounts.

However when it come to the reports for layout reasons 3 of the 14 reports needed are tailored to a 1, 2 or 3 schedule, and i have created reports and buttons on the form according to the number of schedules for that account.

What I want to know is this, I have a field on my form that states the number of schedule for that account, is it possible that if say the account i am working on is a 2 schedule account, that it disables the 1 and 3 buttons?

I know this would need some coding but am at a little bit at a loss as to where to start

Thanks

 

[GohDiamond]

...is it possible that if say the account i am working on is a 2 schedule account, that it disables the 1 and 3 buttons?

Let's say:
Your Field with the Number of Schedules = [Number_of_Schedules]

Your Buttons are: cmd_ONE_SCHEDULE, cmd_TWO_SCHEDULES, cmd_THREE_SCHEDULES

Private Sub Form_Current()

Select Case Me.Number_of_Schedules

Case 1 ' Enable only Button 1

Me.cmd_ONE_SCHEDULE.Enabled = True
Me.cmd_TWO_SCHEDULES.Enabled = False
Me.cmd_THREE_SCHEDULES.Enabled = False​

Case 2 ' Enable only Button 2

Me.cmd_TWO_SCHEDULES.Enabled = True
Me.cmd_ONE_SCHEDULE.Enabled = False
Me.cmd_THREE_SCHEDULES.Enabled = False​

Case 3 ' Enable only Button 3

Me.cmd_THREE_SCHEDULES.Enabled = True
Me.cmd_ONE_SCHEDULE.Enabled = False
Me.cmd_TWO_SCHEDULES.Enabled = False​

Case Else ' Enable all Buttons to allow a choice to be made

Me.cmd_THREE_SCHEDULES.Enabled = True
Me.cmd_ONE_SCHEDULE.Enabled = True
Me.cmd_TWO_SCHEDULES.Enabled = True​

End Select​

End Sub

So if the Number of Schedules is different on the next account record then the enabled button will change.
AND if you forgot to put in the Number of Schedules then all the buttons will be enabled to make a selection.
You'd have to write the ON_CLICK event for each button to run your reports.
That's pretty simple. Check back if you need help with that part.

Cheers!
Goh

 

[Keiath]

Hi Thanks for this, I changed my buttons ect to read what you have it works sort of

when I go to a record that has say 3 schedules it doesn't allow me to enter 1 and 2 great, but when I go to the next record and say that's a 1 schedule its not resetting, ie, 1 and 2 are still not available but number 3 is. The only way I can reset it is to close the form down and go to the next record

Hope that makes sense

 

[GohDiamond]

Where are the buttons? In the form Header?
You did put the procedure in Form_Current, right?

If so add Me.Refresh a line above "Select Case..."

Cheers!
Goh

Btw: It appears to work on the form I tested with the same code without me.refresh.
What version of Access are you using?

 

[Keiath]

Hi Yeah didn't put it in the form current, added the refresh line and all works prefect thank you

 

[Keiath]

Hi Again

Here's a question the above works perfect for the 3 buttons i have- with covers 1 set of reports.

As I said there are 3 sets of reports requiring 3 schedule reports as above

I tried to do the same again on the next group of reports but I cant use the same button name, so I tried changing the button number and adding to the current form

Case 1 ' Enable only Button 1
Me.cmd_ONE_SCHEDULE1.Enabled = True
Me.cmd_TWO_SCHEDULES1.Enabled = False
Me.cmd_THREE_SCHEDULES1.Enabled = False

But that then error'd the form its self,

How do I get around this problem?

 

[GohDiamond]

Are the additional buttons you want to work with on a Different form or on the Same form as the first 3 buttons?

Goh

 

[Keiath]

Hi Goh

Same Form opening a different set of reports

So the buttons i have are IandE1, IandE2, IandE3 they in turn open a set of reports according to its number of schedules

Now on the same form I have another 3 buttons BalS1, BalS2, Bal3 and again they in turn open there reports according to the number of schedule.

I also have a 3rd set of 3 buttons Budget1, budget2, budget3, and they will need to open there reports accordingly

Hope that makes sense

 

[GohDiamond]

Set up ANOTHER Select Case... if the [Number of Schedules] for the 2nd and 3rd group of reports is different from the first group of reports.

BUT, If all the report groups have the same [Number of Schedules] then add commands for the 2nd and 3rd rows/groups of buttons inside the appropriate section of the current Select Case

Ex if the [Number of Schedules] for the 2nd and 3rd group of reports is different

Select Case [[Number of Schedules2]

Case 1
Case 2
Case 3​

End Select​

Likewise for the 3rd group with [Number of Schedules3]

OR If all the report groups have the same [Number of Schedules]

Make a change inside each CASE already in place

With Me

'Group 1 Report Buttons
.cmd_ONE_SCHEDULE.Enabled = True
.cmd_TWO_SCHEDULES.Enabled = False
.cmd_THREE_SCHEDULES.Enabled = False

'Group 2 Report Buttons
.cmd_BalS1.Enabled = True
.cmd_BalS2.Enabled = False
.cmd_BalS3.Enabled = False

'Group 3 Report Buttons
.cmd_Budget1.Enabled = True
.cmd_Budget2.Enabled = False
.cmd_Budget3.Enabled = False​

End With​

Likewise for each Case
(using With Me cuts down on the Me's

You don't have to rename your buttons, just use the names that you've already assigned. In trying to normalize object names I use cmd_ to start the name of any button, it helps keep things organized in your VB Editor when you are adding Events to the command buttons.

Cheers!
Goh

 

[Keiath]

Yep that was it, works perfect

Thank you so much for your help

Keiath