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

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.

 

[Pat Hartman]

@dibblermail Welcome, you are already in good hands. You are going to be outright amazed at how using a relational database can simplify creating Excel models like invoicing. You don't need 25 of anything You just need 1 and you tell it which of the "25" you want it to show. And if you need 26 for this client, you don't need to make a single change. That is the magic of a relational database but it requires a sound foundation. As you post more details, we can work you through the process.

 

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