Need to count and dim each text box in a form 1

[Scoty]

I need to count the number of text boxes in a form then run them through an if..then statement in order to make sure they are not blank.. I am running a letter generating database with several different form letters with different amounts of text boxes on each form. I intend on using the code in the "before update" event to make sure none of the fields have been left blank. I did at one time run the forms with required fields but I have a large end user base and some of the records were not saving because the info did not get entered into the required fields. So I had to kill the required fields opt. But now I am getting a bunch of junk records from users who are not filling out the previously required fields. Instead of going through and hard coding each form (52 at present) I want to check all text boxes in a form to make sure none are blank. Thanks in advance
Scoty

 

[DougP]

Well there is a little known command called Control Object
Here is a DEMO from help
you should be able to modify it slightly to check if each box has a value in it or not

' Call SetTextBoxProperties procedure.
SetTextBoxProperties Me

Sub SetTextBoxProperties(frm As Form)
Dim ctl As Control

' Enumerate Controls collection.
For Each ctl In frm.Controls
' Check to see if control is text box.
If ctl.ControlType = acTextBox Then
' Set control properties.
With ctl
.SetFocus
.Enabled = True
.Height = 400
.SpecialEffect = 0
End With
End If
Next ctl
End Sub

DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[JimHorton]

Scoty,
This is an issue that should be dealt with at design time. When you create the form, whether using form wizard or not, you should always rename the controls to something other than the default (the underlying fieldname). For the fields that require validation, you might name them all in this format
"txt" + fieldname + "VLD" + n Where 'n' is an incremented number, ie
txtFirstNameVLD1
txtLastNameVLD2...and so on. Then, in the final validation, you just say (using some of DougP's example:

dim Ctlnm as string
For Each ctl In Forms!Myfrm.Controls
[tab]Ctlnm = ctl.name
[tab]IF instr(Ctlnm,"VLD&quot Then
[tab][tab]IF isnull(ctl) or ctl = "" Then
[tab][tab][tab]msgbox "Need a value for " & mid(ctlnm,4,instr(ctlnnm,"VLD&quot-4)
[tab][tab][tab]ctl.setfocus
[tab][tab][tab]exit function
[tab][tab]End If
[tab]End IF
Next

The enumeration of the textbox names is not explicitly needed here, since fieldnames will differ, but it's a construct that is extremely usefull when doing mass-validation or other mass changes, etc. on a form with many fields.
--Jim

 

[jimmythegeek]

Use the example below, then you don't have to worry about
what the fields are named. You can add a message box before the Exit sub if you want, otherwise it will just set focus to the first text box that is null.

----------
Dim ctrl As Control

For Each ctrl In Me
If TypeOf ctrl Is TextBox Then
If IsNull(ctrl) Then
Me(ctrl.Name).SetFocus
Exit Sub
End If
End If
Next ctrl

' Do whatever you want here cause if it gets to this point, all the text boxes have values
---------- Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[Scoty]

Jimmy,
Thanks for the code snippet this has put me on the right track. I would like to know if there is any way to tell what the name of

Me(ctrl.Name)

is. This will be helpful if I have some text fields where it doesn't matter if they are null or not. Thanks again
Scoty

 

[jimmythegeek]

Sure, just set a variable, and you can pick up the name anywhere in your code. For instance, using the example from before:

----------
Dim ctrl As Control
Dim strCtrl As String

For Each ctrl In Me
If TypeOf ctrl Is TextBox Then
If IsNull(ctrl) Then
strCtrl = Me(ctrl.Name)
Me(strCtrl).SetFocus
Exit Sub
End If
End If
Next ctrl
----------

You can use the strCtrl variable now, to refer to a particular control. Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[Scoty]

Jimmy,
I emplimeted the new piece of code as suggested above now it stating that i have a runtime error '94'...invalid use of null. I believe that it is trying to set

strCtrl

to the value of

Me(ctrl.Name)

rather than the name of

Me(ctrl.Name)

. I'm wondering if there is a "delimiter" I can use to just get the name of the

TextBox

. Thanks in advance.
Scoty

 

[jimmythegeek]

Sorry about that, the following line:

strCtrl = Me(ctrl.Name)

Should read:

strCtrl = ctrl.Name Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[Scoty]

Jimmy you da' man...whooohooo...you got my vote..and

ME(ctr.FEELINGS)

in other words graditude and ahw. Thanks Again
Scoty :