Need to speed up my Code

D

djossh

Registered User.
Local time
Today, 19:33
Joined
Oct 19, 2011
Messages
89
Hi all, I have this code from one of the forum and it is working fine, but everytime I run this code it takes a while to complete my data. Are there any code to speed up this or other code that will give me same results but faster speed. Thanks


here's my current code.

Private Sub Command32_Click()
Dim i As Integer

For i = 1 To Val(Me.Field1)
Dim v1 As Variant
Dim v2 As Variant
Dim v3 As Variant
Dim v4 As Variant
Dim v5 As Variant
Dim v6 As Variant
Dim v7 As Variant
Dim v8 As Variant

v1 = Me!Field2.Value
v2 = Me!Field3.Value
v3 = Me!Field4.Value
v4 = Me!Field5.Value
v5 = Me!Field6.Value
v6 = Me!Field7.Value
v7 = Me!Field8Value
v8 = Me!Field9.Value

RunCommand acCmdRecordsGoToNew

Me!Field2= v1
Me!Field3= v2
Me!Field4= v3
Me!Field5= v4
Me!Field6= v5
Me!Field7= v6
Me!Field8= v7
Me!Field9= v8

Next
End Sub
 
If I have understood the code correctly, it creates a number of new but duplicate records using the form. I think it would be faster using an append query or even a recordset if you are creating many new records but can I ask you why you need lots of duplicated records?
 
Agree with Bob.

Also I would not be using Variant in anything of a similar nature.
 
bob fitz said:
If I have understood the code correctly, it creates a number of new but duplicate records using the form. I think it would be faster using an append query or even a recordset if you are creating many new records but can I ask you why you need lots of duplicated records?
Click to expand...

Hi thanks for the reply, I'm a newbie in access and zero knowledge in writing codes, yes you are right that code creates multiple records.. I am actually making a timesheets for payroll..."Field1" is the monthdays... so it will create records based on whatever number of days in months in "Field1".... since almost all of the data are same especially the project and Overtime hrs.. it is more easy for me to edit few fields than typing it one by one.. If you have better idea please let know.. Thank you so much...
 
RainLover said:
Agree with Bob.

Also I would not be using Variant in anything of a similar nature.
Click to expand...

Thanks for the reply.. I really don't know how to write codes... i even don't know how that "variant" is working... Thanks again..
 
Access Help will tell you about Variants.

Look it up. Besides you need to learn Access Help features as it contains a lot of technical information.
 
RainLover said:
Access Help will tell you about Variants.

Look it up. Besides you need to learn Access Help features as it contains a lot of technical information.
Click to expand...


Thanks.. but the way i see it,.. I'll still be using my current code. co'z there's no alternative to what im currently using... Thanks again..
 
There is. It will be along the lines of:

Code: 
Private Sub Command32_Click()
    Dim i As Integer
    For i = 1 To Me.Field1
        CurrentDb.Execute "INSERT INTO Table1 (Field2, Field3) VALUES (" & Me.Field2 & ", " & Me.Field3 & ")"
    Next
End Sub
 
There is always a different way.

You have now been shown two.
 
VilaRestal said:
There is. It will be along the lines of:

Code: 
Private Sub Command32_Click()
    Dim i As Integer
    For i = 1 To Me.Field1
        CurrentDb.Execute "INSERT INTO Table1 (Field2, Field3) VALUES (" & Me.Field2 & ", " & Me.Field3 & ")"
    Next
End Sub
Click to expand...

Thanks.. I'll try your code.. Thank you.. I really Appreciate it.
 
MAKE A BACKUP FIRST. Then try this:
Code: 
Private Sub Command18_Click()
On Error GoTo Err_Command18_Click_Error
Dim i As Integer
Dim rec As Recordset
 
Set rec = CurrentDb.OpenRecordset("[B][COLOR=red]tblYourTableName[/COLOR][/B]")
For i = 1 To Nz(Me.Field1, 0)
  With rec
    .AddNew
    !Field2 = Me.Field2
    !Field3 = Me.Field3
    !Field4 = Me.Field4
    !Field5 = Me.Field5
    !Field6 = Me.Field6
    !Field7 = Me.Field7
    !Field8 = Me.Field8
    !Field9 = Me.Field9
    .Update
  End With
Next
Me.Requery
DoCmd.GoToRecord , , acLast
Exit_ErrorHandler:
  Set rec = Nothing
  Exit Sub
Err_Command18_Click_Error:
  MsgBox "Error " & err.Number & " (" & err.Description & ") in procedure Command18_Click of VBA Document Form_frmYourFormName at Line " & Erl
  Resume Exit_ErrorHandler
End Sub
Change tblYourTableName to the name of your table.
 
Bob, could you not also do that with the form's recordset:

Set rec = Me.Recordset

?
 
One could add them to the Me.RecordsetClone:

Code: 
With Me.RecordsetClone             
   .AddNew                 
        !CustomerID = Me.CustomerID 
        !EmployeeID = Me.EmployeeID                 
        !OrderDate = Date                 'etc for other fields.             
    .Update
End With

grabbed from Allen Browne's goldmine http://allenbrowne.com/ser-57.html
 
VilaRestal said:
Bob, could you not also do that with the form's recordset:

Set rec = Me.Recordset

?
Click to expand...
I'm not sure that would work, but
Set rec = Me.RecordsetClone
certianly would work, I just didn't think of doing it that way. Thank you.
 
As the OP request was to speed up the code then adding to the form's recordset directly wouldn't be the best way. A clone would be faster.
 
spikepl said:
One could add them to the Me.RecordsetClone:

Code: 
With Me.RecordsetClone             
   .AddNew                 
        !CustomerID = Me.CustomerID 
        !EmployeeID = Me.EmployeeID                 
        !OrderDate = Date                 'etc for other fields.             
    .Update
End With

grabbed from Allen Browne's goldmine http://allenbrowne.com/ser-57.html
Click to expand...
Where should I put this code? sorry for asking too much.. I really appreciate all the reply and all the helps here.. Thank you all guys...Thanks Spikepl
 
Just paste this over your original code:
Code: 
Private Sub Command18_Click()
On Error GoTo Err_Command18_Click_Error
Dim i As Integer
Dim rec As Recordset
 
Set rec = Me.RecordsetClone
For i = 1 To Nz(Me.Field1, 0)
  With rec
    .AddNew
    !Field2 = Me.Field2
    !Field3 = Me.Field3
    !Field4 = Me.Field4
    !Field5 = Me.Field5
    !Field6 = Me.Field6
    !Field7 = Me.Field7
    !Field8 = Me.Field8
    !Field9 = Me.Field9
    .Update
  End With
Next
Me.Requery
DoCmd.GoToRecord , , acLast
Exit_ErrorHandler:
  Set rec = Nothing
  Exit Sub
Err_Command18_Click_Error:
  MsgBox "Error " & err.Number & " (" & err.Description & ") in procedure Command18_Click of VBA Document Form_frmYourFormName at Line " & Erl
  Resume Exit_ErrorHandler
End Sub
 
Last edited:
Hopefully the responses you have been given and tried have solved your immediate "speed" problem.
I have a comment based on the posts that you may wish to consider.

Often, if you describe your problem/opportunity/business in plain English so people get a feel for WHAT you are trying to do/achieve, they will provide posts of options, ask for clarifications, or suggestions. Once the WHAT of the problem/opportunity is understood, the How portion can be attacked.

As you have found, there are always more than 1 way to do just about anything.

There is an excellent post by Rain in post #16 at
http://www.access-programmers.co.uk/forums/showthread.php?t=225683&page=2
that may provide more useful background, and help you with future issues.

Good luck.
 
bob fitz said:
Just paste this over your original code:
Code: 
Private Sub Command18_Click()
On Error GoTo Err_Command18_Click_Error
Dim i As Integer
Dim rec As Recordset
 
Set rec = Me.RecordsetClone
For i = 1 To Nz(Me.Field1, 0)
  With rec
    .AddNew
    !Field2 = Me.Field2
    !Field3 = Me.Field3
    !Field4 = Me.Field4
    !Field5 = Me.Field5
    !Field6 = Me.Field6
    !Field7 = Me.Field7
    !Field8 = Me.Field8
    !Field9 = Me.Field9
    .Update
  End With
Next
Me.Requery
DoCmd.GoToRecord , , acLast
Exit_ErrorHandler:
  Set rec = Nothing
  Exit Sub
Err_Command18_Click_Error:
  MsgBox "Error " & err.Number & " (" & err.Description & ") in procedure Command18_Click of VBA Document Form_frmYourFormName at Line " & Erl
  Resume Exit_ErrorHandler
End Sub
Click to expand...


Hi Bob,, Im getting error 3265 (Item not found in this collection).. can you fixed it? thank you again..
 
You must log in or register to reply here.

Similar threads

S
Permission Denied while trying to save a back-up copy through VBA code
Replies
5
Views
1,704
SachAccess
S
S
From Excel to Access
Replies
4
Views
963
plog
P
G
Union all & NZ query
Replies
3
Views
834
Minty
Minty
H
query returning duplicate rows : Invoicing
Replies
3
Views
1,000
mike60smart
mike60smart
W
VBA Insert Sql Statement
Replies
7
Views
842
Workaholic_711
W

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom