UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using 'canshrink' For Subforms On Form, Access 2016    
 
   
jonanem
post Jan 27 2020, 08:58 AM
Post#1



Posts: 7
Joined: 14-October 19



Hello. I am trying to design a database for my son’s sign making business. I’ve managed to create vertical tabs (in a very ponderous way) on frmEnquiry and want to use the Order button on the form to open frmOrder showing only subforms for items ordered with no gaps for “empty” subforms. For instance, someone may order only a shop fascia and some business cards but frmOrder shows all the subforms including empty ones. I have tried shrinking the controls of each subform so that only the subforms with new records line up underneath each other (with no gaps) but it hasn’t worked- will this only work in print preview? I wondered about using ‘select case’ but am not sure how or where to write this in code. Any help would be most appreciated- thanks.Attached File  SampleDB2.zip ( 432.22K )Number of downloads: 4
Go to the top of the page
 
Larry Larsen
post Jan 27 2020, 10:02 AM
Post#2


UA Editor + Utterly Certified
Posts: 24,435
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
welcome2UA.gif

All way's interested in viewing db examples so I had a look, the first "port" of call is always the structure and relationships that are available to see..

Having spread out the amount of linked table, I see you have (4) tables that simple represent different types of vehicles.

Here I would suggest we condense these to (1), most of all the fields across all four tables are duplicated..

I would add a new table that holds and identifies the different types of vehicles..

With regards to the subform issue have you considered using a Tab Control that holds each subform.

You then have the option to hide & shows tabs... (just a thought)

thumbup.gif

Attached File(s)
Attached File  2020_01_27_14_37_31.jpg ( 174.97K )Number of downloads: 4
 

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
GroverParkGeorge
post Jan 27 2020, 11:49 AM
Post#3


UA Admin
Posts: 36,754
Joined: 20-June 02
From: Newcastle, WA


As Larry pointed out, you're heading down a long, dark, dangerous alley with this table design. Best to avoid it now. Take the well lighted main street instead.

Correcting the problem means understanding the basics of Relational Database Design, referred to as Normalization.

You sell PRODUCTS. Each table in your database that refers to a product, such as "SiteBoard", or "BusinessCards", should be ONE RECORD in a Product table. Start there.

Invest some time in learning how Relational Database Design works, come back and redesign these tables. Your problem with multiple subforms will be greatly reduced if not eliminated.


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Jan 27 2020, 11:51 AM
Post#4


UA Admin
Posts: 36,754
Joined: 20-June 02
From: Newcastle, WA


By the way, you may also want to rethink the wisdom of using Mult-Value Fields, typically shortened to MVF. They tend to be more trouble than they are worth. Most seasoned Access developers avoid them. They can be used, but the cost of doing so is fairly high in the long run.

Attached File  MVFDoom.png ( 8.4K )Number of downloads: 0

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
jonanem
post Jan 27 2020, 03:05 PM
Post#5



Posts: 7
Joined: 14-October 19



Thanks for your speedy response. I suppose I thought the idea of having one table containing all the 'objects' was that there would be an awful lot of fields (even allowing for cutting out the various duplicated fields), but I will give it a go, so thanks for that. As regards tab controls I was concerned that the idea of (at least) 17 tabs and some with long field names would be difficult to negotiate- I'm guessing it would use about 3 or 4 rows of tabs. I will certainly investigate rationalizing the tables and their repeated fields- I guess when I first started investigating Access that I thought that each different form had to have a 'matching' table rather than you could pick and choose any fields for any form from 'one big table'!
Thanks again
Go to the top of the page
 
jonanem
post Jan 27 2020, 03:07 PM
Post#6



Posts: 7
Joined: 14-October 19



Thanks for your response. I will follow yours and Larry's suggestion of rationalizing and amalgamating my tables and sorting out the multivalue fields- onwards and upwards and thanks again
Go to the top of the page
 
June7
post Jan 27 2020, 03:17 PM
Post#7



Posts: 1,220
Joined: 25-January 16
From: The Great Land


I haven't looked at db to determine if you have committed these 'fouls', just throwing out warnings:

Don't use spaces nor punctuation/special characters in naming convention - you seem to have done well in that regard.

Don't build lookup fields in tables.

Don't do field formatting in tables.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
BruceM
post Jan 28 2020, 11:26 AM
Post#8


UtterAccess VIP
Posts: 8,026
Joined: 24-May 10
From: Downeast Maine


Another point is that forms should be used for data entry and editing (and viewing, but not printing). For printing, use reports.

It is possible to print forms, but there is rarely a good reason for doing so. Can Grow and Can Shrink apply only to printing. They are available on forms in case you decide to print one, but they have no effect on the onscreen display other than in print preview.
Go to the top of the page
 
jonanem
post Jan 29 2020, 10:03 AM
Post#9



Posts: 7
Joined: 14-October 19



Thank you for your reply. Does that mean that if I have an order form with, for instance, sfrmA, sfrmB,sfrmC, sfrmD stacked underneath each other and for example if for record 1that sfrmA and sfrmC are empty I cannot easily move up sfrmB and sfrmD to 'fill in' the empty space? I know I can use 'move' and put in suitable parameters to do this but then if record 2 has blank sfrmB and sfrmC that would need a different set of parameters. I may, in the end, have 17 subforms on the same order form and if my math is correct there would be 2 ^17 (that's 131072) possible combinations. Anyway, thanks for your help- it may be that I have to use a report
Go to the top of the page
 
jonanem
post Jan 29 2020, 10:05 AM
Post#10



Posts: 7
Joined: 14-October 19



Thanks for taking the trouble to reply- I feared that was the case but hoped that it wasn't. Thanks again
Go to the top of the page
 
jonanem
post Jan 29 2020, 10:05 AM
Post#11



Posts: 7
Joined: 14-October 19



Thanks for your support. Onwards and upwards!
Go to the top of the page
 
cheekybuddha
post Jan 29 2020, 10:29 AM
Post#12


UtterAccess Moderator
Posts: 12,262
Joined: 6-December 03
From: Telegraph Hill


Perhaps consider a tab control to house all the subforms?

By the time you have that many subforms, you might also consider having just one subform control that you load on demand, otherwise performance may suffer.

>> I thought the idea of having one table containing all the 'objects' was that there would be an awful lot of fields (even allowing for cutting out the various duplicated fields) <<

The advice against using Lookup fields is not the same as telling you not to use Lookups! Lookup fields are evil, lookups to related tables are good!

Do not go adding a field for every option to your table. You place the options in a 'lookup' table, and then join to your main table via primary key to foreign key for a one-tomany relationship, or as it would appear in this scenario, you would require a junction table for your many-to-many relationship.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Larry Larsen
post Jan 29 2020, 10:41 AM
Post#13


UA Editor + Utterly Certified
Posts: 24,435
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
As we all have mentioned using tab controls this article may give some direction..
Use Late Binding for Subforms on Tab Pages

HTH's
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
GroverParkGeorge
post Jan 29 2020, 10:45 AM
Post#14


UA Admin
Posts: 36,754
Joined: 20-June 02
From: Newcastle, WA


It's probably a good idea at this point to put everything on pause and revisit the fundamentals of Relational Database Application design.

One of the most common problems encountered by people new to Access is that they think in terms of their experience with Excel. In Excel, if you need to track an additional data point, your throw one more column out there on the right side of the spreadsheet for it. That's the only option for Excel, but it's a very dangerous approach in a Relational Database Application, like the kind you build with Access.

Reading through this thread we see a number of problems related to that unfortunate approach. Multiple fields, multiple subforms, complex code and SQL to manage it all and no end in sight.

Let's step away from forms for the time being and get back to the basics of solid Relational Database Application design.

That means TABLES. That means Relationships between tables.

Here are some good starter articles. Here is a good blog series discussing the dangers of "spreadsheet style" tables. Note that Roger has had some problems with his blog host and is slowly recovering some of the images from his posts. It's still a good resource and will return to its full value soon, I hope.

It's not simply a matter of creating multiple tables from one big fat table. It's about understanding the principles underlying that process.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th February 2020 - 05:38 AM