Forcing user to enter data into certain fields

[jlee9562]

Yes, validation text.

Hmm.. I checked the tags, SetVisible properties, and the validation text are all there. The conditions are also in the validation rule.

I first entered the abstractstartdate and abstractenddate, then the [date of abstract] before plugging in for PCI_Date1.

What else do you think I should check?

 

[jlee9562]

Hmm.. I closed Access and reopened it. The validation text is now popping up again. But the date field wont pass even if the condition is satisfied. I hope it's not my computer!

 

[jlee9562]

Hi CJ,

I tried opening the db on a different computer but still encountered the same issue. I also tried doing the same with the last copy of the db you attached, but with no luck.

It seems to work fine for the abstractenddate, and dateofabstract fields but not for the other dates like PCI_Date1. That is, I get the error messages at the right time for the first two but not the other date fields with conditions set.

Here are the values I entered:

12/1/2013 into abstractstartdate
12/10/2013 into abstractenddate
12/19/2013 into dateofabstract

Then when I go to the PCI or CABG tab and enter Yes for revascularization, 1 for PCI and 12/9/2013 into PCI1, I get the error message from my validation text no matter what date I put in, and I'm stuck on that field.

Could you take another look?

Many thanks,
Jim

 

[jlee9562]

Hi CJ,

Hope you're doing well.

I think I fixed the date issue. Looks like deleting the control and adding it back again did the trick!

I was wondering how to add the conditional statement to the me.determination_of_appropriate.rowsource statement. You had completed a select case statement in your other code but not sure how to do it for this.

I would like to do a conditional statement like the following (though I'm sure there's probably a more effecient way of writing it). If the first lines are satisfied the next lines should not be checked. I think the if then elseif statement does just this. I tried just adding the entire thing in quotes but couldn't get it to work.

  If Me.Timing_of_Surgery = "Emergent/Urgent (To be performed in <48 hours)" Then Me.Determination_of_Appropriateness = "Inappropriate"
        ElseIf Me.Timing_of_Surgery = "Elective/Semi-urgent" And Me.Cardiac_Surgery = "Yes" Then Me.Determination_of_Appropriateness = "Inappropriate"
        ElseIf Me.Low_Risk_Surgery = "Yes" Then Me.Determination_of_Appropriateness = "Inappropriate"
        ElseIf Me.Low_Risk_Surgery = "No" And Me.CM_1B = "N/A" Then Me.Determination_of_Appropriateness = "Inappropriate"
        ElseIf Me.Low_Risk_Surgery = "No" And Me.Functional_Status_by_METS = ">=4 METS" Then Me.Determination_of_Appropriateness = "Inappropriate"
        ElseIf Me.Low_Risk_Surgery = "No" And Me.Functional_Status_by_Activity_Level = "Yes" Then Me.Determination_of_Appropriateness = "Inappropriate"
        ElseIf Me.Low_Risk_Surgery = "No" And Me.Functional_Status_by_Activity_Level = "Information not available" Then Me.Determination_of_Appropriateness = "Uncertain"
        ElseIf Me.Low_Risk_Surgery = "No" And Me.CM_1B <> "N/A" And Me.Functional_Status_by_METS = "<4 METS" Then Me.Determination_of_Appropriateness = "Appropriate"
        ElseIf Me.Low_Risk_Surgery = "No" And Me.CM_1B <> "N/A" And Me.Functional_Status_by_Activity_Level = "No" Then Me.Determination_of_Appropriateness = "Appropriate"
        ElseIf Me.Low_Risk_Surgery = "No" And Me.CM_1B <> "N/A" And (Me.Functional_Status_by_METS = ">=4 METS" Or Me.Functional_Status_by_Activity_Level = _
            "Information not available") Then Me.Determination_of_Appropriateness = "Uncertain"
        End If

Much appreciated,
Jim

 

[CJ_London]

I can't quite work out what you mean when you say you can't get it to work - your elseif's do not appear to cover all eventualities - for example if cardiac_surgery<>yes doa will remain blank

Elseif's work but personally I find using case statements more readable and easier to 'debug'

 

[jlee9562]

Hi CJ,

I'm unsure how to write my if statements into a select case statement.

For example: if A=x and B=y then C=z

How would I write this into a select case because doesn't the case handle only one variable (testexpression) at a time?

Select [ Case ] testexpression
[ Case expressionlist
[ statements ] ]
[ Case Else
[ elsestatements ] ]
End Select

...

The problem I have with my form now is that the DOA [determination_of_appropriateness] field doesn't populate after I populate the rest of the fields (by clicking dummy) when I click on addNewRecord even though I set DOA.rowsource=value when another field has a value set. I purposely set fields to a certain value so that the DOA field will be set the value, say, "Inappropriate", as written in the if then statement.

I tried putting the if then statement into the "whatever you are assigning here" part in

Determination_of_Appropriateness.rowsource="whatever you are assigning here"

but VBA won't take it and the code turns red.

I can't quite work out what you mean when you say you can't get it to work - your elseif's do not appear to cover all eventualities - for example if cardiac_surgery<>yes doa will remain blank

I will have many more conditions to set for the DOA field so if I can make it work for one case I think I should be able to make it work for any new additions.

I couldn't set the [if then statement] into .rowsource so I just wrote out the if then statement and set DOA=value. Not sure if this will work the same way though. Also, I have 2 if then statements in my code which I think are the same but not positive (one is in quotes). Here is the code below:

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, "") = "" Or Nz(.Value, Null) = Null 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

    Determination_of_Appropriateness.Visible = True
    Determination_of_Appropriateness.Tag = "CheckifCompleted"
    
'    If Me.Timing_of_Surgery = "Emergent/Urgent (To be performed in <48 hours)" Then Me.Determination_of_Appropriateness = "Inappropriate"
'        ElseIf Me.Timing_of_Surgery = "Elective/Semi-urgent" And Me.Cardiac_Surgery = "Yes" Then Me.Determination_of_Appropriateness = "Inappropriate"
'        ElseIf Me.Low_Risk_Surgery = "Yes" Then Me.Determination_of_Appropriateness = "Inappropriate"
'        ElseIf Me.Low_Risk_Surgery = "No" And Me.CM_1B = "N/A" Then Me.Determination_of_Appropriateness = "Inappropriate"
'        ElseIf Me.Low_Risk_Surgery = "No" And Me.Functional_Status_by_METS = ">=4 METS" Then Me.Determination_of_Appropriateness = "Inappropriate"
'        ElseIf Me.Low_Risk_Surgery = "No" And Me.Functional_Status_by_Activity_Level = "Yes" Then Me.Determination_of_Appropriateness = "Inappropriate"
'        ElseIf Me.Low_Risk_Surgery = "No" And Me.Functional_Status_by_Activity_Level = "Information not available" Then Me.Determination_of_Appropriateness = "Uncertain"
'        ElseIf Me.Low_Risk_Surgery = "No" And Me.CM_1B <> "N/A" And Me.Functional_Status_by_METS = "<4 METS" Then Me.Determination_of_Appropriateness = "Appropriate"
'        ElseIf Me.Low_Risk_Surgery = "No" And Me.CM_1B <> "N/A" And Me.Functional_Status_by_Activity_Level = "No" Then Me.Determination_of_Appropriateness = "Appropriate"
'        ElseIf Me.Low_Risk_Surgery = "No" And Me.CM_1B <> "N/A" And (Me.Functional_Status_by_METS = ">=4 METS" Or Me.Functional_Status_by_Activity_Level = _
'            "Information not available") Then Me.Determination_of_Appropriateness = "Uncertain"
'        End If
        
    If Me.Timing_of_Surgery = "Emergent/Urgent (To be performed in <48 hours)" Or _
        (Me.Timing_of_Surgery = "Elective/Semi-urgent" And Me.Cardiac_Surgery = "Yes") Or _
            Me.Low_Risk_Surgery = "Yes" Or (Me.Low_Risk_Surgery = "No" And Me.CM_1B = "N/A") Or _
                (Me.Low_Risk_Surgery = "No" And Me.Functional_Status_by_METS = ">=4 METS") Or _
                    (Me.Low_Risk_Surgery = "No" And Me.Functional_Status_by_Activity_Level = "Yes") _
                        Then Me.Determination_of_Appropriateness.RowSource = "Inappropriate"
    If (Me.Low_Risk_Surgery = "No" And Me.Functional_Status_by_Activity_Level = "Information not available") Or _
        ((Me.Low_Risk_Surgery = "No" And Me.CM_1B <> "N/A") And (Me.Functional_Status_by_METS = ">=4 METS" Or _
            Me.Functional_Status_by_Activity_Level = "Information not available")) _
                Then Me.Determination_of_Appropriateness.RowSource = "Uncertain"
    If (Me.Low_Risk_Surgery = "No" And Me.CM_1B <> "N/A" And Me.Functional_Status_by_METS = "<4 METS") Or _
        (Me.Low_Risk_Surgery = "No" And Me.CM_1B <> "N/A" And Me.Functional_Status_by_Activity_Level = "No") _
            Then Me.Determination_of_Appropriateness.RowSource = "Appropriate"

    Cancel = Nz(Determination_of_Appropriateness) = ""
    If Cancel = True Then DoCmd.GoToControl Determination_of_Appropriateness


    Exit Sub
   
End Sub

Really appreciate the help!

Thx,
Jim

 

[CJ_London]

I'm not clear what you actually want - do you want the doa to be completed automatically or do you want it to be populated with a rowsource from which the user selects a vlaue?

 

[jlee9562]

Hi CJ,

I would like the DOA field to be automatically selected a value when all other fields have been entered. So, the user should not be able to choose the DOA value but I think its field and value selected should be visible before the form is submitted.

Hope this makes sense.

Thx,
Jim

 

[CJ_London]

I don't have the time to work out exactly what your elseif are supposed to do but your case statement would look something like this

Select Case Timing_of_Surgery
    case "Emergent/Urgent (To be performed in <48 hours)" 
        Determination_of_Appropriateness = "Inappropriate"
 
    case "Elective/Semi-urgent"
        Select Case Cardiac_Surgery
            Case "Yes" 
                 Determination_of_Appropriateness = "Inappropriate"
             Case Else
                  Select Case Low_Risk_Surgery
                      Case "Yes" 
                          Determination_of_Appropriateness = "Inappropriate"
                      Case "No"
                          Select Case CM_1B
                              Case "N/A" 
                                  Determination_of_Appropriateness = "Inappropriate"
[COLOR=red]                              Case Else[/COLOR]
[COLOR=red]                                  Select Case Functional_Status_by_METS[/COLOR]
[COLOR=red]                                      Case ">=4 METS" [/COLOR]
[COLOR=red]                                           Determination_of_Appropriateness = "Inappropriate"[/COLOR]
[COLOR=red]                                       Case "<4 METS" [/COLOR]
[COLOR=red]                                           Determination_of_Appropriateness = "Appropriate"[/COLOR]
[COLOR=red]                                   Case Else[/COLOR]
[COLOR=red]                                       Select Case Functional_Status_by_Activity_Level[/COLOR]
[COLOR=red]                                           Case "Yes"[/COLOR]
[COLOR=red]                                            Determination_of_Appropriateness = "Inappropriate"[/COLOR]
[COLOR=red]                                            Case "No"[/COLOR]
[COLOR=red]                                            Determination_of_Appropriateness = "Appropriate"[/COLOR]

The bit highlighted in red does not make sense to me - you can also see it in your code - in your code you will never get to.

Also, most of the time you are assigning 'inappropriate' so it might be an idea to look at the logic again to just assign 'Appropriate' or 'Uncertain' otherwise assign 'inappropriate' - something like

If Me.Low_Risk_Surgery = "No" And Me.CM_1B <> "N/A" And (Me.Functional_Status_by_METS = ">=4 METS" Or Me.Functional_Status_by_Activity_Level = _
            "Information not available") Then Me.Determination_of_Appropriateness = "Uncertain"
ElseIf Me.Low_Risk_Surgery = "No" And Me.Functional_Status_by_Activity_Level = "Information not available" Then Me.Determination_of_Appropriateness = "Uncertain"
ElseIf Me.Low_Risk_Surgery = "No" And Me.CM_1B <> "N/A" And Me.Functional_Status_by_METS = "<4 METS" Then Me.Determination_of_Appropriateness = "Appropriate"
ElseIf Me.Low_Risk_Surgery = "No" And Me.CM_1B <> "N/A" And Me.Functional_Status_by_Activity_Level = "No" Then Me.Determination_of_Appropriateness = "Appropriate"
Else
Me.Determination_of_Appropriateness = "Inappropriate"
End if

 

[jlee9562]

Hi CJ,

Thanks! I know I've been taking way too much of your time lately so I'll try to refrain from asking too many questions.

Regarding the conditional statement, I think I will use your suggestion for the if then statement. It seems to make more sense to me and it is working. Only problem is maybe there will be too many elseif statements. Hopefully there is not a limit to the number of elseif Access will take.

Coming back to the date fields, you had previously suggested that I make the tabctrl invisible until the user fields out the main form. I tried adding some code for this but ran into some problem.

Private Sub SetVisible()
    'main form
    SetControl me.tabctrl59, me.abstractenddate
...

and

Private Sub AbstractEndDate_AfterUpdate()
    SetVisible
End Sub

The tabctrl is invisible before I fill out the abstractenddate field but after I fill out abstractenddate, the tabctrl appears and seems to spin around going through each of the tabs. How can I fix this?

I also tried (instead of making the tabctrl invisible) to just add a control event to the dates. For example,

Private Sub AbstractEndDate_Change()
   If me.abstractstartdate="" then 
       msgbox "Please enter a value for abstract start date first."
       me.abstractstartdate.setfocus
   else
       if me.abstractenddate <= me.abstractstartdate then
           msgbox "Abstract end date must fall after abstract start date." 
           me.abstractenddate=""
           me.abstractstartdate.setfocus
       endif
   endif
End Sub

But for some reason, the code doesn't seem to fire (nothing happens). I also tried placing the same code in the control events AfterUpdate, GotFocus, and Exit but none of them fired either. Why wouldn't this work?

Thx,
Jim

 

[CJ_London]

The problem is it is a date field - and I'm 99% certain you are using the datepicker -which may not trigger the change event - try using the afterupdate event instead and click on another field to trigger this event

 

[Muaz]

Hi;

I am also facing similar problem, to make certain fields mandatory, on different tabs of the same form. I download the attached database to get some idea but unable to open.

Can someone provide me this data base in Access 2003 format ?

Thanks in advance.

Regards
Muaz

 

[CJ_London]

This may not have all the functionality described in this long thread, but the basics are there

 

[jlee9562]

Hi CJ,

Hope you're doing well! Really appreciate all the help you've so far. I'm still testing and making changes to the program but I've run into another problem I'm not sure how to fix.

I've added this line of code to the SetVisible Sub:

    For k = 2 To 5
        SetControl Me("Test1Result" & k), Me.Test1Result1
        SetControl Me("Test1Result" & k & "a"), Me.Test1Result1
        SetControl Me("Test1Result" & k & "b"), Me.Test1Result1
        SetControl Me("Test1Result" & k & "c"), Me.Test1Result1
        
        SetControl Me("Test2Result" & k), Me.Test2Result1
        SetControl Me("Test2Result" & k & "a"), Me.Test2Result1
        SetControl Me("Test2Result" & k & "b"), Me.Test2Result1
        SetControl Me("Test2Result" & k & "c"), Me.Test2Result1
        
        SetControl Me("Test3Result" & k), Me.Test3Result1
        SetControl Me("Test3Result" & k & "a"), Me.Test3Result1
        SetControl Me("Test3Result" & k & "b"), Me.Test3Result1
        SetControl Me("Test3Result" & k & "c"), Me.Test3Result1
        
        SetControl Me("Test4Result" & k), Me.Test4Result1
        SetControl Me("Test4Result" & k & "a"), Me.Test4Result1
        SetControl Me("Test4Result" & k & "b"), Me.Test4Result1
        SetControl Me("Test4Result" & k & "c"), Me.Test4Result1
        
        SetControl Me("Test5Result" & k), Me.Test5Result1
        SetControl Me("Test5Result" & k & "a"), Me.Test5Result1
        SetControl Me("Test5Result" & k & "b"), Me.Test5Result1
        SetControl Me("Test5Result" & k & "c"), Me.Test5Result1
    Next k

The fields disappear and appear fine but the problem is I only want TestnResult5 to be visible=true only when Testn="Stress ECG" AND when TestnResult1="Abnormal". Right now, TestnResult5 is always appearing when TestnResult1="Abnormal", no matter what Testn is. I tried adding another SetControl property (below) but TestnResult5 still seems to be appear always.

Private Sub SetControl4(ThisCtrl As Control, LastCtrl As Control, Optional Process As Boolean = True)
 
    On Error Resume Next
    If LastCtrl = "Stress ECG" And Me(LastCtrl & "Result1") = "Abnormal" Then
        ThisCtrl.Visible = True
    Else
        ThisCtrl.Visible = False
    End If
 
    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

In SetVisible Sub:

'    For l = 1 To 5
'        SetControl4 Me("Test" & l & "Result5"), Me("Test" & l)
'        SetControl4 Me("Test" & l & "Result5a"), Me("Test" & l)
'        SetControl4 Me("Test" & l & "Result5b"), Me("Test" & l)
'        SetControl4 Me("Test" & l & "Result5c"), Me("Test" & l)
'    Next l

Can you shed some light please?

Thx,
Jim

P.S. I was able to fix the date fields by using the Nz() function. For example,
Nz(Me.AbstractStartDate, "") = "". I think it wasn't reading the date as null when I was just checking say, abstractstartdate="".

 

[CJ_London]

I would look at what you are doing here (in the PCU ir CABG group) and apply the same principle

SetControl Me("Size" & i), Revascularization[COLOR=red], Val(Nz(PCI_Count, 0)) >= i And Val(Nz(PCI_Count, 0)) > 0[/COLOR]

 

[jlee9562]

Hi CJ,

Is this correct?

For m = 5 To 5
        SetControl Me("Test1Result" & k), Me.Test1Result1, (Nz(Me.Test1, "")) = "Stress ECG"
        SetControl Me("Test1Result" & k & "a"), Me.Test1Result1, (Nz(Me.Test1, "")) = "Stress ECG"
        SetControl Me("Test1Result" & k & "b"), Me.Test1Result1, (Nz(Me.Test1, "")) = "Stress ECG"
        SetControl Me("Test1Result" & k & "c"), Me.Test1Result1, (Nz(Me.Test1, "")) = "Stress ECG"
        
        SetControl Me("Test2Result" & k), Me.Test2Result1, (Nz(Me.Test2, "")) = "Stress ECG"
        SetControl Me("Test2Result" & k & "a"), Me.Test2Result1, (Nz(Me.Test2, "")) = "Stress ECG"
        SetControl Me("Test2Result" & k & "b"), Me.Test2Result1, (Nz(Me.Test2, "")) = "Stress ECG"
        SetControl Me("Test2Result" & k & "c"), Me.Test2Result1, (Nz(Me.Test2, "")) = "Stress ECG"
        
        SetControl Me("Test3Result" & k), Me.Test3Result1, (Nz(Me.Test3, "")) = "Stress ECG"
        SetControl Me("Test3Result" & k & "a"), Me.Test3Result1, (Nz(Me.Test3, "")) = "Stress ECG"
        SetControl Me("Test3Result" & k & "b"), Me.Test3Result1, (Nz(Me.Test3, "")) = "Stress ECG"
        SetControl Me("Test3Result" & k & "c"), Me.Test3Result1, (Nz(Me.Test3, "")) = "Stress ECG"
        
        SetControl Me("Test4Result" & k), Me.Test4Result1, (Nz(Me.Test4, "")) = "Stress ECG"
        SetControl Me("Test4Result" & k & "a"), Me.Test4Result1, (Nz(Me.Test4, "")) = "Stress ECG"
        SetControl Me("Test4Result" & k & "b"), Me.Test4Result1, (Nz(Me.Test4, "")) = "Stress ECG"
        SetControl Me("Test4Result" & k & "c"), Me.Test4Result1, (Nz(Me.Test4, "")) = "Stress ECG"
        
        SetControl Me("Test5Result" & k), Me.Test5Result1, (Nz(Me.Test5, "")) = "Stress ECG"
        SetControl Me("Test5Result" & k & "a"), Me.Test5Result1, (Nz(Me.Test5, "")) = "Stress ECG"
        SetControl Me("Test5Result" & k & "b"), Me.Test5Result1, (Nz(Me.Test5, "")) = "Stress ECG"
        SetControl Me("Test5Result" & k & "c"), Me.Test5Result1, (Nz(Me.Test5, "")) = "Stress ECG"
    Next m

It seems to work. So does the line (Nz(Me.Test5, "")) = "Stress ECG" mean an additional condition for the control to work? Why doesn't just me.test5="Stress ECG" work? Just trying to wrap my head around it.

Thanks,
Jim

 

[CJ_London]

Hi Jim,

if it works, it works! You have the principle correct - as you say, it adds a second condition to set the visibility. If you look at the procedure you'll see it is optional so you may not need it for the first line of each group

SetControl Me("Test1Result" & k & "c"), Me.Test1Result1, (Nz(Me.Test1, "")) = "Stress ECG"

is basically saying 'show Test1Result" & k & "c" if Me.Test1Result1 is visible and Nz(Me.Test1, "") = "Stress ECG"

Why use nz? because if you don't and a value hasn't been completed, it will be null and the comparsion will have an error - remove an nz and see what happens

You don't actually need the outer brackets or Me.

Test1Result1, Nz(Me.Test5, "") = "Stress ECG"

is sufficient

 

[jlee9562]

Awesome. Thanks again!

Jim

 

[jlee9562]

Hi CJ,

I'm back for more questions. I just can't seem to get enough of you!

My question is on saving data in the form. Whenever I close the form after updating the fields and submitting the records, the data gets saved into the table but when I open the form again, the data is gone from the form. Is there a way to open the form so that all the data that has been entered appears again (i.e. so that I can scroll through the pages of data by clicking the left and right arrows at the bottom of the form?)

Thanks!

Jim

 

[CJ_London]

The short answer is yes, however I don't have a current copy of the db so if you can upload another one I'll take a look.

In principle, all you should need is the table as the recordsource to the form and it will open to the first record in the table and you can just scroll through but you seem to be saying this is not happening?

Also, scrolling in this way is not very efficient - if you have say 200 records and you are trying to find one of them, scrolling through could take some time - have you considered a lookup form to find the one you want?