Sub form linkage problem

[NES]

I have a primary form whose data source is a "Master Table Query" which
contains 4 tables all properly linked I believe.

I have a sub form whose data source is a "Detail Table" which is not
included in the above mentioned query. On the occasions that I have tried to
include it, I get SQL error problems.

The sub form seems to be following the main form records ok. but even though
the Master Table Query and the Detail Table share common names, field types
and sizes, they do not share that. The record number from the Master is
appearing in the Detail table, but other information is missing.

Now... I understand that when looking at the data tab in the sub form (at
least when in design view) should be showing Master and Child links. In fact,
no such links occur, or even have a PLACE to appear. It's like the sub form
isn't linked.

Please tell me what I'm doing wrong. Many many thanks for some help with
this. I have spent about a week trying to figure this out.

 

[Jeanette Cunningham]

Norm,
You can type in the name of the link field for the Master and Child links
yourself. It is usually the same field with the same name for both Master
and Child. Make sure you spell it correctly, use square brackets if there is
a space in the name. If there is still a problem with the subform, you may
need to check the relationships between the tables in the Master query and
the Detail table and perhaps correct the relationships or change the master
query.

Jeanette Cunningham

 

[NES]

Jeanette, thank you for replying. I draw your attention to the following in
my original message..."no such links occur, or even have a PLACE to appear."

I emphasized PLACE because it is not displaying anyplace where I can type in
the links. That's what is baffling me. Can you imagine what might be
happening? I'm at a loss.

--
Norm Shimmel
Butler, PA

Norm,
You can type in the name of the link field for the Master and Child links
yourself. It is usually the same field with the same name for both Master
and Child. Make sure you spell it correctly, use square brackets if there is
a space in the name. If there is still a problem with the subform, you may
need to check the relationships between the tables in the Master query and
the Detail table and perhaps correct the relationships or change the master
query.

Jeanette Cunningham

 

[Jeanette Cunningham]

That's it for me, someone else might have seen this before.

Jeanette

Jeanette, thank you for replying. I draw your attention to the following
in
my original message..."no such links occur, or even have a PLACE to
appear."

I emphasized PLACE because it is not displaying anyplace where I can type
in
the links. That's what is baffling me. Can you imagine what might be
happening? I'm at a loss.

 

[NES]

Nevertheless, I thank you for the effort. I would make a good beta testor
since I find all the problems in software. But this is probably my own fault.
<s>

 

[Rick Brandt]

I have a primary form whose data source is a "Master Table Query"
which contains 4 tables all properly linked I believe.

I have a sub form whose data source is a "Detail Table" which is not
included in the above mentioned query. On the occasions that I have
tried to include it, I get SQL error problems.

The sub form seems to be following the main form records ok. but even
though the Master Table Query and the Detail Table share common
names, field types and sizes, they do not share that. The record
number from the Master is appearing in the Detail table, but other
information is missing.

Now... I understand that when looking at the data tab in the sub form
(at least when in design view) should be showing Master and Child
links. In fact, no such links occur, or even have a PLACE to appear.
It's like the sub form isn't linked.

Please tell me what I'm doing wrong. Many many thanks for some help
with this. I have spent about a week trying to figure this out.

Ever since Access 2000 Microsoft has made it a bit difficult to see the
properties of the subform control. If you just click anywhere on the subform
while in design view of the main form you will see the properties of the form
contained within the subform control. That's not what you want. You want the
properties of the subform control itself. For that you have to click on the
very edge of the subform control. Either that or select the control name from
the drop-down list in the upper left.

 

[NES]

Yesssssss! That was it. Thank you so much. Indeed, it shows the linkage as
it should be. I suspected it was so, but couldn't confirm it.

Now the questions is why data in identically-named fields in the primary
form (control is a query) is not being sent to the sub form (control is a
table). The linkages in the query appear to be correct. but does not contain
the table mentioned above. I'm expecting the form to do that.

BTW, moments ago I noticed that in another form, where I'm entereing a total
cost and number of items, and have created a calculated field (only for
edification) that divides the total_cost/items gives correct values for most
figures. However, by happenstance I entered 10 items at 17.53, and the result
was 1.753000069.

With all the problems I'm having (clearly I'm in way over my head) this was
the icing on the cake. If I were not doing this for charity, I'd have given
up.

 

[Rick Brandt]

Yesssssss! That was it. Thank you so much. Indeed, it shows the
linkage as it should be. I suspected it was so, but couldn't confirm
it.

Now the questions is why data in identically-named fields in the
primary form (control is a query) is not being sent to the sub form
(control is a table). The linkages in the query appear to be
correct. but does not contain the table mentioned above. I'm
expecting the form to do that.

Not sure I follow you. The values that are automatically pushed to the subform
when you create new records in the subform are those listed in the MasterLink
and ChildLink properties. Any relationships or joins you might have in tables
or queries is irrevelant.

Are you saying that the values in the MasterLink fields are NOT being pushed to
the subform?

 

[NES]

Rick, I'm not sure I follow you now. But I'll try to explain a bit better.
I am working with a DB to handle prescription medication and perhaps
controlled substances. The form that I use for prescriptions holds:
Prescription #, Dr., Patient, NDC number (National Drug Code), Medication
discription, Instructions, Fill size, and Number of refills.
The sub form is for dispensing and tracking of fills and refills, and for
getting reports. So it contains: Prescription #, NDC number, Date(), refill
#, Qty.Dispensed and the patient # .

The linked fields are the prescription number in each form.

The Prescription # does appear in the sub form when I begin entering the
Qty.Dispensed, but as I tab to the NDC field (which is there for
verification) nothing happens. It does not get filled. Ditto the Patient #.

When I examine the Prescription Master table, all the info including the NDC
# is there. But the Detail table (sub form linked) does not show anything
from the master form except the Prescription #. It shows the date, refill #,
qty.dispensed -- all of which I fill in on the sub form. But the two
additional items I need to have pushed to the the subform (Patient # and NDC
#) do not appear in the detail table.

I hope that clarifies it a bit.

 

[Rick Brandt]

Rick, I'm not sure I follow you now. But I'll try to explain a bit
better.
I am working with a DB to handle prescription medication and perhaps
controlled substances. The form that I use for prescriptions holds:
Prescription #, Dr., Patient, NDC number (National Drug Code),
Medication discription, Instructions, Fill size, and Number of
refills.
The sub form is for dispensing and tracking of fills and refills, and
for getting reports. So it contains: Prescription #, NDC number,
Date(), refill #, Qty.Dispensed and the patient # .

The linked fields are the prescription number in each form.

The Prescription # does appear in the sub form when I begin entering
the Qty.Dispensed, but as I tab to the NDC field (which is there for
verification) nothing happens. It does not get filled. Ditto the
Patient #.

If you want them automatically populated with values from the main form then you
have to include them in the MasterLink and ChildLink properties. That's just how
forms and subforms work.

When I examine the Prescription Master table, all the info including
the NDC # is there. But the Detail table (sub form linked) does not
show anything from the master form except the Prescription #. It
shows the date, refill #, qty.dispensed -- all of which I fill in on
the sub form. But the two additional items I need to have pushed to
the the subform (Patient # and NDC #) do not appear in the detail
table.

I hope that clarifies it a bit.

You design is flawed actually. You should ONLY be storing in your child table
the prescription number and data about the fill/refill. All other data about
the prescription is stored in the main table and that is the only place it
should be stored.

 

[NES]

Thanks Rick. I knew the criticism was coming. Part of the choice was my
insecurity about where the data is going. That is, how reliable is my own
work. I have no beta testors, and the people for whom I'm doing this are
rightfully going to assume it's all "connected" properly. It's reassuring to
note the data flowing to the correct tables or forms. Removing the duplicate
data will be a snap once I verify that I actually have the tables "connected".

When you refer to the "properties" of the master and child links, I'm not
sure what you mean, nor where would I find them. I have not seen a list of
fields that are to be transferred, per se. I assumed that the linkage of the
two fields would allow data to flow from and to identically labeled fields.

What is really aggravating about all this is that I've done the same basic
stuff in Access 97 two years ago and didn't have these problems. So any
pointers would be gratefully accepted.

Thanks much.