How to delay code execution or run it at certain time? 1

[anaguib]

I have a big collection of records in an Access database and in "Current" procedure there is a big chunk of code that takes long when it runs. When I am just navigating from one record to another, the whole code in "Current" runs before I can move to the next record! This takes long time and makes navigation really slow. I thought of using the "TimeValue" function and "OnTime" to delay running the code in "Current" for few seconds when I am navigating quickly ... But, discovered that "OnTime" function doesn't exist in Access VBA like it exists in Excel VBA! Do you have a way I can make navigation faster or find similar function in Acces VBA that delays or controls when codes is executed to solve this problem?
Many thanks

 

[THWatson]

anaguib
The code on the Current event that is taking so long to run...what is it doing?

You could create an OnTimer event, but would this cause difficulties with the Current event code?

Tom

 

[BoxHead]

According to Help: Microsoft Access runs the Current macro or event procedure before the first or next record is displayed.


You might want to consider another way to navigate from one record to the next. A combo box or list box, maybe?





John

Use what you have,
Learn what you can,
Create what you need.

 

[THWatson]

John
Two questions for clarification:
1. Does not the Current event take place whenever the focus moves from one record to the next?

2. Is it the case, then, that going to a different record via a combo or list box would bypass the Current event?

The reason for my question in my original post was to determine what the code on the Current event was doing, because I have never had so much code on that event that it slowed down record navigation.

Tom

 

[BoxHead]

Hi ya, Tom.

Two answers:
1. Whenever a record gets focus or a view gets refreshed, or requeried the Current Event runs.

2. The combo box or list box would not bybass the Current event. It would allow the user to bybass all of the records that they didn't want to look at.

If you're at the first record and you want to scroll ahead to the 60th record with the Next Record button, the Current event will run for each record you go through. The combo box was an idea to let the user go from the first record to the 60th without visiting the other 58 records. The Current event would indeed still run on the 60th record when it is selected ad the focus changes.


Another option if the code is not essential to the operation of the form or the integrity of the data (dancing text boxes and flying command buttons):

Declare a public variable like myScroll as a boolean and put an unbound check box on the form. For the checkbox click event change the True/False value of myScroll.

Option Compare Database
Public myScroll As Boolean
__________________________________________________________
Private Sub ck1_Click()
myScroll = ck1

End Sub

On the Form's Current Event, isolate the code you want to not run in an If...Then Statement:

Private Sub Form_Current()
If myScroll = True Then
Exit Sub
Else

Dim myL As Integer, myT As Integer, myW As Integer, myH As Integer
Dim intL As Integer
myL = c1.Left
myT = c1.Top
myW = c1.Width
myH = c1.Height

For intL = 1 To myL
c1.Move intL, myT, myW, myH
Me.Repaint
Next intL
End If

With this, if I have the checkbox unchecked, a command button (c1) rolls across the screen into position on the Current event. If I tick the checkbox, the command button just appears in it's proper position and I can scroll through records without waiting.

HTH




End Sub


John

Use what you have,
Learn what you can,
Create what you need.

 

[THWatson]

anaguib
John is absolutely correct in his explanation. There may be some things you wish to try.

Thanks, John.

Tom

 

[BoxHead]

But, as Tom had originally asked, what is the code doing that is taking so long?

John

Use what you have,
Learn what you can,
Create what you need.

 

[anaguib]

Hello Tom and John,
Thanks for your thoughtful ideas, here is a bit more about the issue, and the reason why I am asking for some way to delay the execution of code in the Current procedure.
The form allows the user to make a reservation of rooms (about 200) for certain dates and for certain amount of time. Each record represents one incident of reservation, thus represents the room number, date and time. The Current procedure fills two list boxes;
The first List Box for listing all the other reservations taking place in this room in different times and is sorted by time, this one gets longer the more records are added, and
The second List Box for possible conflicts when two records share the same room number, date and time, this one is usually short!
So, the more reservations are added the longer the first list gets and the longer to load.
What I thought of is to delay the execution of the code in the Current procedure for few seconds so if the user keeps pressing forward or backword buttons fast, s/he navigates through the records fast without the need to fill up the two lists, until s/he stops for say three seconds then the code is executed filling the two lists for that specific record stopped at.
I hope this will explain few things ...
Many Thanks for your concern and help : )
ANaguib

 

[PHV]

And you can't optimize the first List Box's SQL ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[THWatson]

anaguib
I'm not sure how your list boxes are set up, but can you not restrict the list boxes to a particular date, or date and time, that has been selected?

Tom

 

[lesw1433]

Why not just place a command button to fill the list boxes when needed???

 

[anaguib]

Hello PHV
The code in the first list box is an SQL statment!
Is there is a way to optimise it so it delays executing the code??
Thanks
ANaguib

 

[BoxHead]

You said,

The second List Box for possible conflicts when two records share the same room number, date and time, this one is usually short!

The big question is, If you're using a database for scheduling reservations, why would there be any conflicts?

It seems the first listbox should be a simple SELECT query on the room number. I would suspect that the second list box may be causing the delay. If your SQL is taking each record and comparing it to
1.) all records for that room for a specific time period,
2.) all records for all rooms for a specific time period,
3.) all records for that room for eternity,
4.) all other records for all rooms for eternity...
there may be some cleaning up that can be done there.

If you're not using the Timer Interval for anything else in the form, you should be able to code in a delay, but I'd suggest commenting out portions of the code until you know what is causing the delay.

HTH




John

Use what you have,
Learn what you can,
Create what you need.

 

[PHV]

Is there is a way to optimise it so it delays executing the code??
Usually the optimisation process is an attempt to shorten the response delay.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[anaguib]

Hi Hohn(BoxHead),
Thanks to you and others for the reply!

The conflict list box is a one lean and fast SQL "Select" statment that compares the presence of other rooms with the same number, date and time!

The First List Box selects all the Reservations done for the current Room No. and this is the one that takes time!
[red]
What I want is some code to place in the "Current" procedure and it allows the execution of the rest of the code after three seconds or so, in a way that if the user doesn't stop those three seconds in that record the rest of the code will not run ...
[/red]
[blue]What I have in mind is creating a Variable called say "TimeToRunCurrent" and set its value to 3 seconds from now, use for that the "TimeValue" procedure (see the code below) and then I am looking for a way to run the rest of the code 3 seconds later
once the time or Now() = "TimeToRunCurrent" ..[/blue]

Dim TimeToRunCurrent As Date
TimeToRunCurrent = Now + TimeValue("00:00:03")
[red] Need here a statment to delay the running of the rest of code until[/red] 
[b]Now()=TimeToRunCurrent[/b]
 or
[b]Delay execution until TimeToRunCode [/b]
 or
[b]On TimeToRuncCurrent do ...[/b]

(if any such thing exist!)

[blue]
I think in Excell VBA there is a function called OnTime that alows execution of code at certain time! Any ideas?
[/blue]
Many thanks
ANaguib

 

[PHV]

So you don't think the 1st listbox loading may be speeded up ...

You want something like this ?
While TimeToRunCurrent < Now
DoEvents
WEnd

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BoxHead]

ANaguib,

I hope it's clear that everyone responding here is concerned that the delay may be an indication of a process problem and that simply providing a bandage to this problem could be detrimental in the long run.

That being said, I don't understand how TimeToRunCurrent would ever be anything but 'three seconds from now' whenever now may be.

If it were me and I was absolutely certain that the delay was otherwise uncontrollable, I would:

Create a Public variable myTimer as Integer

Option Compare Database
Public myTimer As Integer
______________________________________

Create a Private Sub myCurEvents() and move all of the OnCurrent events you want delayed into this new Sub.

Private Sub myCurEvents() 
'All of the code you want to delay
End Sub

On the form's OnCurrent Event set the Timer Interval and set myTimer to 0

Private Sub Form_Current()
Me.TimerInterval = 1000
myTimer = 0
End Sub

On the Form's Timer event begin counting. If you get to 3, run the new sub to update the listboxes. If you don't get to 3, it's because the CUrrent Event reset myTimer to 0.

Private Sub Form_Timer()
myTimer = myTimer + 1
If myTimer = 3 Then
myCurEvents
Me.TimerInterval = 0
myTimer = 0
End If
End Sub

This is what works on my form with the flying command buttons.

HTH





John

Use what you have,
Learn what you can,
Create what you need.

 

[JoeAtWork]

If this project were handed to me, the first thing I would do is take the time-wasting code out of the Current event. What you are trying to do is add a lot of confusing code to support a design concept that obviously doesn't work.

A much simpler approach:
1. User first navigates to the record they want to work with.
2. Once he is on the correct record, press a Load button to fill in the listboxes.

You can keep the RowSource properties of the listboxes empty (unbound) until the user has navigated to the record he's interested in. When the Load button is pressed, then you can fill in the appropriate SQL statements in the RowSources and requery the listboxes.

So the user has to do one extra click to load all the information, big deal. This approach is a lot cleaner and straightforward. The Current event is meant to have code that you want to run every time a record is navigated to. Since you really don't want that code to run every time, then it should be somewhere else.

 

[BoxHead]

Well, Joe, I guess it's all a matter of degree.

I used to hear the same 'a lot of confusing code' argument when I suggested that a database might be a better way of collecting and storing information. Instead, we continued with the process of having a sales force of 60 call a group of admins each day to report their activities so the admins could key it into Excel and have the head admin compile it into a Word document for reporting the following day. I guess using both Excel and Word made it doubly automated. At least it beat the mammoth blood on the cave wall process the company had been using.

I suppose it all depends on who's confused by the code.

John

Use what you have,
Learn what you can,
Create what you need.

 

[THWatson]

John
You sure try your very best to help. And your responses are so thoughtful and complete. You deserve a star, and I'm going to plug one in.

Additionally, I love your "blood on the cave wall" metaphor. That in itself made my day.

Tom