Forcing user to enter data into certain fields

[CJ_London]

But under Result2b, there are too many values. There should NOT be the other values in the list: Inferior, Posterior, ..., Apical.

n that case, all you need to do is edit the value list in the result2b control

 

[jlee9562]

Hi CJ,

When you say edit the value list in result2b control do you mean the rowsource in property sheet? Or do you mean the case statement? Don't I need to include all of the possible values in the rowsource because if the test is something other than "CTA" then there will be different values in result2b. I've copied the statement below. It looks to me like everything is there when say, test1="CTA", then test1result2b="LM;LAD;Diag;LCx;OM;RCA;PDA;PL;LIMA;RIMA;SVG;N/A". But this isn't the case when I choose "CTA" for test1; all 22 values are listed instead.

   '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 & "Result2a"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result2b"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result2c"), 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 & "Result3a"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result3b"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result3c"), Me.Prior_Noninvasive_Stress_Test, Val(Nz(Me.PriorTestCount, 0)) >= i And Val(Nz(Me.PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result4"), Prior_Noninvasive_Stress_Test, Val(Nz(PriorTestCount, 0)) >= i And Val(Nz(PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result4a"), Prior_Noninvasive_Stress_Test, Val(Nz(PriorTestCount, 0)) >= i And Val(Nz(PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result4b"), Prior_Noninvasive_Stress_Test, Val(Nz(PriorTestCount, 0)) >= i And Val(Nz(PriorTestCount, 0)) > 0
        SetControl Me("Test" & i & "Result4c"), Prior_Noninvasive_Stress_Test, Val(Nz(PriorTestCount, 0)) >= i And Val(Nz(PriorTestCount, 0)) > 0
        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") = "Stress ECG"
        SetControl Me("Test" & i & "Result5a"), Prior_Noninvasive_Stress_Test, Val(Nz(PriorTestCount, 0)) >= i And Val(Nz(PriorTestCount, 0)) > 0 And Nz(Me("Test" & i), "Stress ECG") = "Stress ECG"
        SetControl Me("Test" & i & "Result5b"), Prior_Noninvasive_Stress_Test, Val(Nz(PriorTestCount, 0)) >= i And Val(Nz(PriorTestCount, 0)) > 0 And Nz(Me("Test" & i), "Stress ECG") = "Stress ECG"
        SetControl Me("Test" & i & "Result5c"), Prior_Noninvasive_Stress_Test, Val(Nz(PriorTestCount, 0)) >= i And Val(Nz(PriorTestCount, 0)) > 0 And Nz(Me("Test" & i), "Stress ECG") = "Stress ECG"
        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 & "Result2a").RowSource = "Anterior;Inferior;Posterior;Septal;Lateral;N/A"
               Me("Test" & i & "Result2b").RowSource = "Anterior;Inferior;Posterior;Septal;Lateral;N/A"
               Me("Test" & i & "Result2c").RowSource = "Anterior;Inferior;Posterior;Septal;Lateral;N/A"
               Me("Test" & i & "Result3").RowSource = "Upsloping;Horizontal;Downsloping"
               Me("Test" & i & "Result3a").RowSource = "Upsloping;Horizontal;Downsloping;N/A"
               Me("Test" & i & "Result3b").RowSource = "Upsloping;Horizontal;Downsloping;N/A"
               Me("Test" & i & "Result3c").RowSource = "Upsloping;Horizontal;Downsloping;N/A"
               Me("Test" & i & "Result4").RowSource = "ST Depression;ST Elevation"
               Me("Test" & i & "Result4a").RowSource = "ST Depression;ST Elevation;N/A"
               Me("Test" & i & "Result4b").RowSource = "ST Depression;ST Elevation;N/A"
               Me("Test" & i & "Result4c").RowSource = "ST Depression;ST Elevation;N/A"
               Me("Test" & i & "Result5").RowSource = ">=1 mm;>=2 mm;"
               Me("Test" & i & "Result5a").RowSource = ">=1 mm;>=2 mm;N/A"
               Me("Test" & i & "Result5b").RowSource = ">=1 mm;>=2 mm;N/A"
               Me("Test" & i & "Result5c").RowSource = ">=1 mm;>=2 mm;N/A"
        Case "CTA"
               Me("Test" & i & "Result2").RowSource = "LM;LAD;Diag;LCx;OM;RCA;PDA;PL;LIMA;RIMA;SVG"
               Me("Test" & i & "Result2a").RowSource = "LM;LAD;Diag;LCx;OM;RCA;PDA;PL;LIMA;RIMA;SVG;N/A"
               Me("Test" & i & "Result2b").RowSource = "LM;LAD;Diag;LCx;OM;RCA;PDA;PL;LIMA;RIMA;SVG;N/A"
               Me("Test" & i & "Result2c").RowSource = "LM;LAD;Diag;LCx;OM;RCA;PDA;PL;LIMA;RIMA;SVG;N/A"
               Me("Test" & i & "Result3").RowSource = "Prox;Mid;Distal;Prox-mid;Mid-distal"
               Me("Test" & i & "Result3a").RowSource = "Prox;Mid;Distal;Prox-mid;Mid-distal;N/A"
               Me("Test" & i & "Result3b").RowSource = "Prox;Mid;Distal;Prox-mid;Mid-distal;N/A"
               Me("Test" & i & "Result3c").RowSource = "Prox;Mid;Distal;Prox-mid;Mid-distal;N/A"
               Me("Test" & i & "Result4").RowSource = "<50%;50-69%;70-99%;100%"
               Me("Test" & i & "Result4a").RowSource = "<50%;50-69%;70-99%;100%;N/A"
               Me("Test" & i & "Result4b").RowSource = "<50%;50-69%;70-99%;100%;N/A"
               Me("Test" & i & "Result4c").RowSource = "<50%;50-69%;70-99%;100%;N/A"
        Case Else
               Me("Test" & i & "Result2").RowSource = "Inferior;Posterior;Lateral;Anterior;Septal;Inferior Septal;Inferior Posterior;Anterolateral;Anteroseptal;Apical"
               Me("Test" & i & "Result2a").RowSource = "Inferior;Posterior;Lateral;Anterior;Septal;Inferior Septal;Inferior Posterior;Anterolateral;Anteroseptal;Apical;N/A"
               Me("Test" & i & "Result2b").RowSource = "Inferior;Posterior;Lateral;Anterior;Septal;Inferior Septal;Inferior Posterior;Anterolateral;Anteroseptal;Apical;N/A"
               Me("Test" & i & "Result2c").RowSource = "Inferior;Posterior;Lateral;Anterior;Septal;Inferior Septal;Inferior Posterior;Anterolateral;Anteroseptal;Apical;N/A"
               Me("Test" & i & "Result3").RowSource = "Base;Mid;Distal;Basal-mid;Mid-distal"
               Me("Test" & i & "Result3a").RowSource = "Base;Mid;Distal;Basal-mid;Mid-distal;N/A"
               Me("Test" & i & "Result3b").RowSource = "Base;Mid;Distal;Basal-mid;Mid-distal;N/A"
               Me("Test" & i & "Result3c").RowSource = "Base;Mid;Distal;Basal-mid;Mid-distal;N/A"
               Me("Test" & i & "Result4").RowSource = "Ischemia only;Ischemia+Infarct;Infarct only"
               Me("Test" & i & "Result4a").RowSource = "Ischemia only;Ischemia+Infarct;Infarct only;N/A"
               Me("Test" & i & "Result4b").RowSource = "Ischemia only;Ischemia+Infarct;Infarct only;N/A"
               Me("Test" & i & "Result4c").RowSource = "Ischemia only;Ischemia+Infarct;Infarct only;N/A"
        End Select
    
    Next i

 

[CJ_London]

I've spent way to long on this

I now understand the problem but have been unable to resolve it as you would expect. I suspect the db and/or form has become corrupted in some way. However I've tried all my tricks with no success to decorrupt it.

Each vba module should have 'Option Compare Database' at the top but for some reason, it is missing from your modules which may be indicative of corruption. I copied all the code to a text editor, set the 'has module' to no for the form, compacted then copied back but to no avail.

It is also possible that you are approaching the number of controls limit for the form which may be having an effect, although I can't say for definite.

I've attached version 3 which has a revised form (suffixed with CJ) which works slightly differently and utilised tblLookups. If you open the table you will see I have mapped out all the different menus - hope it makes sense.

Then in the setvisible function I have removed your case code and replaced it with a smaller loop to repopulated the dropdowns - note I have changed them from value list to table/query.

Although your method should work, unfortunately I cannot see why it doesn't but my method is working.

See how you get on

 

[jlee9562]

Hi CJ,

This is getting way too complicated for me
Do you think if I started a completely new database and copied the code and layout the corruption would be fixed?

In your program, test2-test5 have value choices of ECG Treadmill, Stress Echo, Stress Nuclear and CTA. But I would like them to each have the same value choices as Test1 which are Stress Echo, Stress SPECT, Stress PET, Stress MRI, Stress ECG, and CTA. I would also like each of them to do essentially the same thing as test1 but I think there are also too many values in the result fields. How to correct the code for this?

Thx,
Jim

 

[jlee9562]

Hi CJ,

I went ahead and tried to redo part of the database again by starting with a completely new database to see if the problem can be fixed. In the table, I added back all of the fields and switched the appropriate ones to combo boxes. In the form, I created the tabs and added the fields for just test1 under Prior tests tab. Then I switched the result fields to combo boxes and added the value list to rowsource. I also just tested the code for test1. It looks like the exact same problem is happening. That is, the values under resulta, resultb, resultc are not the same within each column. So maybe this is a logic error not a corruption of the db itself?

Db attached.

Thx,
Jim

 

[CJ_London]

That is, the values under resulta, resultb, resultc are not the same within each column. So maybe this is a logic error not a corruption of the db itself?

It may be, but I can't see it

In your program, test2-test5 have value choices of ECG Treadmill, Stress Echo, Stress Nuclear and CTA. But I would like them to each have the same value choices as Test1 which are Stress Echo, Stress SPECT, Stress PET, Stress MRI, Stress ECG, and CTA.

I was just going by what you had in your rowsources for those dropdowns. If you want them all to be the same as for Test1 then just modify the rowsource to the same as test1 - basically change 'row=2' to 'row=1'

 

[jlee9562]

Hi CJ,

Thanks! I think it's working now. A couple questions on the query...

In your rowsource for some resultfields like Test1Result2a, the rowsource has the query:
SELECT Description FROM tblLookups WHERE (FldGroup = 'Stress MRI' AND Col=2 AND Row =1) or Col=99 ORDER BY SortOrder;

But there is no fldgroup='Stress MRI' in the lookuptable, yet the program seems to run fine. Is this because the 'populate code' overrides the query in the rowsource?

So, should I change the fldgroup='Stress MRI' to fldgroup='' in the rowsource?

Thx,
Jim

 

[CJ_London]

As you surmise, the rowsource is over written in the setvisible procedure so it does matter what it is in 'developer' view - it is only 'wrong' if it is not visible.

Might be better to go through all those controls and remove the rowsource so the form loads a little quicker!

It is left over from when I was developing the form and testing for the different options

 

[CJ_London]

don't know if you know this but you don't need to delete the rowsources one by one, you can select them all (the rowsources won't show in the properties because they are all different) enter a couple of characters in the rowsource and click on another property to update. Now they all have the same rowsource of a couple of characters, so select the rowsource again and delete the characters - all rowsources deleted in one hit!

 

[jlee9562]

Thanks CJ. I've deleted the rowsource for the result fields but suspect I need to keep the rowsource for test1-test5 since there are no rowsource set for them in VBA.

 

[CJ_London]

correct!

You could put it in the SetVisible procedure if you wanted

 

[jwashington133]

Hi,
I read your post. I think your post very well for every people and I have a small website. It's on General Crane Services. If you like it, please go to this site around once.
Thank you,

gcswa.com.au

 

[jlee9562]

Hi CJ,

I have one more field [determination of appropriation] that I need to populate. Currently it's a drop down list but I would like for it to be "set" one of three values according to what kind of values are selected in various fields on various tabs. I'm thinking of doing this with the case statement but not sure where to put it. Should I put the conditional code in the AddNewRecord_click sub? The field [determination of appropriate] should not be populated until after the user fills out everything else.

Thx!
Jim

 

[CJ_London]

Should I put the conditional code in the AddNewRecord_click sub?

No - since at the moment, the user has a number of ways they can update the form without clicking the button - see below.

Since you don't want the user to complete it until all other fields are completed I would set it's visible property to false and value to null in the setvisible procedure - I would also be inclined to move the control to somewhere on the form so it is more associated with submitting the record.

the code is simply

Determination_of_Appropriateness.visible=false
Determination_of_Appropriateness=null

To set its visible property to true only when all other records are completed you need to make a change to the form before update event.

Near the bottom after the Next Ctrl put

Determination_of_Appropriateness.visible=true
Determination_of_Appropriateness.tag="CheckifCompleted"
Determination_of_Appropriateness.rowsource="whatever you are assigning here"
Cancel=nz(Determination_of_Appropriateness)=""
if Cancel=true then DoCmd.GoToControl Determination_of_Appropriateness

This bit of code should only run once all required fields have been completed.

So what should happen is the DoA control remains hidden until all visible controls are completed, then if the user tries to close the form, save the form, clicks on the submit record button, move to the next record, the control becomes visible and is added to the controls that need to be checked (i.e.visible and tag=checkifcompleted). Once it has been completed, Cancel will be false so the record should then update

If the user clears another control for some reason, then the DoA control willl become hidden again and it's value reset to null.

according to what kind of values are selected in various fields on various tabs

With regards this comment, I would also put a bit of code in each of these controls afterupdate event to reset the value of Determination_of_Appropriateness to null - reason being that if the user changes one of these values, that will affect the rowsource for DoA so a previously entered value may not be relevant

 

[jlee9562]

Hi CJ,

Thank you, I've added your code. But I'm still unsure where to put the conditional statement. Should it be after the NextCtrl in form beforeupdate property?

Regarding your comment:

With regards this comment, I would also put a bit of code in each of these controls afterupdate event to reset the value of Determination_of_Appropriateness to null - reason being that if the user changes one of these values, that will affect the rowsource for DoA so a previously entered value may not be relevant

Do I add Determination_of_Appropriateness=null after every field that is part of the condition statement?

 

[CJ_London]

But I'm still unsure where to put the conditional statement

If by the conditional statement for the Determination_of_Appropriateness rowsource the it goes in the before update event

Determination_of_Appropriateness.visible=true
Determination_of_Appropriateness.tag="CheckifCompleted"
[COLOR=red]Determination_of_Appropriateness.rowsource="whatever you are assigning here"
[/COLOR]Cancel=nz(Determination_of_Appropriateness)=""
if Cancel=true then DoCmd.GoToControl Determination_of_Appropriateness

I don't know how many controls are required to determine the rowsource so couldn't put any more than I did

Do I add Determination_of_Appropriateness=null after every field

Yes!

 

[jlee9562]

Hi CJ,

Thanks. I thought the .rowsource values can only be the values for the field. Didn't realize I can put the conditional statement in there!

An an aside, I think I'm having problems w/ the dates again. Not sure why, but I have the conditions (>=[abstractstartdate] AND <=[abstractenddate]) in the property sheet row source for like PCI_Date1, PCI_Date2, etc. But Access doesn't seem to recognize the dates. I mean, it doesn't matter if the date I put in satisfy the condition or not, Access is giving me the error in the validation text I wrote in. Are you having the same problem?

Thx,
Jim

 

[CJ_London]

(>=[abstractstartdate] AND <=[abstractenddate]) in the property sheet row source

It needs to go in the validation property, not the rowsource.

 

[jlee9562]

Oops. I meant the Validation Rule, not rowsource. It doesn't have a rowsource because it's a date value not a value list!

I made some changes to the program and now it's not even giving me the error message. It's stuck on that date field. But I think before I made the changes it was giving me the error message even if I satisfied the condition and won't let me go to any other field.

 

[CJ_London]

by error message, do you mean the validation text? If you aren't getting anything then you have probably deleted the text

I've used the formula without a problem - but have you checked the start and end dates are populated?

Perhaps you need to consider hiding all the tabs until the top part of the form has been completed?