I have a form with a large number of text boxes that must be completed. I would like to use a formula if possible to check if any are incomplete rather than loop through using VBA.
I feel sure that it should be possible but cannot find any reference on the web.
The easiest way is to loop through the form controls, especially if you have a large number of then.
I use the following public function. In the tag property of the controls to check add "V8". It fires a message box of the missing fields and returns a boolean value.
Code:
Public Function IsfrmValid(frm As Form) As Boolean
Dim ctl As Variant
Dim flg As Boolean
Dim strMsg As String
flg = True
For Each ctl In frm.Controls
If InStr(1, ctl.Tag, "V8") Then
If Nz(ctl, "") = "" Then
flg = False
ctl.BorderColor = vbRed
strMsg = strMsg & Space(20) & "* " & ctl.Controls.Item(0).Caption & vbNewLine
Else
ctl.BorderColor = vbBlack
End If
End If
Next ctl
IsfrmValid = flg
If flg = False Then
MsgBox "The following item(s) are required:" & vbNewLine & vbNewLine & strMsg
End If
End Function
You have already received an excellent answer from moke123
I would add the following observations:-
I think your question needs some clarification, my guess is you mean a macro and not VBA code.
It also appears you are checking for partially filled text boxes, this presents the problem of what conditions represent a completed, or uncompleted text box you need to explain the conditions.
.
As far as I am aware the only way to do this is by looping through the controls with VBA code. Having said that I'm sure somebody has written code which works without a loop, and if they have I'd love to see it. I'm sure it would be an excellent example of why not to do it that way!
Hi. You can use the principle of null propagation to determine if any textbox is empty without using a loop, but it won't tell you which one is empty, only that there is at least one empty box.
Jeff,
More info needed. Perhaps you could define "incomplete" as it relates to your project.
A few examples or scenarios describing the before and after of your "textbox checking process" would help with context.
As always, clarity in the requirement description will expedite your receiving more focused responses.
Sorry I could not post yesterday and thank you to all responders.
I am attaching a sample illustrating what I used that generates an error message if there is no data or if an empty box, or boxes, is left in any of the groups (both five and three).
you already have the sub there, only some slight modification:
Code:
Private Sub cmdSetMInMax_Click()
Dim tbmin As Integer
Dim tbmax As Integer
Dim gnum As Integer
Dim error As Boolean
Dim count As Integer
Dim totcount As Integer
For gnum = 1 To 9
Let count = 0
For tbmin = 1 To 5
If Not IsNull(Me.Controls("tbgamesMin" & gnum & tbmin)) Then
Let count = count + 1
Let totcount = totcount + 1
Else
Exit For
End If
Next tbmin
If count < 5 Then
Let error = True
Exit For
End If
Let count = 0
For tbmax = 1 To 3
If Not IsNull(Me.Controls("tbgamesMax" & gnum & tbmax)) Then
Let count = count + 1
Let totcount = totcount + 1
Else
Exit For
End If
Next tbmax
If count < 3 Then
Let error = True
Exit For
End If
Next gnum
If error = True Then
MsgBox "Please complete all values before continuing.", vbCritical, "Missing data"
End
End If
If totcount = 0 Then
MsgBox "No data has been entered, please complete all values before continuing.", vbCritical, "Missing data"
End
End If
' Additional code here
End Sub
Please find attached CountMT_TextBoxes_NiftyAccess.accdb which uses the controls collection as originally suggested by moke123. I strongly suggest you study this method as understanding it will help you take your coding to the next level.
To aid you in understanding the "For Each Ctrl in Me.Controls" method, I have done a YouTube video explaining how the code works...
Private Function fSomeMT() As Boolean
'fSomeMT = Some Empty
Dim conAppName As String
conAppName = "(Replace this Local Variable with a Global One) "
Dim strSubName As String
Dim strModuleName As String
strSubName = "fSomeMT"
strModuleName = "Form - " & Me.Name
On Error GoTo Error_Handler
Dim Ctrl As Control
Dim intEmptyCtrls As Integer
For Each Ctrl In Me.Controls
Select Case Ctrl.ControlType
Case acTextBox
If Left(Ctrl.Name, 6) = "tbGame" Then
Ctrl.BorderColor = vbBlack
Ctrl.BackColor = vbWhite
If IsNull(Ctrl) Then
intEmptyCtrls = intEmptyCtrls + 1
Ctrl.BorderColor = vbRed
Ctrl.BackColor = vbYellow
End If
End If
End Select
Next Ctrl
If intEmptyCtrls > 0 Then
fSomeMT = True
End If
Exit_ErrorHandler:
Exit Function
Error_Handler: 'Version - 1a
Dim strErrFrom As String
Dim strErrInfo As String
strErrFrom = "Error From:-" & vbCrLf & strModuleName & vbCrLf & "Subroutine >>>>>>> " & strSubName
strErrInfo = "" & vbCrLf & "Error Number >>>>> " & Err.Number & vbCrLf & "Error Descscription:-" & vbCrLf & Err.Description
Select Case Err.Number
Case 0.123 'When Required, Replace Place Holder (0.123) with an Error Number
MsgBox "Error produced by Place Holder please check your code!" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
Case Else
MsgBox "Case Else Error" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
End Select
Resume Exit_ErrorHandler
End Function 'fSomeMT
A recent question on Access World Forums provided me the opportunity to demonstrate a new variation on one of my favourite coding methods with MS Access VBA:-“For Each Control in Me.Controls”.This technique allows you to look at each control on your Access Form in turn, make a decision about the...
gum.co
A couple of points about your original code:-
I believe "Error" is a reserved word, in other words you could have unintended consequences by using that word in your code... Use a naming convention when you write your code & you avoid this type of Error... I would replace it with a variable that doesn't mean anything in the literal sense... For example use:- "Dim blnError As Boolean"
In your function:-
There's a problem...
'Only this Message is Activated
MsgBox "Please complete all values before continuing.", vbCritical, "Missing data"
'This Message is never Activated
MsgBox "No data has been entered, please complete all values before continuing.", vbCritical, "Missing data"
When I first started out with MS Access I would use "Let" ... I think I inherited the idea from BASIC. I posted some code on a forum, pretty sure it was Woody's Access Lounge. Someone told me that I shouldn't use "Let" because Microsoft may discontinue it.
It wasn't until years later I discovered that the "Let" Statement is used as you describe, in Property Statements. I realised that it would never be discontinued, and I should have stuck to my guns and kept using it!
These days I do occasionally put it in, mostly to make the code line up and look prettier. There is one really good reason for using it, it distinguishes your code, as in a signature. It would make your code much easier to track down if you thought someone had stolen your code! Now I've revealed that use, I've just taken away the advantage!
Or to put it a different way, I'm fairly sure arnel's code would work exactly the same if all instances of Let were removed
For example just use TotCount=TotCount+1
BTW in addition to Error, Count is a reserved word so I would use e.g. iCount instead.