Help!!! I can't update or write to a table. I am using SQL to write to a table. I am using 2 forms. The second form is unbound and after writing to the table I want to display the first form with changes made to the second form.
I am having trouble with memo fields. How can I allow the user to call up a record and then edit that record. Now I want to write that edit back to the table.
My heart ache is that I can't use a memow field with over 2048 characters and the unbound form and an UPDATE command.
On 11 Nov 2002 10:50:55 -0800, in message <855c5011.0211111050.228e5...@posting.google.com>, roryja...@yahoo.com
(Rory Jakes) wrote: >Help!!! I can't update or write to a table. I am using SQL to write to >a table. I am using 2 forms. The second form is unbound and after >writing to the table I want to display the first form with changes >made to the second form.
>I am having trouble with memo fields. How can I allow the user to call >up a record and then edit that record. Now I want to write that edit >back to the table.
>My heart ache is that I can't use a memow field with over 2048 >characters and the unbound form and an UPDATE command.
Hmmmm, I created a form and put 3072 'x's into a memo (sql text) and saved the record to see what SQL was generated (from Profiler), it wasn't conclusive.
>On 11 Nov 2002 10:50:55 -0800, in message ><855c5011.0211111050.228e5...@posting.google.com>, ><roryja...@yahoo.com >(Rory Jakes) wrote:
>>Help!!! I can't update or write to a table. I am using SQL to >>write to a table. I am using 2 forms. The second form is unbound >>and after writing to the table I want to display the first form >>with changes made to the second form.
>>I am having trouble with memo fields. How can I allow the user to >>call up a record and then edit that record. Now I want to write >>that edit back to the table.
>>My heart ache is that I can't use a memow field with over 2048 >>characters and the unbound form and an UPDATE command.
>Hmmmm, I created a form and put 3072 'x's into a memo (sql text) >and saved the record to see what SQL was generated (from >Profiler), it wasn't conclusive.
>When reading the record back I got 3072 'x's, but executing the >same statement from query analyser produced a record with just 16 >'x's, wierd.
>You might try using a ADO or DAO recordset to put the values back.
There is a bug somewhere between Jet and VBA that can cause concatenate memo field values to return gibberish. I've worked very hard on this one, thinking it was a ByRef problem, a limitation of string variables and any number of other things, but could never solve it. This current problem might be related, especially if there's any concatenation being done in code.
I have never had a problem with an unbound memo field in any other context, however.
We're seeing the same behavior in our applications. While I don't have an explicit solution, I do have some observations and a possible workaround which may or may not apply to your situation as well.
I've found that users can add records with more than 2048 characters in a memo field, but once a record has a memo field with more than 2048 characters, attempts to edit that record result in the "Could not update; currently locked by another session on this machine" error.
In our case, our applications typically work as follows:
1. user selects search criteria which causes a result set to be displayed (either as a datasheet, a continuous form or a listbox). In all cases, the result set is read only.
2. user selects one of the items in the result set to edit which causes another form to be displayed (sometimes bound, sometimes unbound) and causes the form showing the result set to be hidden
3. user edits data and clicks a "save" button which updates the record (either by saving the bound record, or via DAO), closes the edit form and returns the user to the form displaying the result set.
Recently (in the last week or so), I discovered that if I close the form that displays the result set, then saving the edited record does NOT cause the error, even if it contains more than 2048 characters.
This is not an ideal solution since I'd rather not have to requery to get the result set after the user saves the record or cancels the edit (the memo field(s) are not displayed in the result set and are not part of the search criteria, so it shouldn't matter if they're edited), but so far it seems to work.
For what it's worth,
Dexter.
[remove the NO SPAM from the e-mail address to reply]
On 11 Nov 2002 10:50:55 -0800, roryja...@yahoo.com (Rory Jakes) wrote:
>Help!!! I can't update or write to a table. I am using SQL to write to >a table. I am using 2 forms. The second form is unbound and after >writing to the table I want to display the first form with changes >made to the second form.
>I am having trouble with memo fields. How can I allow the user to call >up a record and then edit that record. Now I want to write that edit >back to the table.
>My heart ache is that I can't use a memow field with over 2048 >characters and the unbound form and an UPDATE command.
> We're seeing the same behavior in our applications. While I don't have > an explicit solution, I do have some observations and a possible > workaround which may or may not apply to your situation as well.
> I've found that users can add records with more than 2048 characters > in a memo field, but once a record has a memo field with more than > 2048 characters, attempts to edit that record result in the "Could not > update; currently locked by another session on this machine" error.
> In our case, our applications typically work as follows:
> 1. user selects search criteria which causes a result set to be > displayed (either as a datasheet, a continuous form or a listbox). In > all cases, the result set is read only.
> 2. user selects one of the items in the result set to edit which > causes another form to be displayed (sometimes bound, sometimes > unbound) and causes the form showing the result set to be hidden
> 3. user edits data and clicks a "save" button which updates the record > (either by saving the bound record, or via DAO), closes the edit form > and returns the user to the form displaying the result set.
> Recently (in the last week or so), I discovered that if I close the > form that displays the result set, then saving the edited record does > NOT cause the error, even if it contains more than 2048 characters.
> This is not an ideal solution since I'd rather not have to requery to > get the result set after the user saves the record or cancels the edit > (the memo field(s) are not displayed in the result set and are not > part of the search criteria, so it shouldn't matter if they're > edited), but so far it seems to work.
> For what it's worth,
> Dexter.
> [remove the NO SPAM from the e-mail address to reply]
> On 11 Nov 2002 10:50:55 -0800, roryja...@yahoo.com (Rory Jakes) wrote:
> >Help!!! I can't update or write to a table. I am using SQL to write to > >a table. I am using 2 forms. The second form is unbound and after > >writing to the table I want to display the first form with changes > >made to the second form.
> >I am having trouble with memo fields. How can I allow the user to call > >up a record and then edit that record. Now I want to write that edit > >back to the table.
> >My heart ache is that I can't use a memow field with over 2048 > >characters and the unbound form and an UPDATE command.
I am using the same scenario, except I am using an unbound form that is re-formatted to be used to Add or Edit depending on the user's choice. The point is that I discovered the same work around with closing a form that "displays the result set" before writing to a table, no problem! By the way, I am using DAO recordsets. Thanks for replying.
|
