Using an option button to change an Event Procedure

[jerry.ranch]

I have a series of comboboxes on a form that are used to display forms
in DS view (theres a qry behind it all). This is working well for me.
Some people like to see the data in DS view, some in normal form view.
I know they have the option to change that, but I trying to make this
as user friendly as possible (and to learn about VBA for myself)

So I thought I'd use two option buttons to identify this choice
........one for Forms view, one for DS view. One set of buttons that
could be used for ALL the comboboxes

In the on change event procedure for the combo boxes (one example
enclosed) can one specify which view to use to show the resultant
form by passing either acFormDS or acNormal to the code from the
option button?

opForm = option button, yes or no (on change turns opDatasheet to
"no", opForm yo "yes")
opDataSheet = option button, yes or no (on change turns opDatasheet
to "yes", opForm to "no")
Current VBA
Private Sub cmbPHP_Change()
DoCmd.OpenForm "frmPHP", acFormDS
DoCmd.Maximize
Me.cmbPHP = ""
End Sub

Proposed VBA (can I do this? it doesn't work, so it seems not, but I
may have the syntax wrong)
Private Sub cmbPHP_Change()
DoCmd.OpenForm "frmPHP", IFF("opForm"="yes", acNormal, acFormMS)
DoCmd.Maximize
Me.cmbPHP = ""
End Sub
Thanks Jerry

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ya threw me w/ "cmbPHP" as a ComboBox designation. ComboBox prefix is
"cbo," and "cmb" is the prefix for a CommandBar.

You should change the event to AfterUpdate instead of OnChange 'cuz
OnChange fires everytime the user types a character into the ComboBox.
AfterUpdate fires when the contents of the ComboBox have changed. Kinda
counterintuitive, but that's how it works.

The IIf() function should be like this:

DoCmd.OpenForm "frmPHP", IIf(Me!opForm=True, acNormal, acFormDS)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9aTkoechKqOuFEgEQJ+/wCgzICT1dOfZq4DlLxTNEPqJI2ATiYAnii9
/gxVKxyPYHLxAw+af5SOZ7WH
=l7nz
-----END PGP SIGNATURE-----
je*********@pioneer.com wrote:

I have a series of comboboxes on a form that are used to display forms
in DS view (theres a qry behind it all). This is working well for me.
Some people like to see the data in DS view, some in normal form view.
I know they have the option to change that, but I trying to make this
as user friendly as possible (and to learn about VBA for myself)

So I thought I'd use two option buttons to identify this choice
.......one for Forms view, one for DS view. One set of buttons that
could be used for ALL the comboboxes

In the on change event procedure for the combo boxes (one example
enclosed) can one specify which view to use to show the resultant
form by passing either acFormDS or acNormal to the code from the
option button?

opForm = option button, yes or no (on change turns opDatasheet to
"no", opForm yo "yes")
opDataSheet = option button, yes or no (on change turns opDatasheet
to "yes", opForm to "no")
Current VBA
Private Sub cmbPHP_Change()
DoCmd.OpenForm "frmPHP", acFormDS
DoCmd.Maximize
Me.cmbPHP = ""
End Sub

Proposed VBA (can I do this? it doesn't work, so it seems not, but I
may have the syntax wrong)
Private Sub cmbPHP_Change()
DoCmd.OpenForm "frmPHP", IFF("opForm"="yes", acNormal, acFormMS)
DoCmd.Maximize
Me.cmbPHP = ""
End Sub

[Jerome Ranch]

Worked like a charm !!
Thanks for the help.
I picked up the use of cmb for combobox from some instructional
materials. I don't have so much code that I can't change that.

Jerry

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ya threw me w/ "cmbPHP" as a ComboBox designation. ComboBox prefix is
"cbo," and "cmb" is the prefix for a CommandBar.

You should change the event to AfterUpdate instead of OnChange 'cuz
OnChange fires everytime the user types a character into the ComboBox.
AfterUpdate fires when the contents of the ComboBox have changed. Kinda
counterintuitive, but that's how it works.

The IIf() function should be like this:

DoCmd.OpenForm "frmPHP", IIf(Me!opForm=True, acNormal, acFormDS)

[Jerome Ranch]

I have another question.
I'd like the DS to open maximized, but the form to open restored cued
by this same afterupdate event

example

DoCmd.OpenForm "frmPHP", IIf(Me!opForm=True, acNormal, acFormDS)
IIf(Me!opForm=True, Restore, Maximize)
(but obviously these two statments aren't linked in any way, by the
time the IIf statement is reached the form is already opening)


I tried
DoCmd.OpenForm "frmPHP", IIf(Me!opForm=True, acNormal=Restore,
acFormDS = Maximize)
The syntax wasn't flagged, but the form always opened up restored
regardless of the status of opForm
Should this be a case tree, or perhaps nested IIf's? I'm still
pondering this.

Thanks
Jerry

DoCmd.OpenForm "frmPHP", IIf(Me!opForm=True, acNormal, acFormDS)

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Easiest way is to have the DS form Maximize in it's OnOpen event:

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
End Sub

To restore when the form closes:

Private Sub Form_Close()
DoCmd.Restore
End Sub
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9cC0YechKqOuFEgEQKxjwCg/TQyC/9rGTUXinNPO7O9wFxAIEYAoPk9
S6PosVLJQjy3xvUdA53iNGRd
=D3lP
-----END PGP SIGNATURE-----

Jerome Ranch wrote:

I have another question.
I'd like the DS to open maximized, but the form to open restored cued
by this same afterupdate event

example

DoCmd.OpenForm "frmPHP", IIf(Me!opForm=True, acNormal, acFormDS)
IIf(Me!opForm=True, Restore, Maximize)
(but obviously these two statments aren't linked in any way, by the
time the IIf statement is reached the form is already opening)


I tried
DoCmd.OpenForm "frmPHP", IIf(Me!opForm=True, acNormal=Restore,
acFormDS = Maximize)
The syntax wasn't flagged, but the form always opened up restored
regardless of the status of opForm
Should this be a case tree, or perhaps nested IIf's? I'm still
pondering this.

Thanks
Jerry

DoCmd.OpenForm "frmPHP", IIf(Me!opForm=True, acNormal, acFormDS)

[jerry.ranch]

Let me digest this

So what you have done here is to specify in a separate VBA statemnent
that when opening ANY Form as acNormal, open it restored; but when
opening any Form as acFormDS, open it maximized?

Thanks
Jerry

On Wed, 25 Jan 2006 04:47:24 GMT, MGFoster <[e-mail]> wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Easiest way is to have the DS form Maximize in it's OnOpen event:

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
End Sub

To restore when the form closes:

Private Sub Form_Close()
DoCmd.Restore
End Sub

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ah... I see what you want: If the form opens in DS you want it
maximized, but if it opens in Normal mode you don't want it maximized.
If this is correct then you'll have to handle the Maximizing from the
routine that opens the form and not in the target form's OnOpen event.

In the target form leave the OnClose event Restoring the form. In the
source routine do this:

DoCmd.OpenForm "frmPHP", IIf(Me!opForm=True, acNormal, acFormDS)

If Me!opForm = False Then DoCmd.Maximize

The If statement will maximize the form when you want a DS display.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9fic4echKqOuFEgEQJvYgCfSFGRnfk8B6ZTrgbqU9JHx9 RLDeUAn2Te
rIRpxjtEtsCdPiaMDKjHVMUr
=r83e
-----END PGP SIGNATURE-----

je*********@pioneer.com wrote:

Let me digest this

So what you have done here is to specify in a separate VBA statemnent
that when opening ANY Form as acNormal, open it restored; but when
opening any Form as acFormDS, open it maximized?

Thanks
Jerry

On Wed, 25 Jan 2006 04:47:24 GMT, MGFoster <[e-mail]> wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Easiest way is to have the DS form Maximize in it's OnOpen event:

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
End Sub

To restore when the form closes:

Private Sub Form_Close()
DoCmd.Restore
End Sub

[jerry.ranch]

Yep
Works Great
Thanks for your help
Jerry
On Wed, 25 Jan 2006 20:41:24 GMT, MGFoster <[e-mail]> wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ah... I see what you want: If the form opens in DS you want it
maximized, but if it opens in Normal mode you don't want it maximized.
If this is correct then you'll have to handle the Maximizing from the
routine that opens the form and not in the target form's OnOpen event.

In the target form leave the OnClose event Restoring the form. In the
source routine do this:

DoCmd.OpenForm "frmPHP", IIf(Me!opForm=True, acNormal, acFormDS)

If Me!opForm = False Then DoCmd.Maximize

The If statement will maximize the form when you want a DS display.

[jerry.ranch]

DoCmd.OpenForm "frmPHP", IIf(Me!opForm=True, acNormal, acFormDS)
If Me!opForm = False Then DoCmd.Maximize

Works famously. Got just the behavior I wanted.

So now, if I wanted to make sure that the columns autofit when I open
in DS view, I see some VBA code on the web that uses this line

ActiveSheet.Columns.Autofit

as part of a larger VBA sub.

Can't seem to get it to work, but then, I'm not really displaying a
sheet, I'm displaying a Form in Sheet view. So maybe the syntax is
wrong?
Jerry
On Wed, 25 Jan 2006 20:41:24 GMT, MGFoster <[e-mail]> wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ah... I see what you want: If the form opens in DS you want it
maximized, but if it opens in Normal mode you don't want it maximized.
If this is correct then you'll have to handle the Maximizing from the
routine that opens the form and not in the target form's OnOpen event.

In the target form leave the OnClose event Restoring the form. In the
source routine do this:

DoCmd.OpenForm "frmPHP", IIf(Me!opForm=True, acNormal, acFormDS)

If Me!opForm = False Then DoCmd.Maximize

The If statement will maximize the form when you want a DS display.

[MGFoster]

That is the command for an Excel spreadsheet & won't work in Access.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

je*********@pioneer.com wrote:

So now, if I wanted to make sure that the columns autofit when I open
in DS view, I see some VBA code on the web that uses this line

ActiveSheet.Columns.Autofit

[Jerome Ranch]

I see there is a ColumnWidth property in Access, that when set = -2,
autosizes the column to fit the largest item
Looks like I'd have to set each data column individually
Jerry

On Thu, 26 Jan 2006 01:26:52 GMT, MGFoster <[e-mail]> wrote:

That is the command for an Excel spreadsheet & won't work in Access.