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
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
  • 3
LVL 48

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 48

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 48

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

705 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