Forcing user to enter data into certain fields

Hi CJ,

I think I'm in over my head trying to learn and manage your code. I think I should really go back to what I had before and try to fix that. That way I will know exactly what I'm doing. Plus, after trying your code again some of the fields didn't appear show up correctly. For example, when I select N/A for CM_3B both CM_4B and CM_5B appeared when it shouldn't. And when I leave Character_of_Chest_Pain empty the record was still submitted. Not to hate on your code, but I think it will take me too much time to make everything work again especially since everything has been stripped away. I had a lot of the things appear the way I wanted before. The only problem was I needed to prompt the user to enter something if a field is visible and missing.

I tried building another button (check if completed), so that when the user clicks on the button and if the field is visible and missing then a msgbox will come up saying one of the fields is missing in the tab. It seems easy enough but I'm getting an error. It says Test2Result4 cannot be found and I can't figure out why. I checked the spelling of the name and all but I just can't figure it out.

I can send my code later for this button. Would you be willing to fix this one problem?

Thx,
Jim
 
especially since everything has been stripped away
Click to expand...
Your code is still there but in the original form - in the db you sent me and the one I returned.

For example, when I select N/A for CM_3B both CM_4B and CM_5B appeared when it shouldn't
Click to expand...
Just tried it on the database I sent you - they don't which suggests you may not have copied the code correctly. Did you open the database I sent you and run the copy of the form? Or just copy the bit I posted in the thread (which is not all of it)

Would you be willing to fix this one problem?
Click to expand...
The problem is your code is 100's of lines long, I looked at it before and could not understand it - I'm sure you do - you wrote it, but I don't have time to trawl through all that code. Post by all means and if I can help and it only takes a few minutes, I will
 
Hi CJ,

Here is my code for the "No" and "N/A" values:
Private Sub SetControl(ThisCtrl As Control, LastCtrl As Control)

Debug.Print ThisCtrl.Name
ThisCtrl.Visible = Replace(Nz(LastCtrl.Value, "N/A"), "N/A", "No") <> "No"
If ThisCtrl.Visible = False Then ThisCtrl.Value = Null

End Sub
Private Sub SetVisible()
'Complete in a similar vein to the Co-Morbidities example

'Symptoms
SetControl Me.Character_of_Chest_Pain, Me.Chest_Pain

'image testing

'Co-Morbidities
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

I looked at it again and realized I had put in the wrong case for "NO". I fixed this and the Co_morbidities tab now seem to work but for the Symptoms tab when I leave Character_of_Chest_Pain blank it still submits the record. Also, the labels does not appear for Character_of_Chest_Pain.

If you can have a look, here is my db for my original code again. I just placed a bunch of if then statements in Private Sub CheckIfCompleted_Click(). I don't think there's anything wrong w/ the syntax but it's probably not the most efficient way to write it. Nevertheless, I don't know why it's giving me a "Compile Error: method or data member not found". Of course, I would rather just have these code in the Private Sub Submit_Record_click() but I figure it would be less prone to errors if there is less stuff in each Sub. So here's the process that got me the error. I go to a blank form and hit 'dummy' then go to 'Prior tests' tab and select 'Yes' and then '5' for how many tests performed. Then you have the option of filling stuff in. Even w/ the tests fields showing it immediately gives me an error for Test2Result4 when I hit the button 'CheckIfCompleted'. Actually, you don't even really need to select anything, it immediately jumps to that error when clicking on that button. Anyway, if you can fix this, I think most if not all my problems will be solved :)

Thx,
Jim
 

Attachments

for the Symptoms tab when I leave Character_of_Chest_Pain blank it still submits the record
Click to expand...
Just put this one into the db I sent you and it works OK. Did you remember to put 'CheckifCompleted' in the tag property?

an error for Test2Result4
Click to expand...
Looks like it is because it is null, if you wrap it in the nz function the error then moves on to another field - also null

With regards labels not showing, this seems to be down to your original code which has somehow negated the parent/child link. I deleted the Character_of_Chest_Pain control and dragged a new one on - and added the tag and it worked perfectly.

Here is a modification to the set Control code which will save you having to do that:

Code: 
    ThisCtrl.Visible = Nz(LastCtrl.Value, "N/A") <> "N/A"
    ThisCtrl.Controls(0).Visible = ThisCtrl.Visible
    If ThisCtrl.Visible = False Then ThisCtrl.Value = Null
 
In fact here is further update to the SetControl code which means you don't need to populate the tag of the controls you want to check.

Code: 
ThisCtrl.Visible = Nz(LastCtrl.Value, "N/A") <> "N/A"
ThisCtrl.Controls(0).Visible = ThisCtrl.Visible
If ThisCtrl.Visible = False Then ThisCtrl.Value = Null
ThisCtrl.Tag="CheckifCompleted"

So all you need to do now is to maintain the SetVisible procedure and call it from each control that requires another control to be visible
 
Hi CJ,

What should I add to your code to get the labels (box and labels) under Character_of_Chest_Pain to disappear and then appear only if Chest_Pain="Yes"?

I have a lot more conditions to set so I'm thinking the code is going to get complicated.

For the imaging test tab, I want CTAlocation and CTAstenosis to only appear if index_imaging_test="CTA" AND imagingtestresult="Abnormal". Similarly, I want TestLocation1, TestLocation2, and TestFinding to only appear if Index_imaging_test<>"CTA" AND imagingtestresult="Abnormal". How do add code for this?

Regarding the error for test2result4 on my original code, I had used the rowsource keywords to set the result fields to something depending on the name of the test (ie., me.test1). I couldn't figure any other way to do this. So, I'm not sure why the error is jumping to test2result4 because I used the AfterUpdate for each test1-test5 with the same code. Is there another way to code this without the rowsource?

Here's part of that code:

Private Sub Test3_AfterUpdate()
If (Me.Test3 = "Stress Echo") Or (Me.Test3 = "Stress SPECT") Or (Me.Test3 = "Stress PET") Or (Me.Test3 = "Stress MRI") Then
Me.Test3_Date = ""
Me.Test3_Prior = ""
Me.Test3Change = ""
Me.Test3Result1 = ""
Me.Test3Result2 = ""
Me.Test3Result3 = ""
Me.Test3Result4 = ""
Me.Test3Result5 = ""
Me.Test3Result2.RowSource = "Anterior;Inferior;Anterior Septal;Posterior;Inferior Septal;Lateral"
Me.Test3Result3.RowSource = "Base;Mid;Distal;Apical"
Me.Test3Result4.RowSource = "Ischemia only;Ischemia+Infarct;Infarct only;Normal"
Me.Test3Result5.Visible = False
Me.Test3Result4.Visible = True
End If

If Me.Test3 = "Stress ECG" Then
Me.Test3_Date = ""
Me.Test3_Prior = ""
Me.Test3Change = ""
Me.Test3Result1 = ""
Me.Test3Result2 = ""
Me.Test3Result3 = ""
Me.Test3Result4 = ""
Me.Test3Result5 = ""
Me.Test3Result2.RowSource = "Anterior;Inferior;Posterior;Septal;Lateral"
Me.Test3Result3.RowSource = "Upsloping;Horizontal;Downsloping"
Me.Test3Result4.RowSource = "ST Depression;ST Elevation"
Me.Test3Result5.RowSource = ">=1 mm;>=2 mm"
Me.Test3Result4.Visible = True
Me.Test3Result5.Visible = True
End If

If Me.Test3 = "CTA" Then
Me.Test3_Date = ""
Me.Test3_Prior = ""
Me.Test3Change = ""
Me.Test3Result1 = ""
Me.Test3Result2 = ""
Me.Test3Result3 = ""
Me.Test3Result4 = ""
Me.Test3Result5 = ""
Me.Test3Result2.RowSource = "LM;LAD;Diag;LCx;OM;RCA;PDA;PL;LIMA;RIMA;SVG"
Me.Test3Result3.RowSource = "0%;<50%;50-69%;70-99%;100%"
Me.Test3Result4.Visible = False
Me.Test3Result5.Visible = False
End If
Me.Test3_Date.SetFocus
End Sub

Thx,
Jim
 
What should I add to your code to get the labels (box and labels) under Character_of_Chest_Pain to disappear and then appear only if Chest_Pain="Yes"?
Click to expand...
Since the options are yes or no it will work as is

For the imaging test tab, I want CTAlocation and CTAstenosis to only appear if index_imaging_test="CTA" AND imagingtestresult="Abnormal".
Click to expand...
OK - that doesn't fit with the code as is

Similarly, I want TestLocation1, TestLocation2, and TestFinding to only appear if Index_imaging_test<>"CTA" AND imagingtestresult="Abnormal"
Click to expand...
and nor does this

But you can modify the code slightly for an exception - this is the revised code - I've highlighted the changes in red - including the call for setcontrol

Code: 
Private Sub SetControl(ThisCtrl As Control, LastCtrl As Control[COLOR=red], Optional Process As Boolean = True[/COLOR])
 
    ThisCtrl.Visible = [COLOR=red]Replace([/COLOR]Replace(Nz(LastCtrl.Value, "N/A"), "N/A", "No"), [COLOR=red]"Normal", "No")[/COLOR] <> "No" [COLOR=red]And Process = True[/COLOR]
    
    ThisCtrl.Controls(0).Visible = ThisCtrl.Visible
    If ThisCtrl.Visible = False Then ThisCtrl.Value = Null
    ThisCtrl.Tag = "CheckifCompleted"
End Sub
Private Sub SetVisible()
'Complete in a similar vein to the Co-Morbidities example
 
    'Symptoms
    SetControl Me.Character_of_Chest_Pain, Me.Chest_Pain
 
    'image testing
    SetControl CTALocation, ImagingTestResult[COLOR=red], [index imaging test] = "CTA"[/COLOR]
    SetControl CTAstenosis, ImagingTestResult[COLOR=red], [index imaging test] = "CTA"[/COLOR]
 
    SetControl TestLocation1, ImagingTestResult[COLOR=red], [index imaging test] <> "CTA"[/COLOR]
    SetControl TestLocation2, ImagingTestResult[COLOR=red],[/COLOR] [COLOR=red][index imaging test] <> "CTA"[/COLOR]
    SetControl TestFinding, ImagingTestResult[COLOR=red], [index imaging test] <> "CTA"[/COLOR]
    
    'Co-Morbidities
    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
 
Don't forget to add the call to SetVisible to the ImagingTestResult control, and if you want to also the index imaging test control
 
Sorry, picked up a small bug - you need to wrap index imaging test with nz because it can be null e.g.

Code: 
SetControl CTALocation, ImagingTestResult, Nz([index imaging test]) = "CTA"
 
Hi CJ,

Thanks. I got your code to work. But I am still having trouble with the label under character_of_chest_pain. I copied your code as is but it is always visible. The label was created from a textbox and does not have a related entry field. I wonder if this has anything to do with the problem. Db attached.

I would also like to move on to the PCI or CABG tab. Here's my go at the Private Sub SetVisible(). I have no idea how to amend the Private Sub SetControl() code. So, I want if revascularization=Yes and PCI_Count=1 then show the row for PCI1. if Revascularization=Yes and PCI_Count=2 then show the row for PCI1 and PCI2, etc. If revascularization=No then show none of the below fields. Similarly, for CABG, if revacularization=Yes and CABG=1 then show row for CABG1, etc. I suspect I would also need to setVisible for the afterUpdate subs for those fields in the PCI/cABG tab, so I did that.

'PCI or CABG
SetControl Me.PCI_Date1, Me.PCI_Count
SetControl Me.PCI_Location1, Me.PCI_Count
SetControl Me.PTCAorStent1, Me.PCI_Count
SetControl Me.PCI_Type1, Me.PCI_Count
SetControl Me.Size1, Me.PCI_Count

SetControl Me.PCI_Location2, Nz(Me.PCI_Count) < 3
SetControl Me.PCI_Date2, Nz(Me.PCI_Count) < 3
SetControl Me.PTCAorStent2, Nz(Me.PCI_Count) < 3
SetControl Me.PCI_Type2, Nz(Me.PCI_Count) < 3
SetControl Me.Size2, Nz(Me.PCI_Count) < 3

SetControl Me.PCI_Date3, Nz(Me.PCI_Count) < 4
SetControl Me.PCI_Location3, Nz(Me.PCI_Count) < 4
SetControl Me.PTCAorStent3, Nz(Me.PCI_Count) < 4
SetControl Me.PCI_Type3, Nz(Me.PCI_Count) < 4
SetControl Me.Size3, Nz(Me.PCI_Count) < 4

SetControl Me.PCI_Date4, Nz(Me.PCI_Count) < 5
SetControl Me.PCI_Location4, Nz(Me.PCI_Count) < 5
SetControl Me.PTCAorStent4, Nz(Me.PCI_Count) < 5
SetControl Me.PCI_Type4, Nz(Me.PCI_Count) < 5
SetControl Me.Size4, Nz(Me.PCI_Count) < 5

SetControl Me.CABG_Date1, Me.CABG_Count
SetControl Me.CABG_Location1, Me.CABG_Count
SetControl Me.CABG_Type1, Me.CABG_Count
SetControl Me.Complete_Revascularization1, Me.CABG_Count

SetControl Me.CABG_Date2, Nz(Me.CABG_Count) < 3
SetControl Me.CABG_Location2, Nz(Me.CABG_Count) < 3
SetControl Me.CABG_Type2, Nz(Me.CABG_Count) < 3
SetControl Me.Complete_Revascularization2, Nz(Me.CABG_Count) < 3


Thx,
Jim
 

Attachments

Bit of a long response but if you follow it it solves the problem
The label was created from a textbox and does not have a related entry field.
Click to expand...
The simplest solution is to delete the control and label then add it again. The alternative is to cut the label (select then hit ctrl-x) then select the control and paste (ctrl-v).

I want if revascularization=Yes and PCI_Count=1 then show the row for PCI1
Click to expand...
You've almost got it right
Code: 
SetControl PCI_Date1, Revascularization, Nz(PCI_Count, 5) <= 1
SetControl PCI_Location1, Revascularization, Nz(PCI_Count, 5) <= 1
SetControl PTCAorStent1, Revascularization, Nz(PCI_Count, 5) <= 1
SetControl PCI_Type1, Revascularization, Nz(PCI_Count, 5) <= 1
SetControl Size1, Revascularization, Nz(PCI_Count, 5) <= 1
 
SetControl PCI_Date2, Revascularization, Nz(PCI_Count, 5) <= 2
SetControl PCI_Location2, Revascularization, Nz(PCI_Count, 5) <= 2
SetControl PTCAorStent2, Revascularization, Nz(PCI_Count, 5) <= 2
SetControl PCI_Type2, Revascularization, Nz(PCI_Count, 5) <= 2
SetControl Size2, Revascularization, Nz(PCI_Count, 5) <= 2
 
etc
 
SetControl Me.CABG_Date1, Revascularization, Nz(CABG_Count, 3) <= 1
SetControl Me.CABG_Location1, Revascularization, Nz(CABG_Count, 3) <= 1
SetControl Me.CABG_Type1, Revascularization, Nz(CABG_Count, 3) <= 1
SetControl Me.Complete_Revascularization, Revascularization, Nz(CABG_Count, 3) <= 1
 
etc
or if your really want to keep it tight you just need this
Code: 
for i=1 to 4
    SetControl me("PCI_Date" & i), Revascularization, Nz(PCI_Count, 5) <= i
    SetControl me("PCI_Location" & i),  Revascularization, Nz(PCI_Count, 5) <= i
    SetControl me("PTCAorStent" & i), Revascularization, Nz(PCI_Count, 5) <= i
    SetControl me("PCI_Type" & i), Revascularization, Nz(PCI_Count, 5) <= i
    SetControl me("Size" & i), Revascularization, Nz(PCI_Count, 5) <= i
next i
 
for i=1 to 2
    SetControl Me("CABG_Date" & i), Revascularization, Nz(CABG_Count, 3) <= i
    SetControl Me("CABG_Location" & i), Revascularization, Nz(CABG_Count, 3) <= i
    SetControl Me("CABG_Type" & i), Revascularization, Nz(CABG_Count, 3) <= i
    SetControl Me("Complete_Revascularization" & i), Revascularization, Nz(CABG_Count, 3) <= i
next i

You may need to declare i (Dim i as integer) - recommend you do, it is good practice

Also, there are a couple of small problems in showing the labels, so I would associate the labels down the side with the first column controls (PCI_Date etc) and put the following in the SetVisible procedure below the above code- a slight cheat!

Code: 
label454.visible=PCI_Date1.visible
label453.visible=PCI_Location1.visible
etc
although it would be clearer to rename your labels as PCILbl1, PCILbl2 etc!

This also means there are no associated labels for a number of the controls - all those that are not in the first column. So there is a small modification required to the SetControl procedure - you just need to add

On Error Resume Next

as the first line of the code so ths procedure now looks like this

Code: 
Private Sub SetControl(ThisCtrl As Control, LastCtrl As Control, Optional Process As Boolean = True)
 
    On Error Resume Next 
    ThisCtrl.Visible = Replace(Replace(Nz(LastCtrl.Value, "N/A"), "N/A", "No"), "Normal", "No") <> "No" And Process = True
 
    ThisCtrl.Controls(0).Visible = ThisCtrl.Visible ' Possible error here if there is not an associated label
    If ThisCtrl.Visible = False Then ThisCtrl.Value = Null
    ThisCtrl.Tag = "CheckifCompleted"
End Sub
 
I have no idea how to amend the Private Sub SetControl() code.
Click to expand...
Just noticed this - all you need to do is copy and paste it from my post - but now copy from my last post!
 
Just realised your PCI_Count can be 0 (which is not null) so you need to modify your code in SetVisible slightly. You will also probably noticed I hadn't set PCI_Count to be visible so here is the revised code. Also PCI_Count in the table is text whereas I thought it was numeric- I'm assuming you want to leave it as text. Just copy and paste into the SetVisible procedure

So here is the rvised code - it will be similar for CABG Just copy and paste into the SetVisible procedure
Code: 
     'OCI or CABG
    SetControl PCI_Count, Revascularization
    For i = 1 To 4
        SetControl Me("PCI_Date" & i), Revascularization, Val(Nz(PCI_Count, 0)) >= i And Val(Nz(PCI_Count, 0)) > 0
        SetControl Me("PCI_Location" & i), Revascularization, Val(Nz(PCI_Count, 0)) >= i And Val(Nz(PCI_Count, 0)) > 0
        SetControl Me("PTCAorStent" & i), Revascularization, Val(Nz(PCI_Count, 0)) >= i And Val(Nz(PCI_Count, 0)) > 0
        SetControl Me("PCI_Type" & i), Revascularization, Val(Nz(PCI_Count, 0)) >= i And Val(Nz(PCI_Count, 0)) > 0
        SetControl Me("Size" & i), Revascularization, Val(Nz(PCI_Count, 0)) >= i And Val(Nz(PCI_Count, 0)) > 0
    Next i
 
    SetControl CABG_Count, Revascularization
 
Hi CJ,

Sorry, it's not working for me. For the label under character_of_chest_pain, I selected the labels (box335, label307, label332, label333, label334) then Ctrl-X, then select character_of_chest_pain control, then Ctrl-V. Yes, it did paste it but when I go back to the form the labels are still visible at the start. I also tried deleting the character_of_chest_pain control and adding it back but still same effect.

For the PCI/CABG tab, is the code below correct? After clicking Yes for revascularization, nothing appears. I assume I don't have to set the tags since it's already set in the code.

Private Sub SetControl(ThisCtrl As Control, LastCtrl As Control, Optional Process As Boolean = True)

On Error Resume Next
ThisCtrl.Visible = Replace(Replace(Nz(LastCtrl.Value, "N/A"), "N/A", "No"), "Normal", "No") <> "No" And Process = True

ThisCtrl.Controls(0).Visible = ThisCtrl.Visible ' Possible error here if there is not an associated label
If ThisCtrl.Visible = False Then ThisCtrl.Value = Null
ThisCtrl.Tag = "CheckifCompleted"
End Sub

Private Sub SetVisible()
'Complete in a similar vein to the Co-Morbidities example

'Symptoms
SetControl Me.Character_of_Chest_Pain, Me.Chest_Pain

'image testing
SetControl CTALocation, ImagingTestResult, Nz([index imaging test]) = "CTA"
SetControl CTAstenosis, ImagingTestResult, Nz([index imaging test]) = "CTA"

SetControl TestLocation1, ImagingTestResult, Nz([index imaging test]) <> "CTA"
SetControl TestLocation2, ImagingTestResult, Nz([index imaging test]) <> "CTA"
SetControl TestFinding, ImagingTestResult, Nz([index imaging test]) <> "CTA"

'Co-Morbidities
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

'PCI or CABG
SetControl PCI_Count, Revascularization
For i = 1 To 4
SetControl Me("PCI_Date" & i), Revascularization, Val(Nz(PCI_Count, 0)) >= i And Val(Nz(PCI_Count, 0)) > 0
SetControl Me("PCI_Location" & i), Revascularization, Val(Nz(PCI_Count, 0)) >= i And Val(Nz(PCI_Count, 0)) > 0
SetControl Me("PTCAorStent" & i), Revascularization, Val(Nz(PCI_Count, 0)) >= i And Val(Nz(PCI_Count, 0)) > 0
SetControl Me("PCI_Type" & i), Revascularization, Val(Nz(PCI_Count, 0)) >= i And Val(Nz(PCI_Count, 0)) > 0
SetControl Me("Size" & i), Revascularization, Val(Nz(PCI_Count, 0)) >= i And Val(Nz(PCI_Count, 0)) > 0
Next i

SetControl CABG_Count, Revascularization
For i = 1 To 2
SetControl Me("CABG_Date" & i), Revascularization, Val(Nz(CABG_Count, 0)) >= i And Val(Nz(CABG_Count, 0)) > 0
SetControl Me("CABG_Location" & i), Revascularization, Val(Nz(CABG_Count, 0)) >= i And Val(Nz(CABG_Count, 0)) > 0
SetControl Me("CABG_Type" & i), Revascularization, Val(Nz(CABG_Count, 0)) >= i And Val(Nz(CABG_Count, 0)) > 0
SetControl Me("Complete_Revascularization" & i), Revascularization, Val(Nz(CABG_Count, 0)) >= i And Val(Nz(CABG_Count, 0)) > 0
Next i

Label454.Visible = PCI_Date1.Visible
Label453.Visible = PCI_Location1.Visible
Label456.Visible = PTCAorStent1.Visible
Label455.Visible = PCI_Type1.Visible
Label457.Visible = Size1.Visible

'prior tests

End Sub

Thx,
Jim
 
Hi Jim,

Please can you learn how to use the code tags - it's difficult to read when you don't use them - code tags preserve spacing and indentation. To use them go to the advanced editor and you will see the # button. Highlight the text and then click on the # button.

I selected the labels (box335, label307, label332, label333, label334) then Ctrl-X, then select character_of_chest_pain control, then Ctrl-V.
Click to expand...
You can have only one label per control - and a box is not a label so this won't work. You will need to use:

Code: 
box355.visible=[Character of Chest Pain].visible
label307.visible=[Character of Chest Pain].visible
etc

After clicking Yes for revascularization, nothing appears
Click to expand...
The code looks correct - I'm building it on the db you sent me so providing you haven't changed names it shoudl work. What should appear is PCI Count and CABG Count - did you put setvisible in the revasculation after update event? You will also need to put it in the PC_Count and CABG_Count event

You'll also need to set the visible properties for the CABG columns as you have for the PCI columns
 
Hi CJ,

Thanks! I finally got the labels to work. I love the cheat of setting the label visible if the control is also visible. I also got the PCI/CABG tab to work. I did forget to put setVisible for the revascularization afterUpdate().

Now on to the Prior Tests tab! I think I got the layout working from your code for the PCI/CABG tab. But there's additional conditions, for example, if test1="CTA" then I want test1result3 and test1result4 to visible=false because CTA only should have 3 result fields. Also, if the test is CTA, then the values selected for result fields 1-3 will have different values than if the test were something else. If the test is Stress ECG, then all 5 result fields should be shown. But if they are the other tests, then they should have 4 result fields in addition to the different values in the fields for those tests (see if then statement in code below for test1_afterUpdate()).

Sorry for not using the code tag. Here are my codes below.

Code: 
Private Sub SetControl(ThisCtrl As Control, LastCtrl As Control, Optional Process As Boolean = True)
 
    On Error Resume Next
    ThisCtrl.Visible = Replace(Replace(Nz(LastCtrl.Value, "N/A"), "N/A", "No"), "Normal", "No") <> "No" And Process = True
 
    ThisCtrl.Controls(0).Visible = ThisCtrl.Visible ' Possible error here if there is not an associated label
    If ThisCtrl.Visible = False Then ThisCtrl.Value = Null
    ThisCtrl.Tag = "CheckifCompleted"
End Sub

Private Sub SetVisible()
'Complete in a similar vein to the Co-Morbidities example
 
    'Symptoms
    SetControl Me.Character_of_Chest_Pain, Me.Chest_Pain
    Box335.Visible = Me.Character_of_Chest_Pain.Visible
    Label307.Visible = Me.Character_of_Chest_Pain.Visible
    Label332.Visible = Me.Character_of_Chest_Pain.Visible
    Label333.Visible = Me.Character_of_Chest_Pain.Visible
    Label334.Visible = Me.Character_of_Chest_Pain.Visible
 
    'image testing
    SetControl CTALocation, ImagingTestResult, Nz([index imaging test]) = "CTA"
    SetControl CTAstenosis, ImagingTestResult, Nz([index imaging test]) = "CTA"
 
    SetControl TestLocation1, ImagingTestResult, Nz([index imaging test]) <> "CTA"
    SetControl TestLocation2, ImagingTestResult, Nz([index imaging test]) <> "CTA"
    SetControl TestFinding, ImagingTestResult, Nz([index imaging test]) <> "CTA"
    
    'Co-Morbidities
    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
 
    'PCI or CABG
    SetControl PCI_Count, Revascularization
    For i = 1 To 4
        SetControl Me("PCI_Date" & i), Revascularization, Val(Nz(PCI_Count, 0)) >= i And Val(Nz(PCI_Count, 0)) > 0
        SetControl Me("PCI_Location" & i), Revascularization, Val(Nz(PCI_Count, 0)) >= i And Val(Nz(PCI_Count, 0)) > 0
        SetControl Me("PTCAorStent" & i), Revascularization, Val(Nz(PCI_Count, 0)) >= i And Val(Nz(PCI_Count, 0)) > 0
        SetControl Me("PCI_Type" & i), Revascularization, Val(Nz(PCI_Count, 0)) >= i And Val(Nz(PCI_Count, 0)) > 0
        SetControl Me("Size" & i), Revascularization, Val(Nz(PCI_Count, 0)) >= i And Val(Nz(PCI_Count, 0)) > 0
    Next i
 
    SetControl CABG_Count, Revascularization
    For i = 1 To 2
    SetControl Me("CABG_Date" & i), Revascularization, Val(Nz(CABG_Count, 0)) >= i And Val(Nz(CABG_Count, 0)) > 0
    SetControl Me("CABG_Location" & i), Revascularization, Val(Nz(CABG_Count, 0)) >= i And Val(Nz(CABG_Count, 0)) > 0
    SetControl Me("CABG_Type" & i), Revascularization, Val(Nz(CABG_Count, 0)) >= i And Val(Nz(CABG_Count, 0)) > 0
    SetControl Me("Complete_Revascularization" & i), Revascularization, Val(Nz(CABG_Count, 0)) >= i And Val(Nz(CABG_Count, 0)) > 0
    Next i

    Label74.Visible = Me.PCI_Count.Visible
    Label77.Visible = Me.CABG_Count.Visible
    Label454.Visible = PCI_Date1.Visible
    Label453.Visible = PCI_Location1.Visible
    Label456.Visible = PTCAorStent1.Visible
    Label455.Visible = PCI_Type1.Visible
    Label457.Visible = Size1.Visible
    Label452.Visible = Me.PCI_Date1.Visible
    Label458.Visible = Me.PCI_Date2.Visible
    Label459.Visible = Me.PCI_Date3.Visible
    Label460.Visible = Me.PCI_Date4.Visible
    Label491.Visible = Me.CABG_Date1.Visible
    Label490.Visible = Me.CABG_Date1.Visible
    Label492.Visible = Me.CABG_Date2.Visible

    'prior tests
    SetControl Me.PriorTestCount, Me.Prior_Noninvasive_Stress_Test
    For i = 1 To 5
        SetControl Me("Test" & i), Me.PriorTestCount, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & " Date"), Me.PriorTestCount, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & " Prior"), Me.PriorTestCount, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result1"), Me.PriorTestCount, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result2"), Me.PriorTestCount, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result3"), Me.PriorTestCount, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result4"), Me.PriorTestCount, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result5"), Me.PriorTestCount, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Change"), Me.PriorTestCount, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
    Next i
     
    Box349.Visible = Me.Test1.Visible
    Label238.Visible = Me.Test1.Visible
    Label239.Visible = Me.Test1.Visible
    Label240.Visible = Me.Test1_Date.Visible
    Label241.Visible = Me.Test1_Prior.Visible
    Label510.Visible = Me.Test1Result1.Visible
    Label555.Visible = Me.Test1Result2.Visible
    Label557.Visible = Me.Test1Result3.Visible
    Label558.Visible = Me.Test1Result4.Visible
    Label559.Visible = Me.Test1Result5.Visible
    Label525.Visible = Me.Test1Change.Visible
    
End Sub

Code: 
Private Sub Test1_AfterUpdate()
If (Me.Test1 = "Stress Echo") Or (Me.Test1 = "Stress SPECT") Or (Me.Test1 = "Stress PET") Or (Me.Test1 = "Stress MRI") Then
        Me.Test1Result2.RowSource = "Anterior;Inferior;Anterior Septal;Posterior;Inferior Septal;Lateral"
        Me.Test1Result3.RowSource = "Base;Mid;Distal;Apical"
        Me.Test1Result4.RowSource = "Ischemia only;Ischemia+Infarct;Infarct only;Normal"
        Me.Test1Result5.Visible = False
        Me.Test1Result4.Visible = True
    End If
    
    If Me.Test1 = "Stress ECG" Then
        Me.Test1Result2.RowSource = "Anterior;Inferior;Posterior;Septal;Lateral"
        Me.Test1Result3.RowSource = "Upsloping;Horizontal;Downsloping"
        Me.Test1Result4.RowSource = "ST Depression;ST Elevation"
        Me.Test1Result5.RowSource = ">=1 mm;>=2 mm"
        Me.Test1Result4.Visible = True
        Me.Test1Result5.Visible = True
    End If
        
    If Me.Test1 = "CTA" Then
        Me.Test1Result2.RowSource = "LM;LAD;Diag;LCx;OM;RCA;PDA;PL;LIMA;RIMA;SVG"
        Me.Test1Result3.RowSource = "0%;<50%;50-69%;70-99%;100%"
        Me.Test1Result4.Visible = False
        Me.Test1Result5.Visible = False
    End If
End Sub

So, I think for the most part, my test1_afterupdate() code is working. The problem is, after selecting CTA for test1 I see the 3 fields that should be shown, but once I select a value for another field in the row, result fields 4 and 5 is visible again. I'm guessing the code for the setvisible() has to be fixed?

Thx,
Jim
 
Almost right - it should be:
Code: 
SetControl Me("Test" & i), [COLOR=red]Prior_Noninvasive_Stress_Test[/COLOR], Val(Nz(PriorTestCount, 0)) >= i And Val(Nz(PriorTestCount, 0)) > 0
To check for No, N/A, etc

To get the visibles right, you need to modify the code for results 4 & 5 as follows:
Code: 
SetControl Me("Test" & i & "Result4"), Prior_Noninvasive_Stress_Test, Val(Nz(PriorTestCount, 0)) >= i And Val(Nz(PriorTestCount, 0)) > 0 [COLOR=red]And Nz(Me("Test" & i)) <> "CTA"[/COLOR]
SetControl Me("Test" & i & "Result5"), Prior_Noninvasive_Stress_Test, Val(Nz(PriorTestCount, 0)) >= i And Val(Nz(PriorTestCount, 0)) > 0 [COLOR=red]And Nz(Me("Test" & i)) = "Stress ECG"[/COLOR]
Don't forget to put setvisible in the after udate of each of your Test controls

With regards the rowsources I'll post separately - need to reboot my pc!
 
For the rowsources, put the following in the SetVisible procedure below all the setcontrol code for prior tests. I note that rowsource for test2 is the same as test1 and also there is a CTA for the other rows.

Code: 
Select Case Me("Test" & i)
    Case "Stress ECG"
           Me("Test" & i & "Result2").RowSource = "Anterior;Inferior;Posterior;Septal;Lateral"
           Me("Test" & i & "Result3").RowSource = "Upsloping;Horizontal;Downsloping"
           Me("Test" & i & "Result4").RowSource = "ST Depression;ST Elevation"
    Case "CTA"
           Me("Test" & i & "Result2").RowSource = "LM;LAD;Diag;LCx;OM;RCA;PDA;PL;LIMA;RIMA;SVG"
           Me("Test" & i & "Result3").RowSource = "0%;<50%;50-69%;70-99%;100%"
    Case Else
           Me("Test" & i & "Result2").RowSource = "Anterior;Inferior;Anterior Septal;Posterior;Inferior Septal;Lateral"
           Me("Test" & i & "Result3").RowSource = "Base;Mid;Distal;Apical"
           Me("Test" & i & "Result4").RowSource = "Ischemia only;Ischemia+Infarct;Infarct only;Normal"
End Select
This will set rowsource to test rows that aren't visible, but it doesn't matter since they can't be accessed by the user when they are not visible. If you only want to set it for the first row (Test1) then you can keep your original code but remove the =visible lines - or replace that code with the above code which is a bit more efficient.

I'm now away from email for a few days, so you are on your own for now. Hopefully all issues are resolved - the important thing is the SetControl function is used to set visible/not visible and the tag for all controls you want to check before updating the record
 
Hi CJ,

I think I got everything working now. Thanks for all of your help!! Can I keep you in the loop in case I have more questions later?

Thx,
Jim
 
Hi CJ,

I have one other minor issue with the appearance of the program. For the Prior tests tab, when I select a number for PriorTestsCount, the result5 field does not become visible. I'm guessing it's the way the SetControls are set up for Result5 under the SetVisible Sub? Does the Result4 field show because an empty value is the same as <> "CTA"?

Code: 
SetControl Me("Test" & i & "Result4"), Prior_Noninvasive_Stress_Test, Val(Nz(PriorTestCount, 0)) >= i And Val(Nz(PriorTestCount, 0)) > 0 And Nz(Me("Test" & i)) <> "CTA"
SetControl Me("Test" & i & "Result5"), Prior_Noninvasive_Stress_Test, Val(Nz(PriorTestCount, 0)) >= i And Val(Nz(PriorTestCount, 0)) > 0 And Nz(Me("Test" & i)) = "Stress ECG"

How to tweak this so that all result fields are shown before selecting the type of test?

Thx,
Jim
 
You must log in or register to reply here.

Similar threads

A
Solved Referring to a control on a subform from the parent form.
Replies
3
Views
389
AlliCarr
A
D
Solved Unbound form required using VBA
Replies
9
Views
674
Pat Hartman
P
T
Solved vbinformation
Replies
15
Views
498
isladogs
isladogs
Mike Krailo
Solved Need help with before update code logic?
Replies
12
Views
412
Mike Krailo
Mike Krailo
V
Need help with VBA to check for required fields
Replies
6
Views
422
MajP
MajP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom