Need help with code...

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...


0
Utf
10/8/2007 3:44:02 PM
access.formscoding 7493 articles. 0 followers. Follow

7 Replies
1027 Views

Similar Articles

[PageSpeed] 40

"scubadiver" <scubadiver@discussions.microsoft.com> wrote in message 
news:8403F2B9-42D0-4BF3-8C21-85F77D55856E@microsoft.com...
>
> 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. 


0
BruceM
10/8/2007 6:01:48 PM
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.








"BruceM" wrote:

> 
> "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message 
> news:8403F2B9-42D0-4BF3-8C21-85F77D55856E@microsoft.com...
> >
> > 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. 
> 
> 
> 
0
Utf
10/9/2007 7:21:00 AM
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







"BruceM" wrote:

> 
> "scubadiver" <scubadiver@discussions.microsoft.com> wrote in message 
> news:8403F2B9-42D0-4BF3-8C21-85F77D55856E@microsoft.com...
> >
> > 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. 
> 
> 
> 
0
Utf
10/9/2007 7:48:03 AM
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.


"scubadiver" wrote:

> 
> 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...
> 
> 
0
Utf
10/9/2007 10:48:02 AM
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.

"scubadiver" <scubadiver@discussions.microsoft.com> wrote in message 
news:ECE648B9-ABE7-4595-82A0-D434E1633B28@microsoft.com...
>
> 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.
>
>
> "scubadiver" wrote:
>
>>
>> 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...
>>
>> 


0
BruceM
10/9/2007 12:56:10 PM
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
-- 
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"scubadiver" wrote:

> 
> 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.
> 
> 
> "scubadiver" wrote:
> 
> > 
> > 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...
> > 
> > 
0
Utf
10/9/2007 3:43:00 PM
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)


"Steve Sanford" wrote:

> 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
> -- 
> Steve S
> --------------------------------
> "Veni, Vidi, Velcro"
> (I came; I saw; I stuck around.)
> 
> 
> "scubadiver" wrote:
> 
> > 
> > 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.
> > 
> > 
> > "scubadiver" wrote:
> > 
> > > 
> > > 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...
> > > 
> > > 
0
Utf
10/9/2007 4:47:02 PM
Reply:

Similar Artilces:

Help getting SheetName into a cell
I need to refer to the sheet name in a formula and can't figure out how to do it. I can't find a Function which will do this. I did discover ActiveSheet.Name but I have been unsuccessful in getting it work in a function. Specifically I have sheets named "1.a" , "1.b" , "1.c" . . . "99.a" , "99.b" , "99.c" and need to get these names into cell C3 of each sheet. The sheets may not be in order and there may be missing sheets. Thanks in advance for any help. Omar Hi You could put somthing like this in a macro or attatch ...

Help with SQL Query 06-30-10
We have a distinct list of email addresses in alpha order and we need to transform it from a single column into a grid of three columns maintaining the alpha order. The list is contained in a temp table inside of our query. We then use the temp table list and perform case statement with a mod on the row_number in a select statement to columnze the data. However, the columnar data contains a null value in two of the three columns and we are needing to remove the nulls and have the actual values on each row in the output. Here is our current sql: CREATE TABLE #tmpTable ( Email_Add...

Messages not delivered to another Routing Group HELP NEEDED
I have three routing groups ( exchange in the 3 of them are Exch 2k sp3 ). Mail from one server ( in my headquarter routing group ) to another ( in one branch office routing group ) is getting stucked in MESSAGES WITH UNREACHABLE DESTINATION and not routed to that server. Connectors seems to be working just fine. After add my other exchange server from my headqurter group to the connector to be albe to send mail it shows the queue now in the connector but does not sends anything. Otherwise, mail from my branch office to my headquarter correctly sends mail. Any help would be gre...

Creating Templates, Need Advice
What is the best way to setup a template? I am building a PPT file and want to preserve test look and feel and custom animations as well as image custom animations, background and other miscellaneous graphic/page layout lements. In brief I want to let people insert their own text and images but preserve the properties of the text or image box. Dont think slide master is the answer, perhaps there is another way? Thanks for reading and offering advice, bmgid What version of PPT are you working with? -- Troy @ TLC MS PowerPoint MVP Check out www.ThePowerPointBlog.com ...

Get everything you need for a successful Marketing Campaign Today!
Get everything you need for a successful Marketing Campaign Today! Marketing Software - Promotion Center - Guaranteed Visitors - Guaranteed Signups Solo Emails - 1 Million Opt In Leads - Banner ads - Website Submission and Lots More http://www.typeinternational.com/idevaffiliate/idevaffiliate.php?id=3736_34_3_74 ...

ZIP codes beginning with 0
I'm having trouble getting ZIP codes beginning with 0 to stick, even when specifying ZIP code in the format category type. No matter what I do, the initial 0 always gets dropped. Advice? Thanks. Hi Peter.......... Either pre-format your cells as TEXT, or enter a leading apostrophe before entering the number with the leading 0........the apostrophe won't show, it only forces TEXT formatting on that cell.......... Vaya con Dios,] Chuck, CABGx3 "Peter" <anonymous@discussions.microsoft.com> wrote in message news:2ab7001c46836$3382cf70$a601280a@phx.gbl... > I&...

Need help with Combo Box?
I would appreciate any help with this. I currently have a form with two combo boxes and a subform. The first combo box lists counties and the second box lists doctors in selected county. After selecting county, doc the subform lists pts for this doc. All this works fine. However, I need to add a couple of more filters. I am stuck and would like to know how to do this. I don't want to mess up what I already have. How can I incorporate a couple more filters? I thought maybe adding an option box to the form????? Can someone please help me to accomplish this? Thank you. Sure...

I have Korean Office...I need English (US) Office
LIPs don't seem to provide me with a solution...apparently there is no English LIP!?!?!?!?!? Seriously? That's crazy! LIP = Language Instruction Pack?? Why NO English? Where is Windows / Office developed? America, right? I'm in Korea. I bought a Korean computer. It came with Korean Windows. I need English menus and whatnot on my MS Office applications. In news:7430603D-8534-4235-A16D-D073B471EF85@microsoft.com, abundantmind <abundantmind@discussions.microsoft.com> typed: > LIPs don't seem to provide me with a solution...apparently > there is...

Error 8231
When attempting to create a new mailbox, the box was not stamped with any addresses. It appears that RUS is not working properly. We recently have been experiencing error messages in the logs that state the following: Event Type: Error Event Source: MSExchangeAL Event Category: Service Controller Event ID: 8231 Description: Permanent failure reported by policy group provider for 'CN=Recipient Policies,CN=Name,CN=MicrosoftExchange,CN=Services,CN=Configuration,DC=Name,DC=com':'MAD.EXE', error=8000ffff. Taking provider offline. We are running a single domain, native mode Exchang...

Store Operation 2.0 In Item Property showing On Order 6 or so.Need
I my Store Operation 2.0 database in am showing some #s in on order. I alreddy all PO closed and I don't have any any back order and its still showing On Order Quantity (Different #s for different items) How do I make this On Order Quantity Zero "0" I am looking for SQL query Update Items Set OnOrder = 0 some thing like that but I dont know the Database field name for Onorder Please help me. ...

Need remote Clients to have open SMTP relay with windows authentication in Exchange 2003
Hi all ... first time posting here, but i have been able to find many answers to other questions in the past ... Unfortunatley I may be in a bit over my head with this one. I have about 100 remote users and until now they have not been required to send email over SMTP to domains out side of ours. Now we have a directive from above to allow relaying from dialup and remote users but need to setup SMTP authentication. Current config ... Server: Windows 2003 server Exchange 2003 server SP1 Firewall permitting any traffic on ports 110 and 25 Security options for SMTP in the System Manager is se...

Do I need DSClient to run Exchange 5.5 on an NT4.0 box in Windows 2003 Native Mode ADS?
Good Evening, I am in the process of migrating my WinNT4.0 domain and Exchange 5.5 Org to Windows 2003 ADS/Exchange 2003. I know best practice is to change the domain to Native mode, but how does this affect my NT4.0 server running 5.5? Do I need to simply load the DSClient onto the server? I should also note that we did an inplace upgrade from NT 4.0. This is a single domain environment. As long as you no longer have NT 4.0 BDCs you should be able to move to native mode. Your NT 4.0 server running E55 will not be affected (unless of course it is also a BDC). It is a good idea to...

Need RULE to search for Text in Body with varying # of spaces
Hi there, I have Outlook 2002 and have been using the outlook rule condition: "With specific words in the body" However the "Specific words" I'm looking for might contain varying number of spaces: EX: "some text: some more text" "some text: some more text" Is there a wildcard or something to match multiple spaces? --Thanks very much, Kenneth Kenneth Parker <kenneth.parker@sas.com> wrote: > I have Outlook 2002 and have been using the outlook rule condition: > "With specific words in the body" > > However...

RMS and Bar Coding
We are having a year end inventory count performed and need to send a file with all bar codes. Can we delete old bar codes that have not been used for awhile to shrink this file? Or can we send a numerical file with just the bar codes that currently show inventory? If so, how is this done? We own Store Operations 1.20 -- Joanne Mahoney SDN Consulting Jacksonville, FL ...

How to get looping VBA code to respond to events on worksheet
Hello, I have some code of the form do while not g_Paused bunch of calls ... loop where I'd like the variable g_Paused to be toggled by a button on the worksheet. The only way I can seem to get the code to respond to the button click is to use do while not g_Paused bunch of calls ... ThisWorkbook.Worksheets("Name").Activate DoEvents loop But this is pretty slow and causes the sheet to flicker...is there a better method? PS I'm using buttons from the Control Toolbox Thank you. ...

Tax Codes?
Does anyone know the tax codes Microsoft Money uses for transactions? Thanks, Brook In microsoft.public.money, Brook wrote: >Does anyone know the tax codes Microsoft Money uses for >transactions? Explain what you mean. What I am looking for are the Tax Codes for Different Transactions: I.E. If I wanted to create a 401k tracking within my Microsoft Money, I was trying to figure out how I need to tax code my transaction so everything appears properly. Hope this is clear. Brook >-----Original Message----- >In microsoft.public.money, Brook wrote: > >>Does anyone...

Add a new code macro
I have workbook that I am trying to use with a macro form another post on this form. It inserts a new row with the formulas and contents of the selected row. The problem is that it copies the row below the one selected, and because of this, I can not make a copy of row 2 below the column headers. Any ideas? Thanks. Here is the code: '/=======Start of Code==========================/ Sub InsertRowsAndFillFormulas() 'adds desired # of lines below the current line and ' copies the formulas to that/those lines 'added selection of more than one worksheet ' - Gary L. Brown &#...

REALLY NEED HELP
Hi guys, i'm hoping someone could lend me a hand. I'm setting up an excel file to input our fees received from our customers sent to collections. I have a main page with all the customers names, and each name is a hyper link to that customers separate worksheet - where a running tab is kept. I have a button called "update" and i've assigned the following macro (also called UPDATE) move the info over to the respective customers worksheet. I'm trying to get a loop going. A# is the customers name (first name will be starting at A4. B# to F# (first transaction...

Using Code instead of Criteria
In my query builder, I'm getting the message: "The string returned by the builder is too long. The result will be truncated." I think I understand why, as I am using a form to specify a large number of options for the report. Can I use VB to specify these criteria instead of the query builder? Examples in query builder: If [Forms]![boxSeatsMailings]![PW] Criteria =1 and (PFDirect>0 or PFBulk>0 or PIDirect>0) then select record. If [Forms]![boxSeatsMailings]![PW] Criteria =2 and (PFDirect>0 or PFBulk>0 or PIDirect>0) then select record. As ind...

Workbook there but not visible! Help!
I was working on some VBA code for an excel application I am writing. I switched between the VBE and the spreadsheet and all of a sudden my workbook is no longer visible! In the VBE project explorer window it shows my project and my code but in the excel window...no workbook! Nothing! Ran a test procedure calling IsAddin to see if somehow I accidentally clicked a button/box telling Excel to make this file an addin but it returned false. I did close the file and re-open it. Any ideas? Maybe it's just off the visible screen: Window|Arrange|tiled (and resize manually) or maybe y...

Help making BINGO cards
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am quite the novice at using Excel. I'm trying to create a set of BINGO cards for a church group and I'm having trouble figuring it out. I saw a post about something called RAND function but I don't know what that means. <br><br>What I need is 56 unique cards with 5 columns and 5 rows each. The cards will be using the numbers 1-24 leaving the center square blank or &quot;0&quot;. <br><br>Can anyone please assist? This is a multi-part message in MIME format. ----...

Calculating Averages
Hi, I am using the newest version of Excel on Windows 7, and I need some help with a complex calculation. In cells A1-A100, I have dollar amounts ranging from $1-$1000. In cells B1-B100, I have percentages ranging from 0%-100%. I want to break out the cells in A1-A100 in several groups, like follows: $1-$99, $100-$199, $200-$299, and so on up to $1000. Then, I need to calculate the average percentage for jobs in those categories. So, for the category of $1-$99, lets say there are two cells with amounts in that range, A1 and A2. Their percentages in B1 and B2 are 40% and 6...

Do I need Publisher installed to read/edit articles in an e-mail?
I will be receiving e-mails with attachments containing Publisher2002 created articles. Do I need to have Publisher 2002 or later editions in order to read and edit these e-mail articles? On Mon, 7 Nov 2005 22:20:03 +0000, quilter512 wrote (in article <E5DAA762-0ABA-49C2-8DDB-9A0D05162496@microsoft.com>): > I will be receiving e-mails with attachments containing Publisher2002 created > articles. Do I need to have Publisher 2002 or later editions in order to > read and edit these e-mail articles? You can answer your own question by STFW. You not heard of Google Grou...

Please help Password Trouble
I am using outlook 2000 on a DSL line on XP Home. I have chosen to not save my password, and the problem that I am having is that everytime I go to check my mail it makes me type in my password. There must be a way (like in express) that allows you to just type in your password the first time you log into outlook and keeps you logged in until you decide to log out?? Thanks for your help!!!! -- Virtualliance, Inc. Mark Needham 7 Kimball Lane Bldg A Lynnfield, Ma 01940 T 001-781-224-4700 F 001-781-224-2414 C 001-617-799-4597 www.virtuallianceinc.com mneedham@virtuallianceinc.com im: vaincmar...

COM communicate with managed code(.NET)
Hi! When I have a communication from COM to managed code(.NET) a type library(TLB) is necessary in between to handle the communication from COM to ..NET. I tried to use Red Gats's reflector to look into the type library that have extension tlb but it's not possible. So why is it not possible ? //Tony "Tony Johansson" <johansson.andersson@telia.com> wrote in message news:O%23lutgv5KHA.5464@TK2MSFTNGP05.phx.gbl... > I tried to use Red Gats's reflector to look into the type library that > have extension tlb but it's not possible. ...