Full Version: Make sure text is filled out
UtterAccess Forums > MicrosoftŪ Access > Access Forms
williams9969
Greetings all-
I have a function that I need to ensure two text boxes are filled out...
Can someone provide me with guidance on programatically ensuring the text boxes are filled out...and if not, go to the text boxes so the user can fill them out
Help is appreciated
V/R
DWilliams
LenaWood
You could use an If statement
If isnull([TextBox1] then
MsgBox "You can't leave this field blank"
me.textbox1.setfocus
exit sub
end if
do the same for the second text box.
I have done this type of thing behind a button that closes the form...this helps make sure that your fields are all filled out before closing the form.
This is just one way to do it...not necessarily the best way though.
Lena
Larry Larsen
Hi
I use a slightly different method which uses the "Tag" property of the controls I want to validate before the record is saved.
On the BeforeUpdate event of the bound form I call a small function which will look through each form control and check to see if I have set a value within the tag property indicating please check this one..
CODE

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not [color="red"]fnValidateForm[/color](Me) Then
        Cancel = True
    End If
End Sub

Here's a similar function that I use as some forms require different valuation criteria.
Note: This particular function checks for both null's and (0) length values also numeric values.
CODE
Public Function [color="red"]fnValidateForm[/color](frmA As Form) As Boolean
Dim ctl As Control
fnValidateForm = True
For Each ctl In frmA.Controls
    'value in the control is required
    If InStr(1, ctl.Tag, "Required") > 0 Then
       ' no value entered or value is null
       ' or zero for numeric fields
       If (IsNull(ctl.Value)) Or (Len(ctl.Value) = 0) Then
          ctl.SetFocus
          MsgBox "Value required"
          fnValidateForm = False
          Exit For
       End If
       If InStr(1, ctl.Tag, "NumberRequired") > 0 Then
          If ctl.Value = 0 Then
                ctl.SetFocus
                MsgBox "Value required"
                fnValidateForm = False
                Exit For
          End If
       End If
    End If
Next
End Function

HTH's
thumbup.gif
williams9969
I am having trouble implementing the If function you gave me:
My code:
Private Sub DepartSoldier_Click()
Me.Refresh

DoCmd.OpenReport "perS1OutprocessingReport", acNormal

DoCmd.OpenQuery "qryDepartAppend"

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.GoToRecord , , acFirst
Forms!frmAll!cboSearch.SetFocus
End Sub
The first thing that should happen is check to see if cboDepartureReason is filled, otherwise go to cboDepartureReason.
Next, check to see if cboDepartureDate is filled, otherwise go to cboDepartureDate.
Once both are filled in..or checked if they are filled in then proceed with the report, then query, etc., etc.
I am getting error, after error...and I need help.
Thanks in advance
V/R
D.Williams
Jack Cowley
If IsNull(Me.cboDepartureReason) Then
MsgBox "You must make a selection from the Departure Reason combo."
Me.cboDepartureReason.SetFocus
Exit Sub
End If
If IsNull(Me.cboDepartureDate) Then
MsgBox "You must make a selection from the Departure Date combo."
Me.cboDepartureDate.SetFocus
Exit Sub
End If
The rest of your code without the Me.Refresh. I do not understand what your code is supposed to do when it runs the query, etc. so I won't go there...
You do not say what errors you get and that can help someone to help you find the problem so be sure in future to say what your errors are.
hth,
Jack
williams9969
What kind of error handling would you suggest for this?
I am new to all of this...but I used this:
My code:
Private Sub DepartSoldier_Click()
On Error GoTo Err_DepartSoldier_Click
If IsNull(Me.cboDepartureReason) Then
MsgBox "You must fill in Departure Reason."
Me.cboDepartureReason.SetFocus
Exit Sub
End If
If IsNull(Me.DepartureDate) Then
MsgBox "You must fill in Departure Date."
Me.DepartureDate.SetFocus
Exit Sub
End If

DoCmd.OpenReport "perS1OutprocessingReport", acNormal

DoCmd.OpenQuery "qryDepartAppend"

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.GoToRecord , , acFirst
Forms!frmAll!cboSearch.SetFocus
Exit_DepartSoldier_Click:
Exit Sub
Err_DepartSoldier_Click:
MsgBox Err.Description
Resume Exit_DepartSoldier_Click
End Sub
Is this sufficient in your opinion?
Thank you
V/R
D. Williams
Jack Cowley
D. Williams -
The 'validation' code should work. If that is what you used and it is doing what you want then that should be all that you need to do...
Let us know if you are still having a problem...
Jack
niesz
Jack,
don't think IsNull(YourControlName) tests for Zero-Length strings. You may want to add this test or replace it with
If Nz(YourControlName) = "" then
...
Jack Cowley
niesz -
You are certainly correct in the fact that the code does not check for zero length strings!
If Len(Me.MyControl & vbNullString) = 0 Then
Your suggestion or the code above would have been a better solution.. Thanks for the heads up!
Jack
Oused to use: If IsNull(Me.MyControl) or Me.MyControl = "" Then. Sometimes it is hard to teach an old dog new tricks...
Chas_Large_UK
Just like to say thanks for all the replies to the posts above. They have helped me termendously even though I didn't post the original question.
Thanks
Chas
Jack Cowley
Welcome to Utter Access Forums!
He are glad you joined us and that you found this post helpful!
Jack
Senilex
How can you do this if you need to base it on another criteria?
Such as only when certain values are selected from a combo box.
So it would be, check if a field is completed only when a certain value is selected on a combobox.
hx in advanced.
Jack Cowley
If Me.ComboBoxName = "Wednesday" Then
If Len(Me.MyControl & vbNullString) = 0 Then
MsgBox "You must enter data into MyControl."
Cancel = True
Me.MyControl.SetFocus
End If
End if
nother possibility:
Select Case Me.ComboBoxName
Case "Wednesday"
MsgBox "..."
Cancel = True
Me.MyControl.SetFocus
Case "Friday"
MsgBox "..."
Cancel = True
Me.MyControl.SetFocus
End Select
The Cancel = True may not apply as it is dependent on where you use your code.
hth,
Jack
beyondnerd
I'm trying to accomplish the same thing as williams9969 and have been trying to use the same method that that LenaWood mentioned. My Code:
Private Sub cmbStaff_Exit(Cancel As Integer)
If IsNull(Me.cmbStaff) Then
MsgBox "You must Specify a staff Member responsible for the Purchase", vbOKOnly, "Select Staff"
DoCmd.GoToControl "cmbStaff"
Else
End If
End Sub
My problem is that when I test the code if I click on another control on the form, the message box appears but after clicking OK the focus goes to the the other control not to cmbStaff. I also tried cmbStaff.SetFocus, same results. Also tried theese variations on the after Update Event, same result. any suggestions?
Jack Cowley
Generally you validate your data in the Forms BEFORE Update event. Otherwise the user can use the mouse to pick and choose which controls to move to and in the process your control can be missed. Try code like this in the Forms Before Update event:
If Len(Me.cmbStaff & vbNullString) = 0 Then
MsgBox "You must Specify a staff Member responsible for the Purchase", vbOKOnly, "Select Staff"
Cancel = True
Me.cmbStaff.SetFocus
End If
hth,
Jack
beyondnerd
Jack thanks for the reply. I tried your code, what's happening now is that if I make an entry on another control for the record while leaving cmbStaff blank and try to move to another (or new) record the message box appears and the focus returns to cmbStaff. Just what I'm looking for, however if after the combo box appeard I again select another record the message box appears but when I click OK the focus does not go to cmbStaff thus allowing the user to skip the necassary field (Perhaps because the record is not actually updating). I know how to set up the form so that all necessary fields are validated before closing the form but I'm looking to set up a process whereby the user is prevented from moving to a new record unless all necessary data has been entered, Ideally the check will occor after each control is filled out or left blank.
Jack Cowley
Checking each control as it is completed is not necessarily going to work unless they do not have a mouse on their computers. It is easy to pick and choose which controls to fill if a mouse is available so my suggestion is to put code in the Forms Before Update event and check all the controls there. I don't see how you can guarantee all controls have data if you check each control separately.
do not quite understand what your problem was with using the Forms Before Update event so I am not sure what to tell you. If you have code in the Before Update event to check all controls the code will find the firest blank control, notify the user and then set the focus to that control. If there are additional blank controls the code will keep sending them back until all the controls have data. They cannot leave that record until data entry is complete.
Does that answer your question?
Jack
beyondnerd
Jack I used your code incorrectly, I left out Cancel=True, It's now working properly, care to edify me with an explanation of what that line does? I understand what your saying about not being able to check after each control and I think having it validate for the record rather than after each control will work fine. In any case thanks.
Jack Cowley
The Cancel = True cancels the update. If you leave that out the record is saved instead of being ignored until the form is completed by the user.
Good luck with this and I do not know how strong your VBA coding is, but you can loop through the controls on your form rather than using If - Then syntax. The very general outline would be:
Dim ctl As Control
' Enumerate Controls collection.
For Each ctl In Me.Controls
' Check to see if control is text box.
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
' Set control properties.
With ctl
If ctl.Value = "" Or IsNull(ctl.Value) Then
MsgBox "Missing Data"
Cancel = True
ctl.SetFocus
End If
End With
End If
Next ctl
The above is 'air code' so any resemblence to real code is purely a coincidence!
hth,
Jack
beyondnerd
I've never done loops and my coding isn't to strong, thanks for the sample, I think I'll use the If then method so I can have individual MsgBox messages.
Thanks for all your help
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.