Forcing user to enter data into certain fields

[jlee9562]

Hi All,

I have a form with various tabs and a number of fields. If I could I would just select all fields to "required=Yes" in the table design mode. But some fields should only have data entered if another field has a certain value. So, I think I just want all visible fields for this data requirement (I set some fields to visible=false if I don't want them to have a value). Does anyone know the easiest way to do this? Perhaps, a few lines of VBA code?

So far I tried to do this on click of the Submit Record button which I created using the "docmd.gotorecord, , acnewrec" statement, but I can't seem to make it work when combining it with if then msgbox statements. Plus I typed an if then statement for every required field. It's definitely a little cumbersome but don't know any other way to do this.

Here's my code below:

Private Sub AddNewRecord_Click()
On Error GoTo Err1
DoCmd.GoToRecord , , acNewRec

Me.Label216.Visible = False
Me.CM_2A.Visible = False
Me.Label221.Visible = False
Me.CM_2B.Visible = False
Me.Label222.Visible = False
Me.CM_3B.Visible = False
Me.Label223.Visible = False
Me.CM_4B.Visible = False
Me.Label443.Visible = False
Me.CM_5B.Visible = False
Me.PCI_Date1.Visible = False
Me.PCI_Date2.Visible = False
Me.PCI_Date3.Visible = False
Me.PCI_Date4.Visible = False
Me.CABG_Date1.Visible = False
Me.CABG_Date2.Visible = False
Me.Box349.Visible = False
Me.Label560.Visible = False
Me.PriorTestCount.Visible = False
Me.Label238.Visible = False
Me.Label239.Visible = False
Me.Label240.Visible = False
Me.Label241.Visible = False
Me.Label243.Visible = False
Me.Label247.Visible = False
Me.Label251.Visible = False
Me.Label255.Visible = False
Me.Label259.Visible = False
Me.Label510.Visible = False
Me.Label555.Visible = False
Me.Label557.Visible = False
Me.Label558.Visible = False
Me.Label559.Visible = False
Me.Label525.Visible = False
Me.Test1.Visible = False
Me.Test1_Date.Visible = False
Me.Test1_Prior.Visible = False
Me.Test1Result1.Visible = False
Me.Test1Result2.Visible = False
Me.Test1Result3.Visible = False
Me.Test1Result4.Visible = False
Me.Test1Result5.Visible = False
Me.Test1Change.Visible = False
Me.Test2.Visible = False
Me.Test2_Date.Visible = False
Me.Test2_Prior.Visible = False
Me.Test2Result1.Visible = False
Me.Test2Result2.Visible = False
Me.Test2Result3.Visible = False
Me.Test2Result4.Visible = False
Me.Test2Result5.Visible = False
Me.Test2Change.Visible = False
Me.Test3.Visible = False
Me.Test3_Date.Visible = False
Me.Test3_Prior.Visible = False
Me.Test3Result1.Visible = False
Me.Test3Result2.Visible = False
Me.Test3Result3.Visible = False
Me.Test3Result4.Visible = False
Me.Test3Result5.Visible = False
Me.Test3Change.Visible = False
Me.Test4.Visible = False
Me.Test4_Date.Visible = False
Me.Test4_Prior.Visible = False
Me.Test4Result1.Visible = False
Me.Test4Result2.Visible = False
Me.Test4Result3.Visible = False
Me.Test4Result4.Visible = False
Me.Test4Result5.Visible = False
Me.Test4Change.Visible = False
Me.Test5.Visible = False
Me.Test5_Date.Visible = False
Me.Test5_Prior.Visible = False
Me.Test5Result1.Visible = False
Me.Test5Result2.Visible = False
Me.Test5Result3.Visible = False
Me.Test5Result4.Visible = False
Me.Test5Result5.Visible = False
Me.Test5Change.Visible = False
Me.Label505.Visible = False
Me.Label506.Visible = False
Me.Label507.Visible = False
Me.Label508.Visible = False
Me.Label509.Visible = False
Me.CTALocation.Visible = False
Me.CTAstenosis.Visible = False
Me.TestLocation1.Visible = False
Me.TestLocation2.Visible = False
Me.TestFinding.Visible = False
Exit Sub
Err1:
If Me.Date_of_Abstract = "" Then
MsgBox "Date of Abstract is required."
Cancel = True
End If

If Me.Index_Imaging_Test = "CTA" And Me.ImagingTestResult = "Abnormal" And (IsNull(Me.CTALocation) Or IsNull(Me.CTAstenosis)) Then
MsgBox "One or more required fields in the Imaging Test tab is missing."
Cancel = True
ElseIf Me.Index_Imaging_Test <> "CTA" And Me.ImagingTestResult = "Abnormal" And (IsNull(Me.TestLocation1) Or IsNull(Me.TestLocation2) _
Or IsNull(Me.TestFinding)) Then
MsgBox "One or more required fields in the Imaging Test tab is missing."
Cancel = True
End If

If Me.CM_1A <> "N/A" And IsNull(Me.CM_2A) Then
MsgBox "One or more required fields in the Co-Morbidities tab is missing."
Cancel = True
End If
If Me.CM_1B <> "N/A" And IsNull(Me.CM_2B) Then
MsgBox "One or more required fields in the Co-Morbidities tab is missing."
Cancel = True
End If

If Me.CM_2B <> "N/A" And IsNull(Me.CM_3B) Then
MsgBox "One or more required fields in the Co-Morbidities tab is missing."
Cancel = True
End If

If Me.CM_3B <> "N/A" And IsNull(Me.CM_4B) Then
MsgBox "One or more required fields in the Co-Morbidities tab is missing."
Cancel = True
End If

If Me.CM_4B <> "N/A" And IsNull(Me.CM_5B) Then
MsgBox "One or more required fields in the Co-Morbidities tab is missing."
Cancel = True
End If

If Me.Revascularization = "Yes" And Me.PCI_Count = 1 And (IsNull(Me.PCI_Date1) Or IsNull(Me.PCI_Location1) Or IsNull(Me.PCI_Type1) _
Or IsNull(Me.Size1) Or IsNull(Me.PTCAorStent1)) Then
MsgBox "One or more required fields in the PCI or CABG tab is missing."
Cancel = True
End If

If Me.Revascularization = "Yes" And Me.PCI_Count = 2 And (IsNull(Me.PCI_Date1) Or IsNull(Me.PCI_Location1) Or IsNull(Me.PCI_Type1) _
Or IsNull(Me.Size1) Or IsNull(Me.PTCAorStent1) Or IsNull(Me.PCI_Date2) Or IsNull(Me.PCI_Location2) Or IsNull(Me.PCI_Type2) _
Or IsNull(Me.Size2) Or IsNull(Me.PTCAorStent2)) Then
MsgBox "One or more required fields in the PCI or CABG tab is missing."
Cancel = True
End If

If Me.Revascularization = "Yes" And Me.PCI_Count = 3 And (IsNull(Me.PCI_Date1) Or IsNull(Me.PCI_Location1) Or IsNull(Me.PCI_Type1) _
Or IsNull(Me.Size1) Or IsNull(Me.PTCAorStent1) Or IsNull(Me.PCI_Date2) Or IsNull(Me.PCI_Location2) Or IsNull(Me.PCI_Type2) _
Or IsNull(Me.Size2) Or IsNull(Me.PTCAorStent2) Or IsNull(Me.PCI_Date3) Or IsNull(Me.PCI_Location3) Or IsNull(Me.PCI_Type3) _
Or IsNull(Me.Size3) Or IsNull(Me.PTCAorStent3)) Then
MsgBox "One or more required fields in the PCI or CABG tab is missing."
Cancel = True
End If

If Me.Revascularization = "Yes" And Me.PCI_Count = 4 And (IsNull(Me.PCI_Date1) Or IsNull(Me.PCI_Location1) Or IsNull(Me.PCI_Type1) _
Or IsNull(Me.Size1) Or IsNull(Me.PTCAorStent1) Or IsNull(Me.PCI_Date2) Or IsNull(Me.PCI_Location2) Or IsNull(Me.PCI_Type2) _
Or IsNull(Me.Size2) Or IsNull(Me.PTCAorStent2) Or IsNull(Me.PCI_Date3) Or IsNull(Me.PCI_Location3) Or IsNull(Me.PCI_Type3) _
Or IsNull(Me.Size3) Or IsNull(Me.PTCAorStent3) Or IsNull(Me.PCI_Date4) Or IsNull(Me.PCI_Location4) Or IsNull(Me.PCI_Type4) _
Or IsNull(Me.Size4) Or IsNull(Me.PTCAorStent4)) Then
MsgBox "One or more required fields in the PCI or CABG tab is missing."
Cancel = True
End If

If Me.Revascularization = "Yes" And Me.CABG_Count = 1 And (IsNull(Me.CABG_Date1) Or IsNull(Me.CABG_Location1) Or IsNull(Me.CABG_Type1) _
Or IsNull(Me.Complete_Revascularization1)) Then
MsgBox "One or more required fields in the PCI or CABG tab is missing."
Cancel = True
End If

If Me.Revascularization = "Yes" And Me.CABG_Count = 2 And (IsNull(Me.CABG_Date1) Or IsNull(Me.CABG_Location1) Or IsNull(Me.CABG_Type1) _
Or IsNull(Me.Complete_Revascularization1) Or IsNull(Me.CABG_Date2) Or IsNull(Me.CABG_Location2) Or IsNull(Me.CABG_Type2) _
Or IsNull(Me.Complete_Revascularization2)) Then
MsgBox "One or more required fields in the PCI or CABG tab is missing."
Cancel = True
End If

If Me.Prior_Noninvasive_Stress_Test = "Yes" And IsNull(Me.PriorTestCount) Then
MsgBox "One or more required fields is missing in the Prior Tests tab."
Cancel = True
End If

If Me.PriorTestCount = 1 And IsNull(Me.Test1) Then
MsgBox "One or more required fields is missing in the Prior Tests tab."
Cancel = True
End If

If Me.PriorTestCount = 2 And (IsNull(Me.Test1) Or IsNull(Me.Test2)) Then
MsgBox "One or more required fields is missing in the Prior Tests tab."
Cancel = True
End If

If Me.PriorTestCount = 3 And (IsNull(Me.Test1) Or IsNull(Me.Test2) Or IsNull(Me.Test3)) Then
MsgBox "One or more required fields is missing in the Prior Tests tab."
Cancel = True
End If

If Me.PriorTestCount = 4 And (IsNull(Me.Test1) Or IsNull(Me.Test2) Or IsNull(Me.Test3) _
Or IsNull(Me.Test4)) Then
MsgBox "One or more required fields is missing in the Prior Tests tab."
Cancel = True
End If

If Me.PriorTestCount = 5 And (IsNull(Me.Test1) Or IsNull(Me.Test2) Or IsNull(Me.Test3) _
Or IsNull(Me.Test4) Or IsNull(Me.Test5)) Then
MsgBox "One or more required fields is missing in the Prior Tests tab."
Cancel = True
End If

If Me.Chest_Pain = "Yes" And IsNull(Me.Character_of_Chest_Pain) Then
MsgBox "One required value is missing for Symptoms."
Cancel = True
End If
End Sub

Any help is greatly appreciated.

Thx,
Jim

 

[bob fitz]

Validation is best done in the form's Before Update event.
If the validation fails, the update can be cancelled and the user prompted (with a message box) to correct the data.

 

[CJ_London]

not sure why you are having to make labels visible or not - if their parent is hidden then the child label will be as well.

rather than using a messagebox which can be tiresome for users, have a warning label appear on the form as a prompt - perhaps in bright red.

then in your form before update event you can loop through the controls with code something like this (not tested for tabs):

dim ctrl as control
 
warningLabel.caption=""
on error resume next 'labels do not have a value property
for each ctrl in me.controls
    if ctrl.visible=true and nz(ctrl.value,"")="" then
         warningLabel.caption="Field " & crtl.name & " must be completed"
         docmd.gotocontrol ctrl.name
         cancel=true
         exit sub
    end if
next

 

[jlee9562]

Hi Bob,

I tried placing the if then msgbox statements in the form's before update event. But it doesn't seem to do anything after I click on the Add New Record button (which I created using the form's macro wizard for adding a new record) if there are still missing values in some fields. In other words, when I clicked on the Add New Record button, it submits the data and creates a new record in the table but no messages pop up to tell me there are still some required fields that is missing data. Does the form's before update event control all fields before the entire form is updated?

I also want to set some fields to visible=false after submitting each new record. Should I put these statements in the form's before update event as well?

Thanks,
Jimmy

 

[jlee9562]

Hi CJ,

Is this the correct way to use your code for the field CTAlocation?

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim CTALocation As Control

warningLabel.Caption = "Some required fields are still empty."
On Error Resume Next 'labels do not have a value property
If Me.CTALocation.Visible = True And Nz(Me.CTALocation.Value, "") = "" Then
warningLabel.Caption = "Field " & CTALocation.Name & " must be completed"
DoCmd.GoToControl CTALocation.Name
Cancel = True
Exit Sub
End If

End Sub

After I hit submit record I get an runtime error 424.

 

[CJ_London]

No - compare with my example

This warningLabel.Caption = "Some required fields are still empty."
should be warningLabel.Caption = ""

And you are missing the loop (for each ctrl) so Access doesn't know which control you are referring to.

Also this does assume that all visible controls need to be tested, if you have controls which are visible and don't need to be tested you need to modify it slightly. I would suggest that you use the tag property to indicate testing required so modify the code

If Me.CTALocation.Visible = True And Nz(Me.CTALocation.Value, "") = ""

to

If Me.CTALocation.Visible = True AND CTALocation.Tag="Test" And Nz(Me.CTALocation.Value, "") = ""

 

[jlee9562]

Hi CJ,

Sorry, I'm not really getting it. I think this example may be too advanced for me. I've just been using Access for the last 3 weeks. Any chance you can tell me what's wrong with my original code?

Still getting an error with this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim CTALocation As Control

warningLabel.Caption = ""
On Error Resume Next 'labels do not have a value property
If Me.CTALocation.Visible = True And CTALocation.Tag = "Test" And Nz(Me.CTALocation.Value, "") = "" = "" Then
warningLabel.Caption = "Field " & CTALocation.Name & " must be completed"
DoCmd.GoToControl CTALocation.Name
Cancel = True
Exit Sub
End If

End Sub

 

[CJ_London]

Any chance you can tell me what's wrong with my original code?

Not really, without knowing what all your fields mean - I can tell you it is difficult to read. I don't know why you are hiding labels, I don't know why you exit the sub before applying the tests, I don't know why you are going to a new record before applying all your tests.

Using this bit of your code as an example and using in context of my code

If Me.Index_Imaging_Test = "CTA" And Me.ImagingTestResult = "Abnormal" And (IsNull(Me.CTALocation) Or IsNull(Me.CTAstenosis)) Then
MsgBox "One or more required fields in the Imaging Test tab is missing."
Cancel = True
ElseIf Me.Index_Imaging_Test <> "CTA" And Me.ImagingTestResult = "Abnormal" And (IsNull(Me.TestLocation1) Or IsNull(Me.TestLocation2) _
Or IsNull(Me.TestFinding)) Then
MsgBox "One or more required fields in the Imaging Test tab is missing."
Cancel = True
End If

I would put in the afterudate event of both Index_Imaging_Test ImagingTestResult the following code:

CTALocation.visible=Index_Imaging_Test ="CTA" and "ImagingTestResult"="Abnormal"
CTAstenosis.visible=Index_Imaging_Test ="CTA" and "ImagingTestResult"="Abnormal"
CTALocation.tag="Test"
CTAstenosis.tab="Test"

Still getting an error with this:

see below - you have not copied the bits in red correctly

on error resume next 'labels do not have a value property
[COLOR=red]for each ctrl in me.controls[/COLOR]
    If Me.CTALocation.Visible = True AND CTALocation.Tag="Test" And Nz(Me.CTALocation.Value, [COLOR=red]"") = "" Then[/COLOR]
         warningLabel.caption="Field " & crtl.name & " must be completed"
         docmd.gotocontrol ctrl.name
         cancel=true
         exit sub
    end if
[COLOR=red]next[/COLOR]

 

[jlee9562]

Hi CJ,

Can I send you my db? I have a form with multiple tabs with multiple fields on each tab. I want to hide certain labels and their fields depending on values of another field. For example, PCI and CABG is a type of revascularization technique. I have a field for revascularization with values Yes and No. If the user selects No, then PCI and CABG should both be hidden so that the user does not place anything into those fields.

The reason I am hiding the labels in the on click Add New Record button is because I want the form to appear the same for each new record. I had set the properties to visible=false for a lot of the labels and fields because they shouldn't appear unless a value is selected for a certain field. Then after I submit a new record the visibility does not reset to false. I hope I am making sense.

The on click Add New Record button seems to work if I just include the line docmd.gotorecord,,acnewrec but after adding the other lines of code it stops working again. I also can simply create the Add New Record button by going to the tool wizard and having Access create a AddNewRecord macro for me but I also want to be able to add code. I couldn't find a way to convert the macros to VBA.

I hope it's clearer now what I'd like to do! :\

Btw, I still get an error for this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim CTALocation As Control

warningLabel.Caption = ""
On Error Resume Next 'labels do not have a value property
For Each ctrl In Me.Controls
If Me.CTALocation.Visible = True And CTALocation.Tag = "Test" And Nz(Me.CTALocation.Value, "") = "" Then
warningLabel.Caption = "Field " & crtl.Name & " must be completed"
DoCmd.GoToControl ctrl.Name
Cancel = True
Exit Sub
End If
Next
End Sub

The line warningLabel.Caption="" turns to yellow with runtime error 424.

Thx,
Jim

 

[CJ_London]

The reason you are getting the error message is because you haven't created a label called warningLabel

I would fix this first and if it still doesn't make sense then send the db but replace data with test data and remove all tables/forms/queries/reports not relevant to the issue in hand. What I will do is provide some examples - you will need to follow them to complete the rest of your form.

Before sending the db also compact it and zip it

 

[jlee9562]

Hi CJ,

I think my db form/table layout and structure is just about set. I only have one table associated with it and everything is test data. There's just a couple things I would like to fix. 1) Force the users to enter data into all visible fields. 2) Fix any errors that arises.

I've created the label and named it WarningLabel but I don't understand the code so I'm not sure when it's supposed to run. I'm guessing after I hit the Add New Record button, right before the record gets updated. So, I tested the form with some dummy values and left out the CTAlocation field blank. When I hit the Add New Record button, an error pops up saying "You cant go to the specified record." and the warning label disappears with no message. It's supposed to say "Field CTALocation must be completed." right?

I also tried doing the same thing with a completely new database but this time with only a few fields. I got the same message when I left CTAlocation blank after clicking on the AddNewRecord button. So I'm not sure how to proceed. I've attached my db for your review

Many thanks,
Jim

 

[CJ_London]

thanks, I'll take a look later today

 

[CJ_London]

It took a bit longer than expected and I've had to make some changes:

1. I have only looked at the fields on the Co-Morbidities tab - my form is called Copy of frmPatientInfo, I have left yours untouched.

2. I stripped out nearly all of your code because it was giving issues

3. I've changed the names of your fields relating to Co-Morbidities because they had hyphens in them which can cause problems - have changed them to underscores (if you look in your existing db, you will see they are changed to underscores in the vba code automatically by Access) - there are still some fields to change

4. I've removed all required=yes from your table design, trying to control it partly there and partly from the form wasn't working

5. Just with regards the fields relating to morbidities, I have changed the field definition of allow zero length string to no - this provides more consistency with the code - you may well need to change it in your other fields. Normally it defaults to yes but then means it starts off as null and then becomes a zero length string if something is entered and then cleared.

6. I've added 'CheckifRequired' to the tag property for each control that needs to be completed - you will need to do the same for the other controls where it must be completed

7. There are primarily two bits of code to look at. First a sub called SetVisible which calls another sub called SetControl - see below

Private Sub SetControl(ThisCtrl As Control, LastCtrl As Control)
 
    ThisCtrl.Visible = Nz(LastCtrl.Value, "N/A") <> "N/A"
    If ThisCtrl.Visible = False Then ThisCtrl.Value = Null
 
End Sub
 
Private Sub SetVisible()
'Complete in a similar vein to the Co-Morbidities example
 
    'Symptoms
 
    'image testing
 
    'Co-Morbidities - this replaces the hundreds on lines you had before
    SetControl CM_2A, CM_1A
 
    SetControl CM_2B, CM_1B
    SetControl CM_3B, CM_2B
    SetControl CM_4B, CM_3B
    SetControl CM_5B, CM_4B
 
    'OCI or CABG
 
    'prior tests
 
End Sub

You will need to complete this for the controls in the other tabs. SetVisible is called from the afterupdate events of each control and means you only need to maintain the rules for what is visible or not in one place. It is also called on the form current event to set the form up initially

8. The other bit of code is in the before update event of the form - you will see it is similar to that which I provided earlier. I've also provided a bit of code to highlight the the control which needs completing

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control
    warninglabel = ""
    On Error Resume Next 'labels do not have a value property
 
    For Each ctrl In Me.Controls
        With ctrl
            If .Visible = True And .Tag = "CheckifCompleted" Then
                If Nz(.Value, "") = "" Then
                     warninglabel = "Field " & .Name & " must be completed"
                     Me("" & .Name).BorderColor = 255
                     Me("" & .Name).Controls(0).ForeColor = 255
 
                     DoCmd.GoToControl .Name
                     Cancel = True
                     Exit Sub
                Else
                    Me("" & .Name).BorderColor = Val("&HC0C0C0")
                    Me("" & .Name).Controls(0).ForeColor = vbBlack
                End If
            End If
        End With
    Next ctrl
    Exit Sub
 
End Sub

What you need to do now is to complete the SetVisible sub for the other tabs, add SetVisible to the relevant control afterupdate events and to tag the necessary controls with 'CheckifCompleted'. In your table design, set allow zero length string=no


I've attached the updated db - suggest you

• open it and open my form
• click on dummy to populate
• go to the morbidities tab and add something and leave something blank
• go to a different tab
• click on add record and you will be prompted to complete the field you have left blank
• try again and leave two items blank and see how you are prompted twice

One other tip - don't use lookups in tables - they only cause problems in the long run - have a lookup table with the contents and use a recordsource for the combo boxes. Much easier to maintain - if you need to add or change a value in the future you will have to do it in multiple locations at the moment whereas with a lookup table you only need to change in one place, once

 

[jlee9562]

Hi CJ,

Wow. Thank you for taking time out of your day to look over this. You are truly awesome. Now I really have some work ahead of me

This might take me some time to digest so here's a few questions regarding what you wrote:

ThisCtrl.Visible = Nz(LastCtrl.Value, "N/A") <> "N/A"

I'm stumped on this line. If we're taking CM-2A and CM-1A then CM-2A would be ThisCtrl and CM-1A would be LastCtrl. Nz() returns "N/A" to CM-2A if CM-1A is null. I thought ThisCtrl.visible only takes values of "Yes" or "No"? How can ThisCtrl.visible equal a value of LastCtrl? And what does the last "N/A" refer to?

For the tag properties: Do I simply go to the property sheet for each field and enter "CheckIfCompleted" in the tag line?

Re lookup tables: Do you mean to create an entirely new table and then link them with the main table?

BTW, did you forget to attach your db?

Many thanks,
Jim

 

[CJ_London]

ThisCtrl.visible only takes values of "Yes" or "No"?

It does - it equals the statement

Nz(LastCtrl.Value, "N/A") <> "N/A"

which is either true or false

For the tag properties: Do I simply go to the property sheet for each field and enter "CheckIfCompleted" in the tag line?

Yes - see the examples I did for CM_1A etc

Re lookup tables: Do you mean to create an entirely new table and then link them with the main table?

No - use them as a rowsource to your combo boxes. - I'll try to find time to do an example later

did you forget to attach your db?

Didn't think so but clearly did! - attaching to this post

 

[CJ_London]

use them as a rowsource to your combo boxes. - I'll try to find time to do an example later

Please see attached.

Look at the new table, tblLookups and at the rowsource and rowsource type for the controls on the morbidities tab. I've also removed the lookup elements in the table design for these fields

 

[jlee9562]

Hi CJ,

For SetControl Sub, how do I set conditions for fields on the other tabs? I mean I can't just write ThisCtrl.visible=[condition] since the comorbidities tab will also be affected by this. The other fields do not have values of "N/A" so I can't just add the SetControls for the other fields. It looks like I SetControl and SetVisible Sub are the general subs for all fields?

Thx,
Jim

 

[CJ_London]

What the formula is doing is checking for null. If it is null then it substitutes N/A, So providing your other controls do not use something different for N/A, such as 'Ignore' then it will still work.

What the formula is effectively saying is

If LastCtrl.Value is null or ="N/A" then thisctrl.visible=false

Give me an example where it is not working and I can modify the code accordingly

 

[jlee9562]

Hi CJ,

I tried doing similar thing for Symptoms tab but not working. For this tab I just want field Character_of_Chest_Pain to appear when Chest_Pain=Yes, but Character_of_Chest_Pain stays hidden.

First I set tags for Chest_Pain and Character_of_Chest_Pain to CheckifCompleted in property sheet. Then I added their field names and changed N/A to No in the code.

Code below(I had to type it in since my laptop where I do the work doesn't have internet):

Private Sub SetControl(ThisCtrl as Control, LastCtrl as Control)
Debug.Print ThisCtrl.Name
ThisCtrl.visible=Nz(LastCtrl.Value, "No") <> "No"
If ThisCtrl.Visible=False then ThisCtrl.Value=Null
End Sub

Private Sub SetVisible()
SetControl me.character_of_chest_pain, me.chest_pain
End Sub

I still don't understand the ThisCtrl.visible=Nz(LastCtrl.Value, "No") <> "No" line. For this example, how does Nz(LastCtrl.Value, "No") <> "No" resolve to false or true? LastCtrl is Chest_Pain so I interpret the line as: If Chest_Pain=null then set Chest_Pain to "No" then check if not equal to No. So if Chest_pain is null, then "No" is set and it checks whether "No" is not equal to "No"? No is equal to No so the line is False??

Is there a way to add No and N/A to the same line?

Thx,
Jim

 

[CJ_London]

you can try this:

ThisCtrl.visible=replace(Nz(LastCtrl.Value, "N/A"),"N/A","No") <> "No"

So if lastctrl.value is null, it is converted to N/A, otherwise it stays the same

So now we have the value is either N/A or something else

If it is N/A, it is replaced with No otherwise it stays the same.

Note at this point if lastctrl.value was something like "Carcogen/Air required" it will be converted to "Carcogenoir required", but it will still be not equal to No and the changed value is not saved anywhere, only used in the calculation.

in another control, (your latest example) we have either null, No or something else

If it is null, it is converted to N/A which is then replaced with No
If it is No, it not changed

So in summary, anything that is null, No or N/A is converted to No, otherwise it remains the same, and can then be compared with 'No'.

Let's say you have another set of controls where null or 'dead' (i.e. 'dead' is the equivalent of No or N/A) means the next control is not visible you can modify the above further to this

ThisCtrl.visible=replace(replace(Nz(LastCtrl.Value, "N/A"),"N/A","No"),"dead","No") <> "No"

Hope that all makes sense