Make sure many form text boxes are not empty.

djkay2637 · Jul 5, 2019

Hi All,

Using this forum I discovered this fully working validation check, so first off, thank you.

Code:

Private Sub TextBoxName_LostFocus()
	If Len(TextBoxName.Value & vbNullString) = 0 Then
	'do something
	Endif
End Sub

I have another question to ask. I have quite a lot of text boxes that can not be empty. I could simply copy the same code to all of the text box's Lostfocus subroutine however this is very messy and i know there must be a method to reduce the coding. Perhaps by creating a subroutine or function that the text box's LostFocus could 'call'.

Any guidance as to how that could be done?

Thanks.
Dan

arnelgp · Jul 5, 2019

yes of course you can create a public Function, pass the textboxname to it as parameter:

Code:

Public Function fnTextboxCheck(t As Textbox)
   If Trim(t.Value & vbNullString) = "" Then
      ' textbox is empty
      ' show some message, etc.
   End If
End Function

you can call the function in the LostFocus event as you have:

Code:

On Lost Focus         =fnTextboxCheck([textboxnameHere])

CJ_London · Jul 5, 2019

validation of whether or not a control has not been completed is better to be done in the form beforeupdate event. The reason is a user may not have gone to a control to complete it in the first place.

the principle is to loop through all the forms controls, identifying which ones need to be completed and pop up a message box

Typically you use the tag property to identify the required controls

So you might do something along these lines

in the tag property put 'Req' or similar for the relevant controls

then in the form before update event put something like

Code:

dim ctl as control
for each ctl in me.controls
    if ctl.tag="Req" and isnull(ctl) then
        msgbox ctl.name & " must be completed before this record can be completed"
        ctl.setfocus
        exit sub

next ctl

djkay2637 · Jul 5, 2019

Thank you arnelgp for your advice. I have tried using calling the function but not getting any joy. I was trying to call the function and pass though it's name by using the code.

Code:

Private Sub Text169_LostFocus tbEmpty(Text169)
 
End Sub

CJ_London
I like the idea of this loop it looks far more efficient that what I was going to do. I will also take your advice by using the 'beforeupdate' command.

So to conclude, if i scroll through the property sheet for each text box i want to run this function for and add a string "Req" the code you suggest will look though the entire form and run that function if that tag exists. Would place your code as a function or a sub. Once declared, would i run the code by simply includeing the sub or function name within that text box's beforeupdate.

Thanks again.

MajP · Jul 5, 2019

Similar to already stated. You can build a single function that can handle multiple events. It has to be a function. You can use the activecontrol to simplify.

in the lostfocus events type in
=Alert()

Then add a function like

Code:

Private Function Alert() As Boolean
  Dim ctrl As Access.TextBox
  Set ctrl = ActiveControl
  If Trim(ctrl.Value & " ") = "" Then
    MsgBox ctrl.ControlSource & " is a required field. Please return and fill out.", vbInformation, "Fill in"
  End If
End Function

I would check in the forms before update event.

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Dim ctrl As Access.Control
  Dim rtn As String
  Dim originalRtn As String
  rtn = "The following fields must be filled in: " & vbCrLf
  originalRtn = rtn
  For Each ctrl In Me.Controls
    If ctrl.ControlType = acTextBox Then
        If ctrl.OnLostFocus = "=alert()" And Trim(ctrl.Value & " ") = "" Then
          rtn = rtn & ctrl.ControlSource & vbCrLf
        End If
    End If
  Next ctrl
  If rtn <> originalRtn Then
    MsgBox rtn
    Cancel = True
  End If
End Sub

To identify the required fields I just used those already with a lostfocus event, but more common is to use the tag property.

CJ_London · Jul 5, 2019

would i run the code by simply includeing the sub or function name within that text box's beforeupdate.

not the textbox, the form

the textbox beforeupdate is not fired until the user leaves the textbox by keyboard tabbing or clicking on another control - and at that point the value has not been updated but is stored in a property called text

it might help if you had a better understanding of events - when they are fired and in what order. Here is a link for the order of events

https://support.office.com/en-us/ar...ects-e76fbbfe-6180-4a52-8787-ce86553682f9#bm1

if you want to know more about what a specific event does - this link is about the textbox control beforeupdate event
https://docs.microsoft.com/en-us/office/vba/api/access.textbox.beforeupdate-event

to the left you will see a long list of different types of objects (such as a textbox control, sections and the like). click on one of these, then select events and then the event you are interested in. You might want to bookmark the page for future reference

MajP · Jul 5, 2019

There are three things you can put in an event property
[Embedded Macro] 'tell it that it will be handled by a macro
[Event Procedure] 'tells is that it will be handled by an event procedure
=SomeExpression() 'tells it that it will be handled by a specific function or expression

You do not have to scroll to add a function name. Simply highlight all the textboxes that you want to be handled by the function name. Then add the function name (preceeded by = and followed with () )to the common property. You can do as many controls as you want in a single entry.

CJ_London · Jul 5, 2019

one other thing to consider is to have a visual effect for users to see. One way to achieve this without code is to use the control format property - which can also show text in an otherwise numeric field

for text you would use

@;"Required"
or
@;"Enter Address"

or for a different colour (limited range available) for the message

@;[Red]"Required"

these format the control values when they are not null;null

for numbers you have more options

positive;negative;zero;null

so you might have

0.00;[Red](0.00);"-";"Required"

dates are numbers - so use the number format, but dates cannot be negative so anything in the negative section would be ignored

when the user clicks on the control, the message goes away - only to return if the user doesn't enter anything or deletes what was there before.

Note these are informative messages only, they do not compel a user to complete a particular control.

arnelgp · Jul 5, 2019

after you created the function,
you type:

=fnTextboxCheck([textboxnameHere])

directly on On Lost Focus (Property->Event), and not on it's Vba (code builder).
__________________

djkay2637 · Jul 5, 2019

Thank you for everybodies suggestions. It now works!

Make sure many form text boxes are not empty.

djkay2637

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

CJ_London

Super Moderator

djkay2637

Registered User.

MajP

You've got your good things, and you've got mine.

CJ_London

Super Moderator

MajP

You've got your good things, and you've got mine.

CJ_London

Super Moderator

arnelgp

..forever waiting... waiting for jellybean!

Attachments

djkay2637

Registered User.

Similar threads

Users who are viewing this thread