Forcing user to enter data into certain fields

now back from my travels - suggest instead that setvalues shows all the fields (i.e. remove the <>CTA, =Stress ECG from the condition) and put the call to setcontrol in the test update event instead - although I think you are starting to overthink the way the form works:)
 
Hi CJ,

Welcome back! Hope you had a nice time traveling. I certainly am overthinking, or maybe just not thinking enough for this problem! Here is what I have. It seems to work after selecting the first test. Say, if I select "CTA" for test1, test1result4 and test1result5 is not visible. But after I select a second test, test1result4 and test1result5 are visible again. Not sure what is going on. I took away the conditions <>"CTA" and ="Stress Echo" on the SetVisible Sub to make all fields visible. Since I took those conditions away I figure I need to add them again which I did. So taking your suggestion, for each test's afterUpdate Sub() I added the original lines before I took away <>"CTA" and ="Stress Echo". Code below:

Code: 
 'prior tests
    SetControl Me.PriorTestCount, Me.Prior_Noninvasive_Stress_Test
    
    For i = 1 To 5
        SetControl Me("Test" & i), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & " Date"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & " Prior"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result1"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result2"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result3"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result4"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result5"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Change"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
     
        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"
               Me("Test" & i & "Result5").RowSource = ">=1 mm;>=2 mm"
        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
    
    Next i

Code: 
Private Sub Test1_AfterUpdate()
    SetVisible
    SetControl Me("Test1" & "Result4"), Prior_Noninvasive_Stress_Test, Val(Nz(PriorTestCount, 0)) >= i And Val(Nz(PriorTestCount, 0)) > 0 And Nz(Me("Test1")) <> "CTA"
    SetControl Me("Test1" & "Result5"), Prior_Noninvasive_Stress_Test, Val(Nz(PriorTestCount, 0)) >= i And Val(Nz(PriorTestCount, 0)) > 0 And Nz(Me("Test1")) = "Stress ECG"
End Sub

Private Sub Test2_AfterUpdate()
    SetVisible
    SetControl Me("Test2" & "Result4"), Prior_Noninvasive_Stress_Test, Val(Nz(PriorTestCount, 0)) >= i And Val(Nz(PriorTestCount, 0)) > 0 And Nz(Me("Test2")) <> "CTA"
    SetControl Me("Test2" & "Result5"), Prior_Noninvasive_Stress_Test, Val(Nz(PriorTestCount, 0)) >= i And Val(Nz(PriorTestCount, 0)) > 0 And Nz(Me("Test2")) = "Stress ECG"
End Sub

...
 
you need to remove the following in the after update event (you are not in an 'i' loop and not testing for which row you are in)

Val(Nz(PriorTestCount, 0)) >= i And Val(Nz(PriorTestCount, 0)) > 0 And
Click to expand...
- just leave

Code: 
Nz(Me("Test1")) <> "CTA"

The way you have this set will only apply to the first two rows - is this correct? - what about the CTA in rows 3-5?
 
Hi CJ,

I did also add the afterUpdate subs to test3-test5 (I tried to indicate it with the ellipsis after the afterupdates for test1 and test2!) After removing the line you suggested, I still get the visibility issue. For instance, after selecting "CTA" for test1 results4 and results5 disappear correctly, but when i go on to test2 and select a different test, the results4 and results5 for test1 appear again in the row for test1. The same thing happen for the following rows.

Code: 
'prior tests
    SetControl Me.PriorTestCount, Me.Prior_Noninvasive_Stress_Test
    
    For i = 1 To 5
        SetControl Me("Test" & i), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & " Date"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & " Prior"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result1"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result2"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result3"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result4"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result5"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Change"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
     
        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"
               Me("Test" & i & "Result5").RowSource = ">=1 mm;>=2 mm"
        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
    
    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.Test1.Visible
    Label559.Visible = Me.Test1.Visible
    Label525.Visible = Me.Test1Change.Visible

Code: 
Private Sub Test1_AfterUpdate()
    SetVisible
    SetControl Me("Test1" & "Result4"), Prior_Noninvasive_Stress_Test, Nz(Me("Test1")) <> "CTA"
    SetControl Me("Test1" & "Result5"), Prior_Noninvasive_Stress_Test, Nz(Me("Test1")) = "Stress ECG"
End Sub

Private Sub Test2_AfterUpdate()
    SetVisible
    SetControl Me("Test2" & "Result4"), Prior_Noninvasive_Stress_Test, Nz(Me("Test2")) <> "CTA"
    SetControl Me("Test2" & "Result5"), Prior_Noninvasive_Stress_Test, Nz(Me("Test2")) = "Stress ECG"
End Sub

Private Sub Test3_AfterUpdate()
    SetVisible
    SetControl Me("Test3" & "Result4"), Prior_Noninvasive_Stress_Test, Nz(Me("Test3")) <> "CTA"
    SetControl Me("Test3" & "Result5"), Prior_Noninvasive_Stress_Test, Nz(Me("Test3")) = "Stress ECG"
End Sub

Private Sub Test4_AfterUpdate()
    SetVisible
    SetControl Me("Test4" & "Result4"), Prior_Noninvasive_Stress_Test, Nz(Me("Test4")) <> "CTA"
    SetControl Me("Test4" & "Result5"), Prior_Noninvasive_Stress_Test, Nz(Me("Test4")) = "Stress ECG"
End Sub

Private Sub Test5_AfterUpdate()
    SetVisible
    SetControl Me("Test5" & "Result4"), Prior_Noninvasive_Stress_Test, Nz(Me("Test5")) <> "CTA"
    SetControl Me("Test5" & "Result5"), Prior_Noninvasive_Stress_Test, Nz(Me("Test5")) = "Stress ECG"
End Sub

I do also keep the row for stress ECG, right, since I took that out in the SetVisible sub?

Thx,
Jim
 
This is getting too complicated! Need to step back to post #40 and start again.
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?
Click to expand...
Yes - i it is only visible if test="Stress ecg" - which it doesn't if it is null
Does the Result4 field show because an empty value is the same as <> "CTA"?
Click to expand...
yes

My understanding now is that you want result 5 to show if test='stress ecg' or is null - if so, you need to change the result5 line as below - change indicated in red. You also need to remove all the code you have put into the afterupdate events for the test controls.
Code: 
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)[COLOR=red],"Stress ECG"[/COLOR]) = "Stress ECG"
 
Thanks CJ!! It is now working like a charm! Now on to the real testing...

Jim
 
Hi CJ,

Hope you had a nice weekend!

I still need to make some changes to the program. I'm now trying to validate the date fields. For example, abstract end date must be after abstract start date. I try to do this simply by table design mode and adding

Code: 
>"abstractenddate"
in validation rule for the field abstract start date but it is not working.

Additionally, I want all test dates to be within the abstractstartdate and abstractenddate. So again, I try to go to do this in table design mode validation rule:

Code: 
between "abstractstartdate" and abstractenddate"
but again it is giving me an error. I'm confused why this isn't working.

I've also added a validation text message to each date field.

Can you shed some light on this please?

Thx,
Jim
 
where are you putting this code - in the abstractenddate validation? If so then it should be >[Abstractstartdate]

between only works in sql so you need to use

>=[abstractstartdate] and >=[abstractenddate]
 
Hi CJ,

The line >[abstractstartdate] gives me this errror: Invalid SQL syntax- cannot use multiple columns in a column-level CHECK constraint. But isn't this only one column?
 
Not sure where you are putting this code - I put it in the abstractenddate/date of abstract controls validation rule.

Not sure why you would want to put it in the start date - I would expect users to complete that first!

You can add the visible aspect to set control as follows:

Code: 
    'main form
    SetControl Me.AbstractEndDate, Me.AbstractStartDate
    SetControl Me.Date_of_Abstract, Me.AbstractStartDate, Nz(Me.AbstractEndDate) <> ""
Don't forget to add the setvisible call to the afterupdate events but note that the afterupdate event does not occur until the user moves to another control - e.g.uses the tab key or return key.

The reason is, compared with the other controls which all use comboboxes where the after update event occurs when an item is selected - which is not the same as selecting a date from the datepicker - you'll see that once a date is selected, the cursor remains in the control. The equivalent is for the user in a combobox to type their selection - and then hit return once the autopopulation has 'selected' the right value
 
Hi CJ,

I don't think I need the abstractenddate and dateofabstract to be invisible when the form appears. But yes, I was putting the line >[abstractstartdate] in the [abstractenddate] and [date of abstract] validation rule in table design mode. Any idea why the code isn't working for me? Or is there an easy line of code I can write in VBA instead?

Also, something funky is happening when I use the code >=abstractstartdate and <=abstractenddate for say, PCI_date1. It didn't give me the SQL syntax error but when I go back to the form and input the dates, the dates doesn't validate. For example, if abstractstartdate is entered as 12/1/13 and abstractenddate as 12/3/13, then Access won't accept any values I put into PCI_Date1, even if the range falls between abstractstartdate and abstractenddate. I'm confused, uh!

Thx,
Jim
 
Last edited:
you need to put it in the form control, not the table design
 
Thanks CJ, that did the trick!

I'm revisiting the prior tests tab again and will need to add some additional fields (i.e. Test1Result2a, Test1Result2b, Test1Result2c). I created the new columns and added it to the form as well as defined them in the code. As usual, I'm getting an error. I think it has to do with the fields being textboxes and not combo boxes.

Is there a way, in Property Sheet in form view, to convert a field to a combo box when it is defined as text box in table design mode?

I see that some of the other fields like Test1Result1, Test1Result2, etc. have all selection types of combo boxes. But they are defined as text box in table design mode. How come you have one thing AND the other?

Also, when I delete, say, Test1Result2, and then add it again in the form, the arrow pointing down (that represents the drop down list) disappears. Why doesn't it reappear after adding it again since it's selection type is combo box?

Thx,
Jim
 
just right click on the control and select change>combobox

Your other controls had been setup in your table as lookups and populated with the value list which is why they came through as combo boxes when you dragged them onto the form.

Using lookups in tables is considered (and is) a bad thing to do, but since they are all value lists that is not quite as bad!

Here is a link to why you should not use lookups in tables

http://access.mvps.org/access/lookupfields.htm

You should be able to work out why it is not so bad using value list:)

If you want to mend the error of your ways, just go into your table design and remove the lookups - it won't affect your form.
 
incidentally, if you decide to keep your lookups in your table, be aware that if you change them there, this will not be reflected in the form, only when you create a new control - so any change will need to be done twice, once in the table and again in the form(s).
 
Hi CJ,

I've changed all the result fields to textbox in table design mode. So doesn't this automatically get rid of the lookup list?

On the form, I've then changed all the result fields to combo boxes and added the values (all possible values) in the row source on property sheet. I'm not getting an error anymore but for some reason I'm not getting the values I'm supposed to.

I have now added 60 fields to the prior test tab (i.e. Test1Result2a, Test1Result3a, ..., Test1Result5a, Test1Result2b, ... Test1Result5b, Test1Result2c, ..., Test1Result5c, ..., ..., ..., Test5Result5c). I would like each column of result fields to have the same values depending on the test selected. For example, Test1Result2 should do the same thing as Test1Result2a and all the way down to Test5Result2c. To do this I basically just reused the code in SetControl, and I also added SetVisible in the afterupdate sub for each of the new fields.

The problem now is after I select a test, when I select the drop down list from say, Test1Result2b, I see values for everything listed rather than what they should be depending on the test I select. But for some reason, Test1Result2 works just fine, that is, the correct list of values is shown after selecting the corresponding test.

I can send you the code later tonight but do you have an idea what the problem is? I think my case statement is correct. For the row source in each field, I do type in all the values possible, right?

Thx,
Jim
 
So doesn't this automatically get rid of the lookup list?
Click to expand...
it will in the table, but when you created the control on the form it copied the lookup across - otherwise the two are not intrinsically linked.

For the rest of it, better to send an update of the db
 
Hi CJ,

I deleted the control fields for the original result fields on the form and added them back on and changed them to combo boxes.

Here is the issue. If you select a test, say, CTA for test1, the correct values are there for Test1Result2 and Test1Result2a, but not for Test1Result2b and Test1Result2c (everything is shown there as listed on the row source). The same thing also happens when another test is selected. I checked the case statement, and it looks like everything is there and the correct values listed for each test but values are not popping up correctly.

You may also notice values of "N/A" in some of the result fields. I would like to add them only to result fields other than the first one (a,b, and c). Db attached.

Thx,
Jim
 

Attachments

You'll need to guide me a bit about which controls you are talking about - I presume you mean the ones on the prior tests tab under the test result2 column. But I cannot see what the issue is. Can you take me through one particular example.

The only thing I can see you might want to change is for example in Test1Result2b you have 22 items but the list is limited to 16 rows, so the user needs to scroll to see the rest of the items. You can change this in the format sction of the control properties.

Not to worry you but also a warning. You now have 150 fields in your table (maximum 255) and approaching the limit of the number of controls you can have on a form (think this is around 700). This number includes controls you have deleted (such as labels) so you may need to consider a redesign in the not too distant future - the data is starting to become 'un normalised'

For the table, I see you have what looks like time series data - e.g list tests in chronological order on the prior test lab - this data could be stored in a separate table and shown in a subform on the prior tests tab.

And for the form consider using other subforms to reduce the number of controls per form
 
Hi CJ,

Sorry for the delay in response. Didn't get any free time til now. In response to your question, yes, I am referring to the Prior tests tab but the problem exists in result2, result3, and result4.

In Test1Result2b, I have 22 items, but I only want it limited to the 12, as listed in Test1Result2a.

As an example: After selecting Yes for Prior_noninvasive_stress_test and 5 for Priortestcount, when I select "CTA" for test1 I get different result values for result2. Under Result2, I see LM, LAD, Diag, LCx, OM, RCA, PDA, PL, LIMA, RIMA, SVG, and under Result2a I see the same with an additional "N/A", which is the way I want it. But under Result2b, there are too many values. There should NOT be the other values in the list: Inferior, Posterior, ..., Apical.

I don't think there are any other fields I need to add so hopefully I won't run into the max control issue.

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
377
AlliCarr
A
D
Solved Unbound form required using VBA
Replies
9
Views
661
Pat Hartman
P
T
Solved vbinformation
Replies
15
Views
483
isladogs
isladogs
Mike Krailo
Solved Need help with before update code logic?
Replies
12
Views
390
Mike Krailo
Mike Krailo
V
Need help with VBA to check for required fields
Replies
6
Views
390
MajP
MajP

Users who are viewing this thread

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