Solved

Subform Link field

Posted on 2016-10-16
13
33 Views
Last Modified: 2016-10-17
I have a main form (Table "A") with a command button that opens form "Sets"(Table "Sets"). When a new record is created in the main form I want to be sure that the "Sets" form has a corresponding and linked record even when the form has not been opened.

The linking field between the forms is ItemNumber. So in the past I have used

"If IsNull(Form![Sets].ItemNumber) Then "Sets" form!Sets.ItemNumber =MainForm.ItemNumber"

I am sure that this will not work if the form is closed but I don't want the user to have to open the form or program an open and close procedure.

Is there something in access like ""Always create a record in the "Sets" table where Table "A" has a record

Or would that happen naturally if the tables are linked on a 1 to 1 basis
0
Comment
Question by:DatabaseDek
  • 6
  • 5
  • 2
13 Comments
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41845614
If you put Sets as a subform and link it to TableA...everytime you have a new record on form(tableA) you will get a corresponding record on Sets...
0
 
LVL 19
ID: 41845689
if the tables are 1:1 and you always create a record in the second table, why not just put all the fields in table "A"?
0
 

Author Comment

by:DatabaseDek
ID: 41845822
Crystal my life is never that simple. I already have 210 fields in Table "A" It cannot be any more normalised. I have to close and open because I am bumping up against the 2048 max links with Access. Often the Sets form is just not required. But if I don't have a number in the linked field it causes problems elsewhere.

It just seems daft that you have to force a link.

Hi John
The link in a subform only happens when data is put on the subform (as far as I am aware). As I say I don't want to force an entry because any sensible user will ask why.
0
 
LVL 19
ID: 41845824
210 fields! A well designed database will usually have less than 20 fields in a table, with some exceptions such as Orders that record point-in-time values.
What is the database for?
0
 

Author Comment

by:DatabaseDek
ID: 41845918
The specification of fire doors
Doors.pdf
0
 
LVL 19
ID: 41845927
I don't see a couple hundred fields ...

Each of the panels should be in a related table with dimensions relative to your origin.  However, you probably make other types of doors and even fire doors might look different.  It would be best to see what is in common and break down information so that you can set up tables to define each door type and then equations to render it since people might get a door that is 8' tall or 7' tall or 3' wide or 2'10" wide, etc

You should have several tables.

What is the purpose of the database? to store specs for drawing? for making? for cutting pieces?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:DatabaseDek
ID: 41846478
Hi Crystal

The purpose is all of that. A lot of information is hidden and yet more are in invisible subforms. Here is the input form. I know that it is complex but I will not have my users constantly opening and closing forms. Almost Everything here that describes the product is visible to the operator. So if a customer calls with an inquiry it can be seen immediately.  Also when something is changed I.e. the door width everything related to that has to change automatically so I would have to open a form do a separate calculation and paste it into the table. Everything here is calculated by the underlying query or background code
Capture13.PNG
0
 
LVL 13

Assisted Solution

by:John Tsioumpris
John Tsioumpris earned 150 total points
ID: 41846515
Thats the same issue i am facing since i have inherited a similar case...
Normalization is Normalization and no matter what you have to change the Structure of your Tables....
A long time ago i have also tried to change it but i hit a brick wall....NOT my fault and lets say there is a lot more....
You have to think totally differently...and think on the basis of element - attribute-value...
IT would make a tremendous impact on your database when everything just gets positioned as they should be.....
But probably this is another brick wall....
By the way i am also on the same job....and i mean the same...:)
0
 
LVL 19
ID: 41846801
while I can understand you wanting to see everything on one form ... did you know that a form can be ON another form? It is called a subform when it is used for that purpose.

As an example, you have provisioned for up to 6 vision panels ... what if they want 8? Do you plan to add more fields? I see that you should have a related table for panels.  It could be designed as a continuous form and show however few, or however many, panels there are.  Right away, this would get rid of a bunch of fields in table "A" and move them to where they belong.

here is a short book with lots of screenshots you can read:

Access Basics
http://www.AccessMVP.com/strive4peace

the basics don't change
0
 

Author Comment

by:DatabaseDek
ID: 41846840
The vision panel detail is actually a subform without borders and there are 6 more not visable. I have never seen a doors with more than 6 vision panels in each door.

Hi Crystal

The reason I don't use a continuous form is because if you look at the first numeric box it is entitled DFT (Down From Top of door. The starting point for the first vision panel) the next to the right is BVP (Between the first vision panel and the next) If I had a continuous form it would read DFT which means the user would have to calculate the new distance from the top including the first vision panel and the gap between the two. When you get to the sixth VP that's not so easy. When the information received from THEIR customer would read "Between Vision Panel". My philosophy has always been "make Access work hard, not the customer"

Hi John

Is your problem a product orientated DB as well?

To fully describe a door set you need the equivalent to 500+ fields. I started this 10 years ago and have to accept some bad practice. But I thought a database was supposed to have one table per subject. 500 fields is one subject. I am sure you are both right but I need a simple answer to the question can it be done do you think?
0
 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 350 total points
ID: 41846867
> "When you get to the sixth VP that's not so easy."

The VPs could be numbered. Perhaps you want to enter relative distances ... and then behind the scenes they could be added up when required.  Just giving you ideas as you know your business much better than me; I am giving you my best guesses because I know Access.

> "thought a database was supposed to have one table per subject"

one table per noun, and items need to be stored distinctly. A door is a big subject. It is composed of many other parts.  Each of those part types might need their own tables which have records related to a specific type of door.

It can be done, of course. You have already given this a lot of thought ... now it is just a matter of changing your perspective
0
 

Author Closing Comment

by:DatabaseDek
ID: 41846981
My thanks to you both.

John How are you remedying the situation.
0
 
LVL 19
ID: 41847003
you're welcome ~ happy to help
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now