Solved 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.

 

[DakotaRidge]

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 Pete.

My use of lookups is very simple-minded. Most of my lookup tables work with MVFs. They are contextual, so the user usually only sees just a few attributes about a specific topic.

As I wrote a few days ago, I follow Clayton Christiansen's advice about limiting the number of options presented to folks.

 

[MajP]

My use of lookups is very simple-minded. Most of my lookup tables work with MVFs. They are contextual, so the user usually only sees just a few attributes about a specific topic

Then it is perfect to move them all into a single lookup table. Way easier to manager.

 

[DakotaRidge]

Then it is perfect to move them all into a single lookup table. Way easier to manager.

I don't think so.

Consider the symptoms of diseases, for example. Some symptoms of diabetes are different from those for kidney failure, heart disease, and dementia. I don't want everyone to have to scroll through 200 items when only 15 are possible for their condition. So I have a list of diabetes symptoms, a list of heart failure symptoms, and a list of dementia symptoms in different lookup tables. Yes, some ailments will be in multiple lookups, but that's okay. The people with diabetes will edit the Diabetes Lookup table. The people with kidney stones will maintain their table.

Another use that I have for lookup tables is the medical conditions that bad food cause. That table currently has ten reactions to bad food.

I place little Access "A" icons on forms to help users find the forms that store each list of attributes. Once I create an Access "A" button, I copy it to other forms. The buttons use macros to open the forms. The screenshot below shows this. All the Access "A"s open lookup forms.

 

[Pat Hartman]

As I wrote a few days ago, I follow Clayton Christiansen's advice about limiting the number of options presented to folks.

It isn't the number of items in a table that causes work, it is the number of tables, especially tables that do the same exact thing. It makes absolutely no difference to the logic of an application if you have a table with two items or two million. The number of rows in a table has ZERO impact on the number of related objects or code modules needed to maintain the table.

 

[DakotaRidge]

It isn't the number of items in a table that causes work, it is the number of tables, especially tables that do the same exact thing. It makes absolutely no difference to the logic of an application if you have a table with two items or two million. The number of rows in a table has ZERO impact on the number of related objects or code modules needed to maintain the table.

Pat, my tables are mostly all different. Tables for Grocery are not the same as tables for major diseases such as diabetes. Early in the spring I used Cost as the field name several times, but when I moved into Health, I changed that so now I use DoctorCost, MedicineCost, HospitalCost, and similar labels. When a form calculates the total cost of meds like this =sum([MedicineCost]) in the form header. Individual costs of meds are stored in the subform.

A user will enter data for only one transaction at a time. For example, if a person goes to Walmart today, she will enter the cost of groceries into the database tonight, say it's $132.89. She will tell the database that she used a Visa card to pay and that she went to Walmart today, August 6. The database will translate the date into the day of the week, so she sees that she went on Wednesday, which is not her normal grocery day. If it is not her normal grocery day, she knows that she did not see all of her friends at the store this week.

Boxes in the form header calculate grocery costs for the month, the quarter, half a year, and a whole year. The calculations are simple. To remind her what the form is for, there are pictures of food baskets and grocery stores in the form header.

 

[MajP]

have to scroll through 200 items when only 15 are possible for their condition. So I have a list of diabetes symptoms, a list of heart failure symptoms, and a list of dementia symptoms in different lookup tables.

Access has this cool thing called queries. You should give them a try.

"Select * from tblSymptoms where condition = 'diabetes'"
Provides a list of only diabetes symptoms to use as your rowsource.
or
"Select * from tblSymptoms where condition = 'dementia'"

 

[DakotaRidge]

Access has this cool thing called queries. You should give them a try.

"Select * from tblSymptoms where condition = 'diabetes'"
Provides a list of only diabetes symptoms to use as your rowsource.
or
"Select * from tblSymptoms where condition = 'dementia'"

I could also use the Filter command. No need for a query. It's even cooler. I can teach it to family members in a few minutes. It's right there in the Property Sheet.

 

[Pat Hartman]

I don't think so.

That's because you do not understand what we're talking about. And you haven't looked at the table maintenance app and tried to understand it.

The table maintenance app is very specific. It is for lists that have only a few, common, fields. The ONLY lists that are ever combined are the lists that are specifically used to provide the RowSources for combos and NOTHING else. There are 8 tables in the mini-app but only two are directly related to the table maintenance function. The table of tables and the table of table contents. this could be a single, self-referencing table. I made it two tables because too many people had trouble with the self-referencing concept. Most of the sample tables came from an insurance application and so they are simply test data and would all be removed when you installed this as part of your own app.

Here's the two tables with data: If you take TableID from tblCodeTables and look at the matching tableID in tblCodes, you will see the contents of each table. Of course, if you look at the forms - the user NEVER, EVER sees these raw tables, you choose Account Status Codes from the table list and you see only the four options associated with TableID 3 on the next form.

Table of tables:

Double click on the first table to see the contents of the first table:

Then, I make a separate querydef for each table in the table of tables. Here is the first one. They are all IDENTICAL except for their name and the Criteria. Notice the QueryName column in the first form image. It shows this query name. This is the rowSource for any combo that need s to show the contents of AccountStatusCode (tableID = 3)