Solved

MS Access VB onFocus

Posted on 2014-12-05
4
198 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

628 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