f



"Record Deleted" error when appending to a memo field from a memo field

I found what is causing the error by removing various fields from the
Append Query.  I also get the error when doing an INSERT INTO.

I do not get the error if I do not list the memo field in the Insert
or have it in the Append Query.

This is not happening with the same field in another query (but I need
to check that again).

Any ideas why I am getting the "Record Deleted" error?  No records end
up getting written to the destination table.

I know it used to work.

-paulw
0
PW
10/13/2015 5:46:51 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

13 Replies
584 Views

Similar Articles

[PageSpeed] 8

On Tuesday, October 13, 2015 at 1:46:58 PM UTC-4, PW wrote:
> I found what is causing the error by removing various fields from the
> Append Query.  I also get the error when doing an INSERT INTO.
> 
> I do not get the error if I do not list the memo field in the Insert
> or have it in the Append Query.
> 
> This is not happening with the same field in another query (but I need
> to check that again).
> 
> Any ideas why I am getting the "Record Deleted" error?  No records end
> up getting written to the destination table.
> 
> I know it used to work.
> 
> -paulw

Are you sure the fields in both tables are the same?

What happens if you update record by record?
Dim r as recordset
Dim r1 as recordset
set r = currentdb.openrecordset("Table1",dbopensnapshot)
set r1 = currentdb.openrecordset("Table1",dbopenDynaset)
R.movefirst
Do while not r.eof
  r1.addnew
  r1!Field1 = r!Field1
  r1.update
  r.movenext
loop
0
Patrick
10/13/2015 7:02:45 PM
On Tue, 13 Oct 2015 12:02:45 -0700 (PDT), Patrick Finucane
<patrickfinucanetx@gmail.com> wrote:

>On Tuesday, October 13, 2015 at 1:46:58 PM UTC-4, PW wrote:
>> I found what is causing the error by removing various fields from the
>> Append Query.  I also get the error when doing an INSERT INTO.
>> 
>> I do not get the error if I do not list the memo field in the Insert
>> or have it in the Append Query.
>> 
>> This is not happening with the same field in another query (but I need
>> to check that again).
>> 
>> Any ideas why I am getting the "Record Deleted" error?  No records end
>> up getting written to the destination table.
>> 
>> I know it used to work.
>> 
>> -paulw
>
>Are you sure the fields in both tables are the same?
>
>What happens if you update record by record?
>Dim r as recordset
>Dim r1 as recordset
>set r = currentdb.openrecordset("Table1",dbopensnapshot)
>set r1 = currentdb.openrecordset("Table1",dbopenDynaset)
>R.movefirst
>Do while not r.eof
>  r1.addnew
>  r1!Field1 = r!Field1
>  r1.update
>  r.movenext
>loop

*-----

That is what I ended up doing.  And no errors.  But for some reason I
have a looping problem!

I see nothing wrong here yet.  But it wrote tens of thousands of the
same record to the output table before I had to hit ctrl-break.

rstCaseCorrespondence.MoveFirst
    
Do While Not rstCaseCorrespondence.EOF
    
    With rstWrkf
        
        rstWrkf.AddNew
            
            !correspondence_id =
rstCaseCorrespondence!correspondence_id
            !correspondence_type =
rstCaseCorrespondence!correspondence_type
            !notes = rstCaseCorrespondence!notes
            !followup_date = rstCaseCorrespondence!followup_date
            !start_time = rstCaseCorrespondence!start_time
            !end_time = rstCaseCorrespondence!end_time
            !outlook_yn = rstCaseCorrespondence!outlook_yn
            !RelatedPersonNospace =
rstCaseCorrespondence!RelatedPersonNospace
            !case_number = rstCaseCorrespondence!case_number
            !defendant = rstCaseCorrespondence!defendant
            !followup_yn = rstCaseCorrespondence!followup_yn
            
        rstWrkf.Update
          
    End With
    
    rstCaseCorrespondence.MoveNext

Loop
0
PW
10/14/2015 2:10:47 AM
On Tue, 13 Oct 2015 12:02:45 -0700 (PDT), Patrick Finucane
<patrickfinucanetx@gmail.com> wrote:

>On Tuesday, October 13, 2015 at 1:46:58 PM UTC-4, PW wrote:
>> I found what is causing the error by removing various fields from the
>> Append Query.  I also get the error when doing an INSERT INTO.
>> 
>> I do not get the error if I do not list the memo field in the Insert
>> or have it in the Append Query.
>> 
>> This is not happening with the same field in another query (but I need
>> to check that again).
>> 
>> Any ideas why I am getting the "Record Deleted" error?  No records end
>> up getting written to the destination table.
>> 
>> I know it used to work.
>> 
>> -paulw
>
>Are you sure the fields in both tables are the same?

Yes, they are both memo fields Patrick.With the same name.

-paulw
0
PW
10/14/2015 2:11:37 AM
On Tuesday, October 13, 2015 at 10:10:54 PM UTC-4, PW wrote:
> On Tue, 13 Oct 2015 12:02:45 -0700 (PDT), Patrick Finucane
> <patrickfinucanetx@gmail.com> wrote:
>=20
> >On Tuesday, October 13, 2015 at 1:46:58 PM UTC-4, PW wrote:
> >> I found what is causing the error by removing various fields from the
> >> Append Query.  I also get the error when doing an INSERT INTO.
> >>=20
> >> I do not get the error if I do not list the memo field in the Insert
> >> or have it in the Append Query.
> >>=20
> >> This is not happening with the same field in another query (but I need
> >> to check that again).
> >>=20
> >> Any ideas why I am getting the "Record Deleted" error?  No records end
> >> up getting written to the destination table.
> >>=20
> >> I know it used to work.
> >>=20
> >> -paulw
> >
> >Are you sure the fields in both tables are the same?
> >
> >What happens if you update record by record?
> >Dim r as recordset
> >Dim r1 as recordset
> >set r =3D currentdb.openrecordset("Table1",dbopensnapshot)
> >set r1 =3D currentdb.openrecordset("Table1",dbopenDynaset)
> >R.movefirst
> >Do while not r.eof
> >  r1.addnew
> >  r1!Field1 =3D r!Field1
> >  r1.update
> >  r.movenext
> >loop
>=20
> *-----
>=20
> That is what I ended up doing.  And no errors.  But for some reason I
> have a looping problem!
>=20
> I see nothing wrong here yet.  But it wrote tens of thousands of the
> same record to the output table before I had to hit ctrl-break.
>=20
> rstCaseCorrespondence.MoveFirst
>    =20
> Do While Not rstCaseCorrespondence.EOF
>    =20
>     With rstWrkf
>        =20
>         rstWrkf.AddNew
>            =20
>             !correspondence_id =3D
> rstCaseCorrespondence!correspondence_id
>             !correspondence_type =3D
> rstCaseCorrespondence!correspondence_type
>             !notes =3D rstCaseCorrespondence!notes
>             !followup_date =3D rstCaseCorrespondence!followup_date
>             !start_time =3D rstCaseCorrespondence!start_time
>             !end_time =3D rstCaseCorrespondence!end_time
>             !outlook_yn =3D rstCaseCorrespondence!outlook_yn
>             !RelatedPersonNospace =3D
> rstCaseCorrespondence!RelatedPersonNospace
>             !case_number =3D rstCaseCorrespondence!case_number
>             !defendant =3D rstCaseCorrespondence!defendant
>             !followup_yn =3D rstCaseCorrespondence!followup_yn
>            =20
>         rstWrkf.Update
>          =20
>     End With
>    =20
>     rstCaseCorrespondence.MoveNext
>=20
> Loop

I see nothing wrong with your code.  The With/EndWith lines aren't needed. =
 But you are addnewing/updating then moving to next record in your code.  M=
aybe set a counter and break if counter exceeded.  See if you get dupes of =
same record.  Also, check the validity of rstCaseCorrespondence.  Browse th=
e recordset, see if it what is expected.
0
Patrick
10/14/2015 1:38:31 PM

"PW" <emailaddyinsig@ifIremember.com> wrote in message 
news:87er1b94ldigtjtfohp5e6007ogrch27ti@4ax.com...
> On Tue, 13 Oct 2015 12:02:45 -0700 (PDT), Patrick Finucane
> <patrickfinucanetx@gmail.com> wrote:
>
>>On Tuesday, October 13, 2015 at 1:46:58 PM UTC-4, PW wrote:
>>> I found what is causing the error by removing various fields from the
>>> Append Query.  I also get the error when doing an INSERT INTO.
>>>
>>> I do not get the error if I do not list the memo field in the Insert
>>> or have it in the Append Query.
>>>
>>> This is not happening with the same field in another query (but I need
>>> to check that again).
>>>
>>> Any ideas why I am getting the "Record Deleted" error?  No records end
>>> up getting written to the destination table.
>>>
>>> I know it used to work.
>>>
>>> -paulw
>>
>>Are you sure the fields in both tables are the same?
>>
>>What happens if you update record by record?
>>Dim r as recordset
>>Dim r1 as recordset
>>set r = currentdb.openrecordset("Table1",dbopensnapshot)
>>set r1 = currentdb.openrecordset("Table1",dbopenDynaset)
>>R.movefirst
>>Do while not r.eof
>>  r1.addnew
>>  r1!Field1 = r!Field1
>>  r1.update
>>  r.movenext
>>loop
>
> *-----
>
> That is what I ended up doing.  And no errors.  But for some reason I
> have a looping problem!
>
> I see nothing wrong here yet.  But it wrote tens of thousands of the
> same record to the output table before I had to hit ctrl-break.
>
> rstCaseCorrespondence.MoveFirst
>
> Do While Not rstCaseCorrespondence.EOF
>
>    With rstWrkf
>
>        rstWrkf.AddNew
>
>            !correspondence_id =
> rstCaseCorrespondence!correspondence_id
>            !correspondence_type =
> rstCaseCorrespondence!correspondence_type
>            !notes = rstCaseCorrespondence!notes
>            !followup_date = rstCaseCorrespondence!followup_date
>            !start_time = rstCaseCorrespondence!start_time
>            !end_time = rstCaseCorrespondence!end_time
>            !outlook_yn = rstCaseCorrespondence!outlook_yn
>            !RelatedPersonNospace =
> rstCaseCorrespondence!RelatedPersonNospace
>            !case_number = rstCaseCorrespondence!case_number
>            !defendant = rstCaseCorrespondence!defendant
>            !followup_yn = rstCaseCorrespondence!followup_yn
>
>        rstWrkf.Update
>
>    End With
>
>    rstCaseCorrespondence.MoveNext
>
> Loop

I would have to believe the original problem is caused by corruption. Have 
you done a compact/repair on the database. As for your loop, do you have a 
"On Error resume next" statement before the loop. If so add error checking 
and retest. 

0
Ron
10/14/2015 3:39:27 PM
On Wed, 14 Oct 2015 06:38:31 -0700 (PDT), Patrick Finucane
<patrickfinucanetx@gmail.com> wrote:

>On Tuesday, October 13, 2015 at 10:10:54 PM UTC-4, PW wrote:
>> On Tue, 13 Oct 2015 12:02:45 -0700 (PDT), Patrick Finucane
>> <patrickfinucanetx@gmail.com> wrote:
>> 
>> >On Tuesday, October 13, 2015 at 1:46:58 PM UTC-4, PW wrote:
>> >> I found what is causing the error by removing various fields from the
>> >> Append Query.  I also get the error when doing an INSERT INTO.
>> >> 
>> >> I do not get the error if I do not list the memo field in the Insert
>> >> or have it in the Append Query.
>> >> 
>> >> This is not happening with the same field in another query (but I need
>> >> to check that again).
>> >> 
>> >> Any ideas why I am getting the "Record Deleted" error?  No records end
>> >> up getting written to the destination table.
>> >> 
>> >> I know it used to work.
>> >> 
>> >> -paulw
>> >
>> >Are you sure the fields in both tables are the same?
>> >
>> >What happens if you update record by record?
>> >Dim r as recordset
>> >Dim r1 as recordset
>> >set r = currentdb.openrecordset("Table1",dbopensnapshot)
>> >set r1 = currentdb.openrecordset("Table1",dbopenDynaset)
>> >R.movefirst
>> >Do while not r.eof
>> >  r1.addnew
>> >  r1!Field1 = r!Field1
>> >  r1.update
>> >  r.movenext
>> >loop
>> 
>> *-----
>> 
>> That is what I ended up doing.  And no errors.  But for some reason I
>> have a looping problem!
>> 
>> I see nothing wrong here yet.  But it wrote tens of thousands of the
>> same record to the output table before I had to hit ctrl-break.
>> 
>> rstCaseCorrespondence.MoveFirst
>>     
>> Do While Not rstCaseCorrespondence.EOF
>>     
>>     With rstWrkf
>>         
>>         rstWrkf.AddNew
>>             
>>             !correspondence_id =
>> rstCaseCorrespondence!correspondence_id
>>             !correspondence_type =
>> rstCaseCorrespondence!correspondence_type
>>             !notes = rstCaseCorrespondence!notes
>>             !followup_date = rstCaseCorrespondence!followup_date
>>             !start_time = rstCaseCorrespondence!start_time
>>             !end_time = rstCaseCorrespondence!end_time
>>             !outlook_yn = rstCaseCorrespondence!outlook_yn
>>             !RelatedPersonNospace =
>> rstCaseCorrespondence!RelatedPersonNospace
>>             !case_number = rstCaseCorrespondence!case_number
>>             !defendant = rstCaseCorrespondence!defendant
>>             !followup_yn = rstCaseCorrespondence!followup_yn
>>             
>>         rstWrkf.Update
>>           
>>     End With
>>     
>>     rstCaseCorrespondence.MoveNext
>> 
>> Loop
>
>I see nothing wrong with your code.  The With/EndWith lines aren't needed.  But you are addnewing/updating then moving to next record in your code. 

That is what I want to do.  Write a record to the work table for each
record the query produces.


> Maybe set a counter and break if counter exceeded.  See if you get dupes of same record.  Also, check the validity of rstCaseCorrespondence.  Browse the recordset, see if it what is expected.

The query is working fine.  What is happenening is that only the first
record in the recordset is getting written over and over again, tens
of thousands of times!  So you are correct.  I am getting duplicates
of the first record.

-paulw
0
PW
10/14/2015 5:22:01 PM
On Wed, 14 Oct 2015 10:39:27 -0500, "Ron Paii" <None@none.com> wrote:

>
>
>"PW" <emailaddyinsig@ifIremember.com> wrote in message 
>news:87er1b94ldigtjtfohp5e6007ogrch27ti@4ax.com...
>> On Tue, 13 Oct 2015 12:02:45 -0700 (PDT), Patrick Finucane
>> <patrickfinucanetx@gmail.com> wrote:
>>
>>>On Tuesday, October 13, 2015 at 1:46:58 PM UTC-4, PW wrote:
>>>> I found what is causing the error by removing various fields from the
>>>> Append Query.  I also get the error when doing an INSERT INTO.
>>>>
>>>> I do not get the error if I do not list the memo field in the Insert
>>>> or have it in the Append Query.
>>>>
>>>> This is not happening with the same field in another query (but I need
>>>> to check that again).
>>>>
>>>> Any ideas why I am getting the "Record Deleted" error?  No records end
>>>> up getting written to the destination table.
>>>>
>>>> I know it used to work.
>>>>
>>>> -paulw
>>>
>>>Are you sure the fields in both tables are the same?
>>>
>>>What happens if you update record by record?
>>>Dim r as recordset
>>>Dim r1 as recordset
>>>set r = currentdb.openrecordset("Table1",dbopensnapshot)
>>>set r1 = currentdb.openrecordset("Table1",dbopenDynaset)
>>>R.movefirst
>>>Do while not r.eof
>>>  r1.addnew
>>>  r1!Field1 = r!Field1
>>>  r1.update
>>>  r.movenext
>>>loop
>>
>> *-----
>>
>> That is what I ended up doing.  And no errors.  But for some reason I
>> have a looping problem!
>>
>> I see nothing wrong here yet.  But it wrote tens of thousands of the
>> same record to the output table before I had to hit ctrl-break.
>>
>> rstCaseCorrespondence.MoveFirst
>>
>> Do While Not rstCaseCorrespondence.EOF
>>
>>    With rstWrkf
>>
>>        rstWrkf.AddNew
>>
>>            !correspondence_id =
>> rstCaseCorrespondence!correspondence_id
>>            !correspondence_type =
>> rstCaseCorrespondence!correspondence_type
>>            !notes = rstCaseCorrespondence!notes
>>            !followup_date = rstCaseCorrespondence!followup_date
>>            !start_time = rstCaseCorrespondence!start_time
>>            !end_time = rstCaseCorrespondence!end_time
>>            !outlook_yn = rstCaseCorrespondence!outlook_yn
>>            !RelatedPersonNospace =
>> rstCaseCorrespondence!RelatedPersonNospace
>>            !case_number = rstCaseCorrespondence!case_number
>>            !defendant = rstCaseCorrespondence!defendant
>>            !followup_yn = rstCaseCorrespondence!followup_yn
>>
>>        rstWrkf.Update
>>
>>    End With
>>
>>    rstCaseCorrespondence.MoveNext
>>
>> Loop
>
>I would have to believe the original problem is caused by corruption. Have 
>you done a compact/repair on the database.

Yup I did.

> As for your loop, do you have a 
>"On Error resume next" statement before the loop. If so add error checking 
>and retest. 

No, I do not.  What would that do?

Thanks Ron.

-paulw
0
PW
10/14/2015 5:23:08 PM

"PW" <emailaddyinsig@ifIremember.com> wrote in message 
news:0q3t1b5honvikosk3rq8jgembbhdg96dn0@4ax.com...
> On Wed, 14 Oct 2015 10:39:27 -0500, "Ron Paii" <None@none.com> wrote:
>
>>
>>
>>"PW" <emailaddyinsig@ifIremember.com> wrote in message
>>news:87er1b94ldigtjtfohp5e6007ogrch27ti@4ax.com...
>>> On Tue, 13 Oct 2015 12:02:45 -0700 (PDT), Patrick Finucane
>>> <patrickfinucanetx@gmail.com> wrote:
>>>
>>>>On Tuesday, October 13, 2015 at 1:46:58 PM UTC-4, PW wrote:
>>>>> I found what is causing the error by removing various fields from the
>>>>> Append Query.  I also get the error when doing an INSERT INTO.
>>>>>
>>>>> I do not get the error if I do not list the memo field in the Insert
>>>>> or have it in the Append Query.
>>>>>
>>>>> This is not happening with the same field in another query (but I need
>>>>> to check that again).
>>>>>
>>>>> Any ideas why I am getting the "Record Deleted" error?  No records end
>>>>> up getting written to the destination table.
>>>>>
>>>>> I know it used to work.
>>>>>
>>>>> -paulw
>>>>
>>>>Are you sure the fields in both tables are the same?
>>>>
>>>>What happens if you update record by record?
>>>>Dim r as recordset
>>>>Dim r1 as recordset
>>>>set r = currentdb.openrecordset("Table1",dbopensnapshot)
>>>>set r1 = currentdb.openrecordset("Table1",dbopenDynaset)
>>>>R.movefirst
>>>>Do while not r.eof
>>>>  r1.addnew
>>>>  r1!Field1 = r!Field1
>>>>  r1.update
>>>>  r.movenext
>>>>loop
>>>
>>> *-----
>>>
>>> That is what I ended up doing.  And no errors.  But for some reason I
>>> have a looping problem!
>>>
>>> I see nothing wrong here yet.  But it wrote tens of thousands of the
>>> same record to the output table before I had to hit ctrl-break.
>>>
>>> rstCaseCorrespondence.MoveFirst
>>>
>>> Do While Not rstCaseCorrespondence.EOF
>>>
>>>    With rstWrkf
>>>
>>>        rstWrkf.AddNew
>>>
>>>            !correspondence_id =
>>> rstCaseCorrespondence!correspondence_id
>>>            !correspondence_type =
>>> rstCaseCorrespondence!correspondence_type
>>>            !notes = rstCaseCorrespondence!notes
>>>            !followup_date = rstCaseCorrespondence!followup_date
>>>            !start_time = rstCaseCorrespondence!start_time
>>>            !end_time = rstCaseCorrespondence!end_time
>>>            !outlook_yn = rstCaseCorrespondence!outlook_yn
>>>            !RelatedPersonNospace =
>>> rstCaseCorrespondence!RelatedPersonNospace
>>>            !case_number = rstCaseCorrespondence!case_number
>>>            !defendant = rstCaseCorrespondence!defendant
>>>            !followup_yn = rstCaseCorrespondence!followup_yn
>>>
>>>        rstWrkf.Update
>>>
>>>    End With
>>>
>>>    rstCaseCorrespondence.MoveNext
>>>
>>> Loop
>>
>>I would have to believe the original problem is caused by corruption. Have
>>you done a compact/repair on the database.
>
> Yup I did.
>
>> As for your loop, do you have a
>>"On Error resume next" statement before the loop. If so add error checking
>>and retest.
>
> No, I do not.  What would that do?
>

"resume next" would hide an error caused by the MoveNext, causing EOF never 
being reached. If the function has no error trapping, VBA looks up the call 
tree for trapping in other functions. 

0
Ron
10/14/2015 5:28:57 PM
>>>> That is what I ended up doing.  And no errors.  But for some reason I
>>>> have a looping problem!
>>>>
>>>> I see nothing wrong here yet.  But it wrote tens of thousands of the
>>>> same record to the output table before I had to hit ctrl-break.
>>>>
>>>> rstCaseCorrespondence.MoveFirst
>>>>
>>>> Do While Not rstCaseCorrespondence.EOF
>>>>
>>>>    With rstWrkf
>>>>
>>>>        rstWrkf.AddNew
>>>>
>>>>            !correspondence_id >>> rstCaseCorrespondence!correspondence_id
>>>>            !correspondence_type >>> rstCaseCorrespondence!correspondence_type
>>>>            !notes = rstCaseCorrespondence!notes
>>>>            !followup_date = rstCaseCorrespondence!followup_date
>>>>            !start_time = rstCaseCorrespondence!start_time
>>>>            !end_time = rstCaseCorrespondence!end_time
>>>>            !outlook_yn = rstCaseCorrespondence!outlook_yn
>>>>            !RelatedPersonNospace >>> rstCaseCorrespondence!RelatedPersonNospace
>>>>            !case_number = rstCaseCorrespondence!case_number
>>>>            !defendant = rstCaseCorrespondence!defendant
>>>>            !followup_yn = rstCaseCorrespondence!followup_yn
>>>>
>>>>        rstWrkf.Update
>>>>
>>>>    End With
>>>>
>>>>    rstCaseCorrespondence.MoveNext
>>>>
>>>> Loop
>>>
>>>I would have to believe the original problem is caused by corruption. Have
>>>you done a compact/repair on the database.
>>
>> Yup I did.
>>
>>> As for your loop, do you have a
>>>"On Error resume next" statement before the loop. If so add error checking
>>>and retest.
>>
>> No, I do not.  What would that do?
>>
> 
> "resume next" would hide an error caused by the MoveNext, causing EOF
> never being reached. If the function has no error trapping, VBA looks up
> the call tree for trapping in other functions.
> 
> 
I suppose the Memo field is set up correctly with
Required = No
Allow Zero Length = Yes
Append Only ???? Don't thimk it matters, but you never know.

If not there could be an untrapped error.

Phil

0
Phil
10/14/2015 6:32:01 PM
On Wednesday, October 14, 2015 at 1:22:10 PM UTC-4, PW wrote:
> On Wed, 14 Oct 2015 06:38:31 -0700 (PDT), Patrick Finucane
> <patrickfinucanetx@gmail.com> wrote:
> 
> >On Tuesday, October 13, 2015 at 10:10:54 PM UTC-4, PW wrote:
> >> On Tue, 13 Oct 2015 12:02:45 -0700 (PDT), Patrick Finucane
> >> <patrickfinucanetx@gmail.com> wrote:
> >> 
> >> >On Tuesday, October 13, 2015 at 1:46:58 PM UTC-4, PW wrote:
> >> >> I found what is causing the error by removing various fields from the
> >> >> Append Query.  I also get the error when doing an INSERT INTO.
> >> >> 
> >> >> I do not get the error if I do not list the memo field in the Insert
> >> >> or have it in the Append Query.
> >> >> 
> >> >> This is not happening with the same field in another query (but I need
> >> >> to check that again).
> >> >> 
> >> >> Any ideas why I am getting the "Record Deleted" error?  No records end
> >> >> up getting written to the destination table.
> >> >> 
> >> >> I know it used to work.
> >> >> 
> >> >> -paulw
> >> >
> >> >Are you sure the fields in both tables are the same?
> >> >
> >> >What happens if you update record by record?
> >> >Dim r as recordset
> >> >Dim r1 as recordset
> >> >set r = currentdb.openrecordset("Table1",dbopensnapshot)
> >> >set r1 = currentdb.openrecordset("Table1",dbopenDynaset)
> >> >R.movefirst
> >> >Do while not r.eof
> >> >  r1.addnew
> >> >  r1!Field1 = r!Field1
> >> >  r1.update
> >> >  r.movenext
> >> >loop
> >> 
> >> *-----
> >> 
> >> That is what I ended up doing.  And no errors.  But for some reason I
> >> have a looping problem!
> >> 
> >> I see nothing wrong here yet.  But it wrote tens of thousands of the
> >> same record to the output table before I had to hit ctrl-break.
> >> 
> >> rstCaseCorrespondence.MoveFirst
> >>     
> >> Do While Not rstCaseCorrespondence.EOF
> >>     
> >>     With rstWrkf
> >>         
> >>         rstWrkf.AddNew
> >>             
> >>             !correspondence_id =
> >> rstCaseCorrespondence!correspondence_id
> >>             !correspondence_type =
> >> rstCaseCorrespondence!correspondence_type
> >>             !notes = rstCaseCorrespondence!notes
> >>             !followup_date = rstCaseCorrespondence!followup_date
> >>             !start_time = rstCaseCorrespondence!start_time
> >>             !end_time = rstCaseCorrespondence!end_time
> >>             !outlook_yn = rstCaseCorrespondence!outlook_yn
> >>             !RelatedPersonNospace =
> >> rstCaseCorrespondence!RelatedPersonNospace
> >>             !case_number = rstCaseCorrespondence!case_number
> >>             !defendant = rstCaseCorrespondence!defendant
> >>             !followup_yn = rstCaseCorrespondence!followup_yn
> >>             
> >>         rstWrkf.Update
> >>           
> >>     End With
> >>     
> >>     rstCaseCorrespondence.MoveNext
> >> 
> >> Loop
> >
> >I see nothing wrong with your code.  The With/EndWith lines aren't needed.  But you are addnewing/updating then moving to next record in your code. 
> 
> That is what I want to do.  Write a record to the work table for each
> record the query produces.
> 
> 
> > Maybe set a counter and break if counter exceeded.  See if you get dupes of same record.  Also, check the validity of rstCaseCorrespondence.  Browse the recordset, see if it what is expected.
> 
> The query is working fine.  What is happenening is that only the first
> record in the recordset is getting written over and over again, tens
> of thousands of times!  So you are correct.  I am getting duplicates
> of the first record.
> 
> -paulw

I think I'd put a
 Stop
in your code.  Then have a
 Debug.print YourMemoFieldName
when you get to the MemoFld append.  See if the memos are diff or the same. Within a cpl of records you'll see if the record is the same.  I'd look at the other fields to see if they change as well.
0
Patrick
10/14/2015 7:38:35 PM
On Wednesday, October 14, 2015 at 1:22:10 PM UTC-4, PW wrote:
> On Wed, 14 Oct 2015 06:38:31 -0700 (PDT), Patrick Finucane
> <patrickfinucanetx@gmail.com> wrote:
>=20
> >On Tuesday, October 13, 2015 at 10:10:54 PM UTC-4, PW wrote:
> >> On Tue, 13 Oct 2015 12:02:45 -0700 (PDT), Patrick Finucane
> >> <patrickfinucanetx@gmail.com> wrote:
> >>=20
> >> >On Tuesday, October 13, 2015 at 1:46:58 PM UTC-4, PW wrote:
> >> >> I found what is causing the error by removing various fields from t=
he
> >> >> Append Query.  I also get the error when doing an INSERT INTO.
> >> >>=20
> >> >> I do not get the error if I do not list the memo field in the Inser=
t
> >> >> or have it in the Append Query.
> >> >>=20
> >> >> This is not happening with the same field in another query (but I n=
eed
> >> >> to check that again).
> >> >>=20
> >> >> Any ideas why I am getting the "Record Deleted" error?  No records =
end
> >> >> up getting written to the destination table.
> >> >>=20
> >> >> I know it used to work.
> >> >>=20
> >> >> -paulw
> >> >
> >> >Are you sure the fields in both tables are the same?
> >> >
> >> >What happens if you update record by record?
> >> >Dim r as recordset
> >> >Dim r1 as recordset
> >> >set r =3D currentdb.openrecordset("Table1",dbopensnapshot)
> >> >set r1 =3D currentdb.openrecordset("Table1",dbopenDynaset)
> >> >R.movefirst
> >> >Do while not r.eof
> >> >  r1.addnew
> >> >  r1!Field1 =3D r!Field1
> >> >  r1.update
> >> >  r.movenext
> >> >loop
> >>=20
> >> *-----
> >>=20
> >> That is what I ended up doing.  And no errors.  But for some reason I
> >> have a looping problem!
> >>=20
> >> I see nothing wrong here yet.  But it wrote tens of thousands of the
> >> same record to the output table before I had to hit ctrl-break.
> >>=20
> >> rstCaseCorrespondence.MoveFirst
> >>    =20
> >> Do While Not rstCaseCorrespondence.EOF
> >>    =20
> >>     With rstWrkf
> >>        =20
> >>         rstWrkf.AddNew
> >>            =20
> >>             !correspondence_id =3D
> >> rstCaseCorrespondence!correspondence_id
> >>             !correspondence_type =3D
> >> rstCaseCorrespondence!correspondence_type
> >>             !notes =3D rstCaseCorrespondence!notes
> >>             !followup_date =3D rstCaseCorrespondence!followup_date
> >>             !start_time =3D rstCaseCorrespondence!start_time
> >>             !end_time =3D rstCaseCorrespondence!end_time
> >>             !outlook_yn =3D rstCaseCorrespondence!outlook_yn
> >>             !RelatedPersonNospace =3D
> >> rstCaseCorrespondence!RelatedPersonNospace
> >>             !case_number =3D rstCaseCorrespondence!case_number
> >>             !defendant =3D rstCaseCorrespondence!defendant
> >>             !followup_yn =3D rstCaseCorrespondence!followup_yn
> >>            =20
> >>         rstWrkf.Update
> >>          =20
> >>     End With
> >>    =20
> >>     rstCaseCorrespondence.MoveNext
> >>=20
> >> Loop
> >
> >I see nothing wrong with your code.  The With/EndWith lines aren't neede=
d.  But you are addnewing/updating then moving to next record in your code.=
=20
>=20
> That is what I want to do.  Write a record to the work table for each
> record the query produces.
>=20
>=20
> > Maybe set a counter and break if counter exceeded.  See if you get dupe=
s of same record.  Also, check the validity of rstCaseCorrespondence.  Brow=
se the recordset, see if it what is expected.
>=20
> The query is working fine.  What is happenening is that only the first
> record in the recordset is getting written over and over again, tens
> of thousands of times!  So you are correct.  I am getting duplicates
> of the first record.
>=20
> -paulw

The more I think about it, the more I think you have corruption.  Maybe the=
 corruption is in the first record.  There should be no reason that MoveNex=
t doesn't move to the next record but stays on the same record.  That is wh=
y you need to debug with STOP in your code.

I would suggest you compact your BE database

Are you sure your db doesn't have duplicate records?  Stop will help with t=
hat question.
0
Patrick
10/14/2015 8:24:14 PM
On Wed, 14 Oct 2015 12:38:35 -0700 (PDT), Patrick Finucane
<patrickfinucanetx@gmail.com> wrote:

>On Wednesday, October 14, 2015 at 1:22:10 PM UTC-4, PW wrote:
>> On Wed, 14 Oct 2015 06:38:31 -0700 (PDT), Patrick Finucane
>> <patrickfinucanetx@gmail.com> wrote:
>> 
>> >On Tuesday, October 13, 2015 at 10:10:54 PM UTC-4, PW wrote:
>> >> On Tue, 13 Oct 2015 12:02:45 -0700 (PDT), Patrick Finucane
>> >> <patrickfinucanetx@gmail.com> wrote:
>> >> 
>> >> >On Tuesday, October 13, 2015 at 1:46:58 PM UTC-4, PW wrote:
>> >> >> I found what is causing the error by removing various fields from the
>> >> >> Append Query.  I also get the error when doing an INSERT INTO.
>> >> >> 
>> >> >> I do not get the error if I do not list the memo field in the Insert
>> >> >> or have it in the Append Query.
>> >> >> 
>> >> >> This is not happening with the same field in another query (but I need
>> >> >> to check that again).
>> >> >> 
>> >> >> Any ideas why I am getting the "Record Deleted" error?  No records end
>> >> >> up getting written to the destination table.
>> >> >> 
>> >> >> I know it used to work.
>> >> >> 
>> >> >> -paulw
>> >> >
>> >> >Are you sure the fields in both tables are the same?
>> >> >
>> >> >What happens if you update record by record?
>> >> >Dim r as recordset
>> >> >Dim r1 as recordset
>> >> >set r = currentdb.openrecordset("Table1",dbopensnapshot)
>> >> >set r1 = currentdb.openrecordset("Table1",dbopenDynaset)
>> >> >R.movefirst
>> >> >Do while not r.eof
>> >> >  r1.addnew
>> >> >  r1!Field1 = r!Field1
>> >> >  r1.update
>> >> >  r.movenext
>> >> >loop
>> >> 
>> >> *-----
>> >> 
>> >> That is what I ended up doing.  And no errors.  But for some reason I
>> >> have a looping problem!
>> >> 
>> >> I see nothing wrong here yet.  But it wrote tens of thousands of the
>> >> same record to the output table before I had to hit ctrl-break.
>> >> 
>> >> rstCaseCorrespondence.MoveFirst
>> >>     
>> >> Do While Not rstCaseCorrespondence.EOF
>> >>     
>> >>     With rstWrkf
>> >>         
>> >>         rstWrkf.AddNew
>> >>             
>> >>             !correspondence_id =
>> >> rstCaseCorrespondence!correspondence_id
>> >>             !correspondence_type =
>> >> rstCaseCorrespondence!correspondence_type
>> >>             !notes = rstCaseCorrespondence!notes
>> >>             !followup_date = rstCaseCorrespondence!followup_date
>> >>             !start_time = rstCaseCorrespondence!start_time
>> >>             !end_time = rstCaseCorrespondence!end_time
>> >>             !outlook_yn = rstCaseCorrespondence!outlook_yn
>> >>             !RelatedPersonNospace =
>> >> rstCaseCorrespondence!RelatedPersonNospace
>> >>             !case_number = rstCaseCorrespondence!case_number
>> >>             !defendant = rstCaseCorrespondence!defendant
>> >>             !followup_yn = rstCaseCorrespondence!followup_yn
>> >>             
>> >>         rstWrkf.Update
>> >>           
>> >>     End With
>> >>     
>> >>     rstCaseCorrespondence.MoveNext
>> >> 
>> >> Loop
>> >
>> >I see nothing wrong with your code.  The With/EndWith lines aren't needed.  But you are addnewing/updating then moving to next record in your code. 
>> 
>> That is what I want to do.  Write a record to the work table for each
>> record the query produces.
>> 
>> 
>> > Maybe set a counter and break if counter exceeded.  See if you get dupes of same record.  Also, check the validity of rstCaseCorrespondence.  Browse the recordset, see if it what is expected.
>> 
>> The query is working fine.  What is happenening is that only the first
>> record in the recordset is getting written over and over again, tens
>> of thousands of times!  So you are correct.  I am getting duplicates
>> of the first record.
>> 
>> -paulw
>
>I think I'd put a
> Stop
>in your code.  Then have a
> Debug.print YourMemoFieldName
>when you get to the MemoFld append.  See if the memos are diff or the same. Within a cpl of records you'll see if the record is the same.  I'd look at the other fields to see if they change as well.
*---

But it is a DoCmd.RunSQL....  Not sure if that is possible

Thanks,

-paulw
0
PW
10/15/2015 2:23:06 AM
On Wed, 14 Oct 2015 13:24:14 -0700 (PDT), Patrick Finucane
<patrickfinucanetx@gmail.com> wrote:

>On Wednesday, October 14, 2015 at 1:22:10 PM UTC-4, PW wrote:
>> On Wed, 14 Oct 2015 06:38:31 -0700 (PDT), Patrick Finucane
>> <patrickfinucanetx@gmail.com> wrote:
>> 
>> >On Tuesday, October 13, 2015 at 10:10:54 PM UTC-4, PW wrote:
>> >> On Tue, 13 Oct 2015 12:02:45 -0700 (PDT), Patrick Finucane
>> >> <patrickfinucanetx@gmail.com> wrote:
>> >> 
>> >> >On Tuesday, October 13, 2015 at 1:46:58 PM UTC-4, PW wrote:
>> >> >> I found what is causing the error by removing various fields from the
>> >> >> Append Query.  I also get the error when doing an INSERT INTO.
>> >> >> 
>> >> >> I do not get the error if I do not list the memo field in the Insert
>> >> >> or have it in the Append Query.
>> >> >> 
>> >> >> This is not happening with the same field in another query (but I need
>> >> >> to check that again).
>> >> >> 
>> >> >> Any ideas why I am getting the "Record Deleted" error?  No records end
>> >> >> up getting written to the destination table.
>> >> >> 
>> >> >> I know it used to work.
>> >> >> 
>> >> >> -paulw
>> >> >
>> >> >Are you sure the fields in both tables are the same?
>> >> >
>> >> >What happens if you update record by record?
>> >> >Dim r as recordset
>> >> >Dim r1 as recordset
>> >> >set r = currentdb.openrecordset("Table1",dbopensnapshot)
>> >> >set r1 = currentdb.openrecordset("Table1",dbopenDynaset)
>> >> >R.movefirst
>> >> >Do while not r.eof
>> >> >  r1.addnew
>> >> >  r1!Field1 = r!Field1
>> >> >  r1.update
>> >> >  r.movenext
>> >> >loop
>> >> 
>> >> *-----
>> >> 
>> >> That is what I ended up doing.  And no errors.  But for some reason I
>> >> have a looping problem!
>> >> 
>> >> I see nothing wrong here yet.  But it wrote tens of thousands of the
>> >> same record to the output table before I had to hit ctrl-break.
>> >> 
>> >> rstCaseCorrespondence.MoveFirst
>> >>     
>> >> Do While Not rstCaseCorrespondence.EOF
>> >>     
>> >>     With rstWrkf
>> >>         
>> >>         rstWrkf.AddNew
>> >>             
>> >>             !correspondence_id =
>> >> rstCaseCorrespondence!correspondence_id
>> >>             !correspondence_type =
>> >> rstCaseCorrespondence!correspondence_type
>> >>             !notes = rstCaseCorrespondence!notes
>> >>             !followup_date = rstCaseCorrespondence!followup_date
>> >>             !start_time = rstCaseCorrespondence!start_time
>> >>             !end_time = rstCaseCorrespondence!end_time
>> >>             !outlook_yn = rstCaseCorrespondence!outlook_yn
>> >>             !RelatedPersonNospace =
>> >> rstCaseCorrespondence!RelatedPersonNospace
>> >>             !case_number = rstCaseCorrespondence!case_number
>> >>             !defendant = rstCaseCorrespondence!defendant
>> >>             !followup_yn = rstCaseCorrespondence!followup_yn
>> >>             
>> >>         rstWrkf.Update
>> >>           
>> >>     End With
>> >>     
>> >>     rstCaseCorrespondence.MoveNext
>> >> 
>> >> Loop
>> >
>> >I see nothing wrong with your code.  The With/EndWith lines aren't needed.  But you are addnewing/updating then moving to next record in your code. 
>> 
>> That is what I want to do.  Write a record to the work table for each
>> record the query produces.
>> 
>> 
>> > Maybe set a counter and break if counter exceeded.  See if you get dupes of same record.  Also, check the validity of rstCaseCorrespondence.  Browse the recordset, see if it what is expected.
>> 
>> The query is working fine.  What is happenening is that only the first
>> record in the recordset is getting written over and over again, tens
>> of thousands of times!  So you are correct.  I am getting duplicates
>> of the first record.
>> 
>> -paulw
>
>The more I think about it, the more I think you have corruption.  Maybe the corruption is in the first record.  There should be no reason that MoveNext doesn't move to the next record but stays on the same record.  That is why you need to debug with STOP in your code.
>
>I would suggest you compact your BE database
>
>Are you sure your db doesn't have duplicate records?  Stop will help with that question.

*--

I have run a compact and repair (I saw that on a Google find).  No
duplicates when I look at the query results.

-paulw
0
PW
10/15/2015 2:26:16 AM
Reply:

Similar Artilces:

Compare 2 fields & delete everything in "field 2" that occurs in "field 1"
Is it possible to compare 2 fields and delete everything in "field 2" that also occurs in "field 1" ? FIELD1 800-555-1212 for Waterpocket Fold FIELD2 800-555-1212 for Red Circle Cliffs New FIELD2 Red Circle Cliffs Any suggestions are most welcome I think you'd have to create a calc field that compares the two fields: If( field1 = field2, "duplicate", "other") Then you could search within the calc field for the word "duplicate". Once you attain your desired found set, then perform a re...

Attaching to MySQL tables from MS Access
Is an apostrophe a character of special significance to MySQL in a way that would cause "Bob's dog" to become translated into a 12-character string when typed into a MySQL memo field? If I type Bob's dog into an Access memo field, I get a string that is 9-characters long. When I read "Bob's dog" from a memo field in a MySQL table attacted to MS Access via MyODBC driver, it displays as "Bob&#39s dog" - a twelve character string. the &#39 looks like a HEX character code value designator. Can someone provide me with a logical explanation of why I...

Deleted Fields always shown on "Database Fields" List
Just a question about something that has always annoyed me... Whenever you create a field in a form and save it, it is then shown on the "Database Fields" list. But then even you delete the field, or rename it (if there was a mispelling for example), the fields are always kept on the database fields list. This makes the database very 'unclean' specially when there have been major design changes. IS THERE ANY WAY TO STOP THIS HAPPENING OR CLEANING THIS UP??? Thanks. ...

Oracle
Crystal reports won't allow me to do anything with that field, i.e., I can place it on the report, but can't create a formula to suppress it if it's a particular value. The error message I receive is: "Blob field or memo field cannot be used in a formula." This database field is CLOB. Any help would be grateful.... FYR: Oracle 9.2 - Crytal Report 8.5 Thanks in Advance, Uma "Uma Muthu" <Mail@NoCoolSpamBlue.com> wrote: >Crystal reports won't allow me to do anything with that field, >i.e., I can place it on the report,...

Why function TYPE returns "C" not "M" on memo fields in SQLRDD ?
hi, as in subject am. Dear onet: On Oct 18, 3:38 am, "onet" <A MO...@HOTMAIL.COM> wrote: > hi, > as in subject Since the purpose of SQL is (in part) to hide implementation details, do you really need to know that SQL stores data differently than xBase does? The RDD is supposed to smooth out the interface. Hopefully text was put into the field to begin with. Why does it matter to your program? David A. Smith Dear onet: On Oct 18, 3:38 am, "onet" <A MO...@HOTMAIL.COM> wrote: > hi, > as in subject Since the purpose of SQL is (in part) to hi...

Please help! A2K Memo field showing error "Too much text to edit"
The error does not have an error number. It's not even a normal Access error message box. Hope someone has some insight to this problem. I have a form that contains a large (5" x 4") text box bound to a memo field. This field allows users to type in the text of an email to be sent through Outlook (via automation). I ONE user who send me his back end data base where he has inserted several two - three page emails. When scrolling through the records, two of about twenty records shows and error that says "Too much text to edit". This error occurs when the cursor is ...

Specifying "target field" by calculation (in "Set Field")
In order to make a Find script as generic as possible, I want to be able to pass it (as parameters) a layout name, a field name, and a field value. So the script will go to the layout (using P1), set the field (using P2 and P3), and do the find. Trouble is, I can't see how to use P2 in the Set Field script step - it seems only to allow me to specify an actual field name (rather than a field name calculated from the parameter). Any suggestions? (FM9, PC). Carla. "Carla" <carla_sloan@yahoo.co.uk> schreef in bericht news:5eb843e6-9286-4051-a506-f348eebb6cba@e4g2000hsg....

Too few parameters expected 2 error occurs after migrating "MS Access 2000" to "MS SQL Server"
Hello all I get Too few parameters expected 2 error and "The MS Jet Database engine cannot find the input table or query "myTempTablename". Make sure it exists and that its name is spelled correctly. 3078" after I migrated "MS Access 2000" to "MS SQL Server 2000" and relinked all linked tables. The front-end interface of my application is MS Access 2000. The back-end is MS SQL Server 2000. After I migrated the database and relinked all linked tables, "Too few parameters expected 2" and "input table or query "myTempTablename" 30...

Error 2465 "Access cannot find the field..."
Just thought I'd share my experience today with this error message. I received it when doing an .update on a recordset .add. The fieldname that the error message included wasn't even in the table that I was updating, so I was kind of lost. Eventually, I discovered a required field in the table that I wasn't setting in the .add. When that was corrected the error message cleared. ...

Export truncates field of "memo" datatype.
Access 2000 SR-1 I've spent some time investigating this, and researching both here and on the MS support site. Briefly; Problem Description: ------------------------------- I have the following query which I am using to export data into CSV format; SELECT reference, referenceseq, raisedby, raisedfunction, raiseddate, investby, investfunction, investdate, len(description_rec) AS desc_len, description_rec AS description FROM [SELECT part1.Reference, part1.ReferenceSeq, part1.RaisedBy, part1.RaisedFunction, part1.RaisedDate, part1.InvestBy, part1.InvestFunction, part1.InvestDate, descrip...

"Too much text to edit" in a memo field!!!???
Hi folks, Hope someone has some insight to this problem. I have a form that contains a large (5" x 4") text box bound to a memo field. This field allows users to type in the text of an email to be sent through Outlook (via automation). I ONE user who send me his back end data base where he has inserted several two - three page emails. When scrolling through the records, two of about twenty records shows and error that says "Too much text to edit". This error occurs when the cursor is placed inside the text box that is connected to the memo field. After the error app...

cgiemail: unfilled "required" fields causes "400" error
Hi, I have a form working using cgiemail. It's at http://www.SethL.com/mda. It works just fine as long as I fill in all of the required fields. The problem is if I don't fill in any of the required fields, all I get is a "HTTP 400 - Bad Request" error page at the URL http://www.sethl.com/cgi-bin/cgiemail/mda/formmail.txt, which is my tempate document. What I would have expected to happen is some kind of error page indicating that not all required fields have been filled. Do I need to define that page or something? Any help greatly appreciated! Than...

"Too many Fields" error running query with <255 fields
I'm having a problem running a query. I get the "too many fields" error message, but I only have 162 fields in the query and I thought the limit was 255. The problem query (Query1) is based on query2 that pulls together records from two tables. Could the problem be that some of the fields in Query1 use expressions using (SELECT "[field1]", "query2","[field2]>3")? The rest of the fields in Query1 just use Avg. Thanks in advance, Ian As well as the 255-field limit, there's another limit in Access. All the fields need to fit into the buffer s...

Fields "Who" and "Subject" have disappeared
Hi, I received many messages like this one : ================================begin====================================== X-Original-To: jlc@av.nerim.net Delivered-To: jlc@av.nerim.net Received: from localhost (localhost [127.0.0.1]) by sargeras.nerim.net (Postfix) with ESMTP id 89986EE4421 for <jlc@av.nerim.net>; Fri, 17 Apr 2009 18:14:33 +0200 (CEST) X-DSPAM-Result: Whitelisted X-DSPAM-Confidence: 0.9831 X-DSPAM-Probability: 0.0000 X-DSPAM-Signature: 1577,49e8aae4427911573812339 X-Virus-Scanned: amavisd-new at nerim.net X-Spam-Flag: NO X-Spam-Score: 0.899 X-Spam-Statu...

Web resources about - "Record Deleted" error when appending to a memo field from a memo field - comp.databases.ms-access

Resources last updated: 1/25/2016 3:20:34 AM