Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access VB onFocus

Posted on 2014-12-05
4
Medium Priority
?
200 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 2000 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

722 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