Slow form need help (1 Viewer)

[Yoshi88]

I have a form (billing) that is use often. The form is base on a query of 3 tables and contains about 85 fields. The querry load smoothly but the form can take up to 30 sec to load. What should i do to reduce this.

Thanks

 

[KevinM]

Reduce the number of fields in the form should help.

85 fields from 3 tables ?????
That sounds like an awful lot.
Why so many?
Let me guess, 'unormalized database'.

 

[Yoshi88]

What do you mean by unformalize database?

Its a client billing database, they have a lot of information on the bill and they have a lot of information on their clients.

I forgot to mention that the form is link to 10 other tables for all the combo box needed in the form.

Do you have a solution?

 

[KevinM]

A form linked to 10 other tables?
Again this seems rather excessive.
Please explain the structure of some of your tables and the 85 fields (not all).
How are they linked?
What is the 'lot of information' for each client?

I'm asking these questions as I feel your db may be structured incorrectly and it may be better for you to res-structure it ('normalize' it).

 

[Yoshi88]

Its a DB for a travel agency. They neede it to be flexible to the change that can occur in the future.

The main tables are agent and client with information like:
name, phone numbers, credit card adress, birthday, Air Miles number, their actual agent, what they usually choose when they travel (airplane, train, group, sports, beach...)

There is a biling table:
name (client table), agent (agent table), country, city, hotel, description different information like insurance and tour (10 tables link for this section with combo box because they want to be able to add new information if necessary), the price and quantity, total and deposit.

They other table are the country/city, the 10 tables mention in biling.

Its a one to many relation between agent/biling and client/biling.

I have a query for the biling form for the user let them see the adress of the client and the name of the agent they choose. It run reaaly fast in query mode but in a form its slow. The computer they use are slower than mine so I imagine it will be load much slower on theirs.

I tough i can just put sub form for the adress and the name of the agent to make it faster. And i dont have macro that can slow it down only 2 small code to show the date and time.

Is this normalize or not, do you have any solution?

Thanks for helping me and sorry for the mistake in english but i'm french.

Francois

 

[ElsVanMiert]

Bonjour, François,

Apparemment, l'architecture de ton appli n'est pas optimale. Tu risques d'avoir des problèmes GRAVES dans l'avenir.
Pour accélerer l'ouverture de ton formulaire (c'est uniquement une dépannage!) tu dois prendre en considération que les requêtes de tes zones de liste et de choix multiple prennent 10x de temps que les tables. Donc enleve toutes les sources contrôles et active-les uniquement en GotFocus.
Pareil pour les contrôles onglet.
Pour "réparer" l'architecture de ton appli je te conseille vivement de la faire vérifier par des spécialistes en la matière. Sinon, tu risques des ennuis avec ton/tes clients.

 

[KevinM]

Please explain more on your 10 Billing tables. I'm sure you can get this down to a few. Users shouldn't be adding extra fields to any table, they should be inputting data only which leads me to believe that it is structured wrong.

 

[Yoshi88]

The 10 tables give the possible choice for 10 combo box in my billing form. They are explanation or description of different information on the billing form, for example:

Airplane: Departure from Montreal Arrival Orlando with Air Canada

The user can add other data in the field link by the combo box. I was able to reduce it to 7 but that is the maximum I can do if I want to let the users to add their own data.

I wish it explain a bit more my structure.

Thanks

 

[zunan]

Remove the SQL statement that populates each combo box from the Row Source line on the property sheet, Data tab. Instead, place it into a few lines of code the "On Enter" event. Code would look something like this:
Dim strSQL as String
strSQL = "SELECT DISTINCT [TableName].[FieldName] FROM [TableName] ORDER BY [TableName].[FieldName];"
Me.cboComboBoxName.RowSource = strSQL
If you do this for each Combo Box, your form will not have to populate every one of the before it opens. Insted, it will only populate it when it is needed.