Form - a long time to open

  • Thread starter יריב החביב
  • Start date
×

יריב החביב

Helo,

We have a form that it take a very long time to open (more then 20 second's)

I will be grateful if sombody can take a look and see if i have done
somthing wrong.

the form have more then 20 command option's to run append query's to table's,

so for each table we put textbox wich show (vba code) if the table is alredy
full with

the data of the month that we want to load.

here is the code that we write :

Private Sub Form_Open(Cancel As Integer)

Me.monthfort.DefaultValue = """" & DMax("[f23]", "for_netoonim") & """"

Dim MaxDate As String
Dim MaxDate1 As Integer
Dim MaxDate2 As Integer
Dim MaxDate3 As Integer
Dim MaxDate4 As String
Dim MaxDate5 As String
Dim MaxDate6 As String
Dim MaxDate7 As String
Dim MaxDate8 As String
Dim MaxDate9 As String
Dim MaxDate10 As String

combodate = Me.monthfort

MaxDate = DMax("sacharmonth", "netoonim")
MaxDate1 = DCount("[f23]", "Qnetoonim_machlakot_v")
MaxDate2 = DCount("[f23]", "Qnetoonim_meamamnim_v")
MaxDate3 = DCount("[f23]", "Qnetoonim_shearootim_v")
MaxDate4 = DMax("sacharmonth", "netoonim1")
MaxDate5 = DMax("sacharmonth", "netoonim_mahlaka")
MaxDate6 = DMax("sacharmonth", "netoonim_memamen")
MaxDate7 = DMax("sacharmonth", "netoonim_sheroot")
MaxDate8 = DMax("sacharmonth", "netoonim2")
MaxDate9 = DMax("sacharmonth", "netoonim_mahlaka2")
MaxDate10 = DMax("sacharmonth", "netoonim_memamen2")

paar = DateDiff("m", combodate, MaxDate8)
If paar < 2 Then
Text100.Value = ""
Else
Text100.Value = "warning"
End If

If DateDiff("m", MaxDate, combodate) < 1 Then
Text68.Value = "full"
Else
Text68.Value = ""
End If

If DateDiff("m", MaxDate4, combodate) < 1 Then
Text66.Value = "full"
Else
Text66.Value = ""
End If

If DateDiff("m", MaxDate5, combodate) < 1 Then
Text57.Value = "full"
Else
Text57.Value = ""
End If

If DateDiff("m", MaxDate6, combodate) < 1 Then
Text60.Value = "full"
Else
Text60.Value = ""
End If

If DateDiff("m", MaxDate7, combodate) < 1 Then
Text63.Value = "full"
Else
Text63.Value = ""
End If

If DateDiff("m", MaxDate8, combodate) < 1 Then
Text75.Value = "full"
Else
Text75.Value = ""
End If

If DateDiff("m", MaxDate9, combodate) < 1 Then
Text69.Value = "full"
Else
Text69.Value = ""
End If

If DateDiff("m", MaxDate10, combodate) < 1 Then
Text71.Value = "full"
Else
Text71.Value = ""
End If

End Sub
 
A

a a r o n _ k e m p f

Access can't handle this many queries.

Move to SQL Server; build a couple of indexes-- and rewrite these DMax
functions to use stored procedures.
 
J

Jerry Whittle

The various "D" functions are known to be a little slow. The more records,
the slower they get. I'm not suprised that it takes 20 seconds to open a form
that runs all the DMax and DCount functions on opening.

You might be better served by writing Totals queries against the F23 and
sacharmonth tables. It would gather up the data much faster.

Also judging by your table and field names, I'm assuming that your data
isn't normalized properly. That can slow things down.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


יריב החביב said:
Helo,

We have a form that it take a very long time to open (more then 20 second's)

I will be grateful if sombody can take a look and see if i have done
somthing wrong.

the form have more then 20 command option's to run append query's to table's,

so for each table we put textbox wich show (vba code) if the table is alredy
full with

the data of the month that we want to load.

here is the code that we write :

Private Sub Form_Open(Cancel As Integer)

Me.monthfort.DefaultValue = """" & DMax("[f23]", "for_netoonim") & """"

Dim MaxDate As String
Dim MaxDate1 As Integer
Dim MaxDate2 As Integer
Dim MaxDate3 As Integer
Dim MaxDate4 As String
Dim MaxDate5 As String
Dim MaxDate6 As String
Dim MaxDate7 As String
Dim MaxDate8 As String
Dim MaxDate9 As String
Dim MaxDate10 As String

combodate = Me.monthfort

MaxDate = DMax("sacharmonth", "netoonim")
MaxDate1 = DCount("[f23]", "Qnetoonim_machlakot_v")
MaxDate2 = DCount("[f23]", "Qnetoonim_meamamnim_v")
MaxDate3 = DCount("[f23]", "Qnetoonim_shearootim_v")
MaxDate4 = DMax("sacharmonth", "netoonim1")
MaxDate5 = DMax("sacharmonth", "netoonim_mahlaka")
MaxDate6 = DMax("sacharmonth", "netoonim_memamen")
MaxDate7 = DMax("sacharmonth", "netoonim_sheroot")
MaxDate8 = DMax("sacharmonth", "netoonim2")
MaxDate9 = DMax("sacharmonth", "netoonim_mahlaka2")
MaxDate10 = DMax("sacharmonth", "netoonim_memamen2")

paar = DateDiff("m", combodate, MaxDate8)
If paar < 2 Then
Text100.Value = ""
Else
Text100.Value = "warning"
End If

If DateDiff("m", MaxDate, combodate) < 1 Then
Text68.Value = "full"
Else
Text68.Value = ""
End If

If DateDiff("m", MaxDate4, combodate) < 1 Then
Text66.Value = "full"
Else
Text66.Value = ""
End If

If DateDiff("m", MaxDate5, combodate) < 1 Then
Text57.Value = "full"
Else
Text57.Value = ""
End If

If DateDiff("m", MaxDate6, combodate) < 1 Then
Text60.Value = "full"
Else
Text60.Value = ""
End If

If DateDiff("m", MaxDate7, combodate) < 1 Then
Text63.Value = "full"
Else
Text63.Value = ""
End If

If DateDiff("m", MaxDate8, combodate) < 1 Then
Text75.Value = "full"
Else
Text75.Value = ""
End If

If DateDiff("m", MaxDate9, combodate) < 1 Then
Text69.Value = "full"
Else
Text69.Value = ""
End If

If DateDiff("m", MaxDate10, combodate) < 1 Then
Text71.Value = "full"
Else
Text71.Value = ""
End If

End Sub
Click to expand...
 
A

a a r o n _ k e m p f

Access can't reliably do anything.

Stack a query on top of a query?
Half the time, it will just crap out in Access

Stack a query on top of a query?
Works _GREAT_ in SQL Server







That is incorrect.

Access can't handle this many queries.

Helo,
Click to expand...
We have a form that it take a very long time to open (more then 20
second's)
Click to expand...
I will be grateful if sombody can take a look and see if i have done
somthing wrong.
Click to expand...
the form have more then 20 command option's to run append query's to
table's,
Click to expand...
so for each table we put textbox wich show (vba code) if the table is
alredy
full with
Click to expand...
the data of the month that we want to load.
Click to expand...
here is the code that we write :
Click to expand...
Private Sub Form_Open(Cancel As Integer)
Click to expand...
Me.monthfort.DefaultValue = """" & DMax("[f23]", "for_netoonim") & """"
Click to expand...
Dim MaxDate As String
Dim MaxDate1 As Integer
Dim MaxDate2 As Integer
Dim MaxDate3 As Integer
Dim MaxDate4 As String
Dim MaxDate5 As String
Dim MaxDate6 As String
Dim MaxDate7 As String
Dim MaxDate8 As String
Dim MaxDate9 As String
Dim MaxDate10 As String
Click to expand...
combodate = Me.monthfort
Click to expand...
MaxDate = DMax("sacharmonth", "netoonim")
MaxDate1 = DCount("[f23]", "Qnetoonim_machlakot_v")
MaxDate2 = DCount("[f23]", "Qnetoonim_meamamnim_v")
MaxDate3 = DCount("[f23]", "Qnetoonim_shearootim_v")
MaxDate4 = DMax("sacharmonth", "netoonim1")
MaxDate5 = DMax("sacharmonth", "netoonim_mahlaka")
MaxDate6 = DMax("sacharmonth", "netoonim_memamen")
MaxDate7 = DMax("sacharmonth", "netoonim_sheroot")
MaxDate8 = DMax("sacharmonth", "netoonim2")
MaxDate9 = DMax("sacharmonth", "netoonim_mahlaka2")
MaxDate10 = DMax("sacharmonth", "netoonim_memamen2")
Click to expand...
paar = DateDiff("m", combodate, MaxDate8)
If paar < 2 Then
Text100.Value = ""
Else
Text100.Value = "warning"
End If
Click to expand...
If DateDiff("m", MaxDate, combodate) < 1 Then
Text68.Value = "full"
Else
Text68.Value = ""
End If
Click to expand...
If DateDiff("m", MaxDate4, combodate) < 1 Then
Text66.Value = "full"
Else
Text66.Value = ""
End If
Click to expand...
If DateDiff("m", MaxDate5, combodate) < 1 Then
Text57.Value = "full"
Else
Text57.Value = ""
End If
Click to expand...
If DateDiff("m", MaxDate6, combodate) < 1 Then
Text60.Value = "full"
Else
Text60.Value = ""
End If
Click to expand...
If DateDiff("m", MaxDate7, combodate) < 1 Then
Text63.Value = "full"
Else
Text63.Value = ""
End If
Click to expand...
If DateDiff("m", MaxDate8, combodate) < 1 Then
Text75.Value = "full"
Else
Text75.Value = ""
End If
Click to expand...
If DateDiff("m", MaxDate9, combodate) < 1 Then
Text69.Value = "full"
Else
Text69.Value = ""
End If
Click to expand...
If DateDiff("m", MaxDate10, combodate) < 1 Then
Text71.Value = "full"
Else
Text71.Value = ""
End If
Click to expand...
End Sub
Click to expand...
Click to expand...
 
×

יריב החביב

Thank you Jerry,

Can you give more details what do you mean by

". . .your data isn't normalized properly"

thank's


Jerry Whittle said:
The various "D" functions are known to be a little slow. The more records,
the slower they get. I'm not suprised that it takes 20 seconds to open a form
that runs all the DMax and DCount functions on opening.

You might be better served by writing Totals queries against the F23 and
sacharmonth tables. It would gather up the data much faster.

Also judging by your table and field names, I'm assuming that your data
isn't normalized properly. That can slow things down.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


יריב החביב said:
Helo,

We have a form that it take a very long time to open (more then 20 second's)

I will be grateful if sombody can take a look and see if i have done
somthing wrong.

the form have more then 20 command option's to run append query's to table's,

so for each table we put textbox wich show (vba code) if the table is alredy
full with

the data of the month that we want to load.

here is the code that we write :

Private Sub Form_Open(Cancel As Integer)

Me.monthfort.DefaultValue = """" & DMax("[f23]", "for_netoonim") & """"

Dim MaxDate As String
Dim MaxDate1 As Integer
Dim MaxDate2 As Integer
Dim MaxDate3 As Integer
Dim MaxDate4 As String
Dim MaxDate5 As String
Dim MaxDate6 As String
Dim MaxDate7 As String
Dim MaxDate8 As String
Dim MaxDate9 As String
Dim MaxDate10 As String

combodate = Me.monthfort

MaxDate = DMax("sacharmonth", "netoonim")
MaxDate1 = DCount("[f23]", "Qnetoonim_machlakot_v")
MaxDate2 = DCount("[f23]", "Qnetoonim_meamamnim_v")
MaxDate3 = DCount("[f23]", "Qnetoonim_shearootim_v")
MaxDate4 = DMax("sacharmonth", "netoonim1")
MaxDate5 = DMax("sacharmonth", "netoonim_mahlaka")
MaxDate6 = DMax("sacharmonth", "netoonim_memamen")
MaxDate7 = DMax("sacharmonth", "netoonim_sheroot")
MaxDate8 = DMax("sacharmonth", "netoonim2")
MaxDate9 = DMax("sacharmonth", "netoonim_mahlaka2")
MaxDate10 = DMax("sacharmonth", "netoonim_memamen2")

paar = DateDiff("m", combodate, MaxDate8)
If paar < 2 Then
Text100.Value = ""
Else
Text100.Value = "warning"
End If

If DateDiff("m", MaxDate, combodate) < 1 Then
Text68.Value = "full"
Else
Text68.Value = ""
End If

If DateDiff("m", MaxDate4, combodate) < 1 Then
Text66.Value = "full"
Else
Text66.Value = ""
End If

If DateDiff("m", MaxDate5, combodate) < 1 Then
Text57.Value = "full"
Else
Text57.Value = ""
End If

If DateDiff("m", MaxDate6, combodate) < 1 Then
Text60.Value = "full"
Else
Text60.Value = ""
End If

If DateDiff("m", MaxDate7, combodate) < 1 Then
Text63.Value = "full"
Else
Text63.Value = ""
End If

If DateDiff("m", MaxDate8, combodate) < 1 Then
Text75.Value = "full"
Else
Text75.Value = ""
End If

If DateDiff("m", MaxDate9, combodate) < 1 Then
Text69.Value = "full"
Else
Text69.Value = ""
End If

If DateDiff("m", MaxDate10, combodate) < 1 Then
Text71.Value = "full"
Else
Text71.Value = ""
End If

End Sub
Click to expand...
Click to expand...
 
×

יריב החביב

Thank you Jerry,

Can you give more details what do you mean by

". . .your data isn't normalized properly"

thank's


--
תודה רבה


Jerry Whittle said:
The various "D" functions are known to be a little slow. The more records,
the slower they get. I'm not suprised that it takes 20 seconds to open a form
that runs all the DMax and DCount functions on opening.

You might be better served by writing Totals queries against the F23 and
sacharmonth tables. It would gather up the data much faster.

Also judging by your table and field names, I'm assuming that your data
isn't normalized properly. That can slow things down.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


יריב החביב said:
Helo,

We have a form that it take a very long time to open (more then 20 second's)

I will be grateful if sombody can take a look and see if i have done
somthing wrong.

the form have more then 20 command option's to run append query's to table's,

so for each table we put textbox wich show (vba code) if the table is alredy
full with

the data of the month that we want to load.

here is the code that we write :

Private Sub Form_Open(Cancel As Integer)

Me.monthfort.DefaultValue = """" & DMax("[f23]", "for_netoonim") & """"

Dim MaxDate As String
Dim MaxDate1 As Integer
Dim MaxDate2 As Integer
Dim MaxDate3 As Integer
Dim MaxDate4 As String
Dim MaxDate5 As String
Dim MaxDate6 As String
Dim MaxDate7 As String
Dim MaxDate8 As String
Dim MaxDate9 As String
Dim MaxDate10 As String

combodate = Me.monthfort

MaxDate = DMax("sacharmonth", "netoonim")
MaxDate1 = DCount("[f23]", "Qnetoonim_machlakot_v")
MaxDate2 = DCount("[f23]", "Qnetoonim_meamamnim_v")
MaxDate3 = DCount("[f23]", "Qnetoonim_shearootim_v")
MaxDate4 = DMax("sacharmonth", "netoonim1")
MaxDate5 = DMax("sacharmonth", "netoonim_mahlaka")
MaxDate6 = DMax("sacharmonth", "netoonim_memamen")
MaxDate7 = DMax("sacharmonth", "netoonim_sheroot")
MaxDate8 = DMax("sacharmonth", "netoonim2")
MaxDate9 = DMax("sacharmonth", "netoonim_mahlaka2")
MaxDate10 = DMax("sacharmonth", "netoonim_memamen2")

paar = DateDiff("m", combodate, MaxDate8)
If paar < 2 Then
Text100.Value = ""
Else
Text100.Value = "warning"
End If

If DateDiff("m", MaxDate, combodate) < 1 Then
Text68.Value = "full"
Else
Text68.Value = ""
End If

If DateDiff("m", MaxDate4, combodate) < 1 Then
Text66.Value = "full"
Else
Text66.Value = ""
End If

If DateDiff("m", MaxDate5, combodate) < 1 Then
Text57.Value = "full"
Else
Text57.Value = ""
End If

If DateDiff("m", MaxDate6, combodate) < 1 Then
Text60.Value = "full"
Else
Text60.Value = ""
End If

If DateDiff("m", MaxDate7, combodate) < 1 Then
Text63.Value = "full"
Else
Text63.Value = ""
End If

If DateDiff("m", MaxDate8, combodate) < 1 Then
Text75.Value = "full"
Else
Text75.Value = ""
End If

If DateDiff("m", MaxDate9, combodate) < 1 Then
Text69.Value = "full"
Else
Text69.Value = ""
End If

If DateDiff("m", MaxDate10, combodate) < 1 Then
Text71.Value = "full"
Else
Text71.Value = ""
End If

End Sub
Click to expand...
Click to expand...
 
B

BruceM

Perhaps Jerry isn't monitoring this thread any more. I see the MaxDate 1,
MaxDate 2, etc. variables, and that they are derived from fields, often with
the same names, in ten different tables. This suggests data that are not
organized efficiently or effectively. Running a lot of append queries may
not be the best approach, either. Without knowing more about what you are
trying to accomplish or how your database is structured it is difficult to
be specific.
You would be doing yourself a favor if you named your text boxes and other
controls with names that have some meaning. For instance, if the field is
YourField, the text box bound to the field could be txtYourField. Also,
Value is the default property for a text box, so you don't need to specify
it (although you can if you prefer). This is adequate:
Text100 = ""

יריב החביב said:
Thank you Jerry,

Can you give more details what do you mean by

". . .your data isn't normalized properly"

thank's


--
תודה רבה


Jerry Whittle said:
The various "D" functions are known to be a little slow. The more
records,
the slower they get. I'm not suprised that it takes 20 seconds to open a
form
that runs all the DMax and DCount functions on opening.

You might be better served by writing Totals queries against the F23 and
sacharmonth tables. It would gather up the data much faster.

Also judging by your table and field names, I'm assuming that your data
isn't normalized properly. That can slow things down.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


יריב החביב said:
Helo,

We have a form that it take a very long time to open (more then 20
second's)

I will be grateful if sombody can take a look and see if i have done
somthing wrong.

the form have more then 20 command option's to run append query's to
table's,

so for each table we put textbox wich show (vba code) if the table is
alredy
full with

the data of the month that we want to load.

here is the code that we write :

Private Sub Form_Open(Cancel As Integer)

Me.monthfort.DefaultValue = """" & DMax("[f23]", "for_netoonim") & """"

Dim MaxDate As String
Dim MaxDate1 As Integer
Dim MaxDate2 As Integer
Dim MaxDate3 As Integer
Dim MaxDate4 As String
Dim MaxDate5 As String
Dim MaxDate6 As String
Dim MaxDate7 As String
Dim MaxDate8 As String
Dim MaxDate9 As String
Dim MaxDate10 As String

combodate = Me.monthfort

MaxDate = DMax("sacharmonth", "netoonim")
MaxDate1 = DCount("[f23]", "Qnetoonim_machlakot_v")
MaxDate2 = DCount("[f23]", "Qnetoonim_meamamnim_v")
MaxDate3 = DCount("[f23]", "Qnetoonim_shearootim_v")
MaxDate4 = DMax("sacharmonth", "netoonim1")
MaxDate5 = DMax("sacharmonth", "netoonim_mahlaka")
MaxDate6 = DMax("sacharmonth", "netoonim_memamen")
MaxDate7 = DMax("sacharmonth", "netoonim_sheroot")
MaxDate8 = DMax("sacharmonth", "netoonim2")
MaxDate9 = DMax("sacharmonth", "netoonim_mahlaka2")
MaxDate10 = DMax("sacharmonth", "netoonim_memamen2")

paar = DateDiff("m", combodate, MaxDate8)
If paar < 2 Then
Text100.Value = ""
Else
Text100.Value = "warning"
End If

If DateDiff("m", MaxDate, combodate) < 1 Then
Text68.Value = "full"
Else
Text68.Value = ""
End If

If DateDiff("m", MaxDate4, combodate) < 1 Then
Text66.Value = "full"
Else
Text66.Value = ""
End If

If DateDiff("m", MaxDate5, combodate) < 1 Then
Text57.Value = "full"
Else
Text57.Value = ""
End If

If DateDiff("m", MaxDate6, combodate) < 1 Then
Text60.Value = "full"
Else
Text60.Value = ""
End If

If DateDiff("m", MaxDate7, combodate) < 1 Then
Text63.Value = "full"
Else
Text63.Value = ""
End If

If DateDiff("m", MaxDate8, combodate) < 1 Then
Text75.Value = "full"
Else
Text75.Value = ""
End If

If DateDiff("m", MaxDate9, combodate) < 1 Then
Text69.Value = "full"
Else
Text69.Value = ""
End If

If DateDiff("m", MaxDate10, combodate) < 1 Then
Text71.Value = "full"
Else
Text71.Value = ""
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Open a report using a combobox on a form 1
How to update table with calculated form value? 6
Pivot table - double-click event to open another form 0
Opening an Excel Worksheet from Project yields an empty project... 90%of the time! 2
Opening a Word document or template from Access 0

Top