Writing an If...then event/query/thingy!

[Soni]

I need to write MANY if...then events. I have my tables and need to write a
query based on the YES/NO answer of a box I am going to put in my form. I
want to write/do the following:

IF the value in the YES/NO check box on the ORDERS form is YES (or -1) then
show the ON-SITE DATE box, the VENDOR drop-down list, the TELEPHONE MODEL
box, and the SHIP DATE box. IF the value in the YES/NO check box on the
ORDERS form is NO (or 0) do nothing.

So that is one of many I need to write and I cannot find out what all of the
specific language that is used or needed to make it read properly. NEXT I
need to have a prompt for my next query as follows:

IF First Ink Color is any value but null then pop-up a prompt dialog box
asking DO YOU NEED A SECOND INK COLOR? IF the value is YES (-1) then show the
SECOND INK COLOR box and if the value is NO (0) then finish. If the SECOND
INK COLOR is any value other than null prompt DO YOU NEED A THIRD INK COLOR?
If the value is YES then show the Third INK COlor box etc

There has got to be an easier way to writing this stuff. I am not a VB
person but do have some computer intelligence, if only in the nano amounts!

 

[bhicks11 via AccessMonster.com]

Hi Soni,

You could place the controls on the form and set their visible property to
false, in the OnChange event of the check box on the same form, do this:

If me.checkbox = -1
me.onsitedate.visible=true
me.vendor.visible=true
me.telmodel.visible=true
End If

Similar logic for the second question.

Bonnie
http://www.dataplus-svc.com

 

[bhicks11 via AccessMonster.com]

Of course change the names to match your convention.

Hi Soni,

You could place the controls on the form and set their visible property to
false, in the OnChange event of the check box on the same form, do this:

If me.checkbox = -1
me.onsitedate.visible=true
me.vendor.visible=true
me.telmodel.visible=true
End If

Similar logic for the second question.

Bonnie
http://www.dataplus-svc.com

I need to write MANY if...then events. I have my tables and need to write a
query based on the YES/NO answer of a box I am going to put in my form. I

[quoted text clipped - 17 lines]

There has got to be an easier way to writing this stuff. I am not a VB
person but do have some computer intelligence, if only in the nano amounts!

 

[Ken Sheridan]

Firstly, you should always refer to the Boolean constants True or False, not
the implementation of Boolean values in Access as -1 or 0. Reliance on the
implementation is not good programming practice.

However, you can directly assign the value of the check box to the Visible
property of each of the other controls rather than using an If….End If
construct. Do this in the check box's AfterUpdate event procedure:

Dim ctrl as Control

Set ctrl = Me.ActiveControl

Me.[On Site Date].Visible = ctrl
Me.[Vendor].Visible = ctrl
Me.[Telephone Model].Visible = ctrl

The Visible property of each of the three controls should be set to False
(No) by default in their properties sheets. If the form is a bound one this
should also be done in the form's Current event procedure:

Me.[On Site Date].Visible = False
Me.[Vendor].Visible = False
Me.[Telephone Model].Visible = False

As for the ink colour selections I'd suggest you include these all as
unbound controls on one dialogue form, or on the orders form itself. Again
set all but the first's Visible property to False by default. You can then
show them in the same way as above, with code in the first one's AfterUpdate
event procedure:

Dim ctrl as Control

Set ctrl = Me.ActiveControl

Me.[Second Ink].Visible = Not IsNull(ctrl)

Then in Second Ink's AfterUpdate event procedure:

Dim ctrl as Control

Set ctrl = Me.ActiveControl

Me.[Third Ink].Visible = Not IsNull(ctrl)

and so on….

How you reference these as parameters in the query will depend on the nature
of the query. If they are parameters for separate columns in the query,
implying an AND operation, then they'll be entered in the first 'criteria'
row of each in query design view, testing for OR IS NULL in each case, e.g.

Forms![YourForm]![First Ink] OR Forms![YourForm]![First Ink] IS NULL

Repeat for other ink columns.

If they are criteria for a single column, which would imply an OR operation,
then they'd be entered in separate 'criteria' rows of the column in query
design view. Its impossible to be categorical about this without knowing
exactly what your query is intended to accomplish.

Another thing to be wary of is leaving unbound controls with an unwanted
value when they are hidden. So if hiding unbound controls in a bound form's
Current event procedure be sure to also set them to Null in the code, e.g.

Me.[On Site Date] = Null
Me.[Vendor] = Null
Me.[Telephone Model]= Null

Me.[On Site Date].Visible = False
Me.[Vendor].Visible = False
Me.[Telephone Model].Visible = False

Do similarly for the ink colour controls.

Finally 'easier' does not necessarily mean shorter code. You only write the
code once, but if you don't cover all bases the application screws up every
time!

Ken Sheridan
Stafford, England

 

[bhicks11 via AccessMonster.com]

Thanks Ken - I've been duly scolded. I'll print this out for an example - I
like the way you did it.

Bonnie
http://www.dataplus-svc.com

Firstly, you should always refer to the Boolean constants True or False, not
the implementation of Boolean values in Access as -1 or 0. Reliance on the
implementation is not good programming practice.

However, you can directly assign the value of the check box to the Visible
property of each of the other controls rather than using an If….End If
construct. Do this in the check box's AfterUpdate event procedure:

Dim ctrl as Control

Set ctrl = Me.ActiveControl

Me.[On Site Date].Visible = ctrl
Me.[Vendor].Visible = ctrl
Me.[Telephone Model].Visible = ctrl

The Visible property of each of the three controls should be set to False
(No) by default in their properties sheets. If the form is a bound one this
should also be done in the form's Current event procedure:

Me.[On Site Date].Visible = False
Me.[Vendor].Visible = False
Me.[Telephone Model].Visible = False

As for the ink colour selections I'd suggest you include these all as
unbound controls on one dialogue form, or on the orders form itself. Again
set all but the first's Visible property to False by default. You can then
show them in the same way as above, with code in the first one's AfterUpdate
event procedure:

Dim ctrl as Control

Set ctrl = Me.ActiveControl

Me.[Second Ink].Visible = Not IsNull(ctrl)

Then in Second Ink's AfterUpdate event procedure:

Dim ctrl as Control

Set ctrl = Me.ActiveControl

Me.[Third Ink].Visible = Not IsNull(ctrl)

and so on….

How you reference these as parameters in the query will depend on the nature
of the query. If they are parameters for separate columns in the query,
implying an AND operation, then they'll be entered in the first 'criteria'
row of each in query design view, testing for OR IS NULL in each case, e.g.

Forms![YourForm]![First Ink] OR Forms![YourForm]![First Ink] IS NULL

Repeat for other ink columns.

If they are criteria for a single column, which would imply an OR operation,
then they'd be entered in separate 'criteria' rows of the column in query
design view. Its impossible to be categorical about this without knowing
exactly what your query is intended to accomplish.

Another thing to be wary of is leaving unbound controls with an unwanted
value when they are hidden. So if hiding unbound controls in a bound form's
Current event procedure be sure to also set them to Null in the code, e.g.

Me.[On Site Date] = Null
Me.[Vendor] = Null
Me.[Telephone Model]= Null

Me.[On Site Date].Visible = False
Me.[Vendor].Visible = False
Me.[Telephone Model].Visible = False

Do similarly for the ink colour controls.

Finally 'easier' does not necessarily mean shorter code. You only write the
code once, but if you don't cover all bases the application screws up every
time!

Ken Sheridan
Stafford, England

I need to write MANY if...then events. I have my tables and need to write a
query based on the YES/NO answer of a box I am going to put in my form. I

[quoted text clipped - 17 lines]

There has got to be an easier way to writing this stuff. I am not a VB
person but do have some computer intelligence, if only in the nano amounts!

 

[Ken Sheridan]

Scolding you, Bonnie! I would never be so ungallant. The OP referred to -1
as the value, but as a newbie they can be forgiven of course.

It just momentarily slipped your mind to draw their attention to the fact
that this isn't best practice. I rather like what the head of a software
company of my acquaintance once said of this sort of thing. He called it
"being unduly chummy with the implementation".

Ken Sheridan
Stafford, England

 

[bhicks11 via AccessMonster.com]

Thank you kind sir - I'll remember that!

Scolding you, Bonnie! I would never be so ungallant. The OP referred to -1
as the value, but as a newbie they can be forgiven of course.

It just momentarily slipped your mind to draw their attention to the fact
that this isn't best practice. I rather like what the head of a software
company of my acquaintance once said of this sort of thing. He called it
"being unduly chummy with the implementation".

Ken Sheridan
Stafford, England