RE: Memo Field

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

  1. Suzy

    Suzy Guest

    Hi there... me again! Have been away for two weeks and come back to have
    another go / continue with this database and have come up against a new
    problem relating to the thread we have below...

    When I go to enter a note for the first time for a customer (ie the customer
    does not have any earlier notes in the history) I get the following run time
    error '3201':

    "You cannot add or change a record because a related record is required in
    table 'Customers'. "

    When I choose to 'debug' I am taken through to the Visual Basic window which
    is pointing to the code you supplied below (in the immediate message before
    this reply) and highlighting the line you added to solve the previous
    problem...

    "DoCmd.RunCommand acCmdSaveRecord 'forces record save"

    I appreciate your ('idiots guide to') help with this problem as I am in
    *way* over my head!

    Many thanks,
    Suzy


    "Brian" wrote:

    > I believe this is a record-locking issue. Silly me. I hadn't thought about
    > that when I did the original example, because I had record locking turned off
    > on my test db. When you open the current customer on your form, Access locks
    > the record to prevent others from changing the information while you are
    > viewing/changing it, and then the update query tries to update it; hence the
    > error message.
    >
    > Instead, let's try just changing the contents of the Notes textbox directly
    > on the form and then saving the record. This is much simpler, anyway. When
    > you click your button, have this code instead:
    >
    > 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) & [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
    >
    > Sorry about the confusion. This stuff is complicated enough without me
    > introducing a piece of bad code...
    >
    > "Suzy" wrote:
    >
    > > OK I've put all that in but now it is having a run time error...
    > >
    > > Microsoft Visual Basic window says: "Run-time error '3188': Could not
    > > updated; currently locked by another session on this machine"
    > >
    > > The options are to either 'End', 'Debug' or 'Help'.
    > >
    > > When I choose 'Debug' I'm taken through to the visual basic window and it's
    > > showing me the code you provided earlier in this post to create a 'PostNotes'
    > > button:
    > > PostNotes_Click()
    > > If IsNull(CustomerIDSelector) Or IsNull(NoteNew) Or NoteNew = "" Then Exit
    > > Sub ' does not append blank note
    > > DoCmd.SetWarnings False
    > > DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
    > > DoCmd.SetWarnings True
    > > Me.Refresh 'shows the newly-appended note
    > > NoteNew = Null 'blanks out new note, since it was just appended to Notes entry
    > > End Sub
    > >
    > > The error is pointed out / highlighted in this line:
    > > DoCmd.OpenQuery "PostCustomerNotes" 'appends current note
    > >
    > > But I don't know why... like I said, I'm in WAY over my head here.
    > >
    > >
    > >
    > >
    > >
    > > "Brian" wrote:
    > >
    > > > I should have tried the new version first. Try this for the query instead:
    > > >
    > > > UPDATE CustomerNotes SET CustomerNotes.Notes = Date() & Chr(13) & Chr(10) &
    > > > [Forms]![PostCustomerNotes]![NoteNew] & Chr(13) & Chr(10) & [Notes]
    > > > WHERE
    > > > (((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector]));
    > > >
    > > > Also, unless this happens when closing the form, you will want to have
    > > > something that clears the current note and refreshes the updated/prepended
    > > > note so that the user does not accidentally click "Post" twice and end up
    > > > with the same note in there twice. You will probably also want to ensure that
    > > > the update does not happen if the user has not yet typed anything in the
    > > > notes box: If IsNull(NoteNew) then Exit Sub on the line before the query runs.
    > > >
    > > >
    > > > "Suzy" wrote:
    > > >
    > > > > I appreciate the help, and it's good despite the gap in the timeframe...
    > > > > however there is one small problem... now when I click the Post Customer
    > > > > Notes button the date goes in the top... and the memo text ends up appended
    > > > > at the end of the memo field... like this:
    > > > >
    > > > > 19-Oct-05
    > > > > 18-Oct-05
    > > > > Note added 18-Oct-05
    > > > > Note added 19-Oct-05
    > > > >
    > > > > AND... (but wait, there's more)... all of a sudden each new note I add is
    > > > > being entered to EVERY customer...
    > > > >
    > > > > I'm in WAY over my head!
    > > > >
    > > > > "Brian" wrote:
    > > > >
    > > > > > It's been so long since I posted this that I forgot that I had done it (and
    > > > > > mostly, what I said, also). However, yes there is a way to make the most
    > > > > > current note go to the top. I have not tested this , but you should be able
    > > > > > to just reverse the two portions of the Update query; move the [Notes] to the
    > > > > > end of this:
    > > > > >
    > > > > > UPDATE CustomerNotes SET CustomerNotes.Notes = [Notes] & Chr(13) & Chr(10) &
    > > > > > Date() & Chr(13) & Chr(10) & [Forms]![PostCustomerNotes]![NoteNew]
    > > > > > WHERE
    > > > > > (((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector]));
    > > > > >
    > > > > > so that it looks like this instead:
    > > > > >
    > > > > > UPDATE CustomerNotes SET CustomerNotes.Notes = Date() & Chr(13) & Chr(10) &
    > > > > > [Forms]![PostCustomerNotes]![NoteNew]
    > > > > > WHERE
    > > > > > (((CustomerNotes.CustomerID)=[Forms]![PostCustomerNotes]![CustomerIDSelector])) & Chr(13) & Chr(10) & [Notes];
    > > > > >
    > > > > > This should put the existing notes at the end of the memo field instead of
    > > > > > the beginning.
    > > > > >
    > > > > >
    > > > > > "Suzy" wrote:
    > > > > >
    > > > > > > Brian... this info has been great thank you! I was just wondering if there
    > > > > > > is an easy way to make the new notes added appear at the top of the memo
    > > > > > > field rather than being added to the bottom of the memo, so that the notes
    > > > > > > appear in reverse chronological order?
    > > > > > >
    > > > > > > Eg: The most recent note goes to the top...
    > > > > > >
    > > > > > > 19-Oct-2005
    > > > > > > Posted a question on memo fields.
    > > > > > > 18-Oct-2005
    > > > > > > Set up client database.
    > > > > > > Etc...
    > > > > > >
    > > > > > > Many thanks (in advance)
    > > > > > > Suzanne

    > >
     
Loading...