Link Child / Master fields -- QUESTION

[kealaz]

Hello,

What is the difference between Link Child Fields and Link Master Fields?
I'm trying to get a subform to work, and I might have the wrong values in
these two fields, but don't know which to change?

Thank you.

 

[John W. Vinson]

Hello,

What is the difference between Link Child Fields and Link Master Fields?
I'm trying to get a subform to work, and I might have the wrong values in
these two fields, but don't know which to change?

Thank you.

The Link Master Field (or fields) needs to be the name of a field or a form
control on the mainform.

The Link Child Field(s) must be the name of a table or query field in the
Subform's Recordsource query.

These might have the same names depending on how your forms and tables are set
up.

What are the Recordsources of the main and subforms? What do you have in the
properties? What symptoms are you seeing?

 

[kealaz]

Hi John,


THANK YOU SO MUCH for replying to my post. I REALLY appreciate all the help
I get from these forums!!!


Main form [frmPO_ISSUE]
Record Source: tblPOHIST

Sub form [frmORDER_DETAILS]
Record Source: tblBUYHIST


The record sources [ tblPOHIST and tblBUYHIST ] are the tables that I want
the information to go to once the form is filled out (i.e. the P.O. is
issued).

The information to populate parts of BOTH forms is coming from another table
[tblBUY] which is a temp table that has parts to be purchased.

tblBUY
VENDORNAME
PART_NO
MANUF
MANUF_PN
QTY_ORDER
PROJECT

On my main form I have a combo box [VENDORNAME] which is showing me the
values in the field VENDORNAME from my table [tblBUY]. This is working
successfully! Once I make a selection in this combo box, I would like my
subform to populate with those parts [PART_NO] which have the same value in
the field VENDORNAME as was selected in the combo box [VENDORNAME]. In other
words, once I select who I'm cutting the P.O. to, I would like those parts
that I want to buy from that vendor to show up in my subform.

I have been reading all day about forms and sub-forms, and I think the way I
have them linked may be part of the problem.

Thank you very much for any help you can give me on this.

 

[John W. Vinson]

Hi John,


THANK YOU SO MUCH for replying to my post. I REALLY appreciate all the help
I get from these forums!!!


Main form [frmPO_ISSUE]
Record Source: tblPOHIST

Sub form [frmORDER_DETAILS]
Record Source: tblBUYHIST


The record sources [ tblPOHIST and tblBUYHIST ] are the tables that I want
the information to go to once the form is filled out (i.e. the P.O. is
issued).

The information to populate parts of BOTH forms is coming from another table
[tblBUY] which is a temp table that has parts to be purchased.

tblBUY
VENDORNAME
PART_NO
MANUF
MANUF_PN
QTY_ORDER
PROJECT

On my main form I have a combo box [VENDORNAME] which is showing me the
values in the field VENDORNAME from my table [tblBUY]. This is working
successfully! Once I make a selection in this combo box, I would like my
subform to populate with those parts [PART_NO] which have the same value in
the field VENDORNAME as was selected in the combo box [VENDORNAME]. In other
words, once I select who I'm cutting the P.O. to, I would like those parts
that I want to buy from that vendor to show up in my subform.

I have been reading all day about forms and sub-forms, and I think the way I
have them linked may be part of the problem.

Thank you very much for any help you can give me on this.

Well, the subform link fields will *show existing records* from tblBUYHIST
with the selected VENDORNAME value. It sounds like there *are* no existing
values, and that you want to somehow get information from tblBUY into it. Just
setting a Master/Child Link Field will of course not do that.

How is tblBUY involved? How does information from it get into tblPOHIST? Will
this form be used to manually enter data at all, or is all of the data coming
from this temp table? For that matter, how does the temp table get filled out?

 

[kealaz]

Well, the subform link fields will *show existing records* from
Correct -- There are no existing values (right now) and I do want to
*somehow* get information from tblBUY into is.


I start out by requesting parts to be purchased.

On a form [frmMRTODO - Material Request To Do] I enter the part number, qty,
project and who to deliver the part to once it comes in the door. This form
writes information to a temp table [tblPOTODO].

Once I have parts in my table [tblPOTODO] then I "process" them. Each part
can have up to 3 manuf associated with it and each manuf may have many
vendors that carry this part. I have a form [frmPOTODO_process]. On this
form, for each part, I choose which manuf we are going to go with for this
buy, and then which vendor we will purchase this part from. That information
then gets written to another temp table [tblBUY] and then that part is
deleted from [tblPOTODO]. The parts in tblBUY are now ready to be purchased
and I need to be able to cut a P.O. for them.

I have most of the P.O. working. My combo box VENDORNAME is only showing me
those vendors that are in tblBUY. I have text boxes that are getting the
address, phone number, etc. from another table [tblVENDOR]. If I enter
information manually, I have confirmed that the form and subform is writing
the info to the correct tables. [tblPOHIST and tblBUYHIST respectively]. I
even have all the math working out and the form is correctly calculating
sales tax, freight, subtotal, total, etc...

I am stuck on how to get my sub form to get the information from the table
[tblBUY].

Please point me in the right direction if you can. Thank you SO MUCH!!! for
all your help.

 

[John W. Vinson]

Correct -- There are no existing values (right now) and I do want to
*somehow* get information from tblBUY into is.

In that case no form would be needed at all (for that purpose). Instead you'ld
run an Append query.

I start out by requesting parts to be purchased.

On a form [frmMRTODO - Material Request To Do] I enter the part number, qty,
project and who to deliver the part to once it comes in the door. This form
writes information to a temp table [tblPOTODO].

At the risk of overemphasizing... well, no, it can't be overemphasized.

The tables ARE FUNDAMENTAL. If you're designing tables to fit your forms,
you'll be in all sorts of trouble!!! Data is stored in tables; data is moved
between tables using queries, if need be; forms *are secondary*. I fear you
may be designing the workflow around forms, and then adding tables almost as
an afterthought.

Once I have parts in my table [tblPOTODO] then I "process" them. Each part
can have up to 3 manuf associated with it and each manuf may have many
vendors that carry this part. I have a form [frmPOTODO_process]. On this
form, for each part, I choose which manuf we are going to go with for this
buy, and then which vendor we will purchase this part from. That information
then gets written to another temp table [tblBUY] and then that part is
deleted from [tblPOTODO]. The parts in tblBUY are now ready to be purchased
and I need to be able to cut a P.O. for them.

So you're going from one temp table, to another temp table, and finally to a
"real" table?

Seriously: WHY? Why not work in your real tables, using forms?

I have most of the P.O. working. My combo box VENDORNAME is only showing me
those vendors that are in tblBUY. I have text boxes that are getting the
address, phone number, etc. from another table [tblVENDOR]. If I enter
information manually, I have confirmed that the form and subform is writing
the info to the correct tables. [tblPOHIST and tblBUYHIST respectively]. I
even have all the math working out and the form is correctly calculating
sales tax, freight, subtotal, total, etc...

I am stuck on how to get my sub form to get the information from the table
[tblBUY].

That's because:

Your Subform SHOULD NOT GET ANY INFORMATION AT ALL. That's because *forms
don't contain information*.

Your *table* tblBUYHIST needs to get information.

That information might or might not need to be on the form at all; if you want
to see it, you could use a report instead, or you could just see it on a form,
or if you want to edit it after it's in the table you'ld use a form, but just
getting the data from one table into another table doesn't.

Please point me in the right direction if you can. Thank you SO MUCH!!! for
all your help.

It'd help if you post the names, primary keys, relationships and significance
of your main tables.

 

[kealaz]

John,

Thank you very much for your time and attention to my questions. I can see
that I'm going about this all wrong. Yes, I understand that there real
problems with my design. I have no clue how to fix them. I am converting
from a dbase IV database that was designed about 20 years ago. I'm sure
there were different considerations then. I'm doing the best I can and
gleening help where I can find it.

Again, thank you for your time. I appreciate all of your feedback.

 

[John W. Vinson]

John,

Thank you very much for your time and attention to my questions. I can see
that I'm going about this all wrong. Yes, I understand that there real
problems with my design. I have no clue how to fix them. I am converting
from a dbase IV database that was designed about 20 years ago. I'm sure
there were different considerations then. I'm doing the best I can and
gleening help where I can find it.

Again, thank you for your time. I appreciate all of your feedback.

We'll all be glad to help, and I'm sorry if I came across as attacking (or
worse, condescending); I know I do come across that way at times, but I assure
you that's not my intent.

Sometimes it's best when converting such and old app to newer technology to
stop, step back a bit, and think "Now how would I do this if I were starting
from scratch today". What are the Entities (real-life people, things, or
events) of importance? How are they related? What information do I need to
record about each of them?

It may be worth the effort to restructure the tables into a properly
normalized set. It would be more than a bit unusual for an 1980's dBase
application to be well normalized; the software didn't encourage it. It should
be possible to import the *DATA* from your existing app into a new structure,
without necessarily being enslaved to the old structure!

Feel free to post a description of what the application does, the nature of
the data it uses, and the current structure of your tables. This might
actually be easier than you're visualizing, if you spread your net a bit
wider!