Run-time error 3219: Invalid Operation

[GSMike]

In the following code (which I modified with the help of Rick Sprague)...when it hits "Msgbox fld.value" (green below), I get
Run-time error 3219.
Invalid operation.

===========================================================
Sub ParseDBField()
Dim db As Database, rst As Recordset, tdf As TableDef, x As Integer, y As String
Dim fld As Field
Set db = CurrentDb
Set tdf = db.TableDefs("ControlProperties")
Set fld = tdf.Fields("Tip")
Set rst = tdf.OpenRecordset(dbOpenDynaset)
x = 1

MsgBox fld.Value

Do Until rst.EOF
    rst.MoveFirst
    If asc_code(fld, x) >= 65 And asc_code(fld, x) <= 90 Or asc_code(fld, x) >= 97 And asc_code(fld, x) <= 122 Then
        y = y &amp; Mid(fld.Value, x, 1)
    Else
        y = y &amp; &quot; &quot;
    End If
Loop
End Sub
===========================================================
Public Function asc_code(fld As Field, x As Integer)
asc_code = Asc(Mid(fld.Value, x, 1))
End Function

Any suggestions?
I was getting the error when it hit the function originally, so I put the message box in there to try and isolate the problem. I guess the problem's with "fld.value".
Thank you for your help.
Mike Kemp

 

[RickSpr]

I didn't read your code carefully enough. The statement:
Set fld = tdf.Fields("Tip&quot
sets 'fld' to a Field object from the TableDef's Fields collection. Such a Field object has no Value property. It only contains properties that define the field, which are the properties you can see in Table Design view.

You need to delete the above statement. Then, in your loop that reads the recordset, you need to get the field from the recordset, like this:
Set fld = rst.Fields("Tip&quot

There's another problem. Your loop starts with:
rst.MoveFirst
Thus, every time you return to the top of the loop, you will move back to the first record in the recordset. You'll never hit EOF that way, so your loop will never terminate. You need to move the "rst.MoveFirst" before the loop, and put "rst.MoveNext" just before the Loop statement.

I took this as just experimental code, but now I'm wondering. What your ParseDBField procedure does is collect the first letter of the Tip field of each row into variable 'y' (replacing non-letters with spaces). But then it doesn't do anything with the resulting string, and variable 'y' disappears when the procedure ends. Is this just a code fragment? An experiment? Or do you intend it to do something useful just the way it is? Rick Sprague

 

[GSMike]

Hi Rick,
The value of this field contains some special characters; specifically, the carriage return (because the field gets its value from the ControlTipText of controls on a form). Eventually, this function is supposed to go through the field character by character, and when it finds a character that isn't in the alphabet, replace that character with a " " (space) or maybe a "," (comma).
In this way, the field will look something like:
Value1 Value2 Value3 Value4.
As it is now, it looks like:
Value1
And values 2, 3 and 4 are there, but they're not visible because of the carriage return character. You have to use the right arrow to bring up these values one by one.
Basically, then, I'm trying to filter out the carriage returns from the field value.
"Y" is the variable to hold what will be new value of the field "Tip". This value is built character by character, allowing only alphabetic letters to be part of the value.
michael.kemp@gs.com