Solved

Moving Code out of Access Form

Posted on 2013-11-13
18
544 Views
Last Modified: 2013-12-09
I'm trying to move all of my code that is in the form module to a standard module. The only thing i'm stuck on is how do i move the following to a regular module and still get the keypress ascii value.

Private Sub DueDate_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
    Case 45
    KeyAscii = 0
  Screen.ActiveControl = Nz(Screen.ActiveControl, CDate(Format(Now(), "Short Date"))) - 1
    
    Case 43
    KeyAscii = 0
  Screen.ActiveControl = Nz(Screen.ActiveControl, CDate(Format(Now(), "Short Date"))) + 1

End Select
End Sub

Open in new window

0
Comment
Question by:jb702
[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
  • 3
  • 3
  • +4
18 Comments
 
LVL 25

Assisted Solution

by:chaau
chaau earned 250 total points
ID: 39646972
A question: Why are you doing this?

As for your problem: it is very easy. Create a Sub in your module with the same parameter, as the event procedure:
Private Sub DueDate_handler(KeyAscii As Integer)
Select Case KeyAscii
    Case 45
    KeyAscii = 0
  Screen.ActiveControl = Nz(Screen.ActiveControl, CDate(Format(Now(), "Short Date"))) - 1
    
    Case 43
    KeyAscii = 0
  Screen.ActiveControl = Nz(Screen.ActiveControl, CDate(Format(Now(), "Short Date"))) + 1

End Select
End Sub

Open in new window

And in your Form's sub just call the sub from the module:
Private Sub DueDate_KeyPress(KeyAscii As Integer)
     DueDate_handler(KeyAscii)
End Sub

Open in new window

0
 

Author Comment

by:jb702
ID: 39646993
That doesn't work, i want to be able to set the forms has module property to "no".  All controls have to be event driven functions for this to happen.
0
 
LVL 25

Expert Comment

by:chaau
ID: 39647001
I am just curious: why do you need it this way? Is it some sort of security measure?
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:jb702
ID: 39647006
I'm always updating the database, reports, more functionality, for the office personnel etc. It would seem to me to be a lot easier to make updates to only in  code and never really have to make any modifications to the form.  Plus it should be quicker, faster to load etc.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 250 total points
ID: 39647031
"i want to be able to set the forms has module property to "no"
The only way you will be able to do this - get the ASCII value of the key pressed - will be to use the Windows API. Otherwise, you will need to pass the value as shown.

" Plus it should be quicker, faster to load etc."
I seriously doubt that with today's systems.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39647460
<<  Plus it should be quicker, faster to load etc. >>

In addition to mx's comments, the KeyPress event specifically does not affect page load time.
0
 

Author Comment

by:jb702
ID: 39647465
WOW, Really guys....of course it doesn't but the underlying code that each control has on the form does. Would you agree?
0
 
LVL 25

Expert Comment

by:chaau
ID: 39647536
The will be somewhere anyway, either in the event procedure or in the function. If you believe the event procedure affects the performance why don't you think the function would?
0
 
LVL 84
ID: 39647539
Once the app is compiled it won't make much (if any) difference. When the form loads, a series of events fire, and then it waits for user input. Those events (the Load, Open, GotFocus, etc events) need to be as streamlined as possible, but after that it makes no difference. For example, the KeyPress event of a button is of no consequence in regard to load times.

I have some VERY heavy forms, and they load just as quickly as forms with no modules, assuming I've got the right pieces in place to allow this (i.e. not too many subforms, restricting lists and combos, etc). If you're having issues with your form load times, it's not the code behind that form that's causing your issues, it's what that code DOES that is the issue. Calling code that fires in the Open event from a Module vs calling it from the Form's Class Module isn't going to make a difference.

As to being easier - like beauty, that's in the eye of the beholder, so it's sorta hard to argue that. But having to work down through a chain of code to find out what really happens is always more difficult than being able to directly access the code in the code environment (and you can't do that with events declared directly in the Event Properties window).
0
 
LVL 25

Expert Comment

by:chaau
ID: 39647557
Just wanted to add to the previous comment. If your form is data driven then the underlying table/query may slow down the load time if the records being loaded are not properly indexed.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39647573
<< WOW, Really guys....of course it doesn't but the underlying code that each control has on the form does.  >>

Sure - everything adds up, but I'd be very surprised if moving that particular event would make a noticeable performance difference.

This is worth taking a look at if you haven't seen it already:
http://www.fmsinc.com/tpapers/faster/#Forms

It does list Lightweight/no-code forms as a tip, but if improving performance is the primary goal, I'd personally work on optimizing the form to an acceptable point with just about any of the data related tips first.  ie: I'd focus on the queries, number of controls (especially subforms) and other data-related issues before resorting to API functions for code that built-in Events like the KeyPress event are perfectly suited to handle (just a matter of priority).

Personal preferred style is another matter, of course, if that is the driving factor.
0
 
LVL 75
ID: 39648669
" Would you agree?"
No I would not.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39651028
The only time I move code out of a form is when I refactor the code so that it can be invoked by multiple forms/controls.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 39703905
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39703906
Recommend splitting the points equally between mx and chaau's comments, with mx's comment at  http:#a39647031 marked as Accepted and chaau's comment at http:#a39646972 as Assisted.

The bottom line is as mx pointed out that this cannot be done by the author's method, the way the author wants.  

Alternative solutions were posted in both comments.
0
 
LVL 84
ID: 39704048
I agree.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parameter Query 33 52
Hide shared folder for some users 2 32
Macro problems with Excel file 6 28
Access Report formatting issue 5 26
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 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