if / then ??

[Guest]

I have a form with 4 combo boxes. Each combo box stores a piece of info
into the table. But I need to be able to restrict what the users are
selecting. For example if the field Service is selected, then I need the
field NonService to be null. Also if my field Service is selected, I need
field Product and field ConcernDetail to NOT be null. Then likewise is
NonService is selected, I need field Product, ConcernDetail and Service to
EQUAL null.
Can anyone help?
Thanks a lot!

 

[Guest]

You can control it at two levels; firstly as each control is updated by
enabling/disabling controls; secondly before the form's current record is
updated (which includes saving a new record).

Firstly disable the Product and ConcernDetail controls in the form's Current
event procedure if its at a new record so that a user is forced to select a
value from either the Service or NonService controls first, and if its at an
existing record enable/disable them conditionally:

If Me.NewRecord Then
Me.Service.Enabled = True
Me.NonService.Enabled = True
Me.Product.Enabled = False
Me.ConcernDetail.Enabled = False
Else
If Not IsNull(Me.Service) Then
Me.Service.SetFocus
Me.NonService.Enabled = False
Me.Product.Enabled = True
Me.ConcernDetail.Enabled = True
Else
Me.NonService.SetFocus
Me.Service.Enabled = False
Me.Product.Enabled = False
Me.ConcernDetail.Enabled = False
End If
End If

In the AfterUpdate Event procedure of the Service control put:

If Not IsNull(Me.Service) Then
Me.NonService.Enabled = False
Me.Product.Enabled = True
Me.ConcernDetail.Enabled = True
Else
' call form's Current event procedure
' to set defaults
Form_Current
End If

In the AfterUpdate Event procedure of the Service control put:

If Not IsNull(Me.NonService) Then
Me.Service.Enabled = False
Me.Product.Enabled = False
Me.ConcernDetail.Enabled = False
Else
' call form's Current event procedure
' to set defaults
Form_Current
End If

At form level it can then be confirmed that values have been selected as
required with code in the form's BeforeUpdate event procedure. This includes
a Cancel argument, so in the event of the data npt being validated the update
can be cancelled until it is correctly validated:

Dim strMessage As String

If IsNull(Me,Service) And IsNull(Me.NonService) Then
strMessage = "A Service or Non Service item must be selected."
Else
If Not IsNull(Me.Service)
If IsNull(Me.Product) Or IsNull(Me.ConcernDetail) Then
strMessage = "Product and Concern Detail items must be
selected."
End If
End If
End If

If Len(strMessage) > 0 Then
MsgBox strMessage, vbExclamation, "Incomplete Data"
Cancel = True
End If

Incidentally nothing can ever EQUAL Null, it can only BE Null. Null is not
a value, but an absence of a value so an equality comparison is meaningless;
even the expression NULL = NULL evaluates to NULL (not TRUE as you might
expect). Think of NULL as 'unknown'. If two things are unknown then its
impossible to say that one equals the other, or that one does not equal the
other. The answer to both questions is also unknown, i.e. NULL. NULL also
propagates, so NULL + 42 = NULL, NULL * 5 = NULL etc. NULL is not the same
as zero in these contexts, its again 'unknown', so NULL + 42 is also unknown
(NULL) as there is an infinite number of possible answers. This why we need
the IsNull function in VBA or IS (NOT) NULL in SQL.

Ken Sheridan
Stafford, England

 

[Guest]

Ken - thanks so much. I found some other logic that worked too. But can you
please answer a question? Here's the code:

Private Sub Category_AfterUpdate()
If Me.Category.Value Like "*Billing*" Then
Me.NonSerRel.Value = "n/a"
ElseIf Me.Category.Value Like "*Claims*" Then
Me.NonSerRel.Value = "n/a"
ElseIf Me.Category.Value Like "*Custom*" Then
Me.NonSerRel.Value = "n/a"
ElseIf Me.Category.Value Like "*FGV*" Then
Me.NonSerRel.Value = "n/a"
ElseIf Me.Category.Value Like "*O*" Then
Me.NonSerRel.Value = "n/a"
ElseIf Me.Category.Value Like "*Sales*" Then
Me.NonSerRel.Value = "n/a"


End If


End Sub

My question is how do I make more than one option for billing for example?

If I want Category Billing to have 2 options - Option 1 Late Invoice or
Option 2 Incorrect invoice?

If Me.Category.Value Like "*Billing*" Then
Me.Issue.Value = "Late Invoice" OR Me.Issue.Value = "Incorrect Invoice" -

It won't work with OR in there - any suggestions?
Thanks,
TAmmy

 

[Guest]

Tammy:

The only way you could do this would be to change the RowSource property of
the Issue combo box. If the RowSource property of the Issue combo box is a
query which includes a foreign key Category column then you’d include a
reference to the Billing control as a parameter, e.g.

SELECT Issue
FROM Issues
WHERE Category = Forms!YourForm!Billing
ORDER BY Issue;

In the AfterUpdate event procedure of the Billing control you’d requery the
Issue combo box:

Me.Issue.Requery

That would be the usual approach, but you could also change the RowSource
property to an SQL statement which restricts the rows returned to the literal
values in the AfterUpdate event procedure of the Billing control and then
requery the control:

Me.Issue.RowSource = _
“SELECT Issue “ & _
“FROM Issues “ & _
“WHERE Issue IN( “â€Late Invoiceâ€â€,â€â€Incorrect Invoiceâ€â€) “ & _
“ORDER BY Issueâ€
Me.Issue.Requery

If the RowSource property is a value list then you’d assign a new string
expression as the control’s RowSource property in code:

Me.Issue.RowSource = “LateInvoice;Incorrect Invoiceâ€
Me.Issue.Requery

When you change the RowSource property like this it sticks until you change
it again, so you’d need to reset it to whatever its default is in the form’s
Current event procedure. Also, this only really works in single form view as
in continuous form view the rows where the value of the combo box is not in
the list of values you’ve changed it to will show up blank. This can be got
round by the use of a ‘hybrid’ control, superimposing a bound text box on an
unbound combo box, but it’s a little tricky.

Ken Sheridan
Stafford, England

 

[Guest]

Hi Ken - I don't fully understand the reply. Where you said change the row
source property to a SQL statement.... literal values in the AfterUpdate
event procedure of the Billing control (what is the Billing control) ?

When I type
Me.Issue.Rowsource=_ - I get a compile error. Can you tell me please what
am I supposed to put in the sql statement where you have these characters &_

Tammy

 

[Guest]

Tammy:

Sorry, mea culpa! I was in a bit of a hurry when I replied earlier. I
meant Category control, not Billing control. 'Billing' is of course the
value of the Category control. I'm assuming the control, i.e. the combo box
bound to the category field, is also called Category. By default when you
add a bound control to a form from the field list or by using the forms
wizard its given the same name as the field its bound to. So the SQL
statement would be something like this:

SELECT Issue
FROM Issues
WHERE Category = Forms!YourForm!Category
ORDER BY Issue;

Incidentally I was going to send you a link from which you can download a
demo of mine which shows various ways of setting up 'correlated' combo boxes,
including the 'hybrid' control I mentioned. I was using a different machine
this morning, however, and didn't have the link to hand. Now I'm back at my
own machine the link is as follows, if you are interested. It uses
geographic data, which is a common situation when you want to limit the list
in one combo box by a selection in another, e.g. all cities in a selected
state, but the principle applies to any type of data where you want one
control's list to be determined by the selection in another:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

 

[Guest]

Hello Ken
sorry for bothering you but i noticed that u know well that coding things
i have a similr situation here...in a form a combo box --combo-- takes
values --a--b--c--d--, all i want is another --box-- on the same form to get
disabled when the --combo-- takes the a and b values...
and i would like to ask u if something change when the --combo-- belong to a
mother form and the --box-- value to a subform.
thanks!!!

 

[Guest]

To refer to a control on a subform you use the Form property of the subform
control on the parent form. Subform control means the control on the parent
form which contains the subform.

To disable the combo box on the subform when 'a' or 'b' are selected in the
combo box on the parent form you put code in the AfterUpdate event procedure
of the combo box on the parent form. Let's assume the combo box on the
subform is called cboSub; and the subform control on the parent form is
called sfrSub. How you disable the combo box on the subform depends on
whether it is the only control in the subform or not. If it is not then you
first must set focus to another control on the subfrom. Lets assume another
control is called txtMyID; the code for the AfdterUpdate event of the combo
box on the parent form would be:

Dim strValue As String
Dim blnDisable As Boolean

strValue = Me.cboParent
blnDisable = (strValue = "a" Or strValue = "b")

Me.sfrSub.Form.txtMyID.SetFocus
Me.sfrSub.Form.cboSub.Enabled = Not blnDisable

If the combo box is the only control on the subform then you can't disable
it, but you can disable the subform itself:

Dim strValue As String
Dim blnDisable As Boolean

strValue = Me.cboParent
blnDisable = (strValue = "a" Or strValue = "b")

Me.sfrSub.Enabled = Not blnDisable

As well as doing this when the combo box on the parent form is updated you
also need to do it when the user moves to a record on the parent form, so
also put whichever of the above two pieces of code you use in the Current
event procedure of the parent form.

Ken Sheridan
Stafford, England