How to quickly duplicate Tables & associated subforms within a main form

[GaP42]

Another quick question.

Data validation best practice. How would you control a Combo box to show a known list of values?

Do I create a table for each combo box? Most need to be lists which are occasionally edited by an end user

In excel I had them all in 1 sheet as Named Tables

Most/all of your combos will be driven from the Type tables - using a query of the table to show the displayed value, hiding the stored ID of the particular type. Type table lists should not be arbitrarily edited - you need to avoid multiple terms being used for the same type. However they can be set up so that if a value is entered that does not exist the form controlling such additions is displayed. There are other methods too.. If you discover data items not covered by the Type list that likely leads to needing to create another Type table. As mentioned earlier these simple Type tables can be combined into one master table holding all the types each type being distinguished by the TypeCategory (ActionType, ProjectStatus, etc)
The lists for these Types should not (generally) be long - the user should be able to see the item they wish to select in the list displayed. If they are longer consider a find-as-you-type option for the combo (not available by default).
Anther trick, if needed, is to use a mapping between equivalent types and the master of those types so that selections/entries made in the "real data" can then pick up the masterID for that type. Not often used. However if you need to migrate your spreadsheet data and users have created some false duplicate Types a mapping table is used to clean the data on import.

 

[dibblermail]

Most/all of your combos will be driven from the Type tables - using a query of the table to show the displayed value, hiding the stored ID of the particular type. Type table lists should not be arbitrarily edited - you need to avoid multiple terms being used for the same type. However they can be set up so that if a value is entered that does not exist the form controlling such additions is displayed. There are other methods too.. If you discover data items not covered by the Type list that likely leads to needing to create another Type table. As mentioned earlier these simple Type tables can be combined into one master table holding all the types each type being distinguished by the TypeCategory (ActionType, ProjectStatus, etc)
The lists for these Types should not (generally) be long - the user should be able to see the item they wish to select in the list displayed. If they are longer consider a find-as-you-type option for the combo (not available by default).
Anther trick, if needed, is to use a mapping between equivalent types and the master of those types so that selections/entries made in the "real data" can then pick up the masterID for that type. Not often used. However if you need to migrate your spreadsheet data and users have created some false duplicate Types a mapping table is used to clean the data on import.

Using an additional field for CategoryType to put them all in 1 table is the fix I was looking for. It was mentioned above but got forgotten in the volume of information I'm trying to absorb.

Thanks for the nudge in the right direction.

 

[MajP]

Here is a demo of a single lookup table where you are simply saving a value.
Everyone one of the combo pulls from the same lookup and every lookup is editable. I use this when I simply need a lot of simple "value" lookups without other related fields. Does not make sense for certain lists.
1. Very long lists
2. Lists that need a lot of validation
3. Lists with other related data fields
4. Lists where the value changes need to cascade often.

 

[dibblermail]

Here is a demo of a single lookup table where you are simply saving a value.
Everyone one of the combo pulls from the same lookup and every lookup is editable. I use this when I simply need a lot of simple "value" lookups without other related fields. Does not make sense for certain lists.
1. Very long lists
2. Lists that need a lot of validation
3. Lists with other related data fields
4. Lists where the value changes need to cascade often.

Thanks,

That looks to do everything I would need to control the basic combo boxes I have.

I'll have a play.

 

[dibblermail]

Me again, I've hit a stupid issue.

I wanted a dummy run at building a few linked tables & forms but its going wrong. Its got to be a simple fix, but I'm not sure what it is. When I have a form with more than 1 table (linked tables) it becomes read only & only shows 1 record. Forms for the individual tables show 2 records.

I've tried several times but keep running into the same issues.

Please can someone explain what this muppet did wrong? did I link them incorrectly?

I know I have Client & Company linking (not all entries in tbl_Contacts is a client & I knew I'd trip over that at some time in the future if I used "Client") , that was intentional, hopefully that's not a bad idea?

 

[GPGeorge]

"When I have a form with more than 1 table (linked tables) it becomes read only & only shows 1 record. "

That is pretty much how it usually turns out when you try to use multiple table queries.

The solution is to bind each form to a table. Or bind each form to a query based on one table.

The chances of creating read-only queries increases dramatically when you join in more tables.

If you need to add records to related tables (i.e. a "one-side" table and one or more "many-side" tables) the way to design forms for that is to create a main form/sub form design.

The main form is bound to the "one-side" table and each subform in that main form is bound to one of the "many-side" tables.

 

[dibblermail]

"When I have a form with more than 1 table (linked tables) it becomes read only & only shows 1 record. "

That is pretty much how it usually turns out when you try to use multiple table queries.

The solution is to bind each form to a table. Or bind each form to a query based on one table.

The chances of creating read-only queries increases dramatically when you join in more tables.

If you need to add records to related tables (i.e. a "one-side" table and one or more "many-side" tables) the way to design forms for that is to create a main form/sub form design.

The main form is bound to the "one-side" table and each subform in that main form is bound to one of the "many-side" tables.

Thank you.

I'll have a play.

 

[MajP]

Your tables and relationships do not make any sense.

We relate tables by PK to foreign key, not just haphazardly.

Tbl Client has a PK called ID that is not even used.
ID should be your PK and then renamed ClientID. Client is text field and a clients name and makes a poor choice for your PK
Tbl Job should have a foreign key called ClientID_FK which links to tbl_Client by client_ID
TblContacts should have a ContactID primary key and not a contact name

It appears to me that only clients have contacts. So tbl Contacts should have a foreign key called Client_ID_FK and link to the client table not the job table.

The contact thing is likely more complicated. If you have other contacts that relate to vendors or if jobs have specific client contacts. There may be a need for some junction tables.

 

[LarryE]

Try this design just for a start and I emphasize just a start:

And here's the file in relational database format as myself and others have suggested. I went ahead and completed some foreign keys in the Site and Job table for your convenience to get you started. My suggestion is to:

1. Create a Client form, a Site form, a Job form and Invoice form all based on the appropriate related tables.
2. The Invoice form is a sub-form of the Job form
3. The Job form is a sub-form of the Site form
4. The Site form is a sub-form of the Client form
5. When you build the sub-forms, ACCESS will automatically create the Master/Child links for you.

This should get you started on the right track.

 

[GaP42]

Me again, I've hit a stupid issue.

I wanted a dummy run at building a few linked tables & forms but its going wrong. Its got to be a simple fix, but I'm not sure what it is. When I have a form with more than 1 table (linked tables) it becomes read only & only shows 1 record. Forms for the individual tables show 2 records.

I've tried several times but keep running into the same issues.

Please can someone explain what this muppet did wrong? did I link them incorrectly?

I know I have Client & Company linking (not all entries in tbl_Contacts is a client & I knew I'd trip over that at some time in the future if I used "Client") , that was intentional, hopefully that's not a bad idea?

Refer back to the db schema provided - note the naming of the primary keys and the joins illustrated. MajP discusses this above.
You use the relations/relationship (based upon the keys: Primary Key (PK) / Foreign Key (FK)) to tell you how data is logically associated, and use these to construct queries. This is why the schema design is so important to set up as the foundation for the db application.
Start with simple forms which are based on one table and single table query. Build on that to be able to navigate/open another form to show data in a 1:M child table using the PK - FK join - containing the say jobs of a client: and try using a subform within the main form to display that child data.

 

[Pat Hartman]

Do I create a table for each combo box? Most need to be lists which are occasionally edited by an end user

Only if you want a lot of work. Here is a link to a mini-app that I import into all new databases. It has minor security to limit who gets to change/add items to a list. The mini-app uses two tables two forms/subforms and two reports. It is used for all simple tables. If your table has data other than the value/code, it belongs in a separate table.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

 

[dibblermail]

Thanks for all the replies.

I'm still at the stage of not knowing what the right questions are, let alone the right answers.

Thanks for being patient with me. Every time I think I'm close I find another layer of issues; but that's to be expected.

What I posted was a first go to see if I could get it to work. The extra key was an oversight & I can now see that I should have used it anyway or renamed it rather than creating a new one & deleting it.

I'll have another go based on your guidance. Thanks again for the help.