What am I doing wrong??

[Suzy]

Here are my problems...

1. I am a beginner user of Access and I’m in *way* over my head setting up
a client database for my sister’s beauty therapy business.

2. With the held of some very helpful advice from this Group I have
achieved various tables, queries, macros, forms and reports. But now I’m
stuck! With help I have set a form up where client notes can be entered
following each visit and updated with an ongoing client history. This all
works great, until I want to enter a note for a first time client (ie the
client does not have any earlier notes in the history), when I get the
following run time error '3201': "You cannot add or change a record because a
related record is required in table ‘StaffInfo’. "

When I choose to 'debug' I am taken through to the Visual Basic window which
is highlighting the code "DoCmd.RunCommand acCmdSaveRecord 'forces record
save" as the problem.

Where am I going wrong?? I appreciate your ('idiots guide to') help with
this problem as I am in *way* over my head! Here’s the advice I have been
given previously and which I have used to set up this feature…


Two tables:
1. StaffInfo
Field1: ClientID (primary key, autonumber, long integer)
Field2: FirstName (text)
Field3: LastName (text)
Field4: Title (text)
Etc (other client details)
2. ClientNotes
Field1: ID (Primary key, autonumber, long integer)
Field2: ClientID (long integer, no default)
Field3: Notes (memo)

Create a relationship (enforce referential integrity, cascade update on)
between the two tables on the ClientID field.

Form: PostClientNotes. Default view: Single form. Record source (copy &
paste this into the record source, then look at the query design view using
the ellipsis to see how it was constructed):

SELECT ClientNotes.* FROM ClientNotes WHERE
(((ClientNotes.ClientID)=[Forms]![PostClientNotes]![ClientIDSelector]));

In the form's detail section, create an UNBOUND combo box called
ClientIDSelector.
ColumnCount = 4, BoundColumn = 1, ColumnWidths = 0cm;2.501cm;3cm;1.51cm.
RowSource is:

SELECT StaffInfo.ClientID, StaffInfo.FirstName, StaffInfo.LastName,
StaffInfo.Title FROM StaffInfo ORDER BY StaffInfo.FirstName,
StaffInfo.LastName, StaffInfo.Title;

This code goes into its AfterUpdate event:

Private Sub ClientIDSelector_AfterUpdate()
Me.Requery 'display Notes for selected Customer
End Sub

Also in the form detail, create the UNBOUND NoteNew text box. Be sure to set
the Enter key behavior to New Line in Field for this text box so that the
user can enter multiple lines in one entry.

Also in the form detail, create a button called PostNotes. Put this code in
its Click event:

Private Sub PostNotes_Click()
If IsNull(NoteNew) Or NoteNew = "" Then Exit Sub
If IsNull(Notes) Then 'do not insert blank line after new note
Notes = Date & Chr(13) & Chr(10) & [NoteNew]
Else 'insert blank line between existing note and new note
Notes = Date & Chr(13) & Chr(10) & [NoteNew] & Chr(13) & Chr(10) & Chr(13) &
Chr(10) & [Notes]
End If
NoteNew = Null 'blanks out new note, since it was just appended to Notes entry
DoCmd.RunCommand acCmdSaveRecord 'forces record save
End Sub

Lastly in the form's detail section, create a single bound but disabled text
box called Notes. Its ControlSource is just Notes (i.e. the Notes field).

Create a query called PostClientNotes. Go to SQL view & paste in this:

TO PUT NEW NOTES AT THE TOP OF THE MEMO:
UPDATE ClientNotes SET ClientNotes.Notes = Date() & Chr(13) & Chr(10) &
[Notes] & [Forms]![PostClientNotes]![NoteNew]
WHERE
(((ClientNotes.ClientID)=[Forms]![PostClientNotes]![ClientIDSelector])) &
Chr(13) & Chr(10) & [Notes];

Now, here is what happens. When a user opens the form, it is blank. When the
user selects a Client by Client name from the combo box at the top, it shows
that Clients Notes field from the ClientNotes table. The user can then enter
notes into the NoteNew text box and clicks the button. If the NotesNew field
or ClientIDSelector are null/empty, it does nothing (to prevent an error if
there is nothing to append). If there are NotesNEw, this udpates the existing
Notes field to be:

1. Current date
2. Followed by a linefeed
3. Followed by the new notes
4. Followed by another linefeed
5. Followed by existing notes

The code on the button then clears the new notes field and resets the form
so that the new consolidated note appears in the Notes text box.

 

[John Vinson]

2. With the held of some very helpful advice from this Group I have
achieved various tables, queries, macros, forms and reports. But now I’m
stuck! With help I have set a form up where client notes can be entered
following each visit and updated with an ongoing client history. This all
works great, until I want to enter a note for a first time client (ie the
client does not have any earlier notes in the history), when I get the
following run time error '3201': "You cannot add or change a record because a
related record is required in table ‘StaffInfo’. "

How about a solution which will avoid this error, be simpler to implement
and maintain, and requires NO code at all?

Just put the Notes form as a Subform on the form where you enter client
information. The Master/Child Link field will fill in the ID automatically,
and you'll see just the notes for that client.

 

[Suzy]

Hi John...

Sounds nice and simple but where do I start? My sister and her staff are
less than computer literate so I want to make this as bullet proof as
possible. I have a form "CurrentClients" which now has three tabs:
1. ClientInfo
2. NewsletterOptions
3. CientNotes

I want them to be able to enter new notes on any given day and have these
notes appended to the clients historical notes with the date in the first
line, the new notes in the second line, a blank line and then the historical
notes.

I do not want the historical notes to be able to be edited (and therefore
accidentally deleted) by staff.

Ideally I would see on the 'ClientNotes' tab the same set up mentioned in my
original post. A box to type new notes into, a button to append the new
notes, and a client history below it all which the new notes are added to
along with the current date.

Am I being too ambitious?

 

[Suzy]

I figured it out and it's all working great now. Thanks John, you rock!

 

[Suzy]

Hi John... OK I jumped the gun saying I had it all under control. I still
need to be able to do everything I have listed below. Following your
suggestion about a subform (which I have had a play with, and unless I'm
doing something really wrong, I really don't like the look of it), I fiddled
around a bit more and ended up with the 'new note' field, the 'post notes'
button and the uneditable 'history' on the third tab. This seems to work
fine for clients with existing history, however when I add a new client and I
go to enter a first note for them I get the following message...

"Cannot add record(s); join key of table 'ClientNotes' not in result set."

Have I totally broken my database??

 

[John Vinson]

Hi John... OK I jumped the gun saying I had it all under control. I still
need to be able to do everything I have listed below. Following your
suggestion about a subform (which I have had a play with, and unless I'm
doing something really wrong, I really don't like the look of it), I fiddled
around a bit more and ended up with the 'new note' field, the 'post notes'
button and the uneditable 'history' on the third tab. This seems to work
fine for clients with existing history, however when I add a new client and I
go to enter a first note for them I get the following message...

"Cannot add record(s); join key of table 'ClientNotes' not in result set."

Have I totally broken my database??

No; at worst you've messed up this Form's Recordset.

It sounds like you're trying to base this Form on a Query joining your
client table to the Notes table. Is that correct? Could you open the
Query upon which the form is based (see the Recordsource property in
form design view) and post it here? And could you explain what you
disliked about the subform? You can make it LOOK like pretty much
anything you wish; don't mix up the functionality with the screen
layout!

John W. Vinson[MVP]

 

[Suzy]

Hi John

Could you open the Query upon which the form is based (see the Recordsource
property in form design view) and post it here?
SELECT StaffInfo.*, StaffInfo.[Current Staff], ClientNotes.Notes FROM
StaffInfo INNER JOIN ClientNotes ON StaffInfo.ClientID = ClientNotes.ClientID
ORDER BY StaffInfo.LastName, StaffInfo.FirstName;

Could you explain what you disliked about the subform?
I didn't like it because it looked like a table (boo hoo!) and the poor
girls in the clinic would have just typed the new notes right over the top of
the old notes and not known what they were doing. I loved the idea of them
having one box they can type in, a button they can click to 'post the note'
and the third box at the bottom which is greyed out and uneditable showing
them the previous notes made for that client, plus any added by typing in the
above mentioned box and click the button to add. This idea is working fine
for previous clients with existing notes, but as soon as I add a new client,
it doesn't want to add any new notes... (

Thanks for your help!

 

[John Vinson]

Hi John

Could you open the Query upon which the form is based (see the Recordsource
property in form design view) and post it here?
SELECT StaffInfo.*, StaffInfo.[Current Staff], ClientNotes.Notes FROM
StaffInfo INNER JOIN ClientNotes ON StaffInfo.ClientID = ClientNotes.ClientID
ORDER BY StaffInfo.LastName, StaffInfo.FirstName;

Ok... if you base a Form on this query you'll get some unpleasant and
unwanted effects. In paticular, if you have eleven Notes records for a
given client ID, you will see that client repeated ELEVEN TIMES on the
form, as you scroll from record to record (since each record in
StaffInfo will be joined to ALL the matching records in ClientNotes).

In addition, you'll get the error you posted. That error can be solved
by editing the query to include the ClientNotes ClientID (the "join
key" that it's complaining about):

SELECT StaffInfo.*, StaffInfo.[Current Staff],
ClientNotes.ClientID,ClientNotes.Notes
FROM StaffInfo
INNER JOIN ClientNotes ON StaffInfo.ClientID = ClientNotes.ClientID
ORDER BY StaffInfo.LastName, StaffInfo.FirstName;

Could you explain what you disliked about the subform?
I didn't like it because it looked like a table (boo hoo!) and the poor
girls in the clinic would have just typed the new notes right over the top of
the old notes and not known what they were doing. I loved the idea of them
having one box they can type in, a button they can click to 'post the note'
and the third box at the bottom which is greyed out and uneditable showing
them the previous notes made for that client, plus any added by typing in the
above mentioned box and click the button to add. This idea is working fine
for previous clients with existing notes, but as soon as I add a new client,
it doesn't want to add any new notes... (

I dislike datasheet view too. Fortunately it is one of THREE options;
you don't need to stick with it!

I'd suggest going back to basing your Form on a query sorting
StaffInfo by name (not including the ClientNotes table), and using a
Subform based on ClientNotes. However, change the Default View
property of this Subform from "Datasheet" to "Single Form"; it will
have the ClientID as the Master/Link Field (but not displayed on the
form), and you'll have just one great big textbox for Notes. If you
wish, you can put a command button labeled "Post the Note" with VBA
code in its click event:

Private Sub cmdPostIt_Click()
DoCmd.GoToRecord acNewRecord
End Sub

to write the current record to disk and move to the new record.

You can even use TWO subforms to get the effect you want: both bound
to the Notes table. The first subform would have its "Data Entry"
property set, to allow only new notes to be entered; the second would
be a Continuous form, to show multiple (scrollable) notes. You could
set its Allow Additions and Allow Edits properties to False to make it
read-only for old notes.

John W. Vinson[MVP]

 

[Suzy]

Thanks John. I'll try that in the office tomorrow. Will get back to you and
let you know how I got on.

Hi John

Could you open the Query upon which the form is based (see the Recordsource
property in form design view) and post it here?
SELECT StaffInfo.*, StaffInfo.[Current Staff], ClientNotes.Notes FROM
StaffInfo INNER JOIN ClientNotes ON StaffInfo.ClientID = ClientNotes.ClientID
ORDER BY StaffInfo.LastName, StaffInfo.FirstName;

Ok... if you base a Form on this query you'll get some unpleasant and
unwanted effects. In paticular, if you have eleven Notes records for a
given client ID, you will see that client repeated ELEVEN TIMES on the
form, as you scroll from record to record (since each record in
StaffInfo will be joined to ALL the matching records in ClientNotes).

In addition, you'll get the error you posted. That error can be solved
by editing the query to include the ClientNotes ClientID (the "join
key" that it's complaining about):

SELECT StaffInfo.*, StaffInfo.[Current Staff],
ClientNotes.ClientID,ClientNotes.Notes
FROM StaffInfo
INNER JOIN ClientNotes ON StaffInfo.ClientID = ClientNotes.ClientID
ORDER BY StaffInfo.LastName, StaffInfo.FirstName;

Could you explain what you disliked about the subform?
I didn't like it because it looked like a table (boo hoo!) and the poor
girls in the clinic would have just typed the new notes right over the top of
the old notes and not known what they were doing. I loved the idea of them
having one box they can type in, a button they can click to 'post the note'
and the third box at the bottom which is greyed out and uneditable showing
them the previous notes made for that client, plus any added by typing in the
above mentioned box and click the button to add. This idea is working fine
for previous clients with existing notes, but as soon as I add a new client,
it doesn't want to add any new notes... (

I dislike datasheet view too. Fortunately it is one of THREE options;
you don't need to stick with it!

I'd suggest going back to basing your Form on a query sorting
StaffInfo by name (not including the ClientNotes table), and using a
Subform based on ClientNotes. However, change the Default View
property of this Subform from "Datasheet" to "Single Form"; it will
have the ClientID as the Master/Link Field (but not displayed on the
form), and you'll have just one great big textbox for Notes. If you
wish, you can put a command button labeled "Post the Note" with VBA
code in its click event:

Private Sub cmdPostIt_Click()
DoCmd.GoToRecord acNewRecord
End Sub

to write the current record to disk and move to the new record.

You can even use TWO subforms to get the effect you want: both bound
to the Notes table. The first subform would have its "Data Entry"
property set, to allow only new notes to be entered; the second would
be a Continuous form, to show multiple (scrollable) notes. You could
set its Allow Additions and Allow Edits properties to False to make it
read-only for old notes.

John W. Vinson[MVP]

 

[Suzy]

That error can be solved by editing the query to include the ClientNotes
ClientID (the "join key" that it's complaining about):
SELECT StaffInfo.*, StaffInfo.[Current Staff],
ClientNotes.ClientID,ClientNotes.Notes
FROM StaffInfo
INNER JOIN ClientNotes ON StaffInfo.ClientID = ClientNotes.ClientID
ORDER BY StaffInfo.LastName, StaffInfo.FirstName;

I have an OpenReport action working on this form also. The button when
clicked should open up a report in print preview for only the client
currently selected. ie. I have Mrs Anderson's 'client card' open and have
entered a new note for her and would like to view and print only her client
information sheet. I have the following expression in the WHERE condition of
the PreviewClientCard macro:

[ClientID]=[Forms]![CurrentStaff]![StaffInfo.ClientID]

If I leave this condition out altogether the report will preview all
records. If I have this condition in, I get the following error message:

"Invalid bracketing of name '[Forms]![CurrentStaff]![StaffInfo.ClientID]'

When I click on HELP from there I get the message:

"Invalid bracketing of name <name>. (Error 3126)
The specified name either cannot have brackets around it or the brackets are
mismatched. Check your entry to make sure the brackets are properly matched,
and then try the operation again."

I've tried all the combinations of brackets I can think of but haven't made
any headway.

It's probably something really easy huh?

Hi John

Could you open the Query upon which the form is based (see the Recordsource
property in form design view) and post it here?
SELECT StaffInfo.*, StaffInfo.[Current Staff], ClientNotes.Notes FROM
StaffInfo INNER JOIN ClientNotes ON StaffInfo.ClientID = ClientNotes.ClientID
ORDER BY StaffInfo.LastName, StaffInfo.FirstName;

Ok... if you base a Form on this query you'll get some unpleasant and
unwanted effects. In paticular, if you have eleven Notes records for a
given client ID, you will see that client repeated ELEVEN TIMES on the
form, as you scroll from record to record (since each record in
StaffInfo will be joined to ALL the matching records in ClientNotes).

In addition, you'll get the error you posted. That error can be solved
by editing the query to include the ClientNotes ClientID (the "join
key" that it's complaining about):

SELECT StaffInfo.*, StaffInfo.[Current Staff],
ClientNotes.ClientID,ClientNotes.Notes
FROM StaffInfo
INNER JOIN ClientNotes ON StaffInfo.ClientID = ClientNotes.ClientID
ORDER BY StaffInfo.LastName, StaffInfo.FirstName;

Could you explain what you disliked about the subform?
I didn't like it because it looked like a table (boo hoo!) and the poor
girls in the clinic would have just typed the new notes right over the top of
the old notes and not known what they were doing. I loved the idea of them
having one box they can type in, a button they can click to 'post the note'
and the third box at the bottom which is greyed out and uneditable showing
them the previous notes made for that client, plus any added by typing in the
above mentioned box and click the button to add. This idea is working fine
for previous clients with existing notes, but as soon as I add a new client,
it doesn't want to add any new notes... (

I dislike datasheet view too. Fortunately it is one of THREE options;
you don't need to stick with it!

I'd suggest going back to basing your Form on a query sorting
StaffInfo by name (not including the ClientNotes table), and using a
Subform based on ClientNotes. However, change the Default View
property of this Subform from "Datasheet" to "Single Form"; it will
have the ClientID as the Master/Link Field (but not displayed on the
form), and you'll have just one great big textbox for Notes. If you
wish, you can put a command button labeled "Post the Note" with VBA
code in its click event:

Private Sub cmdPostIt_Click()
DoCmd.GoToRecord acNewRecord
End Sub

to write the current record to disk and move to the new record.

You can even use TWO subforms to get the effect you want: both bound
to the Notes table. The first subform would have its "Data Entry"
property set, to allow only new notes to be entered; the second would
be a Continuous form, to show multiple (scrollable) notes. You could
set its Allow Additions and Allow Edits properties to False to make it
read-only for old notes.

John W. Vinson[MVP]

 

[John Vinson]

I have an OpenReport action working on this form also. The button when
clicked should open up a report in print preview for only the client
currently selected. ie. I have Mrs Anderson's 'client card' open and have
entered a new note for her and would like to view and print only her client
information sheet. I have the following expression in the WHERE condition of
the PreviewClientCard macro:

[ClientID]=[Forms]![CurrentStaff]![StaffInfo.ClientID]

If I leave this condition out altogether the report will preview all
records. If I have this condition in, I get the following error message:

"Invalid bracketing of name '[Forms]![CurrentStaff]![StaffInfo.ClientID]'

Use the Name property of the textbox on the form which contains the
ClientID. It's objecting because there is no control or field with a
period in the name. You can probably just leave off the "StaffInfo."
from the reference.

John W. Vinson[MVP]

 

[Suzy]

You truly are a wonderful help! You have made my day. Hope the rest of your
week goes well!

Thank you, thank you!

I have an OpenReport action working on this form also. The button when
clicked should open up a report in print preview for only the client
currently selected. ie. I have Mrs Anderson's 'client card' open and have
entered a new note for her and would like to view and print only her client
information sheet. I have the following expression in the WHERE condition of
the PreviewClientCard macro:

[ClientID]=[Forms]![CurrentStaff]![StaffInfo.ClientID]

If I leave this condition out altogether the report will preview all
records. If I have this condition in, I get the following error message:

"Invalid bracketing of name '[Forms]![CurrentStaff]![StaffInfo.ClientID]'

Use the Name property of the textbox on the form which contains the
ClientID. It's objecting because there is no control or field with a
period in the name. You can probably just leave off the "StaffInfo."
from the reference.

John W. Vinson[MVP]