Check for Null Values and Display Error Button

[Guest]

I have a form that has a series of questions that can either be answer,
"Yes", "No", or "Undetermined". When "Undetermined" is selected, a comment
box appears that requires data entry. Here is what I want to accomplish when
the user clicks the "Review Complete" button:

1.) Check that each question has been answered. If not, activate a hidden
text box next to each question that indicates that that particular question
has not been answered.

2.) If a question has been answered as "Undetermined", validate that the
comment box is not blank, otherwise it would activate the same box as above
indicating that that particular question is not complete.

3.) If all questions have been answered and comments are entered as
indicated, then the "Choose next case" button is activated.

Kind of complicated and I am a novice programmer. Any help would be
appreciated.

Melinda

 

[Guest]

There are other and better ways to do this, but this is what i came up with
in a hurry.
This is just to give you a few ideas on how to set this up.

Private Sub Combo0_AfterUpdate()
If Me.Combo0.Value = "Undetermined" Then
Me.Text2.Visible = True
Me.Text2.SetFocus
End If
End Sub

Private Sub Command6_Enter()
testIfDone
End Sub

Private Sub Text2_LostFocus()

If Me.Text2.Text = "" Then
MsgBox "You are not done!"
End If

End Sub

Private Sub testIfDone()
'This will test each combo box to see if they didn't even try to pick an
option before moving on
If IsNull(Me.Combo0.Value)Then
MsgBox "You missed me"
Me.Combo0.SetFocus
Exit Sub
End If
If IsNull(Me.Combo4.Value) Then
MsgBox "You missed me"
Me.Combo4.SetFocus
Exit Sub
End If

End Sub

Good Luck!

 

[Guest]

I am open to any suggestions on how to do this better and more efficiently.
Pleae let me know what you mean. Thanks for the help.

Melinda

 

[Guest]

I just got a lot of work dumped on me so iam a little busy right now. But i
will teach you how to do the same thing but with collections. It might be a
few hours or a day or two before i can get back to you, but i will get back
to you.

Sorry about the wait.

 

[Guest]

Thank You very much.... I'll wait to hear from you again. I appreciate it.

 

[Guest]

Ok melinda, i have a new and better way to show you.
This one is a more complex way but will allow you to edit your form without
editing your code.
You will need to make your textbox names start with the name of the combo
box it is connected to like: combo0 --> combo0Textbox0 and combo1 -->
combo1Textbox1 ....
Also set all your textbox visible properties to false

I have not tested this completely so let me know of any bugs or just any
questions.
------------------------------------------------------------------------


' Here is the code
Private Sub Combo0_AfterUpdate()
If Me.Combo0.Value = "Undetermined" Then
'set the visible property to false when u insert each textbox or u can
set the property of each textbox to false in the Open event of the form

Me.Combo0Text2.Visible = True
Me.Combo0Text2.SetFocus
Else
Me.Combo0Text2.Visible = False
End If
End Sub

Private Sub Command6_Enter()
Dim returnValue As Boolean
returnValue = testIfDone
If returnValue = True Then
'put your command here to move to the next part
End If

End Sub

Function testIfDone() As Boolean 'functions are the same as sub's but they
return data where sub's just run code
'this is how you use a collection
Dim oneControl As Control 'this variable to hold one control
Dim allControls As Controls 'this variable to hold all the controls
Dim returnValue As Boolean

Set allControls = Me.Controls 'set the allcontrols variable to the forms
controls collection
testIfDone = False 'if it finds a problem it will return false .. it will
only be set to true if it get to the end
For Each oneControl In allControls 'loop threw all the controls on the form

If oneControl.ControlType = acComboBox Then 'acComboBox is a constant--
which meens that MSAccess kind of like a reserved word
If IsNull(oneControl.Value) Then 'test to see if they even touched
the combo box -- isnull returns a true if it is null -- and if its is null
then they never selected anything
MsgBox "You missed this one. You need to pick something for all
fields."
oneControl.SetFocus
Exit Function
Else 'if they selected something then test for what they picked
If oneControl.Value = "Undetermined" Then
'now because we don't know where the textbox that matches
this combo box is in the collection, we will have to loop threw again and
find it
'but this time we will use the same collection but loop thew
it in a different way

'i would suggest you rename the textboxes to start with the
same name as your combo boxes like: combo0 make combo0textbox0 and combo1
make combo1textbox1 ...
'that way we can just get the first part of the textbox name
and compare it to the combo box name
'Like i said before there are a lot of different ways to do
the same thing. some are faster, some are more complex, but it all depends on
your style and what your goal is
'i like do this way for one, its what i would do, and two it
teaches you sever different things like string parsing, functions and
collections
returnValue = testForEmptyTextbox(oneControl.Name)
If returnValue = True Then 'if you found a problem then exit
and have them repress the button when done entering data
Exit Function
End If

End If
End If
End If

Next oneControl

testIfDone = True

End Function


Function testForEmptyTextbox(strComboName As String) As Boolean
'functions are the same as sub's but they return data where sub's just run
code
'i made this function to search the collection and find the matching textbox
'also i looped threw the controls collection in a different way

Dim allControls As Controls 'this variable to hold all the controls
Dim x As Integer
Dim lengthOfName As Integer

Set allControls = Me.Controls 'set the allcontrols variable to the forms
controls collection
testForEmptyTextbox = False 'i set this to false here because if you don't
find the control then it never changes to true so the caller gets a failed
find
lengthOfName = Len(strComboName) 'because we to know how long it is so we
can grab the same length from the textbox name

For x = 0 To allControls.Count - 1 'loop threw all the controls on the form
If allControls.Item(x).ControlType = acTextBox Then 'same with acTextBox
-- it find all the constant names for controls goto MSDN or MS Knowledge Base
and type in the search "ControlType Property"
If Left(allControls.Item(x).Name, lengthOfName) = strComboName Then
If IsNull(allControls.Item(x).Value) Then
MsgBox "If you pick 'Undetermined' then you need to fill in
the box next to the brop down box. Please fill it in now"
allControls.Item(x).SetFocus
testForEmptyTextbox = True
Exit For
End If
End If
End If
Next x

End Function