Determining Code Action on Checkbox Value

[hbanerje]

Hi,
I have an access form where I have text feilds add_date which are date feilds(for different services, such as IRIS, ACH, CD-ROM.....), then I have several combo boxes with names IRIScontract_received, ACHcontract_received, CD-ROMcontract_received... with values "yes", "No" and "not required" and I have another combo box Master Agreement with values "yes" and "no".
All I want to do is if I say Master Agreement(click) to yes then if the date feild is not empty or null then that contract_received feild should automatically be "yes".
I am not good in VBA, I am trying my best but it is not working!
any help will be appreciated!
Please find my attached code...
I have placed this code in the properties under Master Agreement After update procedure. This code is setting all the cotract_received to yes,
I want to add if Add_date not equal to null.
Thanks.

Private Sub Master_Agreement_AfterUpdate()


If Me![Master Agreement] = "Yes" Then
'For Each ctl In Me.Controls
'If ctl.ControlType = acComboBox Then
'If ctl.Name <> "Master Agreement" Then
'ctl.Value = "Yes"
'End If
'End If
'Next
'End If

[sgrant]

Hello,

You need to qualify a little more which control you want to set to "yes' the ctl.value = "Yes" when not equal to "Master Agreement" will set all other controls to "Yes". I would suggest something like this...

Sub Master_Agreement_AfterUpdate()

If Me![MasterAgreement] = "Yes" Then
'Check Add_Date for null condition
If Not IsNull(Me![Add_Date]) Then
'Set Contract_Received to "Yes"
Me![Contract_Received] = "Yes"
Else
'Optional reset to no if conditions are not met
Me![Contract_Received] = "No"
End If
End If

End Sub

You may have some issues with the isnull function with the date field, you may also need to add or replace it with " And Not IsEmpty(Me![Add_Date]).

Let me know if you have any questions on this code.
Sincerely,
Scott Grant
sgrant1031@yahoo.com

[hbanerje]

Hi,

Thanks for your help. I just did this for one of the services it worked fine. Now how can I incorporate these in all the services with all the contracts.

My code looks like this now...



If Me![Master Agreement] = "Yes" Then

'Check Add_Date for null condition

If Not IsEmpty(Me![IRISadd_date]) Then

'Set Contract_Received to "Yes"

Me![IRIScontract_received] = "Yes"



So for Master agreement = yes,

all the services such as ACH, IRIS, CD-ROM...( look at their add dates if add_date<> empty) then set that corresponding contract_received = yes



If you provide me little more help on this I will really appreciate that.

Thanks.

[sgrant]

If I follow your question correctly.... My answer would be...


If Me![Master Agreement] = "Yes" Then
'Check Add_Date for null condition
If Not IsEmpty(Me![IRISadd_date]) Then

'Set Contract_Received to "Yes"
Me![IRIScontract_received] = "Yes"

End If

If Not IsEmpty(Me![ACHadd_date]) Then
Me![ACHcontract_received] = "Yes"
End If

If Not IsEmpty(Me![CDROMadd_date]) Then
Me![CDROMcontract_received] = "Yes"
End If

... Repeat as needed ...
'End of Master Agreement Yes If Statement

End If


Hope I understood it correctly

[hbanerje]

Hi
It is not working the way I want to, all the contract_received is getting set to Yes,
even it does not have an add_date. Here are the codes

If Me![Master Agreement] = "Yes" Then
'Check Add_Date for null condition
If Not IsEmpty(Me![IRISadd_date]) Then
'Set Contract_Received to "Yes"
Me![IRIScontract_received] = "Yes"
End If
If Not IsEmpty(Me![ACHadd_date]) Then
Me![ACHcontract_received] = "Yes"
End If

If Not IsEmpty(Me![CD-ROMadd_date]) Then
Me![CD-ROMcontract_received] = "Yes"
End If
If Not IsEmpty(Me![ZBAadd_date]) Then
Me![ZBAcontract_received] = "Yes"
End If
If Not IsEmpty(Me![str Fund Mgradd_date]) Then
Me![str Fund Mgrcontract_received] = "Yes"
End If

If Not IsEmpty(Me![TAXCASHEadd_date]) Then
Me![TAXCASHEcontract_received] = "Yes"
End If
If Not IsEmpty(Me![EDIadd_date]) Then
Me![EDIcontract_received] = "Yes"

'End of Master Agreement Yes If Statement

End If
End if
End sub

What should I change in the code??
Thanks for your help.

[sgrant]

The most likely thing are Null Values in the date fields. Although the date fields seem empty it may actually be null. In "Using Access 97" it states "Null is similar but not equivalent to an empty string" pg 132. So you many want to add "And not IsNull(Me![XXXadd_date]) )Then to your statements and try it again.


If ( Not IsEmpty(Me![XXXadd_date] And not IsNull(Me![XXXadd_date]) ) Then
Me![XXXcontract_received] = "Yes"
End If


Otherwise I may need a sample to look at before offering any other suggestions.

[hbanerje]

Hi,
unfortunately, it's not working.
I tried with AND and OR both, but as soon as I set Master Agreement to yes all the contract_received are set to Yes too, as opposed to only which has the date on.
Any clue?
Thanks for the help.

[TonyJollans]

Could you post a copy of your database? It will make the solution much easier to find.

[Pat Hartman]

The IsEmpty() function is only used with variables that are declared in your VBA procedures. Although it doesn't return an error when used in this context, neither will it return what you expect.

You need to modify your code:

If Me.[Master Agreement] = "Yes" Then
'Check Add_Date for null condition
If IsNull(Me.[IRISadd_date]) Then
Me.[IRIScontract_received] = ?????????
Else
'Set Contract_Received to "Yes"
Me.[IRIScontract_received] = "Yes"
End If
....

When you're working with text fields you need to also either test for a zero length string or test for length = 0

If IsNull(Me.[IRISadd_date]) or Len(Me,[IRISadd_date]) = 0 Then

[hbanerje]

Hi, I tried this, this is working only for the IRIS service or the first service on the same page but not working on the other services on the same page...
How do i attach my database here so that you can take a look at it and suggest what I should do!
Thank you