Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

What am I doing wrong??

Discussion in 'Information Technology' started by Suzy, Nov 12, 2005.

Tags:
  1. Suzy

    Suzy
    Expand Collapse
    Guest

    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.
     
  2. Loading...

    Similar Threads Forum Date
    How To Meditate (dhyaan / Simran) ? Am I Doing It All Wrong. I Can't Concentrate And Feel Sleepy Intellectual Articles Mar 25, 2016
    Am I doing something wrong? Sikh Youth Nov 23, 2013
    Conentrating while Doing Paath? Blogs Oct 22, 2015
    Covering Kesh While Eating And Doing Other Activities Questions and Answers Mar 27, 2015
    Doing parkash of SGGS at home? Questions and Answers Mar 31, 2014

  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    "Suzy" wrote:

    > 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.

    --
    John W. Vinson[MVP]
     
  4. Suzy

    Suzy
    Expand Collapse
    Guest

    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?

    "John Vinson" wrote:

    > "Suzy" wrote:
    >
    > > 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.
    >
    > --
    > John W. Vinson[MVP]
    >
    >
    >
    >
    >
     
  5. Suzy

    Suzy
    Expand Collapse
    Guest

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

    "John Vinson" wrote:

    > "Suzy" wrote:
    >
    > > 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.
    >
    > --
    > John W. Vinson[MVP]
    >
    >
    >
    >
    >
     
  6. Suzy

    Suzy
    Expand Collapse
    Guest

    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??

    "Suzy" wrote:

    > 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?
    >
    > "John Vinson" wrote:
    >
    > > "Suzy" wrote:
    > >
    > > > 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.
    > >
    > > --
    > > John W. Vinson[MVP]
    > >
    > >
    > >
    > >
    > >
     
  7. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Mon, 14 Nov 2005 12:25:06 -0800, "Suzy"
    <Suzy@discussions.microsoft.com> wrote:

    >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]
     
  8. Suzy

    Suzy
    Expand Collapse
    Guest

    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... :eek:(

    Thanks for your help!

    "John Vinson" wrote:

    > On Mon, 14 Nov 2005 12:25:06 -0800, "Suzy"
    > <Suzy@discussions.microsoft.com> wrote:
    >
    > >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]
    >
     
  9. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Mon, 14 Nov 2005 20:04:03 -0800, "Suzy"
    <Suzy@discussions.microsoft.com> wrote:

    >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... :eek:(


    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]
     
  10. Suzy

    Suzy
    Expand Collapse
    Guest

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

    "John Vinson" wrote:

    > On Mon, 14 Nov 2005 20:04:03 -0800, "Suzy"
    > <Suzy@discussions.microsoft.com> wrote:
    >
    > >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... :eek:(

    >
    > 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]
    >
     
  11. Suzy

    Suzy
    Expand Collapse
    Guest

    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;

    >>That worked great! However now I've got a new problem (it never ends!)...


    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?


    "John Vinson" wrote:

    > On Mon, 14 Nov 2005 20:04:03 -0800, "Suzy"
    > <Suzy@discussions.microsoft.com> wrote:
    >
    > >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... :eek:(

    >
    > 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]
    >
     
  12. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Tue, 15 Nov 2005 14:19:04 -0800, "Suzy"
    <Suzy@discussions.microsoft.com> wrote:

    >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]
     
  13. Suzy

    Suzy
    Expand Collapse
    Guest

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

    Thank you, thank you!

    "John Vinson" wrote:

    > On Tue, 15 Nov 2005 14:19:04 -0800, "Suzy"
    > <Suzy@discussions.microsoft.com> wrote:
    >
    > >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]
    >
     

Share This Page