Any way to make this code run faster?

[Alisa]

I am using this code:

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
Dim strUpdate As String
Dim F As Form
Dim rst As DAO.Recordset
Dim iDown As Integer
Dim iAcross As Integer
Dim iTop As Long
Dim iLeft As Long
Dim bValidate As Boolean
Select Case KeyCode
Case vbKeyY
 strUpdate = "Y"
Case vbKeyN
 strUpdate = "N"
Case vbKeyA
 strUpdate = "A"
Case vbKeyS
 strUpdate = "S"
Case Else
Exit Sub
End Select

'get selection
   Set F = Forms!ManageRules!ManageRulesDatasheet.Form
   Set rst = F.RecordsetClone
   rst.MoveFirst
   rst.Move (F.SelTop - 1)
  
   For iDown = 1 To F.SelHeight
    iLeft = F.SelLeft
    
    For iAcross = iLeft - 2 To F.SelWidth + iLeft - 3
    If Not iAcross = 0 Then
        rst.Edit
        rst.Fields(iAcross) = strUpdate
        rst.Update
    End If
       Next iAcross
   rst.MoveNext
   Next iDown
F.SelWidth = 0
F.Refresh
End Sub

This code changes the value in all of the selected "cells" on my form that is in datasheet view based on the key that was pressed. (Yes, I know they are not really cells, but you know what I am talking about). So if the user highlights several "cells" and then types "Y", the value in those "cells" changes to Y.
Does anyone see any way to make this run faster? It is soooo slow right now . . .

 

[Alisa]

I know - I am dealing with users that are used to using an older program that "feels" like excel. The new program is in access, but I couldn't figure out any other way to let them edit the data the way *they* want to and still maintain the data structure that I know is correct. If there is no way to make this faster, they will live with it because that is how much they don't want to learn a new way to enter the data. There is a lot more code behind this form that essentially crosstabs the data, lets the user edit it in the form, and then "un" crosstabs it to put it back in the actual data table.

 

[supercool]

acutually I have tried your code with some dummy data, found out the response is quite ok with out noticeable delay. is it because u have some other applications running background? btw, my pc is core 2duo with 1GB ram

 

[ajetrumpet]

btw, my pc is core 2duo with 1GB ram

Isn't that the best that Intel has right now? Maybe that's the reason...

Are you running Vista? I am, and it's impossible to do any multi-tasking with database programs if you don't have at least 2GB Ram. I started with 1, and was waiting, waiting, and then waiting...

 

[Alisa]

I have 2GB of ram (XP) but most of my users will probably only have 1 GB or less of ram. When I run this code after selecting only 10 or so cells, I end up waiting for about 10 seconds. And my users may end up selecting up to 100 or even more cells at a time, I'm not sure they are THAT patient . . . I think that part of what is slowing it down is the conditional formatting I have set up ( "Y" = yellow, "N" = red, etc.) that has to change once the values are changed.

 

[supercool]

i also run XP. 10 cells takes about 10 seconds, that's really bit slow. this morning i test again , found out with 1 selected column of data (27 cells), it takes 0.2 seconds.
I guess there should be some other computation-consuming functions involved or triggered without your notice. Anyway, Vista itself already eat up most of system resource.
one suggestion is that, maybe we could plunge some timer inside ur sourcecode to check which part play dominant role of the snail effect. Then further crack the issue out...