Code for cursor in any field

Posted on 2012-08-24
Medium Priority
Last Modified: 2012-08-27
I need some code to run when the cursor is in any field of a form. I cannot use OnEnter property for the form because this allows any code to run when I move between records using the form's record navigators.

It must run before any data is entered.
Question by:DatabaseDek
  • 3
  • 2
LVL 49

Expert Comment

by:Dale Fye
ID: 38328938
so, you are actually talking about when a user enters a new record?

If so, you can use the Form_Current event, which fires every time a user switches records.

If all you want to do is perform these tasks when the user is on a NewRecord (no data entered yet), then try something like:

Private Sub Form_Current

    if me.NewRecord Then
        'do something here

End Sub

Author Comment

ID: 38329078
Thank you

The problem with that is that when a user deletes a record the code is automatically triggered. So if the code is to auto enter a value it will do it on delete. This means effectively that you cannot delete a record.
LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 total points
ID: 38330774
First, can you state why this functionality is needed.,  ...perhaps there is an alternate approach...
Sometimes there is no real "event" for what you might want to do.

For example, many people request that a message box pop up as soon as a form opens.
So, the form opens and a message box says something like "The customer form is now open".
Unfortunately there is no "On Appear" or "On Form Draw" event to make this happen.
You have to call the two things separately from a separate event
Sub Button_Click
'Code to Open Form
'Code to Open messagebox
end sub.

So, You will have to state very precisely what you need to happen and under what circumstances.

AFAICT, ...from what you posted, :
<when the cursor is in any field of a form>
<It must run before any data is entered. >
You need code like fyed posted on the Enter (or Got focus) event of *every* data control on the form

Sub txt1_Enter()
  if me.NewRecord Then
        'do something here
End Sub

Sub txt2_Enter()
  if me.NewRecord Then
        'do something here
End Sub

Sub txt3_Enter()
  if me.NewRecord Then
        'do something here
End Sub


This is why it is always helpful to explain your ultimate "Need" very precisely.
   "I have a form, and I want to _______ when_________"


Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.


Author Comment

ID: 38332199
Thank you Jeff

Its a common problem for me.  Imagine you have a form a subform and in the subform a subsubform. So you may have customer details Main form Invoice details Subform and items in the subsubform . If a user finds the customer details (combo) and goes straight to the subsubform and enters data there will be an error because a link has not been made to the main and subform. You have to generate the link manuals. I have done this with OnEnter in the past. If you want the code to run only on a new record if the cursor is in the subsubform and the user then uses the record navigator to go to a new record it creates the link. But when you want to delete that record because the code sees that there is no link in main and sub it creates one every time, effectively stopping you deleting any new record. Here is a simpler db sample this time the problem is with the fact that the database I inherited  has a structure simular to the sample db. I have a main form that has a subform that gets its records from a query collecting data from two table linked in a 1 to 1 relationship. Please, Please have a look.

Form 1 is convention and OK Form 2 is the problem

Author Closing Comment

ID: 38332465
Would appreciate your comments on the attached Sample DB

Had to close this I asked for assistance before I realised that your comment above will actually solve the problem. But I may just go On New record Allow edits No then click main form button to add new record
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38339502
<Would appreciate your comments on the attached Sample DB>
I'm confused...

In your explanation you stated:
< Imagine you have a form a subform and in the subform a subsubform. So you may have customer details Main form Invoice details Subform and items in the subsubform .>
This is three forms
main Form
Subform 1
Subform 2.

I don't see a form like this in your sample...?
Form 2 only has a main form and a subform.

<You have to generate the link manuals. >
I don't know what this means, I have never had to create parent/child links manually...?

Why is the subform based on a query of two tables?
Why is the Project table hidden?

I guess my main question here is that I do not understand your design.

Are you quite sure it is optimal for what you are trying to do here?
Perhaps your design is part of the issue...

For a simple example of a main/Sub/sub-subform (Customers/Orders/OrderDetails)
See the Northwind sample database:

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

850 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