Need Additional Subform Link

[Guest]

One minor problem remains.
When I run a report based on the Status Notes Subform -that is based on the
qry of the Notes Table and the Cases Table -I get duplicate notes when there
are 2 children. Example:
TwoFiles: 1 FirstNameChild: Adam
Status Notes: 4/1/05 Adam's PPT is set for 4/20/05 NotesID#: 40
ClientCaseID#12
Status Notes: 4/1/05 Adam's PPT is set for 4/20/04 Notes ID#: 40
ClientCaseID#12
TwoFiles: 2 FirstNameChild: Victoria
Status Notes: 4/1/05 Victoria needs home tutoring NotiesID#:41
ClientCaseID#12
Status Notes: 4/1/05 Victoria needs home tutoring NotiesID#:41
ClientCaseID#12
I did link the Master and Child fields as you instructed.
This isn't a major problem if the solution is an easy one. I think this must
be the longest thread.
Joan

 

[John Vinson]

One minor problem remains.
When I run a report based on the Status Notes Subform -that is based on the
qry of the Notes Table and the Cases Table -I get duplicate notes when there
are 2 children. Example:

I'd use the Report's Sorting and Grouping dialog; group by the CaseID,
and put case-specific information in the Group Header, and note
specific information in the detail section.

John W. Vinson[MVP]

 

[Guest]

I did as you suggested the result is I no longer have duplicate reords in the
Status Report.
However, another monster has appeared: I need to enter all the ClientID#
into the Notes Table which I am doing, but the problem is as follows:
Since I have a NotesID# field in the Notes Table that is an Auto# how do I
create an auto # for ClientID# after I get finished entering the 300 or so
numbers in the Notes table? The ClientID# is the Primary Key auto# in the
Clients Table.
Do I make Client the primary auto in the Notes table? Since there are
already over 300 notes entered I can't start with auto #1.
If you can help, please.
Joan

 

[John Vinson]

I did as you suggested the result is I no longer have duplicate reords in the
Status Report.
However, another monster has appeared: I need to enter all the ClientID#
into the Notes Table which I am doing, but the problem is as follows:
Since I have a NotesID# field in the Notes Table that is an Auto# how do I
create an auto # for ClientID# after I get finished entering the 300 or so
numbers in the Notes table? The ClientID# is the Primary Key auto# in the
Clients Table.
Do I make Client the primary auto in the Notes table? Since there are
already over 300 notes entered I can't start with auto #1.
If you can help, please.
Joan

The ClientID in the Notes table should certainly *NOT* be the Primary
Key, nor should it be an Autonumber. Normally the Notes would be
filled in by using a Form based on the Clients table, with a Subform
based on the Notes table, just as you're doing. The Master/Child Link
Field will fill in.

Question: are you linking the Notes table to the Cases table, by the
CaseClientID and the TwoFiles field? If so, since the Cases table is
already related to the Clients table, do you NEED a ClientID in the
Notes table? If you were to try to link from Clients directly to
Notes, you would lose the information about the case or the child to
which a note refers; you should probably link from Clients to Cases,
and from Cases to Notes. That way you don't NEED a ClientID in the
Notes table.

If I'm misunderstanding the structure please let me know.

John W. Vinson[MVP]

 

[Guest]

You are right. I did not need the clientID# in notes. I was unaware that you
could connect two fields in a qry. One more problem remains:
Since I changed the NotesID# form an autonumber to a number when I added the
clientID# in error, and I have over 300 notes entered, I will have to change
the NotesID# back to an auto# so the next note entered will have its notesid#
automatically attached to it.
I just read how to do this by creating a tbltemp with one field entereing
the next number you want and appending it in my instance to the Notes table.
I don't understand item 3:
If your original table contains property settings that prevent Null (Null: A
value you can enter in a field or use in expressions or queries to indicate
missing or unknown data. In Visual Basic, the Null keyword indicates a Null
value. Some fields, such as primary key fields, can't contain Null.) values
in fields, you must temporarily change those properties. These settings
include:
1.The Required field property set to Yes
2.The Indexed field property set to Yes (No Duplicates)
3.A field and/or record ValidationRule property that prevents Null values in
fields
If I get stuck, I have printed out all the fields in the Notes Table w/the
data.
Is it possible for me just to delete from thethe NotesID# field from the
Notes table and enter is again as a auto# and then just go thru all 315 note
files and enter the correct NotesID#?
If you can clarify the #3 above maybe I can avoid doing the data entry.
I thank you so much for all your help without which I would have been sunk.
[Just received the Inside2003 book you suggested. If anyone needs it, I do!]
Await your reply.
Joan

 

[Guest]

Later: I found another disc with the Notes Table prior to my changing the
NotesID#field from auto to number so I am all set with that.
Now if you can believe it there is another problem:
When I enter a new note in the subform Notes it is saved in the Notes Table
but not when I run a qryNotes or report based on the qryNotes.
The changes I made based on your last message are as follows:
in the qryNotes based on the Clients Table and Notes Table I joined them
"where both fields are equal" by the ClientCaseID# and also the Twofiles
field.
My Notes subform properties are as you suggested:
ClientCaseID#;Twofiles
ClientCaseID#;Twofiles
[I tried to delete my earlier message of today from this site, but if it
doesn't work please ignore my questions about changing number to autonumber:
I found a disc with the Notes Table prior to my changes re the clientid#]
I hope my recent problem will have an easy answer for I, and am sure you are
also, almost ready to give up on this.
Joan

 

[John Vinson]

Later: I found another disc with the Notes Table prior to my changing the
NotesID#field from auto to number so I am all set with that.
Now if you can believe it there is another problem:
When I enter a new note in the subform Notes it is saved in the Notes Table
but not when I run a qryNotes or report based on the qryNotes.

Please post the SQL to qryNotes. I don't understand what you mean
here: it's "saved in the Notes table" from the form, but does qryNotes
"save" data? Is it an insert query or an update query, or is it just
retrieving data from the notes table?

The changes I made based on your last message are as follows:
in the qryNotes based on the Clients Table and Notes Table I joined them
"where both fields are equal" by the ClientCaseID# and also the Twofiles
field.
My Notes subform properties are as you suggested:
ClientCaseID#;Twofiles
ClientCaseID#;Twofiles

[I tried to delete my earlier message of today from this site, but if it
doesn't work please ignore my questions about changing number to autonumber:
I found a disc with the Notes Table prior to my changes re the clientid#]

Well... that's ok; but there are easy ways around the problem. Many of
my tables' primary keys are "custom counters" which aren't autonumbers
but serve the same purpose.

I hope my recent problem will have an easy answer for I, and am sure you are
also, almost ready to give up on this.

I'm actually fascinated, and determined to help you get this working!

John W. Vinson[MVP]