Form used for Number of tables

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

I have 10 pairs of tables, propAdd1, Job1, Propadd2,
Job2, and so on. They all have the propadd as the main
form with the Job as the subform. At the moment I am
using the same form with a different name to view each
pair of tables. Is there any way of just using on form
and having it so if I open it with a command button it
will link to what ever pair of tables I have set on the
command button. If I could do this my database would be
a fraction of the size and it would save me making
multiple changes to the form when I modify the design.

Thanks, Alan
 
Alan,

Yes, there is a way by changing the RecordSource for the form in the OnLoad
Event, but I don't think that is your real solution.

Why do you have a table for each PropAdd and each Job? I don't know what
PropAdd is but I am guessing that it is a property address and the various
job tables are jobs that belong to that address. You should have one
Property table that holds all of the properties and one Jobs table that
holds all of the Jobs and then relate the Jobs to the Properties by using
the Primary Key of the Properties table as a Foreign Key in the Jobs table.

Then you can open one form based on the Property desired and view all of the
Jobs associated with a subform. Now you only have to make changes to one
form.

Gary Miller
 
I have 10 pairs of tables, propAdd1, Job1, Propadd2,
Job2, and so on.
Click to expand...

This isn't a very highly recommended design. I'd STRONGLY suggest
using ONE table, with another indexed field (with values 1 to 10).
Storing data in tablenames is neither necessary nor is it good design!

Just base your Form on a Query using the number as a criterion.

If I could do this my database would be
a fraction of the size and it would save me making
multiple changes to the form when I modify the design.
Click to expand...

And if you normalize the data as above, it will be smaller yet and
require NO changes when you add an eleventh category.
 
-----Original Message-----
Alan,

Yes, there is a way by changing the RecordSource for the form in the OnLoad
Event, but I don't think that is your real solution.

Why do you have a table for each PropAdd and each Job? I don't know what
PropAdd is but I am guessing that it is a property address and the various
job tables are jobs that belong to that address. You should have one
Property table that holds all of the properties and one Jobs table that
holds all of the Jobs and then relate the Jobs to the Properties by using
the Primary Key of the Properties table as a Foreign Key in the Jobs table.

Then you can open one form based on the Property desired and view all of the
Jobs associated with a subform. Now you only have to make changes to one
form.

Gary Miller




.
I did not explain my tables very well.
Click to expand...

Propadd1 (whould have 1,000 + property addresses in it
for a particular customer) Job1 (would be jobs done at
the property linked to the property address)

The problem I have is if I have all the properties
addresses in one table (aprox 10,000+)when searching for
a property address the database runs very slow.

Data base is searched via a Combo box by entering the
house number then selecting the street name from the drop
down list. So if you typed in House No 10 the drop down
list might contain 50+ houses. The database is split
into a front and back end with the back end on a small
office server about 6 computers access the same database
at any one time (not very fast computers P300 only)

Thanks for the reply
 
-----Original Message-----
This isn't a very highly recommended design. I'd STRONGLY suggest
using ONE table, with another indexed field (with values 1 to 10).
Storing data in tablenames is neither necessary nor is it good design!

Just base your Form on a Query using the number as a criterion.

And if you normalize the data as above, it will be smaller yet and
require NO changes when you add an eleventh category.


.
Thanks for the reply.
Click to expand...

You have given me food for thought.

My 10 tables hold 1,000 + property address for specific
customers in each of them with the jobs table linked to
the address.

Do you think it would be feasable to put all the property
address in onle large table 10,000+ address add a extra
field to assign a number (number would be to identify one
of the main 10 customers) have a main form with 10
command buttons on one for each main customer. That when
clicked it would open my property form filtering only the
records for that customer?

Or is there a better way.

Thanks Alan
Click to expand...
 
Back
Top