Need help with code...

[scubadiver]

I have a problem.

I have a drop down combo box called "Query status" with two options:
"outstanding" and "completed".

The record can't be changed to "completed" until certain other fields have
ALL been entered but there is an extra complication. One other combo box can
be either "other" or "invoice". Two extra fields need to be entered if this
combo is "invoice" otherwise they aren't mandatory.

In full this is the code I currently have in the "after update" event of
each field:

If Me.Qry_QryType = "invoice" And Not IsNull(Me!Cont_InvName) And Not
IsNull(Me!Qry_ProdType) And Not IsNull(Me!Qry_CCDesc1) And Not
IsNull(Me!Qry_CntType) And Not IsNull(Me!SLA_Date2) And Not
IsNull(Me!SLA_Date3) And Not IsNull(Me!SLA_Date4) And Not
IsNull(Me!SLA_Date5) And Not IsNull(Me!SLA_Date6) And Not
IsNull(Me!SLA_Date7) Then Me.Qry_Status.Locked = False

If Me.Qry_QryType = "invoice" And IsNull(Me!Cont_InvName) And
IsNull(Me!Qry_ProdType) And IsNull(Me!Qry_CCDesc1) And IsNull(Me!Qry_CntType)
And IsNull(Me!SLA_Date2) And IsNull(Me!SLA_Date3) And IsNull(Me!SLA_Date4)
And IsNull(Me!SLA_Date5) And IsNull(Me!SLA_Date6) And IsNull(Me!SLA_Date7)
Then Me.Qry_Status.Locked = True

If Me.Qry_QryType <> "invoice" And Not IsNull(Me!Cont_InvName) And Not
IsNull(Me!Qry_ProdType) And Not IsNull(Me!Qry_CCDesc1) And Not
IsNull(Me!Qry_CntType) And Not IsNull(Me!SLA_Date2) And Not
IsNull(Me!SLA_Date3) And Not IsNull(Me!SLA_Date4) And Not
IsNull(Me!SLA_Date5) Then Me.Qry_Status.Locked = False

If Me.Qry_QryType <> "invoice" And IsNull(Me!Cont_InvName) And
IsNull(Me!Qry_ProdType) And IsNull(Me!Qry_CCDesc1) And IsNull(Me!Qry_CntType)
And IsNull(Me!SLA_Date2) And IsNull(Me!SLA_Date3) And IsNull(Me!SLA_Date4)
And IsNull(Me!SLA_Date5) Then Me.Qry_Status.Locked = True



I am sure there must be a simpler way...

 

[BruceM]

I have a problem.

I have a drop down combo box called "Query status" with two options:
"outstanding" and "completed".

The record can't be changed to "completed" until certain other fields have
ALL been entered but there is an extra complication. One other combo box
can
be either "other" or "invoice". Two extra fields need to be entered if
this
combo is "invoice" otherwise they aren't mandatory.

In full this is the code I currently have in the "after update" event of
each field:

If Me.Qry_QryType = "invoice" And Not IsNull(Me!Cont_InvName) And Not
IsNull(Me!Qry_ProdType) And Not IsNull(Me!Qry_CCDesc1) And Not
IsNull(Me!Qry_CntType) And Not IsNull(Me!SLA_Date2) And Not
IsNull(Me!SLA_Date3) And Not IsNull(Me!SLA_Date4) And Not
IsNull(Me!SLA_Date5) And Not IsNull(Me!SLA_Date6) And Not
IsNull(Me!SLA_Date7) Then Me.Qry_Status.Locked = False

If Me.Qry_QryType = "invoice" And IsNull(Me!Cont_InvName) And
IsNull(Me!Qry_ProdType) And IsNull(Me!Qry_CCDesc1) And
IsNull(Me!Qry_CntType)
And IsNull(Me!SLA_Date2) And IsNull(Me!SLA_Date3) And IsNull(Me!SLA_Date4)
And IsNull(Me!SLA_Date5) And IsNull(Me!SLA_Date6) And IsNull(Me!SLA_Date7)
Then Me.Qry_Status.Locked = True

If Me.Qry_QryType <> "invoice" And Not IsNull(Me!Cont_InvName) And Not
IsNull(Me!Qry_ProdType) And Not IsNull(Me!Qry_CCDesc1) And Not
IsNull(Me!Qry_CntType) And Not IsNull(Me!SLA_Date2) And Not
IsNull(Me!SLA_Date3) And Not IsNull(Me!SLA_Date4) And Not
IsNull(Me!SLA_Date5) Then Me.Qry_Status.Locked = False

If Me.Qry_QryType <> "invoice" And IsNull(Me!Cont_InvName) And
IsNull(Me!Qry_ProdType) And IsNull(Me!Qry_CCDesc1) And
IsNull(Me!Qry_CntType)
And IsNull(Me!SLA_Date2) And IsNull(Me!SLA_Date3) And IsNull(Me!SLA_Date4)
And IsNull(Me!SLA_Date5) Then Me.Qry_Status.Locked = True



I am sure there must be a simpler way...

When you refer to the After Update event of each field, I assume you mean
controls bound to those fields. If so, do you mean that you run the code
after you have updated Cont_InvName, then again after updating Qry_ProdType,
and so forth through all of the listed controls?
If Qry_QryType = "invoice", Cont_InvName contains a value, and Qry_ProdType
does not, then the first block of code is skipped. Moving to the second
block of code, Cont_InvName contains a value, so that code is skipped too.
The third and fourth blocks of code look at Qry_QryType and see "invoice",
so those blocks don't get past the first logical test. Each of the four
blocks of code fails the logical test, so the "Then" never applies.
You may do better to perform validation in the form's Before Update event,
but you still need to decide what is to happen if one of the controls fails
the test. Again, if one control is null, none of the logical tests pass.
What is Qry_Status?
In general, you can assign the same tag value to each control you want to
test, and loop through the controls that have that tag, but the question
remains about what needs to happen if, for instance, one of the controls to
which you are applying the Not IsNull test is in fact null. I can show you
how I have used the Tag property and looped through controls as I have
described, but since I do not quite understand your aims I will skip the
details for now.

 

[scubadiver]

I thought my message was reasonably clear.

The control for the drop down box is called "Qry_Status" and can either be
"outstanding" or "completed".

The status of the record can't be changed from "outstanding" to "completed"
unless these certain other fields are entered.

In the "before update" event of "Qry_Status" I have this line

If IsNull(Me!Qry_ProdType) Then Me.Qry_Status.Locked = True

But I get an error message: "You can't lock a control while it has unsaved
changes".
Now I appreciate there might be a circular argument here that Access can't
resolve.

 

[scubadiver]

Instead of using the "before update" event I have used the "after update"
event for each control

The list of controls required where [Qry_QryType] is NOT an "invoice"

Cont_InvName
Qry_ProdType
Qry_QryType
Qry_CntType
Qry_CCDesc1
SLA_Date3
SLA_Date4
SLA_Date5

The list of controls required where the option for [Qry_QryType] is "invoice"

The same as above plus the following:

SLA_Date6
SLA_Date7

 

[scubadiver]

I can see the problem now because if one of the fields is not null then the
"qry_status" combo can still be changed because the code specifies they
should ALL be null or ALL should be filled in.

 

[BruceM]

That's what I meant when I said that none of the logical tests will pass if
some controls are filled in and others are not.

Another point is that there are extra controls to test if QryType is
"Invoice", but you can test the controls that need to be filled in whether
or not QryType is "Invoice" without testing the value of QryType (shortened
list):

If Not IsNull(Me.Cont_InvName) And _
Not IsNull(Qry_ProdType) And _
Not IsNull(Qry_QryType) Then
Me.Qry_Status.Locked = False
Else
Me.Qry_Status.Locked = True
End If

You have already tested these controls, so you don't need to test them again
if Qry_Type is "Invoice". You can just test the added controls SLA_Date6
and SLA_Date7:

If Me.Qry_Type = "Invoice" Then
If Not IsNull(Me.SLA_Date6) And _
Not IsNull(Me.SLA_Date7) Then
Me.Qry_Status.Locked = False
Else
Me.Qry_Status.Locked = True
End If
End If

One possibility is that status not be a choice at all, but rather be
generated if all of the needed controls are filled in. You could use the
form's Before Update event to run the test, and use a message box to advise
the user of the status. If a needed control is not filled in you can cancel
the update and send the user back to fill in the information, or else let
the user leave the record incomplete.

If you want Status to be a choice, the Before Update event of Qry_Status may
be a good place to run the validation code. If the selection is
"Completed", test the controls, and cancel the update of Qry_Status if they
are not all filled in as needed.

I realize it feels straightforward from where you are, but my experience
with form-level validation is that it can take a lot of twists and turns
depending on the details of what needs to be checked. One key point is
whether you want to give users a specific message if a field is empty or
otherwise unsuitable, or will you use a generic message saying that
something that needs to be filled in is missing? One way to indicate
several controls that need a value is with something such as this in the
form's Before Update event (I expect some version would work in a combo box
Before Update event, too):

Dim ctl As Control
Dim blnBlue As Boolean

blnBlue = False

For Each ctl In Me.Controls
If ctl.Tag = "Test" Then
If IsNull(ctl) Then
ctl.BackColor = vbBlue
blnBlue = True
End If
End If
Next ctl

If blnBlue = True Then
If MsgBox("Blue controls need to be filled in", vbOKCancel) = vbCancel
Then
For Each ctl In Me.Controls
If ctl.Tag = "Test" Then
ctl.BackColor = vbWhite
End If
Next ctl
Me.Undo
Else
Cancel = True
End If
End If

This assumes the Tag Property has been set to Test for each of the controls
you need to validate. There are many options, depending on the details of
what needs to happen if a needed control is missing a value, etc. The point
is to illustrate an approach that could be adapted to a variety of needs.
You would need After Update code for each control (or a user-defined
function) to set the backcolor to white after a value has been added to the
control.

 

[Steve Sanford]

It looks like Bruce has you fixed up, but I'll throw in my 2 cents worth.

I was able to shorten your code a little. And I changed your logic also.

'-------beg code-----------------------
'Public Sub test()

'by default, set the state
Me.Qry_Status.Locked = True

'set LOCKED to FALSE if any of these are NULL
If IsNull(Me!Cont_InvName) Or IsNull(Me!Qry_ProdType) _
Or IsNull(Me!Qry_CCDesc1) Or IsNull(Me!Qry_CntType) _
Or IsNull(Me!SLA_Date2) Or IsNull(Me!SLA_Date3) _
Or IsNull(Me!SLA_Date4) Or IsNull(Me!SLA_Date5) Then

Me.Qry_Status.Locked = Not Me.Qry_Status.Locked
'Me.Qry_Status.Locked = false

'check if invoice
If Me.Qry_QryType = "invoice" Then
'now check for the 2 additional fields for "invoice"
'but reverse the logicic - use NOT ISNULL
If Not IsNull(Me!SLA_Date6) And Not IsNull(Me!SLA_Date7) Then
'set LOCKED to TRUE if both have data (Not NULL)
Me.Qry_Status.Locked = Not Me.Qry_Status.Locked
'Me.Qry_Status.Locked = True

End If
End If
End If

'End Sub
'-------end code-----------------------


HTH

 

[scubadiver]

Now that I have read your post I should have tried "OR" instead. Wot an
Idyot i've been!

I will give it a go tomorrow (GMT)