We help IT Professionals succeed at work.

Need to add zeros to a auto number field, but also protect from mouse wheel moving to new record.

csall
csall asked
on
478 Views
Last Modified: 2013-11-28
I have two problems that I have code to resolve, but when they work together it causes an error.  The first issue is when someone uses the mouse wheel, I want to prevent the form from going to the next record (open a new record).  I have solved this with the following code in the properties for "When Mouse Wheel" on the form.

     Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)
        On Error Resume Next
         Select Case Sgn(Count)
         Case Is = 1
         DoCmd.GoToRecord record:=acPrevious
         Case Is = -1
         DoCmd.GoToRecord record:=acNext
         End Select
     End Sub

I also have created an auto counter that provides a number in a format like EPCM-0124.  Of course when the counter assigns the number, it gives EPCM-124, but I have been able to add the leading zero by using the following code on "Lost Focus" for the text box that saves the auto number.

     Private Sub Permit_No_LostFocus()
        Dim I As Integer, tmp As String
         tmp = Me.Permit_No.Text
         I = Len(Me.Permit_No)
         If I < 9 Then
             Me.Permit_No.Text = Mid(tmp, 1, 5) & "0" & Mid(tmp, 6, 5)
            End If
     End Sub

The problem is that if you are in the field "Permit No" and you use the mouse wheel, I get an error because of the code trying to add the leading zero.  If I move the code for adding the leading zero to another location, I get an error.  If I use setfocus on entering the "Permit No" field to move to another field, I get the error when it tries to add the leading zero.  If I remove the tab stop for the "Permit No" field, the form will not add the leading zero, but the mouse wheel code works fine.  The mouse wheel code also works fine if you are in any other text box other than "Permit No"

One solution maybe to add the code to insert the leading zero when the auto number is set, but I need help with the code.  Below is the code I have for autonumber when the form is opened:

     Private Sub Form_Open(Cancel As Integer)

     DoCmd.GoToRecord , , acNewRec

     Dim strMax As String
       If Me.NewRecord Then
           If RecordsetClone.RecordCount = 0 Then
           Me.[Counter EPCM] = "1"
            Me.[Permit No] = "EPCM-" + Me.[Counter EPCM]
         Else
             Me.[Counter EPCM] = DMax("val([Counter EPCM])", "[Query EPCM Permits]") + 1
             Me.[Permit No] = "EPCM-" + Me.[Counter EPCM]
       End If
     End If
     Me.[Permit Suffix] = "EPCM"
      DoCmd.Maximize
     End Sub

I would appreciate any help.  Thanks.
Comment
Watch Question

Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
csall,

Regarding the Code to add the special characters to a field:
1. Why not use an input mask to add the first few characters, and avoid the code altogether?

2. Is this an Autonumber field or are you incrementing the numbers yourself?

3. Why is the code on the "LostFocus" event, and not the "AfterUpdate" Event?

JeffCoachman

Author

Commented:
1.  I cannot use an input mask because it Access does not  save the items from the input mask in the table.  For example, if I have "ABC -" as the input mask and the entry is "ABC - 0123" only 123 will be saved in the table.  This would be ok normally, but I have multiple forms that provide a different set of characters depending on permit being issued.  For example, I could have "RD - 0100" and "EPCM - 0100" so I need the letter identifiers saved in the table.

2. I have a counter for each type of permit that is issued to provide incrementing numbers.  The reason is I could have the RD and EPCM examples above where they have different identifiers for the permit but same sequential number.  These would be totally different permits.

3. It is in the "Lost Focus" or "On Exit" because the record is given a number when the form opens.  If it is not on "Lost Focus" or "On Exit" the leading numbers will not be added because there was not change to the text box.  I thought to put the code to add the record number when entering the text box, but if someone tabs though the form, when they hit the ID number text box again, it will erase the original number and add one to it. (If needed I could work around this by adding code to check the text box for an entry and if there is an entry, skip the creating an ID for the record.  I did not do it because it seemed like adding extra code that would not be needed.)
MIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Jeff,

I suspect I have created a monster that has a simpler solution.  Let me explain my problem without any of my solutions:

1.  I have a field in my database that stores a permit number.  To make the database simpler, I have one table that stores multiple types of permits (a majority of the information is the same except for the ID number and a few fields).  Each permit has a unique identifier at the begining of the permit number that identifies what type of permit it is.  For example RD = Road Permit and EPCM = EPCM permit.  To make things simple for the user entering a new permit, each type of permit has its own form.  When you open the form, it automatically fills in the permit number.  So if you open the Enter New Road Permit form, it assigns a permit number of RD-0100 (Road permits have a counter and when you open the form it looks at the counter and adds +1.  I did not use autocounter in access because if you delete the record, I still want to be able to use the number.)  If you open the form Enter New EPCM permit, it will also assign an EPCM number to the permit when the form opens.  Because it uses a differnt counter you could have a permit number EPCM-0100.  This is done by design because they are differnt types of permits and the identifier make the numbers different in the table.  The problem is that with the counter, if the next number is 10, the database will give you the permit number of RD-10 and I need it to be RD-0010 (Need leading numbers).  I need it stored in the table with the leading numbers.

2. The second issue I was dealing with is that I need to disable the mouse wheel from opening a new record.  I did this by using the code in my inital queston to disable the mouse wheel.  The problem was that with the code I had for adding the leading zeros, it caused an error if someone used the mouse wheel.  Now this only occurred if they were on the permit number field.  Anyother field it was ok.  

(Because of the code I had for adding the leading zeros, you had to start out on the permit number filed and leave the field to get the leading zeros added to the number. When you entered the form, you would be on the permit number text box and it would like RD-10, but when you exited the text box it would chage the number to RD-0010.)

In regards to your question if you closed the form or deleted the record, I have validation rules to make sure the proper fields are completed prior to closing.

I am sure this is confusing, but I hope this all makes better sense now.

Carl
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
csall,

Again, systems like this are always hard to manage.

Whatever you do will still leave room for error.
This subject, (and any possible solution) would be something you would have to experiment with yourself.
In order for any expert here to think this through, they would have to have an intimate knowledge of your Database, it purpose, your constaints, your users, your skill level, ...ect)

As for the first part of your question about  storing the inputmask characters, I believe I have answered it fully.

JeffCoachman

Author

Commented:
Jeff,

I am going to give you full points for working with me on this one.  Plus the info you provided on input mask is very useful.

I resolved the issue with the Mouse Weel by adding code in the begining to set focus to another text box before running the mouse wheel code.  That allowed the programming to run that adds the leading zeros, but does not cause an error when the mouse wheel code runs.  The code is below.
 
  Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)
   
        me.[name of text box].set focus

        On Error Resume Next
         Select Case Sgn(Count)
         Case Is = 1
         DoCmd.GoToRecord record:=acPrevious
         Case Is = -1
         DoCmd.GoToRecord record:=acNext
         End Select
       
      End Sub

In regards to why I have to have the code for adding the leading zeros in the "lost focus" or "on exit", that I cannot answer.  I have tried it in other locations and it does not work.  Anyways, it is working now.

Thanks for your help.

Carl

Author

Commented:
I added a message to the bottom of the string.  Thanks for your help, and I found a solution to the problem.  
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
OK

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.