Enable Button if all questions are answered

[vzx]

First some background info about me:

Being a physician I do not have any background in programming language. All I know about access is through a basic database design class I took in med school and online google searches.

I am working on a database which will ask a few questions per screen to patients. I want them to answer each question on the form before proceeding to the next screen (the next form in this case). For e.g. in the attached sample database I have two forms "demographics" and "past medical history".

study participants will be presented with the "demographics" form at the beginning. The idea is to have them answer all questions before proceeding to the next screen/form. All the fields in the form have been set to required (by editing the table properties). I created a button at the end of the form with 2 on_click events. When the button is pressed, it will open a new form and close the current form (after auto saving). My problem is that if participants click on the button directly (without filling out the details) it would still take them to the next screen/form

So I searched around and there is the "AfterUpdate" argument in VB where I can disable the button if the particular value of that field is null. I used the following code

Private Sub First_Name_AfterUpdate()

If Me.First_Name = "" Then
Me.Demo_next.Enabled = False
Else
Me.Demo_next.Enabled = True
End If

End Sub

Private Sub last_Name_AfterUpdate()

If Me.Last_Name = "" Then
Me.Demo_next.Enabled = False
Else
Me.Demo_next.Enabled = True
End If

End Sub

The problem with this type of arrangement is that even if a single condition is met (for e.g. only first name is entered and not last name), the button is enabled. What would be a good workaround for this?

The code that I am currently using is for string variable. What should the code look like if I have numeric variables? or an option box?

I read somewhere on this forum about setting up a command button toggle. I have no idea how to do it and how it works. Can anyone help me please? I would really appreciate any help.

I'm attaching a short demo of my database. It is in access 2007 format- had to zip it due to file size.

 

[GrahamMandeno]

Hello vzx

I don't think it's necessary to disable the "Next" button. Instead, why not check that the required values are supplied when the button is clicked and proceed only if everything is OK?

Since you want to perform this kind of check for several forms, it makes sense to use a general-purpose function which can be re-used. Here is something that might work for you:

Public Function CheckRequiredFields( _
  f As Form, FieldList As Variant, _
  Optional NormalColour As Long = vbWhite, _
  Optional HighlightColour As Long = &H60FFFF) As Integer
Dim iFirstTab As Integer, sFirstTab As String
Dim c As Control, i As Integer, iBadFields As Integer
  For i = LBound(FieldList) To UBound(FieldList)
    Set c = f.Controls(FieldList(i))
    If Len(c.Value & "") = 0 Then
      If iBadFields = 0 Or c.TabIndex < iFirstTab Then
        iFirstTab = c.TabIndex
        sFirstTab = c.Name
      End If
      iBadFields = iBadFields + 1
      c.BackColor = HighlightColour
    Else
      c.BackColor = NormalColour
    End If
  Next
  If iBadFields Then
    f.Controls(sFirstTab).SetFocus
    CheckRequiredFields = iBadFields
  End If
End Function

Call it like this from your "Next" button's Click event procedure:

If CheckRequiredFields( Me, Array( "FirstName", "LastName", "OtherField", "YetAnotherField" ) ) <> 0 then
    MsgBox "Please fill in all required fields before continuing", vbExclamation
Else
    ' do whatever is needed to continue
End If

If any of the fields named in the list you pass are blank then they will be highlighted in yellow and the code will not proceed.

--
Graham Mandeno [Access MVP]

 

[vzx]

It worked. I do not have enough words to thank you. If I would have tried to figure it out alone, it would have taken me weeks. You are a life saver.

Just one last question. I was using the macro builder on the button to get me to the next page. Obviously I will have to use some different code to open the form in VB.

Lets say that I want to do the following:

If all the conditions are met and I want to move to the next form "PMH" (in add data mode) and also close the existing form. what should the last piece of code look like. This is what I did but I got errors

Private Sub Demo_next_Click()

If CheckRequiredFields(Me, Array("First_Name", "Last_Name", "ID")) <> 0 Then
    MsgBox "Please fill in all required fields before continuing", vbExclamation
Else
    DoCmd.OpenForm (pmh)
End If

End Sub

Again I cannot express enough gratitude for all your help.

 

[vzx]

@ GrahamMandeno I figured out the save and open feature but I'm getting errors on certain fields. I am not sure what I'm doing wrong. Can you help me?

Here's the code and the file I was playing with

Private Sub Demo_next_Click()

If CheckRequiredFields(Me, Array("ID", "Last_Name", "First_name", "Age", "DOB", "Street", "Apartment", "Gender", "Race", "Insurance", "height_feet", "Height_inches", "Wt_18_lbs", "Wt_now_lbs", "Waist_inches", "hip_inches", "Education", "Marital status", "income_level")) <> 0 Then
    MsgBox "Please fill in all required fields before continuing. Required fields are highlited in green", vbExclamation
Else
    DoCmd.OpenForm "pmh", acNormal, , , acFormAdd
    DoCmd.Close acForm, "demographics", acSaveYes
End If

End Sub

 

[GrahamMandeno]

That's almost correct, except that the name of the form must be in quotes (otherwise it will be interpreted as the name of a variable) and also, I assume you will want to close the current form when you open the new one. Oh, and I see you want the form opened in data entry mode...

Private Sub Demo_next_Click()

If CheckRequiredFields(Me, Array("First_Name", "Last_Name", "ID")) <> 0 Then
    MsgBox "Please fill in all required fields before continuing", vbExclamation
Else
    DoCmd.OpenForm "PMH", DataMode:=acFormAdd
    DoCmd.Close acForm, Me.Name
End If

End Sub

Good luck!

--
Graham

 

[vzx]

@ GrahamMandeno

Even when I use the corrected code, I get the following error (see screen shot attached) and upon entering the debug mode, I see the following code highlighted

    Set c = f.Controls(FieldList(i))

I am not sure what's going on

 

[vzx]

Never mind... I figured it out burning the midnight oil. It was the comboboxes. They were named as combo123, etc rather than gender, race, etc. I corrected those names and now everything is running smooth

I really appreciate your time and would like to add credit to you at the end of the code. Would that be OK with you? You can PM me with your contact details. Thanks.

 

[GrahamMandeno]

That's great! I'm glad you got it working.

Thanks for offering to credit. Just my sig below will be fine

--
Graham Mandeno [Access MVP - Auckland, New Zealand]

 

[vzx]

@ GrahamMandeno

Hi again. I'm stuck at a point further down in my database design and wanted to see if you would be able to give your input

I'm now at a point where there are boolean questions. i.e. if Answer to question 4 is "no", then skip to question 8. and likewise. If they click on any other response to question 4, then they will need to answer all the questions from 5 to 8. There are many questions like moving forward into the database.

Obviously I do not want patients to become confused and waste their time reading it. So I used the following code to disable them.

Private Sub q4_afterupdate()

If Me.q4.Value = 0 Then
    Me.q5.Enabled = False
    Me.q6.Enabled = False
    Me.q7.Enabled = False
Else
    Me.q5.Enabled = True
    Me.q6.Enabled = True
    Me.q7.Enabled = True
End If

End Sub

I used the following code for validation prior to going to the next form.

If CheckRequiredFields(Me, Array("q4", "q5", "q6", "q7", "q8")) <> 0 Then
    MsgBox "You have missed answering one or more questions. Please go back and answer the highlighted questions.", vbExclamation
Else
    DoCmd.OpenForm "FBD2", DataMode:=acFormAdd
    DoCmd.Close acForm, Me.Name
End If

If a participant selected no to q4, the next few questions q5,q6 & q7 will be disabled. on clicking the next button, I get the following error

runtime error 2110. Access cannot move focus to q5.

On debugging, I get an error on this line of the public function

    f.Controls(sFirstTab).SetFocus

I'm assuming that the public function is trying to highlight the missing variable but since it is disabled, access is confused on how to highlight a disabled field and pops up that message. Is there a way around this conundrum?

 

[GrahamMandeno]

Hello again

The problem is that the list of required fields varies according to the answers that were given.

I suggest you declare a variable for the "required" list and set that variable accordingly, then pass it to the CheckRequiredFields function:

Dim aRequired as Variant
 
If Me.q4 = 0 Then
  aRequired = Array("q4", "q8")
Else
  aRequired = Array("q4", "q5", "q6", "q7", "q8")
End If
 
If CheckRequiredFields(Me, aRequired) <> 0 Then
    MsgBox "You have missed answering one or more questions. Please go back and answer the highlighted questions.", vbExclamation
Else
    DoCmd.OpenForm "FBD2", DataMode:=acFormAdd
    DoCmd.Close acForm, Me.Name
End If

HTH!

--
Graham

 

[vzx]

I get the general idea just a technical snag.

The form that I'm designing has multiple such "skip to" questions.

for e.g.

if fbda1 is 0, skip to fbda5. If fbda1 <> 0 then answer fbda2, 3 and 4

Again on fbda5, if value = 0 skip to next form. If fbda5 <> 0 then answer fbda6 & 7.

going by your logic, this is what I programmed:

Dim aRequired1 As Variant
 
If Me.fbda1 = 0 Then
  aRequired1 = Array("fbda1")
Else
  aRequired1 = Array("fbda1", "fbda2", "fbda3", "fbda4")
End If

Dim aRequired2 As Variant
 
If Me.fbda5 = 0 Then
  aRequired2 = Array("fbda5")
Else
  aRequired2 = Array("fbda5", "fbda6", "fbda7")
End If

How should I code this into the button command. I tried the following and obviously it did not work

If CheckRequiredFields(Me, aRequired1, aRequired2) <> 0 Then
    MsgBox "something here", vbExclamation
Else
    DoCmd.OpenForm "FBD2", DataMode:=acFormAdd
    DoCmd.Close acForm, Me.Name
End If

I anticipate many such skip to questions moving forward. What is the correct syntax to be used in such a situation when there is more than one declared variable which is required.

 

[Adam Caramon]

Hi vzx,

Is there any particular reason you're doing this project in Access? It sounds to me like this type of a project would be a lot easier (and probably better) if it was done with a survey tool, such as surveymonkey.

I work for a hospital and we often use surveymonkey (though for different purposes). Its true for everyone, but especially for physicians, time is money, and I have a feeling surveymonkey would save you a lot of time.

Good luck.

 

[vzx]

That was my first thought but there are patient privacy issues. Cannot post anything online as the information collected is very personal like name, address, medical history, etc

Our IRB did not approve of online tools

 

[vzx]

I kinda found a workaround for the limitation of the code but its too lengthy. I'm pretty sure there might be other easier ways and would welcome an alternative but in the meanwhile this piece of code works

Dim aRequired1 As Variant
 
If Me.fbda1 = 0 And Me.fbda5 = 0 Then
  aRequired1 = Array("fbda1", "fbda5")
Else
    If Me.fbda1 <> 0 And Me.fbda5 = 0 Then
        aRequired1 = Array("fbda1", "fbda2", "fbda3", "fbda4", "fbda5")
    Else
        If Me.fbda1 = 0 And Me.fbda5 <> 0 Then
            aRequired1 = Array("fbda1", "fbda5", "fbda6", "fbda7")
        Else
                aRequired1 = Array("fbda1", "fbda2", "fbda3", "fbda4", "fbda5", "fbda6", "fbda7")
        End If
    End If
End If

Basically I just listed out all the diff combination of all the responses in the variable. This looks easy as long as there are no more than 2 skip questions on a form. If there are more, I could possible go crazy manually coding for each of the responses.

 

[GrahamMandeno]

As someone once said, "necessity is the mother of invention"

Here is a function to append values to an array:

Public Function AppendArray( _
    AddTo As Variant, _
    ParamArray NewVals() As Variant _
  ) As Variant
Dim a As Variant, i As Integer, iFirstNew As Integer
If IsArray(AddTo) Then
  a = AddTo
  iFirstNew = UBound(a) + 1
  ReDim Preserve a(UBound(a) - LBound(a) + UBound(NewVals) + 1)
  For i = 0 To UBound(NewVals)
    a(iFirstNew + i) = NewVals(i)
  Next
  AppendArray = a
Else
  AppendArray = NewVals
End If
End Function

You can use it like this:

Dim aRequired As Variant
 
' initialise array with unconditional required fields
aRequired = Array("fbda1", "fbda5")
 
' now add the conditional fields as required
 
If Me.fbda1 <> 0 Then
  aRequired = AppendArray(aRequired, "fbda2", "fbda3", "fbda4")
End If
 
If Me.fbda5 <> 0 Then
  aRequired = AppendArray(aRequired, "fbda6", "fbda7")
End If
 
If CheckRequiredFields(Me, aRequired) <> 0 Then
    MsgBox "You have missed answering one or more questions. Please go back and answer the highlighted questions.", vbExclamation
Else
    DoCmd.OpenForm "FBD2", DataMode:=acFormAdd
    DoCmd.Close acForm, Me.Name
End If

--
Graham

 

[vzx]

This is what I had wanted. You're a genius!!!

One last thing. Do I have to paste the public function in the VB code for all forms or can I call the function in from1 from form2. I tried calling it but got an error. I didn't think much about it, just pasted the public function in all forms

I just looked at the file size. It shot up from 4MB to 11MB after just adding the code.

 

[GrahamMandeno]

I'm glad it's working well for you

Both the public functions (AppendArray and CheckRequiredFields) should be pasted into a standard module, not a form module. Then they can be called from your form modules.

The file size must have increased for some other reason. Do a compact/repair (Tools > database utilities) to get the space back.

Good luck!

Graham