Solved How to quickly duplicate Tables & associated subforms within a main form (1 Viewer)

[dibblermail]

Hi, I'm very new to vba in Access but have dabbled in Excel for some time.

I'm in Access 2016 (through office 365).

I have a large slow spreadsheet over multiple sheets with lots of VBA which we use to manage our jobs & I'm remaking it in access.

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.

The data in each sub form is repeating (dates, descriptions, etc), but the answers are unique to that Invoice number.

I have Subform1, & SubformTable1 working & the code written to control them.

All the naming in the table & form ends in a "1". The Subform2, & SubformTable2 naming will all end in a 2, and so on.

Each subform has about 40 objects (and therefore 40 table entries)

How do experienced people create the other 24 versions?

Duplicating the tables & renaming the fields is fairly quick & easy

Renaming the code will be slow, but it's not massively time consuming (about 200 lines per subform)

Manually renaming/repathing each subform will take forever; I've come to the conclusion it's probably quicker to remake each subform from its table!

I'm I missing a trick? Or is it man up & get on with it?

 

[MajP]

How do experienced people create the other 24 versions?

You don't!
That would take you a ton of time to do, and be really inefficient.
If you create your tables correctly this can be done very simply.
An invoice is an invoice. So there is one table (or groups of tables) holding invoice information. Maybe there is a field the identifies the type of invoice.
YOU NEVER EVER. duplicate tables.
Tell us in business terms what you want to capture and what the db needs to do.
Then you need to get your tables properly "normalized". The tables in your DB will not look anything like your data structure in Excel.
Currently you are simply building a spread sheet in Access. That will be painful, inflexible, and do very little.
If you can share some of the excel tables "sheets" we might be able to help faster to structure you tables. It is like building a house. The frame has to be square, plum, exact to measurements, and designed to accommodate all planned functions. Before thinking about forms, reports, etcs.

 

[Uncle Gizmo]

your DB will not look anything like your data structure in Excel.

It is very common For People moving from Excel to MS Access to run into problems. I blog about the problem and solution on my website here:-

Excel in Access - Nifty Access

Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...

www.niftyaccess.com

 

[Gasman]

I believe you need to read up on normalization before you even start.
@micron who used to be here provided links, but I can no longer find them here. However they are here as I have posted them previously.

 

[GPGeorge]

The Northwind Developer Version illustrates the tables needed for invoicing as well as the invoice reports.

A simpler version, Northwind Beginner, might be an easier place to get your feet wet.

Featured Access templates - Microsoft Support

Analyze and store large amounts of information, and manage data more efficiently with Access database templates available for download.

support.microsoft.com

 

[arnelgp]

kindly share your workbook (either in a Link to your dropbox/onedrive, etc.), and someone with a good heart will convert it into MS access at no cost.

 

[dibblermail]

Sorry for going silent! It was a very busy weekend & Yesterday was just as crazy.

Thank you to everyone for all the suggestions & help. I have links with some reading to do here & a database to download & explore.

The spreadsheet has been evolved & honed over 5 years to something that works very well, but is far too slow now.

The look & feel is very specific & would be very hard to walk away from. It's set out just how the primary user wants it & so I need to replicate that as closely as possible.

There are currently about 600 columns that I need to capture & currently there are just under 1300 rows (1 row per project number).

I can see that by adding 1 more field I can make the invoice table & form work for all project numbers; but I was concerned it would fill up too quickly if I went that route. From your comments it looks like I may have been wrong?

Give me chance to run through your links & the example database and I'll come back.

 

[CJ_London]

The data in each sub form is repeating (dates, descriptions, etc), but the answers are unique to that Invoice number.

perhaps you don't need a subform, perhaps you need a continuous form

There are currently about 600 columns that I need to capture & currently there are just under 1300 rows (1 row per project number).

the maximum number of columns in Access is 255 per table or query. Have you already got this data into Access?

Either way, it is very difficult to suggest a solution with knowing what you actually need. From your description I would expect you would need tables for

customers
projects
invoice header
invoice line

Quite possibly more

Strongly advise you zip and upload your access file and perhaps your excel file - if you are worried about confidentiality, remove most of the data, leaving enough to illustrate what you are trying to achieve. You can also change data values to something that although meaningless is still relevant (e.g. don't substitute a number with text)

 

[Gasman]

Here is a function you can use to obscure your data.
Run it on a copy of your data. Only works for Access though.

Randomizing Data for Posted db's

We often ask Original Poster to upload a copy of their db but the issue might be that it contains sensitive data. Complete data removal may not allow us to troubleshoot the OP's issue because queries, forms, etc. need records in order to observe or trouble shoot the problem. Here's a...

www.access-programmers.co.uk

 

[GPGeorge]

Sorry for going silent! It was a very busy weekend & Yesterday was just as crazy.

Thank you to everyone for all the suggestions & help. I have links with some reading to do here & a database to download & explore.

The spreadsheet has been evolved & honed over 5 years to something that works very well, but is far too slow now.

The look & feel is very specific & would be very hard to walk away from. It's set out just how the primary user wants it & so I need to replicate that as closely as possible.

There are currently about 600 columns that I need to capture & currently there are just under 1300 rows (1 row per project number).

I can see that by adding 1 more field I can make the invoice table & form work for all project numbers; but I was concerned it would fill up too quickly if I went that route. From your comments it looks like I may have been wrong?

Give me chance to run through your links & the example database and I'll come back.

One of the biggest hurdles for people learning how to create relational database applications with Access can be familiarity with Excel. Ironically, the more you try to replicate Excel in Access, the harder it is to produce a viable application.

In other words, trying to make this Access database application resemble your primary user's Excel experience will, in fact, prevent you from succeeding.

You have two tasks in front of you, therefore.

1. Explain to your user that trying to preserve an Excel experience in Access leads to failure and that in order to succeed, they'll need to embrace change.
2. Learn how to create relational database applications, following the principles of Database Normalization before starting over with this specific project.

 

[dibblermail]

the maximum number of columns in Access is 255 per table or query. Have you already got this data into Access?

But I assumed (maybe I was wrong) that if I used multiple tables, a main 1 to hold job number info (client site address etc) and 1 for each of the 25 Invoices that would help to overcome the 255 limit. hence my original question.

The Invoice tables would each be about 25 fields. (25 fields x 25 invoices is 625 fields in total)

The suggestion here seems to be the 25 fields, plus Job Number & plus Invoice Number so they are linkable, so 26. But that would generate a lot of rows. Does that matter in reality?

The current 1300 rows would probably jump to about 7000 in the invoices table (not every job uses all 25 Invoices, many only 1 or 2). would that cause it to run really slow?

 

[GPGeorge]

But I assumed (maybe I was wrong) that if I used multiple tables, a main 1 to hold job number info (client site address etc) and 1 for each of the 25 Invoices that would help to overcome the 255 limit. hence my original question.

The Invoice tables would each be about 25 fields. (25 fields x 25 invoices is 625 fields in total)

The suggestion here seems to be the 25 fields, plus Job Number & plus Invoice Number so they are linkable, so 26. But that would generate a lot of rows. Does that matter in reality?

The current 1300 rows would probably jump to about 7000 in the invoices table (not every job uses all 25 Invoices, many only 1 or 2). would that cause it to run really slow?

As previously suggested, you may be pursuing a "spreadsheet inside Access" at the risk of never getting a workable solution.

I suggest you take a break here. Invest your time in learning how relational databases work instead of pursuing an unworkable table set up.

Here is a good video that can help you visualize the process to get off on the right foot. There are many other resources available on the internet for "Database Normalization" as well.

 

[dibblermail]

This is a screenshot of the current interface in Excel (As above, I'd like to keep the access version as close to this as I can)

In invoice 1 I've turned on CIS & Part Payment to show the hidden elements.

This is the main form for inputting a new job & managing an existing one, which is about 20% of the spreadsheet as a whole. But its where I've started as I believe the rest will be done by creating reports from this info.

 

[GPGeorge]

View attachment 118006


This is a screenshot of the current interface in Excel (As above, I'd like to keep the access version as close to this as I can)

In invoice 1 I've turned on CIS & Part Payment to show the hidden elements.

This is the main form for inputting a new job & managing an existing one, which is about 20% of the spreadsheet as a whole. But its where I've started as I believe the rest will be done by creating reports from this info.

I assume this is a user form in your Excel solution?

What matters to table design in a relational database application is the tables.

 

[Gasman]

But I assumed (maybe I was wrong) that if I used multiple tables, a main 1 to hold job number info (client site address etc) and 1 for each of the 25 Invoices that would help to overcome the 255 limit. hence my original question.

The Invoice tables would each be about 25 fields. (25 fields x 25 invoices is 625 fields in total)

The suggestion here seems to be the 25 fields, plus Job Number & plus Invoice Number so they are linkable, so 26. But that would generate a lot of rows. Does that matter in reality?

The current 1300 rows would probably jump to about 7000 in the invoices table (not every job uses all 25 Invoices, many only 1 or 2). would that cause it to run really slow?

Yes, you assumed wrong.

 

[MajP]

You have the 3 invoices. Are they different in some way or could you have more than three? I am sure the UI can look very similar. Not important now. If the tables are designed correctly anything is possible. If not designed correctly then everything becomes a big workaround.

Without knowing the real data structure and business, looks like you would have main form with a few different subforms related to different tables (to be determined)
tblJobs
tblIInvoices
tblInvoiceDetails
tblSites

 

[dibblermail]

You have the 3 invoices. Are they different in some way or could you have more than three? I am sure the UI can look very similar. Not important now. If the tables are designed correctly anything is possible. If not designed correctly then everything becomes a big workaround.

Without knowing the real data structure and business, looks like you would have main form with a few different subforms related to different tables (to be determined)
tblJobs
tblIInvoices
tblInvoiceDetails
tblSites

theres a scroll bar on the right... there are 25 Invoices. being able to start with 1 and add them in would be better though.

 

[plog]

Add me to the huge list of people saying your focus is wrong.

Put forms out of your mind. You need to get your tables correct. I advise you read up on normalization (https://en.m.wikipedia.org/wiki/Database_normalization). Google a few tutorials. After you work through them apply what you've learned to your data. Then post a skeleton database of what you think your tables and fields should be back to this post. The we can help get your tables and fields correct.

 

[LarryE]

Just taking a wild guess at what you want to track and how your business operates, I sounds like:

1. We have multiple customers
2. Each customer may have multiple customer sites
3. Each customer site may have multiple jobs
4. Each job is completed over multiple dates and requires multiple people, time ranges and equipment
5. Each job results in an invoice to the customer

Does this sound correct or at least close?
ACCESS projects ALWAYS follow the business workflow. You build your tables and table relationships around that business workflow.

Please describe what your business is and what data you wish to keep track of. Without that understanding, we cannot help you design your application.

 

[Uncle Gizmo]

Guide: Transitioning from Excel to Microsoft Access for Invoicing Systems​

1. Analyze and Understand the Current Excel System​

• Inventory of Sheets and Data:
  • List all worksheets and their purposes (e.g., Invoices, Job Details).
  • Identify relationships between sheets (e.g., linking invoices to jobs).
• Understand VBA Logic:
  • Document all VBA macros, automated calculations, and transformations applied in Excel.
  • Determine which functions are essential and which are cosmetic.
• Review Data Structure:
  • Identify redundant data, repeated fields, and opportunities for normalization.
  • Check for existing unique identifiers like Job Numbers.

---

2. Plan the Access Database Structure​

• Normalization:
  • Break down the Excel data into relational tables (e.g., Jobs, Invoices, Invoice Line Items).
  • Follow database normalization principles (1NF, 2NF, 3NF).
• Define Table Relationships:
  • Establish primary and foreign key relationships (e.g., JobID linking Jobs to Invoices).
• Design Metadata Fields:
  • Include fields for audit trails, timestamps, or user edits if required.

---

3. Prepare the Excel Data for Import​

• Add Unique Identifiers:
  • Ensure all rows have unique keys (e.g., RowID, JobNumber).
• Clean Data:
  • Remove inconsistencies, empty rows, and duplicate data.
• Consolidate Sheets:
  • Combine similar sheets (e.g., multiple invoice sheets) into a single unified dataset with a differentiating field.

---

4. Import Data into Access​

• Link or Import Excel Files:
  • Use Access's External Data Wizard to link or import raw Excel data.
• Stage the Data:
  • Load data into temporary staging tables for cleanup and normalization.
• Validate Imported Data:
  • Verify that data types and formats are correctly handled (e.g., dates, currency).

---

5. Implement Business Logic in Access​

• Recreate VBA Logic:
  • Convert Excel VBA macros into Access queries or VBA modules.
• Design Queries:
  • Use SQL queries to automate calculations, data transformations, and reports.
• Create Forms and Reports:
  • Design user-friendly forms for data entry (e.g., invoice creation).
  • Build reports that replicate or improve upon Excel’s output.

---

6. Optimize and Test​

• Index Key Fields:
  • Improve query performance by indexing fields like JobID, InvoiceID.
• Test Functionality:
  • Validate the database’s functionality against the original Excel system.
• Get Feedback from Users:
  • Ensure that the new system meets the users’ operational and reporting needs.

---

7. Train Users and Transition​

• Train Stakeholders:
  • Provide training on using Access forms and reports.
• Pilot the System:
  • Run the new system alongside Excel to catch issues.
• Fully Transition:
  • Migrate all operations to Access and decommission the Excel system.

---

8. Document the Process​

• Create User Manuals:
  • Document database structure, workflows, and user instructions.
• Maintain a Change Log:
  • Record updates and improvements to the system.

---

Attribution​

This guide was created by ChatGPT in collaboration with Uncle Gizmo, informed by a detailed discussion on transitioning Excel invoicing systems to Access.

Acknowledgment of Contributors​

This guide is based on a discussion thread involving several contributors who shared their expertise on transitioning from Excel to Microsoft Access. Their insights and advice formed the foundation for the process outlined above. Special thanks to:

1. dibblermail - For initiating the discussion and providing a detailed overview of their current Excel system and challenges.
2. MajP - For emphasizing normalization principles and relational database design.
3. Uncle Gizmo - For sharing additional resources and practical advice for transitioning to Access.
4. Gasman - For highlighting the importance of understanding normalization and sharing resources for debugging and data randomization.
5. GPGeorge (George Hepworth) - For offering detailed insights into database structure and user considerations.
6. CJ_London - For discussing table limitations and strategies for managing large datasets.
7. arnelgp - For offering practical help and advice on sharing and anonymizing data.
8. Pat Hartman - For reinforcing the importance of a sound foundation and illustrating the benefits of relational databases.