Form event to catch Issue

Posted on 2014-02-14
Last Modified: 2014-02-14
An item number is always 7 digits. So if my item is 456, by format is should be 0000456. Now there is a access form where users will be entering the number for new items – how do I build the form so that it check for the correct data entry – or if the users enter 456, the form converts that entry to add 4 extra zeroes in front of the that number and then stores it in the table?  Any ideas

Thank you
Question by:Rayne
  • 4
  • 3
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39860536
You could use the AfterUpdate, LostFocus, or Exit event of the control, something like:

Private Sub txt_ItemNumber_AfterUpdate

    me.txt_ItemNumber = Right("0000000" & txt_ItemNumber, 7)

End Sub

This would add 7 zeros to the left of the ItemNumber and then take the right most 7 characters.  Make sure your ItemNumber field is formatted as text, because if it is numeric, the leading zeros will be dropped.

Author Comment

ID: 39860559

Author Comment

ID: 39860563
Hello Dale,

I got error and on debug it gave me this….see attached
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!

LVL 47

Accepted Solution

Dale Fye (Access MVP) earned 500 total points
ID: 39860698
Don't use the BeforeUpdate, use AfterUpdate

Author Comment

ID: 39860725
I did used it, its not adding in the zeroes before and storing the way the number is typed in
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39860762
Did you use the Form_Afterupdate or the control afterupdate.  If you used the control afterupdate, it should save it properly when you save the form.

Author Comment

ID: 39860764
yup yup it worked, thank you Dan

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

744 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

13 Experts available now in Live!

Get 1:1 Help Now