Loop Through Each Field in a Recordset (1 Viewer)

[Pienuts]

Hi all, I am having difficulties working though this loop problem - for some reason, I am missing a vital concept!
BACKGROUND
I am trying to copy certain fields from one record to another using a command button on a form.
In the OnClick event, I set a SQL string as a DAO recordset and then created a SQL UPDATE to add the records.
Everything was working fine and dandy until I came across text or memo fields which contain apostrophes. These are breaking my SQL strings.
This leads us to my current dilemma.
PROBLEM
I need to loop through the fields in the recordset I had created and replace all apostrophes with double apostrophes (please let me know if that logic is false - I had read that was the answer).
Here is one of the... 15? versions I had tried to accomplish this:

Dim fld As Field
Do Until rs.EOF
   With rs
      .MoveLast
      .MoveFirst
      For Each fld In .Fields
         If fld.Type = dbText Or fld.Type = dbMemo Then
            rs.Edit
            fld.Value = Replace(fld.Value & "", "'", "''")
         End If
      Next fld
   End With
Loop

I thought I was looping through all the fields and replacing any apostrophes in text/memo fields. Because there is only ever going to be one record, the EOF should trip after the first time, but it does not.

If someone could let me know what I am doing wrong, it would be greatly appreciated!

 

[MarkK]

There are ways you can copy those fields without the quotes causing a problem. I would seek that solution rather than this one.

As far as your code goes, you never call the .MoveNext method, so you'll move last, then first and then you stay on that first record in an endless loop. You also never call .Update on the recordset, so you endlessly update the record, then you never save it.

Dim fld As Field
Do Until rs.EOF
   With rs
      .MoveLast
      .MoveFirst
      For Each fld In .Fields
         If fld.Type = dbText Or fld.Type = dbMemo Then
            rs.Edit
            fld.Value = Replace(fld.Value & "", "'", "''")
            [COLOR="Red"].Update[/COLOR]
         End If
      Next fld
[COLOR="Red"]      .MoveNext[/COLOR]
   End With
Loop

 

[pbaldy]

Well, the MoveLast/MoveFirst doesn't make sense. MoveNext right before Loop is normal. That said, I'd probably have accounted for the apostrophes in the original process.

 

[pbaldy]

Geez, I must have dozed off while typing.

 

[MarkK]

Lol, that's what usually happens to me!

 

[Pienuts]

Okay, first off - thanks to you two for your assistance!
MarkK, the changes you made work just fine, thanks for the correction! I will burn these details into my brain.
Now after looking at the tables, I’ve discovered that I was actually altering the original data, instead of a “virtual recordset” like I was thinking I had. Whoops. So now the original records have double apostrophes. I suppose I could loop back through and reverse the replace, but this is definitely not sounding efficient.
Maybe I am going about this the wrong way. Perhaps I’ll give you guys a bit more detail and see if there is something else.
I have an inspection form with two related tables – tblInspection and tblInspectionVeg
On the form, I need a “Same as previous Inspection” button which would:
1. Copy the values from 15 fields of a previous inspection (chosen in a pop-up form) in tblInspection into the current record being inspected.
2. Copy all fields (except PK/FK) from tblInspectionVeg into new records with the pk of the current record.
I hope that was brief but clear!
What would be your suggestion to accomplish this?
Thanks again, guys – I don’t know what I would do without you and this site!

 

[MarkK]

I would write an insert query in the query design grid that inserts a record into the same table it selects one from, so something like . . .

INSERT INTO SomeTable ( <field list> )
SELECT <field list> 
FROM SomeTable 
WHERE ID = <id of record to copy>

. . . and then supply id of the record to copy in code, run the query, and any quotes and/or double quotes in your data will not be an issue.

 

[Pienuts]

Okay, I'll give that a try and get back to you.

 

[Pienuts]

Quick question: because the inspection I would be on at the time would already exist in the database, can I still use an INSERT INTO statement? I thought that would add a new record into the table.
Would I instead need an UPDATE statement? And would that be the same syntax?

 

[MarkK]

Everything was working fine and dandy until I came across text or memo fields which contain apostrophes. These are breaking my SQL strings.

I'm addressing this point, and it doesn't matter if it's an insert or update or whatever. Your SQL will still work fine if you do all your data movement, either update or insert, in a single query. The query I posted is an example where there are no delimiters in the SQL, and in that case, data can be transferred between fields even if that data contains both single AND double quotes.

Does that make sense?

 

[Pienuts]

I do get it, I guess I just need to figure out a different syntax for the UPDATE statement. I had originally tried exactly what you had posted, and when I realized it needed to be an UPDATE statement, I didn't think I could translate the INSERT one. The statement I was using (and was being broken by the apostrophes) is:

UPDATE tblInspection SET Field1=" & rs!Field1 & ", Field2=" & rs!Field2..............WHERE PK = " & Forms!MyForm!PK

Which is what I thought I needed for the syntax. I guess that was my problem - I'll do some mucking about with it and see if I can't make it more of a direct update without the recordset middle-man.

 

[gemma-the-husky]

Geez, I must have dozed off while typing.

must be wino moderating mode.

 

[Pienuts]

Okay, I've got it now - thanks for the direction, MarkK.

In case anyone else is curious, I used an UPDATE statement which referenced the same table twice - I didn't realize that this was an option!
Code for the interested:

UPDATE tblInspection AS ti1, tblinspection AS ti2 
SET ti1.Field1 = ti2.Field1, ti1.Field2 = ti2.Field2, ti1.Field3 = ti2.Field3, ...... 
WHERE ti1.PK=" & CurrentInspectionPK & " AND ti2.PK=" & InspectionToCopyPK

This has no issues with punctuation, and will copy perfectly.

I used an INSERT INTO statement to sort out the associated vegetation table information:

INSERT INTO tblInspectionVeg ( Field1, Field2, Field3, FK,  ..... ) 
SELECT tblInspectionVeg.Field1, tblInspectionVeg.Field2, tblInspectionVeg.Field3, ..... & CurrentInspectionPK & " AS NewFK 
FROM tblInspectionVeg 
WHERE tblInspectionVeg.FK=" & InspectionToCopyPK

Thanks again for your help; sometimes I just need a little nudge in the right direction!

 

[pbaldy]

must be wino moderating mode.

Hey, I resemble that remark!

 

[MarkK]

Hey, nicely done all around!