Solved

MS Access VB onFocus

Posted on 2014-12-05
4
190 Views
Last Modified: 2014-12-05
hello,

I have been trying to run this code:

Private Sub FName_Exit(Cancel As Integer)

If IsNull(Me!FName) Then
MsgBox "Eh...fill this in!"
Me.FName.SetFocus
End If

End Sub


But every time it changes to the next field on the form on Access 2007

I can't seem to get it to stay on that field.  I just want it to make sure the field is not null or empty.  Actually I want to have the button I have setup to check then entire form for this...the button is the Add Record/Save but can't figure out how to do this...



Any help?

Ernest
0
Comment
Question by:Ernest Grogg
4 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 40483760
here is a short checking code that i use.
* for all the fields that are "required" place a value of REQ in the tag property
* you can use the codes for any number of controls in your form



Private Sub Form_BeforeUpdate(Cancel as integer)
Dim strCtlName As String, NullCtl As String, Msg As String, ctl As Control
strCtlName = ""
For Each ctl In Me.Controls
    If ctl.Tag = "REQ" Then
        If Len(Me(ctl.Name) & "") = 0 Then
            strCtlName = strCtlName & ctl.Name & ";"
        End If
    End If
Next
If Len(strCtlName) > 0 Then
    NullCtl = Mid(strCtlName, 1, InStr(strCtlName, ";") - 1)
    Msg = "Please fill out the required fields!" & vbCr & vbCr
    Msg = Msg & Left(strCtlName, Len(strCtlName) - 1)
    MsgBox Msg, vbCritical, "Required Fields"
    Cancel=True
    Me(NullCtl).SetFocus
    Exit Sub
End If
end sub
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 40483811
There are several ways to approach this problem.  
1. Code as described above.
2. Make the fields required in the table and then put code to change the Field Labels (To Red or Astricks, etc) to highlight the missing values when you click the save button.
3. Put the code in the lost focus (Property Sheet of field) to highlight the field missing informaiton.

As for what you describe above, I think it is because you are using the 'On Exit' which means the focus is no longer on the field that you are trying to take an action on.  

As a side note, if you make the field required in the table it will not allow you to add the record until all the required fields are entered.  In addition, you can put requirements in the table that control the formating (ie phone number (###) ###-####) or require the value to be in a range, etc.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40483832
Right idea, wrong code, wrong event
Private Sub FName_Exit(Cancel As Integer)
 If IsNull(Me!FName) Then
 MsgBox "Eh...fill this in!"
 Me.FName.SetFocus
 End If
 End Sub


try

Private Sub FName_LostFocus()
Dim Response as integer
If nz(Me.FName.Value,"") = ""Then
    Response =MsgBox ("Eh...fill this in!  If you REALLY don't want/need to, click NO",VbYesNO, "Fill it in")
    If response = vbno then exit sub
    Me.FName.SetFocus
End If

End Sub
0
 

Author Closing Comment

by:Ernest Grogg
ID: 40483947
Great! Works perfectly
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now