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

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

 

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

 

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

 

[MajP]

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

OMG! There is no teaching anybody anything. There is nothing for the user to do. You just set the rowsource of your different lists based on a query.

 

[Ken Sheridan]

This section is a main form which will have multiple sub forms (25 sub forms in total). They are for Invoice 1, 2, 3... to 25.

So, you have 25 possible invoices per what? I'm guessing its per project or similar. In a relational database there would be a one-to-many relationship type between Projects and Invoices, to model which you'd have Projects table and an Invoices table. The latter would include a ProjectID or similar foreign key column referencing the ProjectID or similar primary key of the Projects table. Consequently the number of invoices per project will vary.

The image below is the model for a basic invoicing application. This application is primarily intended to demonstrate how to output invoices as PDF files, but it serves to illustrates the basic structure for an invoicing application. The demo does not include a Projects table, but it would be a simple task to add one, and reference it from the Invoices table.

A point to notice about this model is that the Invoices table repeats the customer's name and address columns. In this example a decision has been taken that previous invoices will retain the customer's name and address data which were current at the time the invoice was raised. It might alternatively have been decided that previous invoices would reflect the customer's current name and address data.

Note also that the InvoiceDetails table repeats the UnitPrice and TaxRate columns from the Products table. In this case the repetition is mandatory, because previous invoices must retain the values current at the time the invoice was raised, regardless of any subsequent changes in these values in the Products table.

The image below is of the data entry form for entering invoice data. Note that this allows for multiple physical instances of an invoice to be raised where partial payments have been made. In the demo the same invoice number is used per instance, but it might alternatively be decided to create a new invoice number per instance.

A copy of the application is attached as a .zip file.

PS: I've just noticed that your original post was in January! Nevertheless, I hope that the above might be helpful, if not to you, at least to the member who has resurrected the thread, and is clearly in need of guidance.