Values onto a Subform

[melwester]

I had asked this question last week. And Marsh answered and it worked last
week. Only now it's NOT working. So here is the question and details again
as I may have had done something to screw it up:

I have an IDnumber on my main form that I want to link the info to my
subforms that I have built under the Tab Control. I was using Popups last
week and found out how to build subforms under the Tab control. Much more
nicer this way.

Someone had recommended that I use subdatasheets to link my data from the
main table. (This I did today). As in the table of (CTANotes) I sometimes
have 2 or more records with the same IDNumber with different dates attached.
What this table is capturing is all the data per doctor as to what was done
and date of occurance. Each doctor has an separate IDNumber. This same ID
number is used on the main form that shows the address, contact person. Right
now on the subdatasheet the IDNumber is not there as this sheet is linked to
the main Table.

For example of the Notes Table:
ID Comments Date
1034 Had checkup 6/3/05
1034 Went in for tests 5/30/04
1035 Please work 7/5/05

I get ALL records in the CTANotes table. I cannot group each line as each
row has a different date. I've tried continuous form and Single form but that
shows the entire table or no data at all. I don't want this.

I've also done what Marsh suggested with the Child/Master links. If I put
in both IDNumber for child/master I get just the headers on the Tab/subform
and nothing else. If I erase out the child but leave in the Master link I
get ALL the files from CTANotes. I Don't want this either. I just want
what was listed on the main form such as 1035 or 1034 with all of it's lines
associated with 1035 or 1034.

How do I get more then 1 line to show on the subform per IDNumber?

 

[John Vinson]

I've also done what Marsh suggested with the Child/Master links. If I put
in both IDNumber for child/master I get just the headers on the Tab/subform
and nothing else. If I erase out the child but leave in the Master link I
get ALL the files from CTANotes. I Don't want this either. I just want
what was listed on the main form such as 1035 or 1034 with all of it's lines
associated with 1035 or 1034.

How do I get more then 1 line to show on the subform per IDNumber?

You don't need Subdatasheets - in fact, they cause a major drag on
performance.

Your main form should be based on the main table; the subform should
be based on the Notes table. You don't need a query joining the two
tables for either form.

Using the IDNumber for the Master/Child Link Field should display only
the notes for the currently displayed mainform record. I'd use a
Single form for the mainform, and a Continuous form for the subform;
you may need to move textboxes up to the top of the detail area, and
snug the bottom of the detail area up to the bottom of the textboxes,
in order to see multiple rows of notes without scrolling.

John W. Vinson[MVP]

 

[melwester]

Hmmm... this is what I had all of last week and it didn't work.

I took out the subdatasheets, I wasn't too keen on them.

I snug up the bottom of the table to show just the rows I want But on the
bottom Left it's saying 1 of 500 (meaning all of the records in the table.
The most I would have per IdNumber would be 16. Some IdNumbers have 1 or 2
or 7 or 16.

The CTANotes subform is running off the CTA Notes query as I'm putting the
dates in decending order. And on Continuous form.

I want it to show how many per IdNumber not the whole table. Like I said
before I had this working until I went in & screwed it up.

Any ideas on how to fix this?

 

[John Vinson]

Hmmm... this is what I had all of last week and it didn't work.

I took out the subdatasheets, I wasn't too keen on them.

I snug up the bottom of the table to show just the rows I want But on the
bottom Left it's saying 1 of 500 (meaning all of the records in the table.
The most I would have per IdNumber would be 16. Some IdNumbers have 1 or 2
or 7 or 16.

The CTANotes subform is running off the CTA Notes query as I'm putting the
dates in decending order. And on Continuous form.

I want it to show how many per IdNumber not the whole table. Like I said
before I had this working until I went in & screwed it up.

Any ideas on how to fix this?

Some questions:

- What is the Recordsource property of the main form?
- What is the Recordsource property of the subform?
- What are the Master and Child Link Fields?
- What are the Primary Keys of the tables for the main and subform?

This is NOT a problem with the way Access works: there's some mistake
in the way you've set up the subform. Let's just fix it!


John W. Vinson[MVP]

 

[melwester]

Hi John,

I agree lets fix it -

- What is the Recordsource property of the main form?
tbl_CTAMain

- What is the Recordsource property of the subform?
qry_CTANotes

- What are the Master and Child Link Fields?

IDNumber for Master Link
None for Child Link (If I put in IDNumber in Child nothing shows up on the
subform, but if I leave it blank at least I get something it's just the whole
table - not what I want)

- What are the Primary Keys of the tables for the main and subform?

Main - None
Subform - None

tbl_CTAMain layout:
IDNumber
CTAName
Address
City
State
Zip
Phone
Fax
Email

tbl_CTANotes layout:
IDNumber
Comments
Date

The Qry of CTANotes as the date in decending order.

 

[John Vinson]

Hi John,

I agree lets fix it -


IDNumber for Master Link
None for Child Link (If I put in IDNumber in Child nothing shows up on the
subform, but if I leave it blank at least I get something it's just the whole
table - not what I want)

It should certainly be IDNumber. A master link without a child link is
like a rope with only one end... not much use for anything!

Main - None
Subform - None

That may be a big part of the problem. A Table without a primary key
IS NOT A TABLE - it's a scrapheap. With no Primary Key I presume there
is also no relationship defined between tbl_CTAMain and tbl_CTANotes?

Is the IDNumber in fact unique within tbl_CTAMain? If not, what
distinguishes multiple records with the same IDNumber? What's the
datatype of IDNumber?

tbl_CTAMain layout:
IDNumber
CTAName
Address
City
State
Zip
Phone
Fax
Email

tbl_CTANotes layout:
IDNumber
Comments
Date

The Qry of CTANotes as the date in decending order.

Is that ALL that it is? Please open the query in SQL view and copy and
paste the SQL here.

You might also want to try just building a new Autoform based on
tbl_CTAMain, and another based on tbl_CTANotes, and dragging the
latter onto the former to create a brand spanking new subform. If
*that* works, then we can be sure it's not the tables!

John W. Vinson[MVP]

 

[melwester]

Ok I made the Child Link IDNumber (Now the form pulls up with nothing on
it but the headers).

That may be a big part of the problem. A Table without a primary key
IS NOT A TABLE - it's a scrapheap. With no Primary Key I presume there
is also no relationship defined between tbl_CTAMain and tbl_CTANotes?

In the tbl_CTAMain I made IDNumber the primary key (There are no duplicates
in the IDNumber in tbl_CTAMain. In tbl_CTANotes it's the same IDNumber but
here there are duplicates. A little history on the Notes: This was taken
from a Webbase database where users would entered in notes about what was
done to solve a problem with a date. This webbase was then pulled in an
Excel file then I took over and imported it into Access. If an IDNumber had
more then 1 comments it came in as 2 or more rows. For example:
1034 Tooth pulled 7/5/05
1034 Had Xrays 7/6/05

Right now I'm trying to make it so that when this client (insurance company)
calls we can tell them what was done just by looking at the screen.

Is the IDNumber in fact unique within tbl_CTAMain? If not, what
distinguishes multiple records with the same IDNumber? What's the
datatype of IDNumber?

The datatype for IDNumber is Number with the field size as Double

Is that ALL that it is? Please open the query in SQL view and copy and
paste the SQL here.

SELECT tbl_CTAMain.IDNumber, tbl_CTANotes.Comments, tbl_CTANotes.Date
FROM tbl_CTAMain INNER JOIN tbl_CTANotes ON tbl_CTAMain.IDNumber =
tbl_CTANotes.IDNumber
ORDER BY tbl_CTAMain.IDNumber, tbl_CTANotes.Date DESC;

I haven't done the new forms but will do this morning.

I really appreciate the help.

 

[John Vinson]

The datatype for IDNumber is Number with the field size as Double

Double is a BAD CHOICE. A double number is useful if you have high
precision (14 decimals or fewer) math calculations; it is not suitable
for an ID, since there will be a risk of roundoff error. If
tbl_CTAMain.IDNumber is an Autonumber, make it a Number... Long
Integer; if both are Double, consider making both of them Long
Integers. You'll never need 14 decimal places for an identifier...
will you?

SELECT tbl_CTAMain.IDNumber, tbl_CTANotes.Comments, tbl_CTANotes.Date
FROM tbl_CTAMain INNER JOIN tbl_CTANotes ON tbl_CTAMain.IDNumber =
tbl_CTANotes.IDNumber
ORDER BY tbl_CTAMain.IDNumber, tbl_CTANotes.Date DESC;

<sigh>

Ok. This is NOT THE NOTES TABLE.

This is a Query joining tbl_CTAMain to tbl_CTANotes. This query will
not be updateable since you don't have a Primary Key, and it is NOT
NECESSARY to join the two tables when you're using a subform. That's
what the subform is FOR - to display records from tblCTA_Main on the
Main form, and from tbl_CTANotes on the Notes subform.

Change this query to

SELET tblCTANotes.IDNumber, tbl_CTANotes.Comments, tbl_CTANotes.[Date]
FROM tbl_CTANotes
ORDER BY tbl_CTANotes.[Date] DESC;

and leave IDNumber as the Master/Child Link Field. This WILL WORK (at
least it's worked in the umpteen forms with subforms I've built over
the past ten years).

You may also want to change the name of the field Date to NoteDate or
something else: Date is a reserved name, for the Date() function
returning the system clock date, and Access can get confused about
which you mean.


John W. Vinson[MVP]

 

[melwester]

OK - I made the IDNumber on both tables plus all the others that have
IDNumber as an Long Integer. Had some problems with that as it kept saying
"I can't change the data type". Went over to the community and found "Allen
Browne" response on how to fix that. (You guys are wonderful!!!!!!!) Then I
was able to change the data type.

I fixed the "Date" in the Notes table to NotesDate and redid the query.

Now I have another problem and another thread but it's getting me no wheres.
"My main form won't open" thread.

My main form won't open it has been working just fine all month long. Now
All I get is the Header at the very top. The rest of the screen is Grayed
out.

I did delete some columns in my main table where the form is based on. And
moved some text boxes that were on the main form into a subform. Did both of
these today "now yesterday". Now the form doesn't show anything.

Most of the tables don't have all the data, So I'm helping out with that by
imputting the data directly into the tables. Is this going to mess up my
forms each time I add something to the tables?

I don't have a backup. But I will now.

What do I need to do to get this working?

The datatype for IDNumber is Number with the field size as Double

Double is a BAD CHOICE. A double number is useful if you have high
precision (14 decimals or fewer) math calculations; it is not suitable
for an ID, since there will be a risk of roundoff error. If
tbl_CTAMain.IDNumber is an Autonumber, make it a Number... Long
Integer; if both are Double, consider making both of them Long
Integers. You'll never need 14 decimal places for an identifier...
will you?

SELECT tbl_CTAMain.IDNumber, tbl_CTANotes.Comments, tbl_CTANotes.Date
FROM tbl_CTAMain INNER JOIN tbl_CTANotes ON tbl_CTAMain.IDNumber =
tbl_CTANotes.IDNumber
ORDER BY tbl_CTAMain.IDNumber, tbl_CTANotes.Date DESC;

<sigh>

Ok. This is NOT THE NOTES TABLE.

This is a Query joining tbl_CTAMain to tbl_CTANotes. This query will
not be updateable since you don't have a Primary Key, and it is NOT
NECESSARY to join the two tables when you're using a subform. That's
what the subform is FOR - to display records from tblCTA_Main on the
Main form, and from tbl_CTANotes on the Notes subform.

Change this query to

SELET tblCTANotes.IDNumber, tbl_CTANotes.Comments, tbl_CTANotes.[Date]
FROM tbl_CTANotes
ORDER BY tbl_CTANotes.[Date] DESC;

and leave IDNumber as the Master/Child Link Field. This WILL WORK (at
least it's worked in the umpteen forms with subforms I've built over
the past ten years).

You may also want to change the name of the field Date to NoteDate or
something else: Date is a reserved name, for the Date() function
returning the system clock date, and Access can get confused about
which you mean.


John W. Vinson[MVP]

 

[melwester]

Hi John,

I got my main form to work. All I did was move the footer up to the edge of
the tab control and when I went into View the form came in. Go figure.

But after making all the necessary changes, fixing the IDNUmber making it
Long Integer on all tables and renaming Date and whatever else you had me do
the CTANotes subform still doesn't work.

In fact when I go to view the form without placing any data in, all the
other subforms pulls up the text boxes to place info in. But in Notes it
does not. All I get here is the header.

Any more tricks of the trade for this one?

I really do appreciate your help.

OK - I made the IDNumber on both tables plus all the others that have
IDNumber as an Long Integer. Had some problems with that as it kept saying
"I can't change the data type". Went over to the community and found "Allen
Browne" response on how to fix that. (You guys are wonderful!!!!!!!) Then I
was able to change the data type.

I fixed the "Date" in the Notes table to NotesDate and redid the query.

Now I have another problem and another thread but it's getting me no wheres.
"My main form won't open" thread.

My main form won't open it has been working just fine all month long. Now
All I get is the Header at the very top. The rest of the screen is Grayed
out.

I did delete some columns in my main table where the form is based on. And
moved some text boxes that were on the main form into a subform. Did both of
these today "now yesterday". Now the form doesn't show anything.

Most of the tables don't have all the data, So I'm helping out with that by
imputting the data directly into the tables. Is this going to mess up my
forms each time I add something to the tables?

I don't have a backup. But I will now.

What do I need to do to get this working?

The datatype for IDNumber is Number with the field size as Double

Double is a BAD CHOICE. A double number is useful if you have high
precision (14 decimals or fewer) math calculations; it is not suitable
for an ID, since there will be a risk of roundoff error. If
tbl_CTAMain.IDNumber is an Autonumber, make it a Number... Long
Integer; if both are Double, consider making both of them Long
Integers. You'll never need 14 decimal places for an identifier...
will you?

Is that ALL that it is? Please open the query in SQL view and copy and
paste the SQL here.

SELECT tbl_CTAMain.IDNumber, tbl_CTANotes.Comments, tbl_CTANotes.Date
FROM tbl_CTAMain INNER JOIN tbl_CTANotes ON tbl_CTAMain.IDNumber =
tbl_CTANotes.IDNumber
ORDER BY tbl_CTAMain.IDNumber, tbl_CTANotes.Date DESC;

<sigh>

Ok. This is NOT THE NOTES TABLE.

This is a Query joining tbl_CTAMain to tbl_CTANotes. This query will
not be updateable since you don't have a Primary Key, and it is NOT
NECESSARY to join the two tables when you're using a subform. That's
what the subform is FOR - to display records from tblCTA_Main on the
Main form, and from tbl_CTANotes on the Notes subform.

Change this query to

SELET tblCTANotes.IDNumber, tbl_CTANotes.Comments, tbl_CTANotes.[Date]
FROM tbl_CTANotes
ORDER BY tbl_CTANotes.[Date] DESC;

and leave IDNumber as the Master/Child Link Field. This WILL WORK (at
least it's worked in the umpteen forms with subforms I've built over
the past ten years).

You may also want to change the name of the field Date to NoteDate or
something else: Date is a reserved name, for the Date() function
returning the system clock date, and Access can get confused about
which you mean.


John W. Vinson[MVP]

 

[John Vinson]

Hi John,

I got my main form to work. All I did was move the footer up to the edge of
the tab control and when I went into View the form came in. Go figure.

But after making all the necessary changes, fixing the IDNUmber making it
Long Integer on all tables and renaming Date and whatever else you had me do
the CTANotes subform still doesn't work.

In fact when I go to view the form without placing any data in, all the
other subforms pulls up the text boxes to place info in. But in Notes it
does not. All I get here is the header.

Any more tricks of the trade for this one?

Not to keep hammering on the same point but *did* you change the
subform's RecordSource to

Change this query to

SELECT tblCTANotes.IDNumber, tbl_CTANotes.Comments,
tbl_CTANotes.[Date]
FROM tbl_CTANotes
ORDER BY tbl_CTANotes.[Date] DESC;

as I suggested? (Use your new datefield name of course).

John W. Vinson[MVP]

 

[melwester]

Not to keep hammering on the same point but *did* you change the

subform's RecordSource to

Change this query to

SELECT tblCTANotes.IDNumber, tbl_CTANotes.Comments,
tbl_CTANotes.[Date]
FROM tbl_CTANotes
ORDER BY tbl_CTANotes.[Date] DESC;
YES!!!


as I suggested? (Use your new datefield name of course).

YEs!!

I did go into properties on the subform and changed the Allow Edits,
deletions, additions to YES. This got the form to show the text boxes but
no data.

Getting there - just need the data link in from the main form.
Master Link is IdNumber
Child Link is IdNumber

Hi John,

I got my main form to work. All I did was move the footer up to the edge of
the tab control and when I went into View the form came in. Go figure.

But after making all the necessary changes, fixing the IDNUmber making it
Long Integer on all tables and renaming Date and whatever else you had me do
the CTANotes subform still doesn't work.

In fact when I go to view the form without placing any data in, all the
other subforms pulls up the text boxes to place info in. But in Notes it
does not. All I get here is the header.

Any more tricks of the trade for this one?

Not to keep hammering on the same point but *did* you change the
subform's RecordSource to

Change this query to

SELECT tblCTANotes.IDNumber, tbl_CTANotes.Comments,
tbl_CTANotes.[Date]
FROM tbl_CTANotes
ORDER BY tbl_CTANotes.[Date] DESC;

as I suggested? (Use your new datefield name of course).

John W. Vinson[MVP]

 

[John Vinson]

Getting there - just need the data link in from the main form.
Master Link is IdNumber
Child Link is IdNumber

And are there records in Notes with matching ID number for the record
shown on the mainform?

John W. Vinson[MVP]

 

[melwester]

And are there records in Notes with matching ID number for the record

shown on the mainform?

YES

The IDNumber in Notes is the same as the main form. Except there is more
then 1 line per IDNUmber in Notes that matches the same one in the main form
Table.

 

[John Vinson]

The IDNumber in Notes is the same as the main form. Except there is more
then 1 line per IDNUmber in Notes that matches the same one in the main form
Table.

This is REALLY bizarre.

Try this, Melwester:

Create a new Query joining the main table to the Notes table by
IDnumber. Sort by the IDnumber, and display the IDnumber and the note.
Open this query as a datasheet.

Do you see the notes that you expect?

If so, I suspect there's just been too much tweaking done on your
form. Try creating a *brand new* form, based on the main table; on it,
put a *brand new* subform, based on the Notes table (not on a query,
just on the table itself). Do this manually, not using the forms
wizard. Does *this* form work?

If not, contact me offline at jvinson <at> wysard of info <dot> com
(deleting the blanks and substituting the punctuation); I'd be willing
to take a quick look at the form and see what's wrong with it.

John W. Vinson[MVP]