faster loading

[skate]

I have a problem with the loading of my forms---they're really slow. I "think" it's b/c my code is long and repetitive. (correct me if I'm wrong) I have these txtboxes linked to number fields in a table txt1, txt2, txt3...etc. There are occassions where I have to run through each one of these txtboxes to do a formula which is really teadious. Anyone know a way I can do a loop of some sort? Someone once mentioned something about collections, but I don't quite get the concept and if that really applies to this.

 

[Dustman]

You can move through all the controls on your for with loops. Here's an example:
[tt]
Dim i As integer
i = 0
For i = 0 To 5 Step 1
Controls("txt" & i).Text = ""
Next i
[/tt]

Thats a good way to do it if you just want certain controls. You can also do it by looking at each control on the form. This is a little more tedious and can take up a lot more time.

[tt]
Dim Ctl As Control
For Each Ctl In Me.Controls
Select Case Ctl.ControlType
Case acTextBox
Select Case Ctl.Name
Case "txt1", "txt2", "txt3"
With Ctl
.Text = "Whatever"
.Enabled = True
End With
End Select
End Select
Next
[/tt]

Other control types are acComboBox, acCheckBox, acListBox, acCommandButton...

If you want to look through all the controls, just don't use the select case statements to limit them.

I hope that gets you rolling in the right direction. Its almost 3am here so I'm not quite sure that I'm speaking clearly..

Good luck! -Dustin
Rom 8:28

 

[Steve101]

You can confirm whether its the code which is slowing down the loading of the form by temporarily disabling the code and seeing whether the form presents earlier.

in the event code, add the line "Exit Sub" immediately after the Sub statement, then open the form, and see if it still takes a long time to load.

Hope this helps, Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[gusbrunston]

[tt]
Hi:

Also...

A big time-waster is the use of the asterisk (*) in queries, especially when a table has many fields. It may help to use only the fields necessary to the procedure(s).

In addition, examine Tools -> Options -> General -> NameAutoCorrect and blank the choices, "Track Name AutoCorrect Info", and "Perform name AutoCorrect", and select "Log name AutoCorrect changes". (Then be careful about changing the names of tables, fields, queries, forms, etc.) This will relieve some of the work load on opening procedures.

Put the "back end" on the server as high in the directory as possible. e.g., in a folder named, "C:\AAABackEnd".

I know nothing about the "With statement". However, I plan to examine it's use, because of the following from Help in Access2000:

"The With statement lets you specify an object or user-defined type once for an entire series of statements. With statements make your procedures run faster and help you avoid repetitive typing."

Also, it goes without saying, use Pentium IV's with 256mb memory, or more, for your workstations, and appropriate hardware for your server.[/tt]

I have been married fifty years now, largely because I never take my laptop on vacations. [tt] Gus Brunston - Access2000(DAO)[/tt] Intermediate skills.

 

[gusbrunston]

Mistake in my last post:
You can't select "Log name AutoCorrect changes" if you disable "Track Name AutoCorrect Info". [tt] Gus Brunston - Access2000(DAO)[/tt] Intermediate skills.

 

[skate]

wow, dustman, thanks. that little code you gave at the beginning is just what i needed. simple, huh? Now, what if the txtbox control is in a subform? What if this txtbox is named 1, 2, 3?

 

[Dustman]

Use the ! or . to access the subforms. Its always good to be specific and use Me first but you don't have to (I prefer doing it because it gives me the autocomplete box when I type Me.) Obviously, if the subform is on another form (it has to be open of course), you can't use Me, you would just use OtherFormName.SubformName.Controls() In the first example:
[tt]
Dim i As integer
i = 0
For i = 0 To 5 Step 1
Me.SubformName.Controls("txt" & i).Text = ""
Next i
[/tt]

That should work. if you just have the text boxes named numericly, use:
[tt]
Dim i As integer
i = 0
For i = 0 To 5 Step 1
SubformName.Controls(i).Text = ""
Next i
[/tt]

In the second example, you can use the same idea either replacing me or adding it after Me :
[tt]
Dim Ctl As Control
For Each Ctl In Me.SubformName.Controls
Select Case Ctl.ControlType
Case acTextBox
Select Case Ctl.Name
Case "txt1", "txt2", "txt3"
With Ctl
.Text = "Whatever"
.Enabled = True
End With
End Select
End Select
Next
[/tt]

I don't have a way to test it right now but you might have to change the With statement to include the Subform. Either by using With Me.SubformName.Ctl or changing it to use With Me.SubformName.Controls(Ctl)

I hope thats what you needed again.

Also in all my experience the ! works just like the . except it 2000 doesn't give you an auto complete box. Some of my books prefer using ! between forms and subforms (as does the built-in expression builder) but I don't know of any difference other than looks.
-Dustin
Rom 8:28

 

[skate]

Hmmm....can anyone see what I'm doing wrong then?

i = 1 'reset i
For i = 1 To 15
j = 1 'reset j
DoCmd.GoToRecord , , acFirst
For j = 1 To RCount
JTotal(i) = JTotal(i) + Forms!Day.Day_subform.Form.Controls(i)
If j < RCount Then
DoCmd.GoToRecord , , acNext
End If
Next j
Forms!Day.Day_Job_Hrs_subform.Form.Controls(i) = JTotal(i)
Next i

i made JTotal an array, initialized it to zero, but somehow my final value are all wrong.

 

[chrissie1]

why are you going to the next record if you are trying to add the values of controls in the same record????

and i cant see where rcount is coming from? &quot;What a wonderfull world&quot; - Louis armstrong

 

[skate]

for each &quot;i&quot; value (txtbox) there are RCount # of records (calculated earlier)
so there are say 10 receords for each of the 15 textboxes

 

[chrissie1]

then try this

i = 1 'reset i
DoCmd.GoToRecord , , acFirst
For i = 1 To rcount
j = 1 'reset j
DoCmd.GoToRecord , , acNext
For j = 1 To 15
JTotal(i) = JTotal(i) + Forms!day.Day_subform.Form.Controls(j)
Next j
Forms!Day.Day_Job_Hrs_subform.Form.Controls(i) = JTotal(i)
Next i
&quot;What a wonderfull world&quot; - Louis armstrong

 

[skate]

actually i want to cycle thru all the records/row to get the totals for each column &quot;i&quot; in my second subform column &quot;i&quot;, then continue to the next column